Convert Access Query w/IIF to SQL Server View

I am brand new to SQL Server. I had to move my data tables from Access 2003
onto SQL Server 2000. I have just upsized my .mdb using the upsizing wizard
and, after some minor changes, have gotten everything to work.
Now, I'm trying to focus on speeding things up. I have several nested
queries, using many tables, with several IIF statements that are used just
for selecting data to be displayed on forms and reports. I attempted to
convert one into a view, but have discovered that views don't allow IIF
statements. Is there a better way to do this? Can you create a procedure tha
t
is linked to Access? How do you reference this in Access?(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||Thanks for the speedy response. Another question: If I create my view using
query analyzer, how do I save it as a query to link to Access?|||If you have a view in SQL Server, like:
CREATE VIEW dbo.MyView
AS
SELECT 1;
Then from Access you can just treat it like a table, e.g. SELECT * FROM
dbo.MyView instead of SELECT * FROM dbo.MyTable.
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:D6BF7042-48B7-4FC0-9EE2-D66026A461D7@.microsoft.com...
> Thanks for the speedy response. Another question: If I create my view
> using
> query analyzer, how do I save it as a query to link to Access?|||Sorry, I'm not following you.
Ok, using SQL Query Analyzer, I created my Select statement using the case
statements instead of IIF's, and parsing completed successfully. It runs and
selects the right information. Now what?
Do I have to save this somewhere special? Then what.|||You do not save it somewhere else. View is a server object, meaning it has
to be created on the SQL Server database (and saved there after creating
it).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:C8C7BE9D-70BD-4BB3-BCA5-7D2ABFD4C3D2@.microsoft.com...
> Sorry, I'm not following you.
> Ok, using SQL Query Analyzer, I created my Select statement using the case
> statements instead of IIF's, and parsing completed successfully. It runs
> and
> selects the right information. Now what?
> Do I have to save this somewhere special? Then what.|||Hello
You can use a CASE expression instead of the IIF, using one of these
syntaxes:
CASE
WHEN condition
THEN result_if_true
ELSE result_if_false
END
or:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
..
ELSE other_result
END
For more informations about CASE, see Books Online.
Razvan|||Use Query Analyzer(QA) to transfer ALL the old data from Access to SQL.
Copy it to tables - or better yet use DTS package to copy entire database.
If you are using a view to call the data for reports, use QA to test and
create a stored procedure with the test script. Call the stored procedure
from your reports.
If you want to use the QA script on occasion and not for any so-called
recurring reports, then save the script written in QA to any location (just
liek a text script) and open up the script when you want to run it and run i
t
at will.
"Holly" wrote:

> Thanks for the speedy response. Another question: If I create my view usin
g
> query analyzer, how do I save it as a query to link to Access?|||On Thu, 1 Jun 2006 11:26:01 -0700, Holly wrote:

>Sorry, I'm not following you.
>Ok, using SQL Query Analyzer, I created my Select statement using the case
>statements instead of IIF's, and parsing completed successfully. It runs an
d
>selects the right information. Now what?
>Do I have to save this somewhere special? Then what.
Hi Holly,
If you have a SELECT statement that returns the data you need, e.g.
SELECT au_fname FROM authors
than you can create a view by typing a CREATE VIEW statement before it
and executing the complete code:
CREATE VIEW Author_FirstNames
AS
SELECT au_fname FROM authors
Once this has executed successfullym you can use the Authro_FirstName
view just as you would use any regular table - and that includes
creating a linked table for it in Access.
Hugo Kornelis, SQL Server MVP

Convert Access query to SQL View

Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
SenthilkumarCheck out CONVERT() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:%234%232fJmJGHA.1388@.TK2MSFTNGP11.phx.gbl...
Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
Senthilkumar|||To add to Tom's reply, you might want to consider leaving off the
formatting in the view and just return the raw data. Let the client
format the result set. Any time you format the data on the server, the
formatting functions you use have to operate on every single row, one
at a time.
--Mary
On Tue, 31 Jan 2006 18:09:30 +0530, "Senthilkumar"
<kesk32@.yahoo.co.in> wrote:

>Hi,
>I have used an access table to create a chart report to show how many calls
>were handled in a month. How do i conver the Access sql to MS sql server
>view. Here is the access sql:
>SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
>FROM [Complaints]
>GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
> (Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
>ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
>In the MS Sql, the word FORMAT is not recognised. I have tried several ways
>like this w/o any luck. Can anyone tell me the correct syntax for the above
>sql querry.
>Thanks
>Senthilkumar
>

Convert Access Query to SQL Server View

SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
dCoveredUnenclosedGrossArea,
IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
Buildings.YearConstructed, Format$([NumberLevels],"00") AS
dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
dUnrelatedGrossArea, Buildings.YearLatestImprovement,
UCASE(Buildings.Address) AS Address, Buildings.CityCode,
CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
Format$([Mechanical],"0000000") AS dMechanical,
Format$([Custodial],"0000000") AS dCustodial
FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode
ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;

Please if anyone can help me in Converting the above given Access Query
to Sql Server. I don't know which function to use for format$, IIF. I
would really appreciate your suggestions.

Thanks,On 17 May 2006 10:02:37 -0700, s_wadhwa@.berkeley.edu wrote:

>SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
>UCASE(Buildings.BuildingName) AS BuildingName,
>Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
>Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
>Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
>dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
>dCoveredUnenclosedGrossArea,
>IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
>Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
>Buildings.YearConstructed, Format$([NumberLevels],"00") AS
>dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
>dUnrelatedGrossArea, Buildings.YearLatestImprovement,
>UCASE(Buildings.Address) AS Address, Buildings.CityCode,
>CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
>Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
>Format$([Mechanical],"0000000") AS dMechanical,
>Format$([Custodial],"0000000") AS dCustodial
>FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode
>ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
>
>Please if anyone can help me in Converting the above given Access Query
>to Sql Server. I don't know which function to use for format$, IIF. I
>would really appreciate your suggestions.
>Thanks,

Hi s_wadhwa,

Change doouble quotes to single quotes.

Use + instead of & for string concatenation.

Replace UCASE() with UPPER()

Replace IIf with CASE (look it up in Books Online)

My knowledge of Format$() is very llimited, but if (for isntance) the
function Format$([Custodial],"0000000") is intended to format Custodial
as a 7-digit numeric string with leading zerooes (e.g. 0001234), then
you can replace it with:
RIGHT (REPLICATE('0', 7) + STR(Custodial), 7)

Finally, remove the constant from the ORDER BY list. It serves no
purpose.

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks a lot for your quick response. the suggestions were really
helpful. Is there any book to refer or some online material to refer
to. I have lots of other doubts regarding converting Access queries to
SQL.

Thanks,
Shalini|||Hi,

If anyone has done the conversion of parameterized query in Access to
SQL Server. Please guide me how to solve this query and how to take
input in SQL query from Access forms.

PARAMETERS [[forms]!frmRooms![BuildingNumber]] Text ( 255 ),
[[forms]!frmRooms![ctlFloor]] Text ( 255 ),
[[forms]!frmRooms![DepartmentFilter]] Text ( 255 );
SELECT *
FROM Rooms
WHERE (((Rooms.BuildingNumber)=[forms]![frmRooms]![BuildingNumber]) AND
((Rooms.Floor) Like [forms]![frmRooms]![ctlFloor]) AND
((Rooms.DepartmentCode) Like
Mid$([forms]![frmRooms]![DepartmentFilter],1,4)) AND
((Rooms.RoomUseCode) Like [forms]![frmRooms]![RmCdFilter]))
ORDER BY Rooms.BuildingNumber, Rooms.RoomNumber;

thanks in advance for your help.
Shalini|||On 18 May 2006 12:59:23 -0700, s_wadhwa@.berkeley.edu wrote:

>Hi,
>If anyone has done the conversion of parameterized query in Access to
>SQL Server. Please guide me how to solve this query and how to take
>input in SQL query from Access forms.

Hi Shalini,

I see that your parameters refer to forms. Keep in mind that SQL Server
is not aware of the forms in yoour front-end - a big difference from
Access, which is DB and front-end lumped together in one application.

One possible way to convert this to SQL Server would be to create a
stored procedure:

CREATE PROC GoodNameGoesHere
(@.BuildingNumber varchar(255),
@.ctlFloor varchar(255),
@.DepartmentFilter varchar(255))
AS
SELECT Col1, Col2, Col3, ...-- Never use SELECT * in production code
FROM Rooms
WHERE BuildingNumber = @.BuildingNumber
AND "Floor" LIKE @.ctlFloor
AND DepartmentCode LIKE SUBSTRING(@.DepartmentFilter, 1, 4)
ORDER BY BuildingNumber, RoomNumber;

BTW, using a char datatype for a column named "Number" is highly
suspicious to me...

--
Hugo Kornelis, SQL Server MVP|||Hi there,

I have an Access query that I've been trying to convert to SQL Server
view and although I've converted most of the syntax from access to sql
I still can't make it work. I'm getting errors within GROUP BY and
HAVING clauses. Anyone has an idea? Thanks...

SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
GetDate()-O_DateOfAddress AS Expr3,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
Expr2,
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
FROM qry_offender_master LEFT JOIN tbl_state ON
qry_offender_master.O_State = tbl_state.State
GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
GetDate()-O_DateOfAddress,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
HAVING (((DateName(qq,O_DateofAddress) + '/' +
DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_Absconder)=0)) OR
(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_DateofAddress) Is Null) AND
((qry_offender_master.O_Absconder)=0))
ORDER BY qry_offender_master.O_LastName

Hugo Kornelis wrote:
> On 18 May 2006 12:59:23 -0700, s_wadhwa@.berkeley.edu wrote:
> >Hi,
> >If anyone has done the conversion of parameterized query in Access to
> >SQL Server. Please guide me how to solve this query and how to take
> >input in SQL query from Access forms.
> Hi Shalini,
> I see that your parameters refer to forms. Keep in mind that SQL Server
> is not aware of the forms in yoour front-end - a big difference from
> Access, which is DB and front-end lumped together in one application.
> One possible way to convert this to SQL Server would be to create a
> stored procedure:
> CREATE PROC GoodNameGoesHere
> (@.BuildingNumber varchar(255),
> @.ctlFloor varchar(255),
> @.DepartmentFilter varchar(255))
> AS
> SELECT Col1, Col2, Col3, ...-- Never use SELECT * in production code
> FROM Rooms
> WHERE BuildingNumber = @.BuildingNumber
> AND "Floor" LIKE @.ctlFloor
> AND DepartmentCode LIKE SUBSTRING(@.DepartmentFilter, 1, 4)
> ORDER BY BuildingNumber, RoomNumber;
> BTW, using a char datatype for a column named "Number" is highly
> suspicious to me...
> --
> Hugo Kornelis, SQL Server MVP|||Ok, I've changed "GetDate()-O_DateOfAddress" to DateDiff(day,
O_DateOfAddress, GetDate()) and the only thing left has to do with
group by clause. So, is there any way I can use expressions (Expr1,
Expr2...) in GROUP By clause because obviously this is possible in
access but not in sql server...I tried both putting "Group by Expr1,
Expr2" and "Group by DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate()) AS Expr1, GetDate()-O_DateOfAddress AS Expr3..."
but no success? Thanks in advance...|||thanks for guidance.
i resolved that issue.

shalini|||On 23 May 2006 05:51:47 -0700, lakimaki wrote:

>Hi there,
>I have an Access query that I've been trying to convert to SQL Server
>view and although I've converted most of the syntax from access to sql
>I still can't make it work. I'm getting errors within GROUP BY and
>HAVING clauses. Anyone has an idea? Thanks...
(snip)

Hi lakimaki,

There are several things in yoour query that I don't understand.

1. Why are you joining in the tblState table? Unless I am missing
something, you only use it to display the tblState.State column, which
is also the joining column. Why not display O_State and remove the join
to tblState?

2. Why do you need the GROUP BY clause? Since you include all columns in
the WHERE clause and since there are no aggregates used anywhere, this
is the same as just using DISTINCT to remove duplicates - and if your
table is well designed, there shouldn't be any duplicates. Just remove
the entire GROUP BY and change HAVING to WHERE.

3. The HAVING part looks strange too. It consists of two parts, combined
with OR. But the second part is an exact copy of the first part, with
only one extra requirement added. And that extra requirement has the
result that this second part can NEVER be true - since DateOfAddress can
never be both NULL and in the current quarter.

4. The test that DateOfAddress is in the current quarter is not done in
the most efficient way. Instead of using an expression to extract
quarter and year from this column, you shoould compare this column to
the start of the current and the next quarter - that way, an index on
DateOfAddress (if any exists) can be used to quickly narrow down the
amount of rows to process.

As far as I see, you could rewrite your query to:

SELECT DATENAME(qq, CURRENT_TIMESTAMP) + '/'
+ DATENAME(yy, CURRENT_TIMESTAMP) AS Expr1,
DATEDIFF(day, O_DateOfAddress, CURRENT_TIMESTAMP) AS Expr3,
DATENAME(qq, O_DateofAddress) + '/'
+ DATENAME(yy, O_DateofAddress) AS Expr2,
O_VerificationQuarter,
O_Probation,
O_active_case,
O_OutOfCounty,
O_deceased,
O_Sex_Probation,
O_DateofAddress,
O_Doc,
O_LastName,
O_FirstName,
O_MiddleName,
O_Address,
O_CityEntry,
O_State,
O_Zip,
O_Sector,
O_State,
Ca_WarrantIssued,
O_DateofBirth,
O_Absconder
FROM qry_offender_master
WHERE O_DateOfAddress >= DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000101')
AND O_DateOfAddress < DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000401')
AND O_VerificationQuarter = DATENAME(qq, CURRENT_TIMESTAMP)
AND O_Probation = 0
AND O_OutOfCounty = 0
AND O_deceased = 0
AND O_Sex_Probation = 0
AND O_Absconder = 0
ORDER BY O_LastName

(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)

>SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
>GetDate()-O_DateOfAddress AS Expr3,
>DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
>Expr2,
>qry_offender_master.O_VerificationQuarter,
>qry_offender_master.O_Probation,
>qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
>qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
>qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
>qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
>qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
>qry_offender_master.O_CityEntry, qry_offender_master.O_State,
>qry_offender_master.O_Zip, qry_offender_master.O_Sector,
>tbl_state.State,
>qry_offender_master.Ca_WarrantIssued,
>qry_offender_master.O_DateofBirth,
>qry_offender_master.O_Absconder
>FROM qry_offender_master LEFT JOIN tbl_state ON
>qry_offender_master.O_State = tbl_state.State
>GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
>GetDate()-O_DateOfAddress,
>DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
>qry_offender_master.O_VerificationQuarter,
>qry_offender_master.O_Probation,
>qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
>qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
>qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
>qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
>qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
>qry_offender_master.O_CityEntry, qry_offender_master.O_State,
>qry_offender_master.O_Zip, qry_offender_master.O_Sector,
>tbl_state.State,
>qry_offender_master.Ca_WarrantIssued,
>qry_offender_master.O_DateofBirth,
>qry_offender_master.O_Absconder
>HAVING (((DateName(qq,O_DateofAddress) + '/' +
>DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
>DateName(yy,GetDate())) AND
>((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
>AND ((qry_offender_master.O_Probation)=0) AND
>((qry_offender_master.O_OutOfCounty)=0) AND
>((qry_offender_master.O_deceased)=0) AND
>((qry_offender_master.O_Sex_Probation)=0) AND
>((qry_offender_master.O_Absconder)=0)) OR
>(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
>DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
>((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
>AND ((qry_offender_master.O_Probation)=0) AND
>((qry_offender_master.O_OutOfCounty)=0) AND
>((qry_offender_master.O_deceased)=0) AND
>((qry_offender_master.O_Sex_Probation)=0) AND
>((qry_offender_master.O_DateofAddress) Is Null) AND
>((qry_offender_master.O_Absconder)=0))
>ORDER BY qry_offender_master.O_LastName
>
>Hugo Kornelis wrote:
>> On 18 May 2006 12:59:23 -0700, s_wadhwa@.berkeley.edu wrote:
>>
>> >Hi,
>>> >If anyone has done the conversion of parameterized query in Access to
>> >SQL Server. Please guide me how to solve this query and how to take
>> >input in SQL query from Access forms.
>>
>> Hi Shalini,
>>
>> I see that your parameters refer to forms. Keep in mind that SQL Server
>> is not aware of the forms in yoour front-end - a big difference from
>> Access, which is DB and front-end lumped together in one application.
>>
>> One possible way to convert this to SQL Server would be to create a
>> stored procedure:
>>
>> CREATE PROC GoodNameGoesHere
>> (@.BuildingNumber varchar(255),
>> @.ctlFloor varchar(255),
>> @.DepartmentFilter varchar(255))
>> AS
>> SELECT Col1, Col2, Col3, ...-- Never use SELECT * in production code
>> FROM Rooms
>> WHERE BuildingNumber = @.BuildingNumber
>> AND "Floor" LIKE @.ctlFloor
>> AND DepartmentCode LIKE SUBSTRING(@.DepartmentFilter, 1, 4)
>> ORDER BY BuildingNumber, RoomNumber;
>>
>> BTW, using a char datatype for a column named "Number" is highly
>> suspicious to me...
>>
>> --
>> Hugo Kornelis, SQL Server MVP

--
Hugo Kornelis, SQL Server MVP

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

how to convert the access query above to sql server 2000

In SQL Server 2000 you have't have predefined operator to get the PIVOT table..

Here you have to manually write the query to get the pivot result...

(Example)

Code Snippet

Create Table #BikeSales
(
Year int,
Product Varchar(100),
Sales Int
)

Insert Into #BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into #BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into #BikeSales Values ('2007', 'HONDA F1', 7000)

Insert Into #BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into #BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into #BikeSales Values ('2007', 'HONDA IRL', 1000)

Insert Into #BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into #BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into #BikeSales Values ('2007', 'HONDA MotoGP', 132)

Insert Into #BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into #BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into #BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
[Main].Product
,Sum([2005].Sales) as [2005]
,Sum([2006].Sales) as [2006]
,Sum([2007].Sales) as [2007]
From (Select Distinct Product From #BikeSales) as [Main]
Left Outer Join (Select * From #BikeSales Where Year=2005) as [2005] On [2005].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2006) as [2006] On [2006].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2007) as [2007] On [2007].Product=[Main].Product
Group By [Main].Product

You can generate the above query dynamically using the following script..

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join (Select * From #BikeSales Where Year=?) as [?] On [?].Product=[Main].Product '
Select @.PreparedSelectQuery =',Sum([?].Sales) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From #BikeSales Group By Year

Exec ('Select [Main].Product' + @.SelectQuery + ' From (Select Distinct Product From #BikeSales) as [Main]' + @.JoinQuery + ' Group By [Main].Product')

|||

Using Manivannan's data, this method of creating a 'pivot' table in SQL 2005 is quite a bit more efficient. (Single Pass, No JOINS, NO Sub-Queries, No Dynamic SQL.)

Code Snippet

DECLARE @.BikeSales table
( [Year] int,
Product varchar(25),
Sales int
)

Insert Into @.BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into @.BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into @.BikeSales Values ('2007', 'HONDA F1', 7000)
Insert Into @.BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into @.BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into @.BikeSales Values ('2007', 'HONDA IRL', 1000)
Insert Into @.BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into @.BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into @.BikeSales Values ('2007', 'HONDA MotoGP', 132)
Insert Into @.BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into @.BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into @.BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
Product,
[2005] = sum( CASE [Year] WHEN 2005 THEN Sales END ),
[2006] = sum( CASE [Year] WHEN 2006 THEN Sales END ),
[2007] = sum( CASE [Year] WHEN 2007 THEN Sales END )
FROM @.BikeSales
GROUP BY Product
ORDER BY Product

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Conversion Problem

I want convert a mysql database in SQL Server 2000. SQL Server forecastes
the possibility to view graphically the relationship between all tables
containing in my database with relative structure by graphic.
About mysql i don't know as actes.
Thanks and i'm sorry for my probable encorrected english but i'm italian boyernix (@.*.it) writes:
> I want convert a mysql database in SQL Server 2000. SQL Server forecastes
> the possibility to view graphically the relationship between all tables
> containing in my database with relative structure by graphic.
> About mysql i don't know as actes.

I'm a little uncertain what you are really asking about. But it is true
that in Enterprise Manager includes a simple diagram feature. However,
this is not an integral part of SQL Server, and there are third power
modelling tools which are much more powerful in this regard. These tools
covers all major RDBMS's, although I don't know about how well MySQL is
supported. The major tools in this area are ErWin (Computer Associaties),
PowerDesigner (Sybase) and Embrocadero (I think their product is called
DataArtisan.) There are also freeware tools in this area, although I don't
have any names.

If you are considering a move only because of the graphic diagram in
Enterprise Manager, frankly, I don't consider this worth the effort.
There might be other and better reasons to make the change, but switching
from one DB engine to another is nothing you do lightly, because of
difference in language and mindset.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Conversation enpoints are not getting cleaned up on target end

Hi,

We are using service broker between two different instances. But were facing issues with increasing row count in conversation_endpoints view. We found that this was because we were using default value for lifetime for the conversation which is value of size int. Later on we changed the lifetime to 1 minute and conversation_endpoints view start getting cleaned up after 30 minutes

Following commands are used to send message

Before :

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

After:

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

But as we use default life time for a long due to which around 15 million records got acumlated in this view. What is the best way to clean up this view.

END Conversation @.handle with cleanup is taking so long is their any other way to do this

Thanks,

Prashant

Not sure if this is the total cause of your problem but ending a conversation before the first message is sent on it tends to leave it in an unstable state. Remus has a good explanation here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx|||

Thanks for information. But as of now i want to know what is the best way do cleanup on sys.conversation_endpoints with 14 million rows

|||

I see - not interested in doing it right but doing it wrong faster.

I assume you have a script that loads all the dialog handles into a cursor and then calls END DIAlOG WITH CLEANUP on each one. If you want your application to continue working while you are cleaning up then that's the only way. If you can shut down your applicationso that there are no dialogs or messages that you care about then an ALTER DATABASE command with the SET NEW_BROKER parameter will blow away all traces of any dialogs and messages.

|||

If you moved to SP2, you can do ALTER DATABASE ... SET NEW_BROKER, it will truncate every relevant internal table (conversation_endpoints, conversation_groups, transmission_queue and all message queues).

Do not attempt this with pre-SP2 SQL 20005 because it will literaly do 14 mil END CONVERSATION ... WITH CLEANUP in one transaction lasting forever.

Note that NEW_BROKER will nuke every conversation, including currently active ones. If you cannot afford this, then you must END conversations individually, if you batch commit it doesn't take that long actually.

|||

Thanks for the instant replies. Unfortunately we cannot shutdown the application here. So i think only option left is using WITH CLEANUP. But this might be a help in sometime in future. Thanks again for wonderful upport

|||In that case, be sure to use Remus' suggestion of batch commits. END a few hundred conversations and then commit the transaction. This is much more efficient than doing each END CONVERSATION in its own transaction.

Controls missing in toolbox

All in sudden all the Controls (Table, Crosstab, textbox...) disappeared from my Toolbox. I clicked View -> Toolbox and still it is empty.

Anyone has any idea what is going on..

Thanks.

Got it. I reset and got all back.

thanks.

Controlling Sa

hii
is it possible to control the sa login , so that he cannot view a specific table. is it possible
regds
ramsa has access to everything. why do you wish to prevent the sa from having access to a table?

Controlling create & drop proc, view privilege

Hi,
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiMani
You can EXECUTION permission on the stored procedure for the user
Also ,you can remove him/her from sysadmin fixed server role but he/she
should be member db_owner fixed database and must qualified User.sp
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:0C3FBF63-843E-465E-98C0-4BE9152BF08F@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
trap.
> since the user can create procedures with dbo as the owner, if the SP has
a
> drop table command, that would execute in the owners context and hence
would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||1. A user needs to be a member of db_owner or db_ddladmin
roles (or sysadmin) to create a objects owned by dbo.
Members of db_owner and db_ddladmin need to qualify the
owner as dbo.object when they create the objects to be owned
by dbo.
2. It depends first on ownership the ownership chain. If the
ownership chains are intact, the secuirty is checked for
permissions to execute the stored procedure only. If the
ownership chain is broken, permissions are checked on each
branch where the owner of the object is different. You can
find more information in books online under ownership chains
-Sue
On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
<Mani@.discussions.microsoft.com> wrote:

>Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
>db_datareader, to only create & drop stored procs and views in DevDB. What
>extra permissions does the user need ?
>I tried playing with the "grant create proc to user" command. But it lets
>the user create procs with him as owner. In the current case, the applicati
on
>needs all objects to be owned by dbo, so the user needs to be able to run
>"create proc dbo.tempProc as ..."
>In case there is a solution to the above, we might fall into the next trap.
>since the user can create procedures with dbo as the owner, if the SP has a
>drop table command, that would execute in the owners context and hence woul
d
>drop the table. Is that right ? I guess the question is when an SP is
>executed does it use the permissions of the owner of the SP or the user
>executing the SP|||Thanks Uri and Sue for your responses.
"Sue Hoegemeier" wrote:

> 1. A user needs to be a member of db_owner or db_ddladmin
> roles (or sysadmin) to create a objects owned by dbo.
> Members of db_owner and db_ddladmin need to qualify the
> owner as dbo.object when they create the objects to be owned
> by dbo.
> 2. It depends first on ownership the ownership chain. If the
> ownership chains are intact, the secuirty is checked for
> permissions to execute the stored procedure only. If the
> ownership chain is broken, permissions are checked on each
> branch where the owner of the object is different. You can
> find more information in books online under ownership chains
> -Sue
>
> On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
> <Mani@.discussions.microsoft.com> wrote:
>
>

Continuous blanks

Hi,
I need a column with title "Sales Revenue"(4 blanks between two words).
It is ok to be viewed in report designer, however, when view it in IE, it is
shown as "Sales Revenue", because IE will take continuous blanks as a single
blank.
Is there any way to keep the format?
Thanks,try
="Sales" & " " & "Revenue"
Greg
"Dai Zefeng" <daizif@.tom.com> wrote in message
news:OylxaxF$FHA.292@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need a column with title "Sales Revenue"(4 blanks between two words).
> It is ok to be viewed in report designer, however, when view it in IE, it
> is
> shown as "Sales Revenue", because IE will take continuous blanks as a
> single
> blank.
> Is there any way to keep the format?
> Thanks,
>

CONTAINSTABLE AND VIEW

Hi all,

is there any way so i can use the view in the containstable,

ie. select * from containstable (SOMEVIEW,SOME_COL_ON_VIEW,SEARCHCOND)

Thank you in advance.

Niranjan

Constainstable() is a fulltext function. As the name suggests, it's used against a table that has been fulltext indexed.

So, the answer is "NO".

Consuming webservices in a view

Hi
Is there a way in sql server 2005 or sql server 2000 to cosume a webservice
over a view?
The aim is to have a view which other appliactions can working with.
The data from the view should not come from a table, it should come per
realtime from a webservice.
thank's
Michel> Is there a way in sql server 2005 or sql server 2000 to cosume a
> webservice over a view?
In SQL 2005, I think you ought to be able to create a CLR function that you
can include in your view. I haven't personally done this, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:e28g7DFgIHA.4396@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way in sql server 2005 or sql server 2000 to cosume a
> webservice over a view?
> The aim is to have a view which other appliactions can working with.
> The data from the view should not come from a table, it should come per
> realtime from a webservice.
> thank's
> Michel
>

Construction of view or sp

I have the following tables:
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
S
Something like:
SELECT
ProjectID,
Account,
OrderAmt = isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account = a.Account GROUP BY Account ), 0 )
TransAmt = isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account = a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and how often you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 wasted keystrokes since they provide no additional value. (Make every keystroke useful.) You know it is a table because it follows the FROM keyword.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<staeri@.gmail.com> wrote in message news:1160455107.932890.296640@.m73g2000cwd.googlegr oups.com...
>I have the following tables:
> tblAccount:
> -Account
> tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
> tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
> tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
> I would like to show all accounts in tblAccount and if there are amount
> values on the accounts in the other tables they should be shown next to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
> Which is the best way to do this, a view or sp and with which syntax?
> Regards,
> S
>

Construction of view or sp

I have the following tables:
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
SThis is a multi-part message in MIME format.
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Something like:
SELECT
ProjectID,
Account,
OrderAmt =3D isnull(( SELECT sum( Amount ) FROM tblOrder WHERE =Account =3D a.Account GROUP BY Account ), 0 )
TransAmt =3D isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE =Account =3D a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and =how often you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 =wasted keystrokes since they provide no additional value. (Make every =keystroke useful.) You know it is a table because it follows the FROM =keyword.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<staeri@.gmail.com> wrote in message =news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...
>I have the following tables:
> > tblAccount:
> -Account
> > tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
> > tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
> > tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
> > I would like to show all accounts in tblAccount and if there are =amount
> values on the accounts in the other tables they should be shown next =to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
> > Which is the best way to do this, a view or sp and with which syntax?
> > Regards,
> > S
>
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Something like:
SELECT
=ProjectID,
=Account,
OrderAmt =3D =isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account =3D a.Account GROUP BY =Account ), 0 )
TransAmt =3D =isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account =3D a.Account GROUP BY =Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends =upon how you will use this, and how often you will use it.
On Another Note: [tbl] as a table =prefix is 'old school'. Actually 3 wasted keystrokes since they provide no additional =value. (Make every keystroke useful.) You know it is a table because it follows =the FROM keyword.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...>I =have the following tables:> > tblAccount:> -Account> => tblAmount:> -ProjectID> -Account> -Amount1> -Amount2> > tblOrder:> -OrderID> =-ProjectID> -Account> -Amount> > tblTransaction:> -TransactionID> -ProjectID> -Account> =-Amount> > I would like to show all accounts in tblAccount and if there =are amount> values on the accounts in the other tables they should be =shown next to> the account number. If there are no values in the other =tables the> account without value should still be shown.> => Which is the best way to do this, a view or sp and with which syntax?> => Regards,> > S>

---

--=_NextPart_000_1050_01C6EBFD.FFAA9360--

Construction of view or sp

I have the following tables:
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
SSomething like:
SELECT
ProjectID,
Account,
OrderAmt = isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account = a.Acc
ount GROUP BY Account ), 0 )
TransAmt = isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account =
a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and how o
ften you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 was
ted keystrokes since they provide no additional value. (Make every keystroke
useful.) You know it is a table because it follows the FROM keyword.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<staeri@.gmail.com> wrote in message news:1160455107.932890.296640@.m73g2000cwd.googlegroups.c
om...
>I have the following tables:
>
> tblAccount:
> -Account
>
> tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
>
> tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
>
> tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
>
> I would like to show all accounts in tblAccount and if there are amount
> values on the accounts in the other tables they should be shown next to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
>
> Which is the best way to do this, a view or sp and with which syntax?
>
> Regards,
>
> S
>

Constructing a View into time dependant data

1. I have a table with data like this:

PersonID
DateTime
Temperature
Pressure

2. I want to build a view into this table so that it shows up as follows:

PersonID DateTime1 DateTime2 DateTime3 .....
1 Pressure1 Pressure2 Pressure3 ......
1 Temperature1 Temperature2 Tempearture3 .....
2 :
:

how would I do this?

Hello,

Your resultset seems to contain and thus represent two disparate data sets (one of Pressure and one of Temperature). Why would you want to do this when there would be no way to determine what is pressure and what is temperature?

Regardless, you would use the new PIVOT operator, and if you did want to include two data sets, you would need to union the results of two separate pivots in your view. BOL has some good examples of using PIVOT.

Cheers,

Rob

|||Thanks for responding!

I will construct 2 separate views - one for temperarure and one for pressure.
But I am confused as to how to do it for even just temperature.

Is there a way of doing this without Pivot? I am using SQL 2000|||

Hello,

Have a look at http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx for starters. The actual solution will depend upon your data.

Cheers,

Rob

constraint view

Hi,

I want to know which table my foreign is reference to

for instance I have this statement

ALTER TABLE BANK ADD CONSTRAINT BANKING_R_154 FOREIGN KEY (
COM_CODE ,
ACCOUNT_CODE ) references BANK_GLT

I want execute ine select * from information_schema.I_don't_know

to get the table BANK_GLT from constraint BANKING_R_154

How can I do this?

cheers,

Alessandro

Select * from INFORMATION_SCHEMA.Referential_Constraints RC

INNER JOIN INFORMATION_SCHEMA.Constraint_Column_usage CCU

ON RC.Constraint_Catalog = CCU.Constraint_Catalog AND

RC.Constraint_Schema = CCU.Constraint_Schema AND

RC.Constraint_Name = CCU.Constraint_Name

Where Table_Name = 'SomeTable' --for a table, use Constraint_Name for querying on Constraint Names
--

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks,

but, I would like to know which table my tables is reference to and which columns as well

cheers,