how to schedule job to run vbscript file

Hi All,
I wrote a vbscript file which copies one table records to another database table. I wrote error handling to send an email if error occurs when copying records from one table to another. I want to schedule a sql job and give this file to execute. Could anyone plz tell me how to schedule sql job which runs vbscript file. i selected ActiveXScript and choose vbscript and gave path. I'm not understanding what the command should be given to run vbscript file. The result of giving path is job is failing.
Any help would be appreciated...plzzzzzzz
thanks
carolThe way I've used the ActiveXScript option in the past is to physciallypaste the VBScript code into the Command textbox. AFAIK you can'ttype in a path.

How to run windows DOS command in SQL?

I want to check to see if any error files from SQL uploads are present in a
folder.
I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
How can I check a windows directory for *.err files in SQL syntax? osql?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
There is an 'undocumented' function that will do that for you
[xp_fileexist]. See this article:
Functions -UnDocumented Check to Verify File Existence
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jsheldon via droptable.com" <u2880@.uwe> wrote in message
news:6909a192e8dbf@.uwe...
>I want to check to see if any error files from SQL uploads are present in a
> folder.
> I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
>
> How can I check a windows directory for *.err files in SQL syntax? osql?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
sql

How to run windows DOS command in SQL?

I want to check to see if any error files from SQL uploads are present in a
folder.
I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
How can I check a windows directory for *.err files in SQL syntax? osql?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1There is an 'undocumented' function that will do that for you
[xp_fileexist]. See this article:
Functions -UnDocumented Check to Verify File Existence
http://www.sqlservercentral.com/col...xpfileexist.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jsheldon via droptable.com" <u2880@.uwe> wrote in message
news:6909a192e8dbf@.uwe...
>I want to check to see if any error files from SQL uploads are present in a
> folder.
> I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
>
> How can I check a windows directory for *.err files in SQL syntax? osql?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

How to run windows DOS command in SQL?

I want to check to see if any error files from SQL uploads are present in a
folder.
I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
How can I check a windows directory for *.err files in SQL syntax? osql?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1There is an 'undocumented' function that will do that for you
[xp_fileexist]. See this article:
Functions -UnDocumented Check to Verify File Existence
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jsheldon via SQLMonster.com" <u2880@.uwe> wrote in message
news:6909a192e8dbf@.uwe...
>I want to check to see if any error files from SQL uploads are present in a
> folder.
> I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
>
> How can I check a windows directory for *.err files in SQL syntax? osql?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

How to run the snapshot agent using the -PublisherLogin ?

I am Merge replicating from MS SQL Server 2000 SP4 to a MS SQL Server 2000
SP3 box.
Get the following error while running the Merge Agent:
Error Message : The process could not log conflict information.
Error Details : The process could not log conflict information. (Source:
Merge Replication Provider (Agent); Error number: -2147200992)
Could not find stored procedure ''.
(Source: <publishing servername> (Data source); Error number: 2812)
On the Microsoft support website
http://support.microsoft.com/default...b;en-us;308743
it says this happens when 'If Merge Replication is configured so that the
Snapshot Agents connect to the Publisher by using a login that is defined as
a member of db_owner (and not a system administrator) in a database that is
merge published, if conflicts are detected during the Merge Process the Merge
Agent fails with this error message'
They offer a workaround which is :
To work around this behavior, run the Snapshot Agent with the
-PublisherLogin parameter and specify a login. For example, use the sa login,
which is a system administrator on the Publisher to generate a new snapshot.
After the snapshot completes, SQL Server creates the conflict stored
procedure. Then, you can rerun the Merge Agent so that SQL Server can log the
conflicts. Because you are not reinitializing the subscription, SQL Server
does not apply the new snapshot to the subscribers.
How do I run the Snapshot Agent with the -PublisherLogin parameter ?
On your publisher server there's a job which creates the snapshot. The
name of the job varies but you can recognize it by the category
REPL-Snapshot.
When you open the properties of the job, go to to Steps tab and edit
the "run agent" step by adding the parameter.
M

how to run sql scripts from commandline in MSDE

Hi,
I am trying to run sql scripts from Commandline, i am getting the error "the database doesnot exist use sp_helpdb to show available databases".
I am running this sql query to create database table, appreciate your help.
ThanksWhat SQL query are you running and what is the connection string you're using?

How to run an exe in the SSIS scripting task

Hi,

Can anyone help me how to run an exe file in the scripting task.

I was trying the below code but it's giving an error.

Set wshShell = WScript.CreateObject ("WSCript.shell")
wshshell.run "c:\Test\ABC.exe", 6, True
set wshshell = nothing

Thanks,

rkn

Here's one way. You may want to check out the MSDN for any security risks, etc.

Dim procID As Integer

procID = Shell("""C:\Program Files\Microsoft Office\OFFICE11\winword.exe"" c:\temp\test.doc")

Larry Pope|||

I would be inclined to choose .NET's Process.Start method as the more language-neutral approach...but it's possible that this is what the VB Shell command calls anyway.

-Doug

|||I believe the Process.Start method has the limitation of not being able

to add cmdline parameters, which was included in the original posters

code.

If I'm wrong about the Process.Start method not being able to use

cmdline parameters, please post some code. I would love to be

able to use that rather than Shell.

Larry|||

I think what you are looking for is something like this:

System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo(Setting.PostProcessor,commandLineArgs);

startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;

startInfo.WorkingDirectory = "C:\\Test";

startInfo.UseShellExecute = false;

System.Diagnostics.Process process = new System.Diagnostics.Process();

process.StartInfo = startInfo;

process.Start();

Hossein Riazi

|||

The Start method also has other overloads suited for command line arguments, such as

Process.Start Method (String, String)

Starts a process resource by specifying the name of an application and a set of command-line arguments, and associates the resource with a new Process component.

-Doug

How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!

How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!

How to run a DTS as a trusted user in a stored proc

I get an error when i try to run a dts
but when i create a new dts and select the right server and user
everything went well.
Can someone help me?What is the error you receive ?

This is a cut from a microsoft article which may address your problem:
"Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run. "

Here is the article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;q269074)sql

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to return 'error' if no rows are found?

I am stuck with this one...

I have a rather simple table with ZIP-codes and a responsible branch-office. When I enter a zip-code, I recieve the branch-city, just as designed. But, I need the text 'error' returned if the zip-code is not valid. I don't know about the USA, but in Austria, EU, there are about 2300 valid 4-number zip-codes, and 6700 unused codes.

There is an application which accesses the SQL 2000 server by ODBC, and that application needs a value returned. This application sends a SQL query to the server, and interprets the return value.

Example:

1100 Wien
1200 Wien
2345 Linz

When queried for '1200', 'Wien' should return (this works of course), but when '1234' is queried, 'error' should return...

Can someone help me, or point me in th right direction?

SELECT COALESCE((SELECT DISTINCT City FROM Person.Address

WHERE PostalCode = 'MyPostalCode') , 'ERROR')

|||Thanks! that was the clue! Now I can use
"SELECT COALESCE ( ( SELECT Route FROM qry_PLZ_Route WHERE plz = 1100 ) , -1 ) AS Route" (plz is german abbr for zipcode) in the application, which is manageable.
Thanks for your help!

How to return error from CLR Stored Procedure

I have a C# stored procedure that I use to run a query, do some processing o
n
the results and send the results back to the caller via
SqlPipe.SendResultsRow(). It looks something like this:
string myConditions = MyFunctionToBuildTheConditionsFromCaller
ProvidedData()
;
SqlCommand cmd = new SqlCommand("SELECT * FROM myTable WHERE" +
myConditions, contextConnection);
dataRecord = MyFunctionToBuildTheDataRecord();
sqlPipe.SendResultsStart(dataRecord);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// Do some processing
if (someInternalConditonForThisRecordIsMet)
{
sqlPipe.SendResultsRow(dataRecord);
}
}
sqlPipe.SendResultsEnd();
}
Not every record from the original dataset is returned. The caller is using
SqlCommand.ExecuteReader() to retrieve the set of records from the stored
procedure.
Here's the problem...
The caller is a web page that allows the user to specify search criteria.
If the user specifies search criteria that is too broad, the query will
return tens of thousands of records. To prevent this, I want my C# procedur
e
to stop after some number of maximum records and return an error. So that
looks something like this:
if (someInternalConditonForThisRecordIsMet)
{
if (++recordCounter <= maximumRecordsToReturn)
{
sqlPipe.SendResultsRow(dataRecord);
}
else
// Bail out and report an error to the caller
}
I've tried throwing an exception in the 'else' but Sql Server seems to
absorb it.
The caller receives exactly the maximum number of records but doesn't catch
an exception.
else
throw new ApplicationException("Too many records matched search
criteria.");
I could set the function to return an integer indicating if there were too
many results, but I could not find a property/method of the SqlDataReader
that retrieved the value returned from the procedure.
else
break; // break out of while (reader.Read())
// this line is at the ned of the procedure
return recordCounter > maximumRecordsToReturn ? 1 : 0;
I could set an output parameter on the stored procedure, but again, I could
not find a property/method of the SqlDataReader class that could retrieve th
e
output parameter.
How could/should I return this error condition back to the caller?
Steven Hughes - MCSDTry closing the datareader and then getting the return value. The return
value is only available after the operation finishes. While the datareader
is open, the operation is not complete. (I know, that seems odd - but who
are we to question...)
dr.Close();
int i=(int)cmd.Parameters["@.retval"].Value;
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steven Hughes" <shughes@.noemail.nospam> wrote in message
news:6C5FAECA-1795-480E-92F2-9AB5A5B126E4@.microsoft.com...
>I have a C# stored procedure that I use to run a query, do some processing
>on
> the results and send the results back to the caller via
> SqlPipe.SendResultsRow(). It looks something like this:
> string myConditions =
> MyFunctionToBuildTheConditionsFromCaller
ProvidedData();
> SqlCommand cmd = new SqlCommand("SELECT * FROM myTable WHERE" +
> myConditions, contextConnection);
> dataRecord = MyFunctionToBuildTheDataRecord();
> sqlPipe.SendResultsStart(dataRecord);
> SqlDataReader reader = cmd.ExecuteReader();
> while (reader.Read())
> {
> // Do some processing
> if (someInternalConditonForThisRecordIsMet)
> {
> sqlPipe.SendResultsRow(dataRecord);
> }
> }
> sqlPipe.SendResultsEnd();
> }
> Not every record from the original dataset is returned. The caller is
> using
> SqlCommand.ExecuteReader() to retrieve the set of records from the stored
> procedure.
>
> Here's the problem...
> The caller is a web page that allows the user to specify search criteria.
> If the user specifies search criteria that is too broad, the query will
> return tens of thousands of records. To prevent this, I want my C#
> procedure
> to stop after some number of maximum records and return an error. So that
> looks something like this:
> if (someInternalConditonForThisRecordIsMet)
> {
> if (++recordCounter <= maximumRecordsToReturn)
> {
> sqlPipe.SendResultsRow(dataRecord);
> }
> else
> // Bail out and report an error to the caller
> }
>
> I've tried throwing an exception in the 'else' but Sql Server seems to
> absorb it.
> The caller receives exactly the maximum number of records but doesn't
> catch
> an exception.
> else
> throw new ApplicationException("Too many records matched search
> criteria.");
> I could set the function to return an integer indicating if there were too
> many results, but I could not find a property/method of the SqlDataReader
> that retrieved the value returned from the procedure.
> else
> break; // break out of while (reader.Read())
> // this line is at the ned of the procedure
> return recordCounter > maximumRecordsToReturn ? 1 : 0;
>
> I could set an output parameter on the stored procedure, but again, I
> could
> not find a property/method of the SqlDataReader class that could retrieve
> the
> output parameter.
>
> How could/should I return this error condition back to the caller?
> --
> Steven Hughes - MCSD|||Hi,
Thanks for your post!
From your description, I understand that:
You were using C# to develop the CLR SQL Stored Procedure;
you found when a heavy load query from Web degraded the performance
seriously.
If I have misunderstood, please to let me know.
You may write a common stored procedure to get any query result row count
for appropriate decision by your application.
Also you may write a pagination stored procedure to get a specified count
of records once for appropriate display.
Here is a sample just for reference:
CREATE Procedure proc_getquerybypage
(
@.PageSize int, -- record count of every page
@.PageNumber int, -- current page number
@.QuerySql varchar(1000),--partial query string ,like '* From TABLENAME
order by ID desc'
@.KeyField varchar(500)
)
AS
Begin
Declare @.SqlTable AS varchar(1000)
Declare @.SqlText AS Varchar(1000)
Set @.SqlTable='Select Top '+CAST(@.PageNumber*@.PageSize AS varchar(30))+'
'+@.QuerySql
Set @.SqlText='Select Top '+Cast(@.PageSize AS varchar(30))+' * From '
+'('+@.SqlTable+') As TembTbA '
+'Where '+@.KeyField+' Not In (Select Top
'+CAST((@.PageNumber-1)*@.PageSize AS varchar(30))+' '+@.KeyField+' From '
+'('+@.SqlTable+') AS TempTbB)'
Exec(@.SqlText)
End
GO
You may refer to this article for "CLR Stored Procedure":
http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
Please note that this is a C#.NET development issue, when you meet such
issue next time, I recommend you post it to
microsoft.public.dotnet.languages.csharp for wider audience and more
professional solution than here.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Unfortunately, the situation is not that simple. Not every row that is
selected in the stored procedures query is returned to the caller. Complex
logic which includes pulling data from other tables for each record in
question must be performed to determine if the record can be sent back to th
e
caller. This logic must run for all records in the table before a count of
how many records will be returned is known.
Steven Hughes - MCSD
"Charles Wang[MSFT]" wrote:

> Hi,
> Thanks for your post!
> From your description, I understand that:
> You were using C# to develop the CLR SQL Stored Procedure;
> you found when a heavy load query from Web degraded the performance
> seriously.
> If I have misunderstood, please to let me know.
> You may write a common stored procedure to get any query result row count
> for appropriate decision by your application.
> Also you may write a pagination stored procedure to get a specified count
> of records once for appropriate display.
> Here is a sample just for reference:
> CREATE Procedure proc_getquerybypage
> (
> @.PageSize int, -- record count of every page
> @.PageNumber int, -- current page number
> @.QuerySql varchar(1000),--partial query string ,like '* From TABLENAME
> order by ID desc'
> @.KeyField varchar(500)
> )
> AS
> Begin
> Declare @.SqlTable AS varchar(1000)
> Declare @.SqlText AS Varchar(1000)
> Set @.SqlTable='Select Top '+CAST(@.PageNumber*@.PageSize AS varchar(30))+'
> '+@.QuerySql
> Set @.SqlText='Select Top '+Cast(@.PageSize AS varchar(30))+' * From '
> +'('+@.SqlTable+') As TembTbA '
> +'Where '+@.KeyField+' Not In (Select Top
> '+CAST((@.PageNumber-1)*@.PageSize AS varchar(30))+' '+@.KeyField+' From '
> +'('+@.SqlTable+') AS TempTbB)'
> Exec(@.SqlText)
> End
> GO
> You may refer to this article for "CLR Stored Procedure":
> http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
> Please note that this is a C#.NET development issue, when you meet such
> issue next time, I recommend you post it to
> microsoft.public.dotnet.languages.csharp for wider audience and more
> professional solution than here.
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||That works!! Thank you.
Too bad I have to read in the resultset before being able to retrieve the
output parameter value though.
Steven Hughes - MCSD
"Arnie Rowland" wrote:

> Try closing the datareader and then getting the return value. The return
> value is only available after the operation finishes. While the datareader
> is open, the operation is not complete. (I know, that seems odd - but who
> are we to question...)
> dr.Close();
> int i=(int)cmd.Parameters["@.retval"].Value;
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steven Hughes" <shughes@.noemail.nospam> wrote in message
> news:6C5FAECA-1795-480E-92F2-9AB5A5B126E4@.microsoft.com...
>
>|||Your stored proc can just return the result code you wish, return a non-zero
value to indicate that something did not go as expected. You can also use
RAISERROR to raise a waring or error to the client. Here is a simple example
for an sp called LimitedSP. colors is a table with some three rows of color
s
colors in it. Using it looks like:
DECLARE @.resultCode int
exec @.resultCode = LimitedSP
SELECT @.resultCode
and returns
color
--
red
green
1
result code of sp was 1, which by convention in this sp means not all rows
were returnd.
Here is the sp itself.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
// make sp return an Int32, this will the result code from SP
public static Int32 LimitedSP()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("SELECT color FROM colors", conn))
{
SqlPipe pipe = SqlContext.Pipe;
conn.Open();
SqlMetaData[] md = new SqlMetaData[1];
md[0] = new SqlMetaData("color", SqlDbType.NVarChar, SqlMetaData.Max);
SqlDataRecord rec = new SqlDataRecord(md);
pipe.SendResultsStart(rec);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
Int32 limit = 2; // don't return more than four rows
Int32 index = 1;
while (rdr.Read())
{
string color = rdr.GetSqlString(0).Value;
rec.SetSqlString(0, color);
if (index > limit)
{
pipe.SendResultsEnd();
rdr.Close();
// use higher level if you want this to be more than a warning
cmd.CommandText = "RAISERROR('too many rows', 10, 1)";
cmd.ExecuteNonQuery();
return 1; // indicates not all rows returned
}
pipe.SendResultsRow(rec);
index++;
}
pipe.SendResultsEnd();
return 0; // indicates all rows were returned
}
}
}
};
Dan
> Unfortunately, the situation is not that simple. Not every row that
> is selected in the stored procedures query is returned to the caller.
> Complex logic which includes pulling data from other tables for each
> record in question must be performed to determine if the record can be
> sent back to the caller. This logic must run for all records in the
> table before a count of how many records will be returned is known.
> "Charles Wang[MSFT]" wrote:
>|||Interesting... so I use RAISEERROR like I would from a standard stored
procedure then.
Ok. Thank you.
Steven Hughes - MCSD
"Dan Sullivan" wrote:

> Your stored proc can just return the result code you wish, return a non-ze
ro
> value to indicate that something did not go as expected. You can also use
> RAISERROR to raise a waring or error to the client. Here is a simple examp
le
> for an sp called LimitedSP. colors is a table with some three rows of col
ors
> colors in it. Using it looks like:
> DECLARE @.resultCode int
> exec @.resultCode = LimitedSP
> SELECT @.resultCode
> and returns
> color
> --
> red
> green
> --
> 1
> result code of sp was 1, which by convention in this sp means not all rows
> were returnd.
>
> Here is the sp itself.
> public partial class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> // make sp return an Int32, this will the result code from SP
> public static Int32 LimitedSP()
> {
> using (SqlConnection conn = new SqlConnection("context connection=true")
)
> using (SqlCommand cmd = new SqlCommand("SELECT color FROM colors", conn)
)
> {
> SqlPipe pipe = SqlContext.Pipe;
> conn.Open();
> SqlMetaData[] md = new SqlMetaData[1];
> md[0] = new SqlMetaData("color", SqlDbType.NVarChar, SqlMetaData.Max);
> SqlDataRecord rec = new SqlDataRecord(md);
> pipe.SendResultsStart(rec);
> using (SqlDataReader rdr = cmd.ExecuteReader())
> {
> Int32 limit = 2; // don't return more than four rows
> Int32 index = 1;
> while (rdr.Read())
> {
> string color = rdr.GetSqlString(0).Value;
> rec.SetSqlString(0, color);
> if (index > limit)
> {
> pipe.SendResultsEnd();
> rdr.Close();
> // use higher level if you want this to be more than a warning
> cmd.CommandText = "RAISERROR('too many rows', 10, 1)";
> cmd.ExecuteNonQuery();
> return 1; // indicates not all rows returned
> }
> pipe.SendResultsRow(rec);
> index++;
> }
> pipe.SendResultsEnd();
> return 0; // indicates all rows were returned
> }
> }
> }
> };
>
> Dan
>
>
>|||Hi,
I'm glad to see you got the answer you want.
Thanks for using Microsoft Newsgroup.
If you have any other concerns, please don't hesitate to let us know.
Enjoy your day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to return a string from a Stored Procedure

Up till now I've used SP's for updates and only ever needed to return error messages.

Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)

It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.

Hope that makes sense.

Thanks
Markuse OUTPUT Parameters...check BOL for more info..

hth|||Thanks, I already have that.

My question was how to return it into VB.Net|||if you mean how to catch the returned value from vb.net

...
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.varchar
mycommand.Parameters.Add(myParam)
mycommand.ExecuteNonQuery()
return convert.todouble((mycommand.Parameters("@.result").Value))
objcon.close
...

hth|||OK, I got what I wanted.

I hadn't thought of using ExecuteScalar with a SP, just needed to broaden my thinking a bit.

so...abbreviated...here's what I did.

Dim con as New SqlConnection("myConnectionString")
Dim cmd = New SqlCommand("mySPname",con)
cmd.CommandType=CommandType.StoredProcedure
cmd.Parameters.Add("@.ItemID",12345)
" " "
" " "
Try
cmd.Connection = con
cmd.Connection.Open()
x = cmd.ExecuteScalar()
Catch exp As SqlException
x = exp.Message
Finally
cmd.Collnection.Close()
End Try

This way I either get the string value I was looking for or the error message.

How to retrieve SQL error code from the Error object?

Hello!
I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On the
server side i have created stored procedure that can raise an error with
some SQL error code like 547 or 550 and i want to catch this code on the
client side to log and to call other procedure, but Error object does not
pass this code, there are only OLE DB error code (same for different errors)
and Description, but NativeError and SQLState fields is empty. How can i
recieve SQL error code over Error object? Thanks!
Best regards, Konstantin KnyazevKostya
There are some errors in SQL Server that terminate batch immediately after
raising like Violation of PRIMARY KEY constraint. In your case I'd try to do
that on the server side
CREATE PROC myProc
AS
--Do something
IF @.@.ERROR <>0
RETURN -1
ELSE
RETURN 0
DECLARE @.err INT
EXEC @.err=myProc
IF @.err =-1
PRINT 'I have got an error'
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:O7pl8EvLFHA.1144@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On
the
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different
errors)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>|||Hi
You can cycle throught the errors in the connection object such as:
procedure TForm1.Button1Click(Sender: TObject);
var
i:integer;
begin
try
form2.show;
sp.ExecProc;
except
on e: Eaccessviolation do
showmessage(e.message)
else
if cnn.errors.Count <> 0 then
begin
for i:=0 to cnn.errors.count-1 do
showmessage(cnn.errors[i].desc_ription);
end;
end;
end;
from the thread http://tinyurl.com/4trhq
John
"Konstantin Knyazev" wrote:

> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On th
e
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different error
s)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>
>|||Hi!
In my case, Connection.Errors collection is empty, probably due a
asynchronious call, there are only Error object passed in the parameters of
the ExecuteComplete method.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:87F6042E-5AFD-4118-A7F9-E7FBB0C8EB4C@.microsoft.com...
> Hi
> You can cycle throught the errors in the connection object such as:
> procedure TForm1.Button1Click(Sender: TObject);
> var
> i:integer;
> begin
> try
> form2.show;
> sp.ExecProc;
> except
> on e: Eaccessviolation do
> showmessage(e.message)
> else
> if cnn.errors.Count <> 0 then
> begin
> for i:=0 to cnn.errors.count-1 do
> showmessage(cnn.errors[i].desc-ription);
> end;
>
> end;
> end;
> from the thread http://tinyurl.com/4trhq
> John
> "Konstantin Knyazev" wrote:
>
the
not
errors)|||It was a while since I did something similar, but I can recall having to use
error values of at least a certain number, otherwise SQL Server supresses
them, you would need to check the documentation to confirm this..
"Konstantin Knyazev" wrote:

> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On th
e
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different error
s)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>
>|||Hello!
Yes, i did it, but further i have changed system to use linked servers and
distributed query that requires XACT_ABORT ON, but your method requires
XACT_ABORT OFF.
Best regards, Konstantin Knyazev
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BQSdu3LFHA.2988@.TK2MSFTNGP14.phx.gbl...
> Kostya
> There are some errors in SQL Server that terminate batch immediately
after
> raising like Violation of PRIMARY KEY constraint. In your case I'd try to
do
> that on the server side
> CREATE PROC myProc
> AS
> --Do something
> IF @.@.ERROR <>0
> RETURN -1
> ELSE
> RETURN 0
> --
> DECLARE @.err INT
> EXEC @.err=myProc
> IF @.err =-1
> PRINT 'I have got an error'
>
>
>
>
>
>
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:O7pl8EvLFHA.1144@.TK2MSFTNGP09.phx.gbl...
> the
not
> errors)
>|||Errors that i'm trying to catch is 547 and 550. Sorry, can you give me a
link to article? I can't find anything :(
Best regards, Konstantin Knyazev
"Mike (adoanywhere)" <Mikeadoanywhere@.discussions.microsoft.com> wrote in
message news:B2304F57-46C6-4033-AB7D-639E89994413@.microsoft.com...
> It was a while since I did something similar, but I can recall having to
use
> error values of at least a certain number, otherwise SQL Server supresses
> them, you would need to check the documentation to confirm this..
> "Konstantin Knyazev" wrote:
>
the
not
errors)|||For my problem I was using RAISERROR in SQL Server Stored Proc
[http://msdn.microsoft.com/library/d...r />
_5ooi.asp]
Try modifying the paramters until you get a reply back to the client. Since
I dont know exactly what you are doing I cant be more specific. But in my
case I wrote a small app to test these paramters until I got a result.
"Konstantin Knyazev" wrote:

> Errors that i'm trying to catch is 547 and 550. Sorry, can you give me a
> link to article? I can't find anything :(
> Best regards, Konstantin Knyazev
> "Mike (adoanywhere)" <Mikeadoanywhere@.discussions.microsoft.com> wrote in
> message news:B2304F57-46C6-4033-AB7D-639E89994413@.microsoft.com...
> use
> the
> not
> errors)
>
>

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w
/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt
',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgra
de
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com