T-SQL Dynamic Stored procedure for SQL2K5 Errors again!

Question

T-SQL Dynamic Stored procedure for SQL2K5 Errors again!

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

jon.hall 50545 Say Hey Kid Points: 669 More actions · Answer 1

To answer your question John - it works fine in VB. But ideally I wanted to turn the code to a Stored Procedure.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

OK, so you need to turn it into a stored procedure. You need to use dynamic SQL to build the query, a bit like you were doing in the thread I helped you with earlier this week. (Yes, I tried to persuade you not to use dynamic SQL, but here it's probably the right thing to do.) Instead of using VB to build your query string, you use T-SQL. The link I posted in that other thread should have enough examples to get you started.

Good luck

John

jcb SSCertifiable Points: 6574 More actions · Answer 3

Use the SQL Server profile (in the SSMS 2008 -> tools)

It can capture any command sent to the server (you ill need to learn how to use it, but that's no hard)

Put the profiler to run just before running your code (use a breakpoint in your VS).

Caught whatever the application is sending to the server and post it here.

And tell your boss old developer to stop to use dynamic SQL!

PS: I'm a old boss developer.

jon.hall 50545 Say Hey Kid Points: 669 More actions · Answer 4

Sorry JCB - do you have time to do a quick run through of how I link SQL Server profiler to visual studio 2010?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

You don't. You link it to SQL Server at the same time that the web page is running. That way you can capture the SQL that is being executed on SQL Server. What it won't do is tell you how to build the SQL statement that is being executed.

John

jon.hall 50545 Say Hey Kid Points: 669 More actions · Answer 6

jon.hall 50545

Say Hey Kid

Points: 669

February 3, 2012 at 7:17 am

#1442414

What do I put in the events section?

jon.hall 50545 Say Hey Kid Points: 669 More actions · Answer 7

I only want to run the profiler for a section of the VB code - is that possible?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Profiler will capture (the whole of) every statement executed on the server, subject to any filter placed on the trace.

http://technet.microsoft.com/en-us/library/ms175047.aspx

jon.hall 50545 Say Hey Kid Points: 669 More actions · Answer 9

I still don't know what to put in the events section - how embarrasing! 🙁

ashutosh.kumarpandey Old Hand Points: 341 More actions · Answer 10

Try creating CLR stored procedure in .Net and then execute it through SSMS......

jcb SSCertifiable Points: 6574 More actions · Answer 11

In your case it ill (presumably) fall in the TSQL:SQL batch completed, but its (probably) marked as default.

If you are conding in visual studio, just put a break point before the execute command.

put the profile to run, it ill capture commands sent to the server.

hit F10 in the vs to execute the command.

go to profile and see what it caught, then you can stop profile or its ill continuous capture anything.