Problem returning data

I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:

ALTER PROCEDURE

dbo.usp_CalcDeliveryCharge

@.mintDistance

int,

@.mintCustomer_ID

int= 0,

@.mintRate

int= 0OUTPUT,

@.mstrZone

nchar(10) =null OUTPUT

AS

/* SET NOCOUNT ON */SELECT@.mintRate=RATE,

@.mstrZone=ZONE

FROMtblRatesWHERECustomer_ID=@.mintCustomer_IDANDMile_Range_Min <= @.mintDistanceANDMile_Range_Max >= @.mintDistance

And this is my code:

sql_Command.CommandText =

"usp_CalcDeliveryCharge"

sql_Command.CommandType = CommandType.StoredProcedure

sql_Command.Parameters.Clear()

sql_Command.Parameters.AddWithValue(

"@.mintDistance", intApproxMiles)

sql_Command.Parameters.AddWithValue(

"@.mintCustomer_ID", Profile.CompanyID)

sql_Conn.Open()

sql_Reader = sql_Command.ExecuteReader()

While (sql_Reader.Read())Me.lblZone.Text = sql_Reader.Item(0).ToStringEndWhile

sql_Conn.Close()

sql_Reader.Close()

sql_Command.Dispose()

If you are using OUTPUT parameters you need to add the output parameters in the asp.net code also and set their direction as output and retrieve the values through those parameters not through datareader.
checkthis article if it helps.

Problem passing variables as parameters to extended stored procedure

Hello all,

I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:

C++ Extended Stored Procedure:

(Basically all this code is doing is retrieving the parameters and printing them back out)

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '\0';

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '\0';

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '\0';

sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);

Calling the XP with literal values:

EXEC xp_mytest 'one','two','three'

Output:

Params received by xp: one, two, three

Calling XP via a stored procedure:

create procedure sp_mytest

(

@.myvar1 nvarchar(200),

@.myvar2 nvarchar(50),

@.myvar3 nvarchar(50)

)

as BEGIN

PRINT @.myvar1

PRINT @.myvar2

PRINT @.myvar3

EXEC xp_mytest @.myvar1, @.myvar2, @.myvar3

END

EXEC sp_mytest 'one','two','three'

Output:

one

two

three

Params received by xp: o,t,t

Any insight or assistance is greatly appreciated!!!

Your definition of sp_mytest implies that its input parameters are not unicode. The first 0 character implies end-of-string. You pass unicode parameters to it from within sp_mytest (you declare them as nvarchar), and these characters are 2-byte with second byte zero, that's why you get back only the first character from the string.

Either change your sp_mytest definition to use varchar instead of nvarchar, or your xp_mytest definition to use wchar instead of char.

Problem passing variables as parameters to extended stored procedure

Hello all,

I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:

C++ Extended Stored Procedure:

(Basically all this code is doing is retrieving the parameters and printing them back out)

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '\0';

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '\0';

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '\0';

sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);

Calling the XP with literal values:

EXECxp_mytest 'one','two','three'

Output:

Params received by xp: one, two, three

Calling XP via a stored procedure:

create procedure sp_mytest

(

@.myvar1 nvarchar(200),

@.myvar2 nvarchar(50),

@.myvar3 nvarchar(50)

)

as BEGIN

PRINT @.myvar1

PRINT @.myvar2

PRINT @.myvar3

EXECxp_mytest @.myvar1, @.myvar2, @.myvar3

END

EXEC sp_mytest 'one','two','three'

Output:

one

two

three

Params received by xp: o,t,t

Any insight or assistance is greatly appreciated!!!

Your definition of sp_mytest implies that its input parameters are not unicode. The first 0 character implies end-of-string. You pass unicode parameters to it from within sp_mytest (you declare them as nvarchar), and these characters are 2-byte with second byte zero, that's why you get back only the first character from the string.

Either change your sp_mytest definition to use varchar instead of nvarchar, or your xp_mytest definition to use wchar instead of char.

Problem on ## table

Hi
I have a stored procedure , wherein i create a ## table and get values in
it, i have 3 more procedures which i call from the original procedure which
use the ##table.
When i execute the stored procedure for first time i get the desired
results. when i re-run it again it says the ## table exists . so when i
delete the ## table from tempdb and then re-run it again it works.
Is there a way to delete this ## table right in the begining of execution of
the stored procedure. when i try drop table ## it says the table does not
exists for the first time of execution, if i put the code if (exist whis
table = ##) even this does not work, if i say delte from tempdb.dbo.## even
this does not work
any suggestionTry,
if object_id('tempdb..##t') is not null
drop table ##t
...
AMB
"Rodger" wrote:

> Hi
> I have a stored procedure , wherein i create a ## table and get values in
> it, i have 3 more procedures which i call from the original procedure whic
h
> use the ##table.
> When i execute the stored procedure for first time i get the desired
> results. when i re-run it again it says the ## table exists . so when i
> delete the ## table from tempdb and then re-run it again it works.
> Is there a way to delete this ## table right in the begining of execution
of
> the stored procedure. when i try drop table ## it says the table does not
> exists for the first time of execution, if i put the code if (exist whis
> table = ##) even this does not work, if i say delte from tempdb.dbo.## eve
n
> this does not work
> any suggestion|||It sounds like you should use a local temp table rather than a global
temp table. Specify #tablename rather than ##tablename. Put the DROP
statement at the END of your code that uses the table so that it cleans
up there rather than at the beginning.
David Portas
SQL Server MVP
--|||1) If the stored Procedure is not running multiple instances concurrently,
(i,e., two processes calling it at the same time or overlappiing) Then why
don't you delete it at the end, when the stored Proc is done?
Drop Table ##TableName
or
2) Make it a permanent table and just delete all therecords in it a tteh
beginning (And maybe also at the end) of the stored proc.
or, if the SPs DO run concurrently, you could
3) change the data structure of the temp table so that records being
inserted can be identified as to which instance of the SP they were created
from, (Add an "Instance" or "RunNo" Column) and then modify all other
statements in the SP to affect only those records,
"Rodger" wrote:

> Hi
> I have a stored procedure , wherein i create a ## table and get values in
> it, i have 3 more procedures which i call from the original procedure whic
h
> use the ##table.
> When i execute the stored procedure for first time i get the desired
> results. when i re-run it again it says the ## table exists . so when i
> delete the ## table from tempdb and then re-run it again it works.
> Is there a way to delete this ## table right in the begining of execution
of
> the stored procedure. when i try drop table ## it says the table does not
> exists for the first time of execution, if i put the code if (exist whis
> table = ##) even this does not work, if i say delte from tempdb.dbo.## eve
n
> this does not work
> any suggestion|||You might want to consider writing a single procedure that replaces
this chain of calls. I have found that temp tables can often be
replaced with VIEWs, derived tables or CTEs.
Programming with temp tables in SQL Server is usually a sign that the
programmer is mimicking a procedural file system model of data in which
each of a series of procedural steps is written to a temp table (aka
"Scratch tape") and processed sequentially.

Problem looping DTS and BCP within a Stored procedure

All,

This is the scenerio

SP=Stored Procedure

I call SP1 which calls a DTS package.

DTS package calls SP2 and SP2 calls SP3 and SP 4 using an IF condition

SP3 has 3 BCP OUT commands after which it calls SP1 again

Now, the problem is that if I follow all the steps, then only 1 of the BCP command executes and the control exits out of SP3 and returns control to SP1.

If I ignore SP1 and DTS and run SP2 independently, then all BCP steps in SP3 are executed and control returns to SP2

I ma not sure if using DTS and BCP in SQL stored procedures might cause any problems

Appreciate your helpIt seems like your DTS is executing under a different security context than when you execute your SP3 from QA. Are you checking for errors after each BCP in your SP3? If you're calling your BCP using xp_cmdshell, - consider doing INSERT @.temp_tbl exec master.dbo.xp_cmdshell [....].

Problem install SQL Server Desktop Engine

I'm in the process of attempting to install Microsoft SQL Server Desktop Engine on my computer in support of an email program call Mailloop6. I've tried going through their tech support but they haven't been able to figure it out.

I'm running Windows XP with SP1 upgrade on a DSL connection.

When I installed the program the first time, I got the following error:

"Setup failed to configure the server. Refer to the server error logs and
setup error logs for more information."

The program obviously wouldn't install. I contacted the vendor and they sent a version with a batch file. Same result. I removed all the appropriate registry entries between installs and removed all the data files as well.

I've tried installing the program using the free MDSE download from the MS site with the following result:

"A Strong SA password is required for security reasons. Please use SAPWD switch to supply the same. Refer to readme for more details. Setup will now exit."

Here's the error log that I got after attempting to install:

2004-02-25 19:31:42.55 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 1)

2004-02-25 19:31:42.55 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-02-25 19:31:42.55 server All rights reserved.
2004-02-25 19:31:42.55 server Server Process ID is 3404.
2004-02-25 19:31:42.55 server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL$MAILLOOP6LOGERRORLOG'.
2004-02-25 19:31:42.56 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-02-25 19:31:42.60 server SQL Server configured for thread mode processing.
2004-02-25 19:31:42.60 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-02-25 19:31:42.63 spid3 Warning ******************
2004-02-25 19:31:42.63 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-02-25 19:31:42.63 spid3 Starting up database 'master'.
2004-02-25 19:31:42.91 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-02-25 19:31:42.94 spid5 Starting up database 'model'.
2004-02-25 19:31:43.24 server SQL server listening on 152.163.0.0: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 172.16.1.33: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 127.0.0.1: 4113.
2004-02-25 19:31:43.25 spid3 Server name is 'MONSTERMAILLOOP6'.
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 5
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 6
2004-02-25 19:31:43.25 spid3 Starting up database 'msdb'.
2004-02-25 19:31:43.56 spid5 Clearing tempdb database.
2004-02-25 19:31:44.13 spid5 Starting up database 'tempdb'.
2004-02-25 19:31:44.17 spid3 Recovery complete.
2004-02-25 19:31:44.17 spid3 SQL global counter collection task is created.
2004-02-25 19:31:44.19 spid3 Warning: override, autoexec procedures skipped.
2004-02-25 19:31:47.75 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-02-25 19:31:47.75 server SQL Server is ready for client connections
2004-02-25 19:31:57.97 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

Any thoughts on this would be very much appreciated!Originally posted by betteru
I'm in the process of attempting to install Microsoft SQL Server Desktop Engine on my computer in support of an email program call Mailloop6. I've tried going through their tech support but they haven't been able to figure it out.

I'm running Windows XP with SP1 upgrade on a DSL connection.

When I installed the program the first time, I got the following error:

"Setup failed to configure the server. Refer to the server error logs and
setup error logs for more information."

The program obviously wouldn't install. I contacted the vendor and they sent a version with a batch file. Same result. I removed all the appropriate registry entries between installs and removed all the data files as well.

I've tried installing the program using the free MDSE download from the MS site with the following result:

"A Strong SA password is required for security reasons. Please use SAPWD switch to supply the same. Refer to readme for more details. Setup will now exit."

Here's the error log that I got after attempting to install:

2004-02-25 19:31:42.55 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 1)

2004-02-25 19:31:42.55 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-02-25 19:31:42.55 server All rights reserved.
2004-02-25 19:31:42.55 server Server Process ID is 3404.
2004-02-25 19:31:42.55 server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL$MAILLOOP6LOGERRORLOG'.
2004-02-25 19:31:42.56 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-02-25 19:31:42.60 server SQL Server configured for thread mode processing.
2004-02-25 19:31:42.60 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-02-25 19:31:42.63 spid3 Warning ******************
2004-02-25 19:31:42.63 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-02-25 19:31:42.63 spid3 Starting up database 'master'.
2004-02-25 19:31:42.91 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-02-25 19:31:42.94 spid5 Starting up database 'model'.
2004-02-25 19:31:43.24 server SQL server listening on 152.163.0.0: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 172.16.1.33: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 127.0.0.1: 4113.
2004-02-25 19:31:43.25 spid3 Server name is 'MONSTERMAILLOOP6'.
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 5
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 6
2004-02-25 19:31:43.25 spid3 Starting up database 'msdb'.
2004-02-25 19:31:43.56 spid5 Clearing tempdb database.
2004-02-25 19:31:44.13 spid5 Starting up database 'tempdb'.
2004-02-25 19:31:44.17 spid3 Recovery complete.
2004-02-25 19:31:44.17 spid3 SQL global counter collection task is created.
2004-02-25 19:31:44.19 spid3 Warning: override, autoexec procedures skipped.
2004-02-25 19:31:47.75 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-02-25 19:31:47.75 server SQL Server is ready for client connections
2004-02-25 19:31:57.97 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

Any thoughts on this would be very much appreciated!

with the desktop edition the setup does not work in windows! you must exit to dos and on the directory where you've installed msde you must write:
setup.exe /qb+ instancename= something sapwd=your password|||I did not know that - the tech support for the software didn't mention a single thing about exiting to DOS. I will definitely give that a try.

Thanks!