[Stored Procedures] How do I calculate 3^1.2?

Hello,

I am working with SQL Server 2005 stored procedures.

How do I do this:
SET @.MyAnswer = 3^1.2
--i.e. 3 to the 1.2 power

Currently it will not allow it because"The data types int and numeric are incompatible in the boolean XOR operator."

~Le

I think you must use FLOAT to use all or most of the mathematical functions and cast the result to Numeric. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms177516.aspx

|||

Caddre:

I think you must use FLOAT to use all or most of the mathematical function and cast the result to Numeric. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms177516.aspx

Excellent! That put me on the right path.

The correct function is:POWER(x,y)

and the key wasx.

Power (1.0000, 1) will return 1.0000
Power (1.00, 1) will return 1.00

Thanks!

`Le

[SS2K5] : Populate a CTE from stored procedure result set

Hi,

I'd like to know if it's possible to populate/load a CTE from a stored procedure result set ?

Kind of :

USE MyDB;
GO
WITH MyCTE (Col1, Col2, Col3)
AS
(

EXECUTE myStoredProc

)
SELECT *
FROM MyCTE
GO

Thanks for your help.

Cheers,

Bertrand

A couple of options include loading your stored procedure output to a temp table:

INSERT NTO #aTempTable
EXEC yourStoredProcedure

and also converting your procedure into an function or perhaps a view and then you can join to the function or view. If the primary objective of the stored procedure is to be used as you describe it might be best from the outset to consider converting it into a function.

But also keep in mind that in many cases the temp table / stored procedure option will outperform the function.

|||

CTE in SQL Server 2005 is just syntactic sugar. It is similar to view in that the query expression of the CTE is parsed into the original query, compiled, optimized and executed. So there is no special optimization in terms of storing intermediate results in case of multiple references to the same CTE and so on. So you have few options:

1. Convert the SP to inline TVF - best performance

2. Convert the SP logic to a view

3. Convert the SP to non-inline TVF

4. Use a temporary table to store results from SP and reuse it

|||

Well in fact I have to perform a row by row operation on the sp result set. Which means using a cursor. And I know that using a cursor with a temporay table is not the best in term of performance so I thought that CTE was the best option...

But your first option could fits for my problem.

Thanks for your guidance.

|||What kind of row by row operation are you performing?|||

Well in fact I received a result set from one stored procedure. Each column in this result set become input parameters for an other stored proc I have to call.

It could be more "easy" if I had the right to modifiy the both of these stored proc but I'm not allowed to and I just can to plug my logic between these sp ....

[SS2K5] : Dynamically construct a FROM statement with current SCHEMA

Hi,

I've got a DB with some schemas.

There's same tables under these schemas as for example :

MySchema1.MyTable1

MySchema2.MyTable1

I wrote CRUD stored procedures for these tables.

I'd like to dynamically create the FROM statement inside the stored procedures by getting the schema name from the current context.

Kind of :

FROM SCHEMA_NAME().MyTable1

So I'll have one generic stored procedure for MyTable1.

This stored proc will be under the DB level and will not have to be replicate under all the schemas.

Is it possible ?

Thanks for any help.

BERTRANDR

Sure, sp_executesql is such a utility to run dynamic sql. Basically you construct a SQL string(NVARCHAR) with parameters first, then use sp_executesql to execute it. Look at Book Online for details.|||Why do you want to use a generic SP? Using dynamic SQL has lot of issues - complexity, performance (it depends on the case), security risks, permissions management, debugging pain, ownership chaining issues in your case etc. You should create a SP in each schema that handles the modifications for each table. This is much more robust, simpler to manage, debug, code etc.|||

Well my point is that I don t want to duplicate objects that have the same purposes and do exactly the same things for the same table but under differents schemas.

I thought it was the best thing to do .

But maybe am I wrong ..

[SQL Server Reporting] Converting a GUID back to its original contents

Hi,
I have an SQL Server Table in which one of the attributes is stored as
a GUID. The GUID actually represents the name of a SQL Server
Reporting Services report.
Once I retrive the GUID from the DB, I cant figure out how to get the
Report Name back from it.
I was wondering if anyone could point me to where the data for all the
GUIDs are stored in an SQL Server DB. Is it in tables? or are there
specific calls i need to make?
Any help would be great, been trying to figure this out for quite some
time now. :)
Thanks in advance.
Regards, LloydOn Jun 19, 11:25 am, lloyddsi...@.gmail.com wrote:
> Hi,
> I have an SQL Server Table in which one of the attributes is stored as
> a GUID. The GUID actually represents the name of a SQL Server
> Reporting Services report.
> Once I retrive the GUID from the DB, I cant figure out how to get the
> Report Name back from it.
> I was wondering if anyone could point me to where the data for all the
> GUIDs are stored in an SQL Server DB. Is it in tables? or are there
> specific calls i need to make?
> Any help would be great, been trying to figure this out for quite some
> time now. :)
> Thanks in advance.
> Regards, Lloyd
Hi,
I've got the answer from my post in the
microsoft.public.sqlserver.reportingsvcs group.
Thanks everyone.
Cheers, Lloyd|||On Jun 19, 1:24 pm, lloyddsi...@.gmail.com wrote:
> On Jun 19, 11:25 am, lloyddsi...@.gmail.com wrote:
>
> > Hi,
> > I have an SQL Server Table in which one of the attributes is stored as
> > a GUID. The GUID actually represents the name of a SQL Server
> > Reporting Services report.
> > Once I retrive the GUID from the DB, I cant figure out how to get the
> > Report Name back from it.
> > I was wondering if anyone could point me to where the data for all the
> > GUIDs are stored in an SQL Server DB. Is it in tables? or are there
> > specific calls i need to make?
> > Any help would be great, been trying to figure this out for quite some
> > time now. :)
> > Thanks in advance.
> > Regards, Lloyd
> Hi,
> I've got the answer from my post in the
> microsoft.public.sqlserver.reportingsvcs group.
> Thanks everyone.
> Cheers, Lloyd
Oops .. i meant the microsoft.public.sqlserver.programming group above
Cheers.

[sql server 2005] - debug a stored procedure

hello world,

how cani debug a stored procedure under sql server 2005?

I usually sprinkle PRINT statements before and after some logical operation to know whats happening in different sections. You can also check for @.@.ERROR after every T-SQL.|||If you are working with vs studio take a look at :How to debug stored procedures in Visual Studio .NET

[Shared Memory] ConnectionRead (recv()) ... error

Hi I've got error below when i ran a stored procedure:
----
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv())
General network error.
----
The same error occured in different places in each run.
The SP was running locally in Query Analyzer and there is
no network connection.
Anyone came across this before?
Cheers
FrankWhat operating system are you running?
Vikram
"frk" <frk_wang@.hotmail.com> wrote in message
news:1a80001c44e83$e5ea8210$a101280a@.phx
.gbl...
> Hi I've got error below when i ran a stored procedure:
> ----
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionRead (recv())
> General network error.
> ----
> The same error occured in different places in each run.
> The SP was running locally in Query Analyzer and there is
> no network connection.
> Anyone came across this before?
> Cheers
> Frank
>|||A couple of ideas.
1. Check the version of MDAC that you're running. Make sure it's patched
as well.
2. Disable Shared Memory using the SQL Client Network Utility. Then retest.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

[Shared Memory] ConnectionRead (recv()) ... error

Hi I've got error below when i ran a stored procedure:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv())
General network error.
The same error occured in different places in each run.
The SP was running locally in Query Analyzer and there is
no network connection.
Anyone came across this before?
Cheers
Frank
What operating system are you running?
Vikram
"frk" <frk_wang@.hotmail.com> wrote in message
news:1a80001c44e83$e5ea8210$a101280a@.phx.gbl...
> Hi I've got error below when i ran a stored procedure:
> ----
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionRead (recv())
> General network error.
> ----
> The same error occured in different places in each run.
> The SP was running locally in Query Analyzer and there is
> no network connection.
> Anyone came across this before?
> Cheers
> Frank
>
|||A couple of ideas.
1. Check the version of MDAC that you're running. Make sure it's patched
as well.
2. Disable Shared Memory using the SQL Client Network Utility. Then retest.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

[scheduling] SSIS Package fails, but scheduler reports success

I have an SSIS Package that executes a stored procedure. It turns out my stored procedure was missing.

However, the SQL Job Scheduler reported that step as success.

Can anyone tell me why?

`Le

Because your package is not exiting with an indication of success/failure.

Moved to the SSIS forum.

|||
when I run it through the command line, it indeed gives me an error code of (1).

But the Scheduler still tells me that it is successful.

`Le
|||You need to check the JOB action to take when a step fails -perhaps it is set to continue and report success.|||The step itself is trying to run a stored procedure that does not exist. The Sql schedule should report a failure, but it is not. It reports a success.

The Sql Server job says "quit with failure" when there is an error. There is clearly a different problem here.

`Le

|||

It is difficult to help you when you have all of the details and we don't. We can't see what you are doing and what happens, Your descriptions are incomplete and do not allow a good problem determination and resolution.

Please post the entire command line that you are having problems with, including any success/failure statements after it executes.

Is this being executed by the Windows Scheduler?

What version of SQL Server are you using?

|||

Arnie Rowland wrote:

It is difficult to help you when you have all of the details and we don't. We can't see what you are doing and what happens, Your descriptions are incomplete and do not allow a good problem determination and resolution.

Please post the entire command line that you are having problems with, including any success/failure statements after it executes.

Is this being executed by the Windows Scheduler?

What version of SQL Server are you using?

Thank you for your reply. I will attempt to be more detailed:

SQL SERVER: 2005
VISUAL STUDIO: 2005
SSIS File Name: Foo.dtsx
Configuration File Name: Config.dtsConfig

This config file has multiple steps, including one step that executes a Stored Procedure.

When I run this through a command line, I run it as:
dtexec.exe /FILE "\\MyServer\Foo.dtsx.dtsx" /CONFIGFILE "\\MyServer\Config.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

When it is run this way, process exits with error (1), because the Stored Procedure in question does not exist on the database. This is the correct error message.

When I run it through SQL SERVER 2005 SCHEDULER, I run it as a SSIS package, with the command line:
/FILE "\\MyServer\Foo.dtsx.dtsx" /CONFIGFILE "\\MyServer\Config.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

However, the package always exits with success.

|||if a task inside a SSIS Package fails doesn't mean that the whole package fails
and the sql agent step would only report an error if the whole package fails

so you could set the property 'Fail Package on failure' to TRUE ... in the task where you execute this stored procedure.

- paul
|||When I run the package via command line (dtexec.exe), I do get fail message:

DTExec: The package execution returned DTSER_FAILURE(1)

~Le

[ODBC SQL Server Driver][TCP/IP Sockets] ConnectionRead (recv())

Hi,
I have developed socket on VB using wsock32.dll, which connects to the data from AS400. This data is then stored in SQL server. MTS is uset to handle the dll objects.

During the process of insertion system returns an error
[ODBC SQL Server Driver][TCP/IP Sockets] ConnectionRead (recv())
[ODBC SQL Server Driver][TCP/IP Sockets] General Network Error

After this error, system does not connect to SQL server and to AS400, until the MS DTC service on MTS is stopped and restarted.

Please help on this.

Note:
I have introduced a trace in each ster to analyze at what point error is happening. I found out that system is not giving problem while opening a connection to SQL server, it only gives an error while inserting the records in SQL server. I think, there might be problem with the locking machanism.

I will appericiate your helps, mean while I try to solve the problem related to locking.

regardsIs there some reason that you didn't want to use DTS packages for this? DTS does quite well connecting to our AS/400 (though it took a bit of configuration).

Regards,

Hugh Scott

Originally posted by attinder
Hi,
I have developed socket on VB using wsock32.dll, which connects to the data from AS400. This data is then stored in SQL server. MTS is uset to handle the dll objects.

During the process of insertion system returns an error
[ODBC SQL Server Driver][TCP/IP Sockets] ConnectionRead (recv())
[ODBC SQL Server Driver][TCP/IP Sockets] General Network Error

After this error, system does not connect to SQL server and to AS400, until the MS DTC service on MTS is stopped and restarted.

Please help on this.

Note:
I have introduced a trace in each ster to analyze at what point error is happening. I found out that system is not giving problem while opening a connection to SQL server, it only gives an error while inserting the records in SQL server. I think, there might be problem with the locking machanism.

I will appericiate your helps, mean while I try to solve the problem related to locking.

regards|||We might have used DTS but we are using the same socket with different applications. They may or may not use SQL server|||Hi Hugh,

You indicated that connecting to AS 400 requires quite a bit of configuration. Can you please elaborate on this point? I need it urgently.

Thanks
Originally posted by hmscott
Is there some reason that you didn't want to use DTS packages for this? DTS does quite well connecting to our AS/400 (though it took a bit of configuration).

Regards,

Hugh Scott|||Okay, here is what I remember:

1. We used the IBM Client Access Express drivers. We are using V5R1M0 with SP SI02795. The SP is critical, since there is a bug in the original release which won't let you connect to a database w/ more than 9 characters in the name (thanks IBM!).

2. The main hassle is that the driver always prompts you for a user name/password unless you config it appropriately. The prompt is not visible when the DTS package runs as a job (since it appears on the virtual desktop of the SQL Agent service account).

If that doesn't make sense to you, stop. Read up on SQL Service accounts and make sure that SQL Agent is set up to run under a service account.

3. Okay, proceeding ahead:

a. The SQL Agent account must be set up with 10 characters or less (SQLAdmin is okay, SQLAgentAccount is not).
b. Set up an account on the AS/400 with the same name as the SQL Agent account.
c. Set the password for both accounts to be identical (and again, no more than 10 characters)

4. Use the ODBC Administration GUI to set up a new connection. Specify that the connection should use the Windows user name/account information. It is helpful to do this set up while logged in as the SQL Agent Service account. It's not critical, but it is helpful.

Some additional notes:
1. We are using MS W2K SP4 (Advanced)
2. We are using MS SQL 2K (SP3a + MS03-031)
3. To verifiy that you have everything working correctly, log in to the server as the SQL Service account. Open a DTS package. Create a connection object for the AS/400. Create a destination connection object. Add a data pump task. When you specify the details of the data pump task, you should never be prompted for the password.

Alternatively: A friend of mine has had good success with the Hit Software OLE DB drivers for AS/400. I have never used them/don't know anything about them. But they may be worth trying. www.hitsw.com.

HTH,

hmscott

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure

I am new to SQL Server and stored procedures and here is my problem.

I am stepping into a stored procedure ‘storedProced1’ using T-SQL debugger. ‘storedProced1’ is calling ‘storedProced2’. When I step into the following line where storedProced2’ is being executed,

exec storedProced2 @.Parameter1, @. Parameter2, @. Parameter3

I got the following message in the immediate window.

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'storedProced2’.

@.RETURN_VALUE = N/A

But “storedProced2” does exists. What am I doing wrong?

Make sure that the procedure exists within the actual database and make sure that you are using the right owner declaration for this, this calling the procedure with the Routine_Schema and Routine_Name from the following query.

SELECT Routine_schema, ROutine_name
From INFORMATION_SCHEMA.Routines
WHere Routine_Name = 'storedProced2'

--> Calling EXEC <SchemaName>.<Objectname>

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

[bug?] Hidden parameters

Hello,
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.
archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.
|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:

> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>
>
|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...[vbcol=seagreen]
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:
> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> > SQL BOL says that the @.terminate parameter for sp_change_primary_role
> > defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> > cases, this param "Specifies that all pending transactions be immediately
> > rolled back" and puts the db in single user mode while the stored proc
> > runs.
> >
> > I'm wondering why each of these has it different default value. I presume
> > that in both cases a value of 1 = true, meaning that it would do the
> > rollback. Am I correct in assuming this? In order to help me understand
> > this
> > better, I hope someone could tell me why I would use the default values,
> > and
> > in what cirucumstance I might not use the default values.
>
>|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:
>> archuleta37,
>> Your installation of the SQL Server 2000 Books Online needs to be
>> updated.
>> There were documentation problems with these procedures. See:
>> http://support.microsoft.com/kb/291160 Terminate Default
>> http://support.microsoft.com/kb/298093 Job Id
>> These KBs also have a link to a corrected version of the BOL:
>> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
>> RLF
>> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
>> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>> > SQL BOL says that the @.terminate parameter for sp_change_primary_role
>> > defaults to 0 while it defaults to 1 in sp_change_secondary_role. In
>> > both
>> > cases, this param "Specifies that all pending transactions be
>> > immediately
>> > rolled back" and puts the db in single user mode while the stored proc
>> > runs.
>> >
>> > I'm wondering why each of these has it different default value. I
>> > presume
>> > that in both cases a value of 1 = true, meaning that it would do the
>> > rollback. Am I correct in assuming this? In order to help me understand
>> > this
>> > better, I hope someone could tell me why I would use the default
>> > values,
>> > and
>> > in what cirucumstance I might not use the default values.
>>

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/...ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/...&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:

> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/...ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>
>|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...[vbcol=seagreen]
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/...&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:
>

@return_status from SQLCLR stored procedure

I'm trying to write a VB CLR stored procedure that will set the return statu
s
to 0 or 1 depending on whether the CLR was successful or not. How do I do
that? Basically I want to use the following T-SQL code to call my CLR and
set the @.return_status
declare @.return_status int
EXEC @.return_status=My_CLR_SP
select @.return_statusI would like to know how to do this as well. Can anyone help us?
Greg Larsen wrote:
> I'm trying to write a VB CLR stored procedure that will set the return sta
tus
> to 0 or 1 depending on whether the CLR was successful or not. How do I do
> that? Basically I want to use the following T-SQL code to call my CLR and
> set the @.return_status
> declare @.return_status int
> EXEC @.return_status=My_CLR_SP
> select @.return_status|||Greg,
I found this at http://msdn2.microsoft.com/en-us/library/ms131094.aspx.
I tried it out and it works great.
Here is a really simple example:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function TestProc() As Int32
TestProc = 1
End Function
And the test script:
DECLARE @.result AS int
EXEC @.result = TestProc
PRINT @.result
Tom
Greg Larsen wrote:
> I'm trying to write a VB CLR stored procedure that will set the return sta
tus
> to 0 or 1 depending on whether the CLR was successful or not. How do I do
> that? Basically I want to use the following T-SQL code to call my CLR and
> set the @.return_status
> declare @.return_status int
> EXEC @.return_status=My_CLR_SP
> select @.return_status|||Thank you for the VB example.
I got mine to working by writing it in C # and doing the following:
[Microsoft.SqlServer.Server.SqlProcedure]
public static int TestProc()
{ return=1;}|||You're welcome. Thanks for posting the C# example.
Tom
Greg Larsen wrote:
> Thank you for the VB example.
> I got mine to working by writing it in C # and doing the following:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static int TestProc()
> { return=1;}

@query error

ODBC error 4604 (42000) There is no such user or group 'sa8'

this is the error message I get when I run my stored procedure that has the @.query parameter and I have been told this is a bug within sql that microsoft doesn't seem to be aware off.

Are you coming across this and what do you do to go around it?

RegardsHi:

I have found this "solution". Please check if you can use it ;)

We had a similar problem, the way we got around it was to add access rights to the TEMP directory in the Document and Settings folder on the account logged into the Server. As we had the NT Administrator account permanatley logged onto the server. If I tried to run a temp query from xp_sendmail it would fail, but if I used the administraor account it worked okay. The query writes the output file it needs to to the temp folder of which we had no access rights. Once I changed the access rights it worked okay. Did find this answer in one of the many SQL forums on the net. Hope this help you.|||I hope I can be more helper ;)

@original and stored procedures

When using @.original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this :

<

UpdateParameters><asp:ParameterType="String"Name="ProductType"/><asp:ParameterType="String"Name="OpportunityType"/><asp:ParameterType="Double"Name="Value"/><asp:ParameterType="String"Name="Probability"/><asp:ParameterType="Int32"Name="OpportunityID"/></UpdateParameters>

And my stored procedure like this :

CREATE procedure dbo.UpdateOpportunity

(
@.ProductType int,
@.OpportunityType int,
@.Value money,
@.Probability int,
@.OpportunityID int
)

as

update Opportunity
set ProductType = @.ProductType, OpportunityType = @.OpportunityType,
[Value] = @.Value, Probability = @.Probability
where OpportunityID = @.OpportunityID

Do I need to change both cases of @.OpportunityID in my stored procedure to @.original_OpportunityID for it to work?

Most likely. It also depends on what you have set for the conflictdetection property. If it's compareallvalues, then you'll need to accept @.original_ for each of the columns that you selected via your select statement.

@NumericVal IS NOT NULL seems not to return a boolean.

I'm trying to ensure that only of the parameters is passed to my stored procedure.

BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.

CREATE PROC sp_OneParm(
@.NumericVal float = null,
@.StringVal nvarchar(200) = null,
@.DateVal datetime = null,
@.BitVal bit = null)
AS
DECLARE @.ValCount tinyint
SET @.ValCount = 0

-- Ensure we've only got one update value specified
IF @.NumericVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.StringVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.DateVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.BitVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.ValCount > 1 RAISERROR ('Only one @.*Val paramater may be specified when calling sp_OneParm()', 16, 1)

-- Other Stuff
GO

Am I missing something simple or do I need to restructure my code to achieve the logic I want?Sure :)

IF @.NumericVal IS NOT NULL SET @.ValCount = @.ValCount + 1|||Bugger. :o
Thank-you roac.

@@RowCount output param

The following stored procedure sets a value for the @.@.RowCount global variable.

How do I make use of it in the Data Access Layer?

When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @.@.RowCount to a Label.Text

What should I do?

ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime@.selectDatedatetime ,@.selectCountryInt ASSELECT DISTINCT confignameFROM ModelRequestsJOIN CC_host.dbo.usr_cmcAs t2ON t2.user_id = ModelRequests.usernameJOIN CountriesONCountries.Country_Short = t2.countryWHERE RequestDateTime >= @.selectDateand RequestDateTime <dateadd(dd,1, @.selectDate)AND configname <>''AND interfacenameLIKE'%DOWNLOAD%'AND result = 0AND Country_ID = @.selectCountryORDER BY confignameSELECT@.@.RowCountAs numberRowsGO

try this:

declare @.MyRowCounter int
select @.MyRowCounter = @.@.RowCount as numberRows

|||

This will work as written, but what you get back is an answer set with 1 row and 1 column, you would use a datareader to read it.

A perhaps better -- or at least clearer -- way is to store the number in an output variable. Then, when you set up the call to execute the proc you declare the parameter direction as OUTPUT (this link has an example --http://www.msdner.com/forum/thread445973.html)

ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@.selectDatedatetime
,@.selectCountryInt

,@.numberRows int OUTPUT
AS

SELECT DISTINCT confignameFROM ModelRequestsJOIN
CC_host.dbo.usr_cmcAs t2ON
t2.user_id = ModelRequests.usernameJOIN
CountriesON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDateand RequestDateTime <dateadd(dd,1, @.selectDate)
AND configname <>''AND interfacenameLIKE'%DOWNLOAD%'AND result = 0AND Country_ID = @.selectCountry
ORDER BY configname
SELECT @.numberRows=@.@.rowcount

@@Rowcount or output parameters error

I have the following stored procedure working with an Access 2000 front end. The output parameters returned to Access are both Null when the record is successfully updated (ie when @.@.Rowcount = 1), but the correct parameters are returned when the update fails. I'm a bit new to using output parameters, but I have them working perfectly with an insert sproc, and they look basically the same. What bonehead error have I made here? The fact that the record is updated indicates to me that the Commit Trans line is being executed, so why aren't the 2 output parameters set?

TIA

EDIT: Solved, sort of. I found that dropping the "@.ResNum +" from "@.ResNum + ' Updated'" resolved the problem (@.ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.

CREATE PROCEDURE [procResUpdate]

Various input parameters here,

@.RetCode as int Output, @.RetResNum as nvarchar(15) Output

AS

Declare @.RowCounter int

Begin Tran

UPDATE tblReservations
SET Various set statements here, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified

SELECT @.RowCounter = @.@.ROWCOUNT

If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = @.ResNum + ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = 'Update Failed'
End
GOPost the complete DDL for the proc (including @.ResNum) and a sample of calling it that produces the problem you describe.

Regards,

hmscott|||Here's the complete sproc (I added back the offending part in red):

CREATE PROCEDURE [procResUpdate]

@.ReqDate as datetime, @.PassName as nvarchar(25), @.DispDate as datetime, @.PassPhone as nvarchar(25),
@.PassQuant as smallint, @.AuthBy as nvarchar(25), @.AcctID as smallint, @.PuLandmark as smallint, @.PuStreet as nvarchar(50),
@.PuCity as smallint, @.PuXStreet as nvarchar(50), @.PuPoint as nvarchar(50), @.DestLandmark as smallint, @.DestStreet as nvarchar(50),
@.DestCity as smallint, @.DestXStreet as nvarchar(50), @.DestPoint as nvarchar(50), @.Operator as smallint, @.Comments as nvarchar(250),
@.DriverReq as nvarchar(25), @.CarType as smallint, @.EstHours as money, @.EstPrice as money, @.CCType as nvarchar(25), @.CCnum as nvarchar(25),
@.CCAuthNum as nvarchar(25), @.CarQuant as tinyint, @.ResNum as int, @.Status as smallint, @.LastModified as tinyint,

@.RetCode as int = Null Output, @.RetResNum as nvarchar(15) = Null Output

AS

Declare @.RowCounter int

Begin Tran

UPDATE tblReservations
SET ReqDateTime = @.ReqDate, PassName = @.PassName, DispDateTime = @.DispDate, PassPhone = @.PassPhone, PassQuant = @.PassQuant, AuthBy = @.AuthBy, AcctID = @.AcctID,
PuLandmarkID = @.PuLandmark, PuStreet = @.PuStreet, PuCityID = @.PuCity, PuXStreet = @.PuXStreet, PuPoint = @.PuPoint, DestLandmarkID = @.DestLandmark, DestStreet = @.DestStreet,
DestCityID = @.DestCity, DestXStreet = @.DestXStreet, DestPoint = @.DestPoint, OperatorID = @.Operator, Comments = @.Comments, DriverReq = @.DriverReq, CarType = @.CarType,
EstHours = @.EstHours, EstPrice = @.EstPrice, CCType = @.CCType, CCNum = @.CCnum, CCAuthNum = @.CCAuthNum, CarQuant = @.CarQuant, Status = @.Status, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified

SELECT @.RowCounter = @.@.ROWCOUNT

If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = @.ResNum + ' Update Failed'
End
GO


and the VBA that calls it. The 2 highlighted lines will be null if the "@.ResNum + " is included. Otherwise, it runs fine.

Public Sub cmdUpdate_Click()
Dim strSQL As String
Dim strField As String
Dim fOK As Integer
Dim intCount As Integer
Dim x As Integer
Dim strMsg As String
Dim cmd As ADODB.Command
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim dteReqDate As Date
Dim dteDispDate As Date

On Error GoTo ErrorHandler

dteReqDate = Me.txtReqDate & " " & Me.txtReqTime
dteDispDate = DateAdd("n", -Nz(Me.txtMinutesAhead, 0), dteReqDate)

Call EstablishConnection
Set db = CurrentDb()
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
Set tdf = db.TableDefs("tblReservations_Local")
rst.ActiveConnection = objConn

With cmd
.ActiveConnection = objConn
.CommandText = "procResUpdate"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@.ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
.Parameters.Append .CreateParameter("@.PassName", adVarChar, adParamInput, 25, Me.txtPassName)
.Parameters.Append .CreateParameter("@.DispDate", adDBTimeStamp, adParamInput, , dteDispDate)
.Parameters.Append .CreateParameter("@.PassPhone", adVarChar, adParamInput, 25, Me.txtPassPhone)
.Parameters.Append .CreateParameter("@.PassQuant", adInteger, adParamInput, , Me.txtPassQuant)
.Parameters.Append .CreateParameter("@.AuthBy", adVarChar, adParamInput, 25, Me.txtAuthBy)
.Parameters.Append .CreateParameter("@.AcctID", adInteger, adParamInput, , Me.cboAcctNum)
.Parameters.Append .CreateParameter("@.PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
.Parameters.Append .CreateParameter("@.PuStreet", adVarChar, adParamInput, 50, Me.txtPuAddress)
.Parameters.Append .CreateParameter("@.PuCity", adInteger, adParamInput, , Me.cboPuCity)
.Parameters.Append .CreateParameter("@.PuXStreet", adVarChar, adParamInput, 50, Me.txtPuXStreet)
.Parameters.Append .CreateParameter("@.PuPoint", adVarChar, adParamInput, 50, Me.txtPuPoint)
.Parameters.Append .CreateParameter("@.DestLandmark", adInteger, adParamInput, , Me.cboDestLandmark)
.Parameters.Append .CreateParameter("@.DestStreet", adVarChar, adParamInput, 50, Me.txtDestAddress)
.Parameters.Append .CreateParameter("@.DestCity", adInteger, adParamInput, , Me.cboDestCity)
.Parameters.Append .CreateParameter("@.DestXStreet", adVarChar, adParamInput, 50, Me.txtDestXStreet)
.Parameters.Append .CreateParameter("@.DestPoint", adVarChar, adParamInput, 50, Me.txtDestPoint)
.Parameters.Append .CreateParameter("@.Operator", adInteger, adParamInput, , Me.txtOperator)
.Parameters.Append .CreateParameter("@.Comments", adVarChar, adParamInput, 250, Me.txtComments)
.Parameters.Append .CreateParameter("@.DriverReq", adVarChar, adParamInput, 25, Me.txtDriverReq)
.Parameters.Append .CreateParameter("@.CarType", adInteger, adParamInput, , Me.cboCarType)
.Parameters.Append .CreateParameter("@.EstHours", adCurrency, adParamInput, , Me.txtEstHours)
.Parameters.Append .CreateParameter("@.EstPrice", adCurrency, adParamInput, , Me.txtEstPrice)
.Parameters.Append .CreateParameter("@.CCType", adVarChar, adParamInput, 25, Me.cboCCType)
.Parameters.Append .CreateParameter("@.CCnum", adVarChar, adParamInput, 25, Me.txtCCNum)
.Parameters.Append .CreateParameter("@.CCAuthNum", adVarChar, adParamInput, 25, Me.txtCCAuthNum)
.Parameters.Append .CreateParameter("@.CarQuant", adInteger, adParamInput, , Me.txtCarQuant)
.Parameters.Append .CreateParameter("@.ResNum", adInteger, adParamInput, , Me.txtResNum)
.Parameters.Append .CreateParameter("@.Status", adInteger, adParamInput, , Me.cboStatus)
.Parameters.Append .CreateParameter("@.LastModified", adInteger, adParamInput, , Me.txtLastModified)

.Parameters.Append .CreateParameter("@.RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@.RetResNum", adVarChar, adParamOutput, 15)

.Execute

End With

fOK = cmd.Parameters("@.RetCode")
strMsg = cmd.Parameters("@.RetResNum")|||Update for anyone interested. It seems that there was a type mismatch occurring. @.ResNum is a numeric value (data type int), and apparently in this line that caused a problem:

Select @.RetResNum = @.ResNum + ' Update Failed'

I assumed it would concatenate the two values. If I create a new varchar variable and populate it with the value of @.ResNum (converted to varchar) and use that, everything works fine. What threw me was that not only was that return parameter null, but the other one was too.