Getting a stored procedures return value -- URGENT !

We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>

getting a store procedure's result

Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.c om...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
|||Use output parameters for SQL procedure from which you wish to assign the
values to the variables. (More information look for: "Returning Data Using a
Return Code" in SQL books online)
HTH
Saleem@.sqlnt.com
"Silvio" wrote:

> Hey guys,
> I have a store procedure that returns a recorset. Here's an example:
> create procedure ABC as
> --some code here that works with @.x and @.y.. and then the last line
> of the proc:
> SELECT @.x,@.y
> That procedure has been used only in a vb code, so they consume the
> result with no problem. Now I need to call that procedure within a
> different proc, and I need to get back the final values of @.x and @.y.
> Is there a way to get these results back in a variable as I call the
> store proc?
> Thanks,
>

getting a store procedure's result

Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,

Getting a return value from a Stored Procedure

Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...

DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst

The SP that you are calling should contain an OUTPUT parameter.
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards

|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst

Getting a file name from Query Analyzer

Hi all,

im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?

dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).

create table #filelist
(
files varchar(500)
)

truncate table #filelist

insert #filelist
EXEC xp_cmdshell 'dir c:*.* /b'

select top 1 * from #filelist

|||

This might help out:

http://stevekass.com/blog/wp-content/Folders/sql/TextDriver.htm

You can use TOP 1 to get just one file name.

Steve Kass

Drew University

http://www.stevekass.com

YoungEngineer@.discussions.microsoft.com wrote:

> Hi all,

>

> im trying to write a stored procedure that will basically browse a

> folder and get me the first file that it sees. Is there any way that I

> can do this in TSQL or using CLR in C#? I was thinking something along

> the lines of using the dos dir command and triyng to pipe it into a

> variable, not sure how to go about doing this. Any suggestions?

>

> dir /b ...gives me the bare file names, but it lists all the files in

> the folder, any way that i can just get the first file ( i dont really

> care what file).

>

>

>

>

Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Hello!
I have a stored procedure that takes a file name as a parameter and
imports the data from there into the appropriate table using the
correct format file. I would like to save the number of rows inserted
at the end of my stored procedure. Since the file name is variable I
use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
generated.
Since some of these files are extremely large I have set the BATCHSIZE
for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
show the number of rows inserted in the last batch. Since the table
name is variable I don't have an easy way of getting the count(*) from
it.
Any suggestions?
Thanks!
-Tom.Aardvark (tom_hummel@.hotmail.com) writes:
> I have a stored procedure that takes a file name as a parameter and
> imports the data from there into the appropriate table using the
> correct format file. I would like to save the number of rows inserted
> at the end of my stored procedure. Since the file name is variable I
> use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
> generated.
> Since some of these files are extremely large I have set the BATCHSIZE
> for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
> show the number of rows inserted in the last batch. Since the table
> name is variable I don't have an easy way of getting the count(*) from
> it.
> Any suggestions?
You could run SELKCT COUNT(*) on the table before and after, but that's
of course ont very appealing on a large table. (The fact that the
table is dynamic should not be a problem. You do know sp_executesql,
don't you? Else read
http://www.sommarskog.se/dynamic_sq...#sp_executesql.
The other option would be to have the client to catch those rows
affected messages for each batch committed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> You could run SELKCT COUNT(*) on the table before and after, but that's
> of course ont very appealing on a large table. (The fact that the
> table is dynamic should not be a problem. You do know sp_executesql,
> don't you? Else read
> http://www.sommarskog.se/dynamic_sq...#sp_executesql.
> The other option would be to have the client to catch those rows
> affected messages for each batch committed.
Thanks for the advice Erland. I had forgotten that sp_executesql
allowed for output parameters, so that should give me what I need using
SELECT COUNT(*). Not the most elegant way and it will suffer a
performance hit since some of the tables can be quite large, but it's a
process that runs for up to 12 hours once a month, so a couple minutes
to get a count from a table shouldn't be a problem.
Thanks!
-Tom.

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

sql

GetString in SP

Hi,
Alternate of Recordset.GetString() in Stored Procedure !If you explain what it does or what you want to accomplish, you make it a lo
t easier for us...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:%23USo8WKRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> Alternate of Recordset.GetString() in Stored Procedure !
>|||Hi
Sounds like you may be wanting to use cursors, in which case you may be
looking at it the wrong way as set based solutions are usually the quickest.
Check out books online which has a wealth of information and examples. The
latest version is downloadable at
http://www.microsoft.com/sql/techin.../2000/books.asp
John
"DMP" wrote:

> Hi,
> Alternate of Recordset.GetString() in Stored Procedure !
>
>sql

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.sql

GetExecutionInfo

Can we use GetExecutionInfo from a stored procedure?
What information does GetExecutionInfo provide?On Oct 9, 6:23 pm, BI_Guy <BI...@.discussions.microsoft.com> wrote:
> Can we use GetExecutionInfo from a stored procedure?
> What information does GetExecutionInfo provide?
These links might give a little better explanation.
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.getexecutioninfo.aspx
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
As far as I know, you cannot reference the information from a stored
procedure; however, there's an off chance that you might be able to
reference it from a CLR stored procedure though. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

GetDate() in Constraint using user's system time, Stored Proc using Server's.

All,

I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().

I performed the following test:

1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.

2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.

*note: These servers are on separate domains and therefore could have different system times.

This causes my select stored procedure to NOT return the values I just entered.

Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?

If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||

Todd:

Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.

Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.

sql

GETDATE() Hangs periodically

We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
Mark
Did you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Mark

GETDATE() Hangs periodically

We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
MarkDid you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Marksql

GETDATE() Hangs periodically

We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
MarkDid you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx
.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Mark

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

Get Value to Return to VB.Net

I have the following stored procedure:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
@.TCoName char(50),
@.TCoAddress char(50),
etc.
@.UserID int OUTPUT,
@.TCoID int OUTPUT
AS
INSERT INTO tblLogin
VALUES
(
@.UserName,
@.Password,
@.EmailAddress
)
Declare @.Ident int
Select @.UserID = @.@.IDENTITY
Select @.Ident = @.UserID
INSERT INTO tblTCompany
VALUES
(
@.Ident,
@.TCoName,
@.TCoAddress,
etc.....
)
Declare @.Ident2 int
Select @.TCoID = @.@.IDENTITY
Select @.Ident2 = @.TCoID
I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I get
the two applications to "talk" to each other?
Any suggestions will be greatly appreciated!
Sandy> I need to grab the @.Ident2 value into VB.Net (for a Web App).
You have a couple of options. One method is to return the value as a result
set:
SELECT @.Ident2
Another technique is to return the value as an OUTPUT parameter:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
etc.,
@.Ident2 OUT
AS
...
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>I have the following stored procedure:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> @.TCoName char(50),
> @.TCoAddress char(50),
> etc.
> @.UserID int OUTPUT,
> @.TCoID int OUTPUT
> AS
> INSERT INTO tblLogin
> VALUES
> (
> @.UserName,
> @.Password,
> @.EmailAddress
> )
> Declare @.Ident int
> Select @.UserID = @.@.IDENTITY
> Select @.Ident = @.UserID
> INSERT INTO tblTCompany
> VALUES
> (
> @.Ident,
> @.TCoName,
> @.TCoAddress,
> etc.....
> )
> Declare @.Ident2 int
> Select @.TCoID = @.@.IDENTITY
> Select @.Ident2 = @.TCoID
> I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I
> get
> the two applications to "talk" to each other?
> Any suggestions will be greatly appreciated!
> Sandy|||Hi Dan -
Thanks for your reply. How do you refer to the value in VB.Net, though?
What's the VB code you would write?
Sandy
"Dan Guzman" wrote:

> You have a couple of options. One method is to return the value as a resu
lt
> set:
> SELECT @.Ident2
> Another technique is to return the value as an OUTPUT parameter:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> etc.,
> @.Ident2 OUT
> AS
> ...
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>
>|||Sandy,
Here's my table definition
CREATE TABLE dbo.Report (
ReportID int IDENTITY (1, 1) NOT NULL ,
Descr varchar (50) NOT NULL ,
ReportName varchar (50) NOT NULL
)
And the associated SP. Notice the @.ReportID as OUTPUT
and the SET @.ReportID as the last statement. That
returns the identity value in the OUTPUT parameter.
---
CREATE PROCEDURE dbo.usp_Report_Ins
@.Descr varchar(50),
@.ReportName varchar(50),
@.ReportID int OUTPUT
AS
INSERT INTO dbo.Report (
Descr,
ReportName
)
VALUES (
@.Descr,
@.ReportName
)
SET @.ReportID = SCOPE_IDENTITY()
This is a snippet of the Insert code. I use the the
Microsoft Data Access Application Block to do the
SQL stuff (SqlHelper statements) to update the DB.
Use whatever code works there. The last statement
retrieves the value of the identity field.
---
Dim params() As SqlParameter = New SqlParameter(2) {}
params(0) = New SqlParameter("@.Descr", Reports.Descr)
params(1) = New SqlParameter("@.ReportName", Reports.ReportName)
params(2) = New SqlParameter("@.ReportID", Reports.ReportID)
params(2).Direction = ParameterDirection.Output
SqlHelperParameterCache.CacheParameterSet(ConnectionSettings.cnString,
_
"usp_Report_Ins", params)
Dim result As Integer =
SqlHelper.ExecuteNonQuery(ConnectionSettings.cnString, _
CommandType.StoredProcedure, "usp_Report_Ins",
params)
dim PrimaryKey as Integer = CInt(params(2).Value)
On Thu, 10 Feb 2005 07:09:02 -0800, "Sandy"
<Sandy@.discussions.microsoft.com> wrote:
>Hi Dan -
>Thanks for your reply. How do you refer to the value in VB.Net, though?
>What's the VB code you would write?
>Sandy
>
>"Dan Guzman" wrote:
>|||larzeb's example shows how you can get a parameter output value. To
retrieve a value returned as a single-row single-column result, you can use
a number of methods, such as SqlCommand.ExecuteScalar,
SqlCommandExecuteReader or SqlDataAdapter.Fill.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:EBDC9678-1001-4809-A0C5-A524115190B0@.microsoft.com...
> Hi Dan -
> Thanks for your reply. How do you refer to the value in VB.Net, though?
> What's the VB code you would write?
> Sandy
>
> "Dan Guzman" wrote:
>