Converting query to stored procedure

[tani1978]

Hello Friends,
I converted an Access 97 database to Access 2003 and then I put Access in frontend and SQL Server in the backend.Now some of my reports are not working.Here is one of these queries.How should I convert this query to stored procedure so that reports should work properly.

TRANSFORM First([Messung_Ottensen].[NO3_N Nitratstickstoff]) AS [SummeVonMessung_Ottensen_NO2_N Nitritstickstoff] SELECT     (Format([Dat], 
 [dd mmm yyyy])) 
 AS Ausdr1
 FROM Messung1
 WHERE     (((Messung1.Dat)  > #9 / 1 / 2003 #) AND 
((Messung1.Messung_Ottensen.Projekt)
= [ottensen]) AND((Messung1.Hangwasseranalyse)
= No))
GROUP BY (Year([Dat]) * 12 + Month([Dat]) - 1), 
Messung1.Dat, (Format([Dat], [dd mmm yyyy])), 
Messung1.Hangwasseranalyse
PIVOT Messung1.Messung_Ottensen.Projekt;

 

[TheQuestioner]

Your going to have to re-write it so that it's compatible with SQL Server syntax.

You can re-write it as a stored procedure and use one of the date format functions (check out this tutotial for further info) within TSQL to get your date formatting.

Also SQL Server understands dates as

'mm/dd/yyy'

- not

#dd/mm/yyyy#

as Access does.

Once you've got it working you can set the recordsource on your report to the stored procedure.

BTW SQL Server Views are not the exact equivalent of Access Queries, Views cannot contain an Order By clause (this is true for SQL Server 7 and below).

 

[SQLDenis]

TheQuestioner,

views can contain an order by clause in SQL server
The trick is to include top 100 percent

USE pubs
create view AuthorsView as
select top 100 percent *
from authors
order by au_lname

select * from AuthorsView

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[TheQuestioner]

SQLDenis, seems like your right mate. Is there a peformance penality on using this method?

 

[SQLDenis]

No I compaired the execution plans from the view and ordering directly from the table and they look identical (bookmark lookup and index scan)


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[tani1978]

I am not very good in SQL server so what is the way out now for this query converting to stored procedure or to a View.If I understood right the only thing wrong with this query is the date format?
Can you people put here your converted version of this query?Would also be very helpful.Thanks

 

[tani1978]

By the way I was getting this error while opening reports " Identifier beginning with ([Messung_Ottensen].[NO3_N Nitratstickstoff]) AS [SummeVonMessung_Ottensen_NO2_N Nitritstickstoff] SELECT (Format([Dat], [dd mmm yyyy]))
AS Ausdr1 FROM Messung1
is too long." The maximum length is 128

 

[fredericofonseca]

you have been asked on another thread to search

http://www.deja.com

for threads referring to this particular type of problem (e.g. converting a TRANSFORM ... PIVOT Access construct to SQL.

Can you please refer us to what you have searched and what you have tried to code so far based on the examples you have found from the above search?

Bear in mind that the conversion will be different depending exactly on what each TRANSFORM is trying to accomplish.


And no your problem is not the date format (although it may be on the final converted code).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com