Question Regarding Running A Stored Procedure As A Job Step
Apr 16, 2007
I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?
I'm having trouble using VS2005 to remotely debug stored procedures in SQL Server 2005.
After setting a breakpoint, when I select "Step into Stored Procedure", I am NOT prompted to enter parameter values. I'm taken straight to the debug window, which is titled "Running" but nothing is happening. The breakpoint icon changes to a translucent icon with a yellow exclamation mark. The mouseover tooltip says:
"The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded".
If I choose "Exceute" instead of "Step into Stored Procedure", I'm prompted to enter parameter values and everything works as expected.
I cannot seem to get this to option to come up without having the full blown studio installed.
Is there something else that needs to be configured on the database before having this option enabled? I have enable clr (which is semi-unrelated to good ol' fashion stored procedure debugging) in the surface area configuration manager, but still no luck.
I am using SQL Server 2005 Standard Edition. I can get this feature working w/ no prob when I have the full blown Visual Studio Professional version installed, but not with just SQL Server - Futher insight would be helpful.
When I start my stored procedure from Query Analyzer it ends in 1 h. I have created a job consisting of 1 step - the same procedure. If I start the job from Enterprise Manager it ends after some 10 h. What can I do to get the same performance?
Can anybody explain how to do debug a stored procedure from SQL Query Analyser.
When i tried opening Query Analyser and pressing F8 i am able to see Object Browser on left side, i selected the d/b and expanded it then i selected a stored procdure by right click of mouse. I selected "Debug".
It shows me alert msg "SQL Debugging may not work properly if you log on as 'Local System Account' while SQl server is configured to run as a service. You can open Event Viewer to see details." DO U WISH TO CONTINUE- I selected "YES"
I am able to see 3 split windows on right side and GO, Toggle, Untoggle are enabled BUT Step Into, Step Over, Step Out...Stop debugging are disabled at menu bar.
The 1st right split window shows the proc code, 2nd split window shows Local-Global-Callstack none of them shows any values(blank), 3rd split window shows records(result) and @RETURN_VALUE = 0 message
I had Toggled at each and every line of the procedure in 1st split window still it doesnt respond anything.
What might be the problem, how to solve it do i need to give any permissions.
i tried logging from wind Authentication and also from Sql Authentication (sa/sa), still same problem occurs. By the way i am using SQL Server 2000.
I couldn't debug SQL Server by "Step into Stored Procedure" on Server Explorer of VS2008 (or VS2005) to SQL 2005 Developer on remote Windows Server 2003 machine, it allway issue exception "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"
1) The environment: The Client: Windows XP SP2 (WORKGROUP) Visual Studio 2008 (or VS2005)
The SQL Server Machine: Windows 2003 Server Sp1 (DOMAIN) SQL Server 2005 Developer
2) User account and Permission login: I create the same user account for both Client and Domain Server with the same password, i also add that user to "Administrators" group in both machine.
At the SQL Server on Server machine, i added that account to ServerSecurityLogin with 'sysadmin' role already
3) Connection and authentication: I used "Windows Authentication" for my connection to SQL server, and i checked sure my account of the connection by SQL command
SELECT SYSTEM_USER, IS_SRVROLEMEMBER ('sysadmin')
4) Firewall: I checked firewall like MSDN helping (i also tried to test by turn off firewall in both machine)
5) Visual Studio Remote Debugger: I read "How to: Enable SQL Server 2005 Debugging" on MSDN with comment "The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging T-SQL code only, then no remote setup is required." so i didn't config Visual Studio Remote Debugger any thing.
Note: If i "Step Into Store Procedure" at Server locally, it works okey, so on at my PC client locally. But if i move debugging from my client to my Server, it occur error "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"???
If i execute store procedure on Server Explore, it works okey!
HiI have a VBScript file which runs fine when I click it or when I run itfrom a command prompt level.I added it as the 1st step of an SqlServer job and the job always failswith the error message"Executed as user: GVIANTAdministrator. The process could not becreated for step 1 of job 0xBE3A0405EB9F624282C1A9474AA9F719 (reason: %1is not a valid Win32 application). The step failed."Cn anyone help me understanding this error message.Thanks 1David Greenberg
I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong. Any help would be greatly appreciated.
Asp.net code;
Dim oCmd As SqlCommand
oCmd = New SqlCommand("exportData", rtConn) oCmd.CommandType = CommandType.StoredProcedure rtConn.Open()
With oCmd .CommandType = CommandType.StoredProcedure Response.write("CommandType.StoredProcedure") End With
Try oCmd.ExecuteNonQuery() Response.write("ExecuteNonQuery") Finally rtConn.Close() End Try
StoredProcedure;
CREATE PROCEDURE exportData AS Exec master..xp_cmdshell 'DTSRUN /local/DTS_ExamResults' GO
Hi,I've written a job to export user and database permissions for alld/b's on a server. As you can see below, the T-SQL commands are thesame for each d/b. Can anyone assist with regard to re-writing this sothat any new d/b's added do not require ammending the job (loop)?Thx,GC.use mastergoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use msdbgoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test1goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test2goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'
I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:
ALTER PROC sp_get_allowed_growers @GrowerList varchar(500) AS BEGIN SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL = 'SELECT nu_code, nu_description, nu_master FROM nursery WHERE nu_master IN (' + @GrowerList + ') ORDER BY nu_code ASC'
EXEC(@SQL) END GO
and the code I'm using to execute the procedure is this:
public DataSet GetGrowers(string Username) { System.Text.StringBuilder UserRoles = new System.Text.StringBuilder(); UsersDB ps = new UsersDB(); SqlDataReader dr = ps.GetRolesByUser(Username); while(dr.Read()) { UserRoles.Append(dr["RoleName"]+","); } UserRoles.Remove(UserRoles.Length-1,1); //Create instance of Connection and Command objects SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]); SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection); //Create and fill the DataSet SqlParameter paramList = new SqlParameter("@GrowerList",SqlDbType.VarChar); paramList.Value = UserRoles.ToString(); transloadCommand.SelectCommand.Parameters.Add(paramList); DataSet dsGrowers = new DataSet(); transloadCommand.Fill(dsGrowers); return dsGrowers;
}
The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:
Line 1: Incorrect syntax near 'sp_get_allowed_growers'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.
hi, i'm using SQL server 2000. i'm getting the below error when i run a store procedure. "Specified column precision 500 is greater than the maximum precision of 38." I have created a temporary table inside the stored procedure inserting the values by selecting the fields from other table. mostly i have given the column type as varchar(50) and some fields are numeric(50).
We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks
I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.
I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)
I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.
Here is my SP
ALTER PROCEDURE uspSelectBarItemID2
(
@BarTabID INT,
@DrinkID INT,
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID
FROM [Bar Items]
WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.
What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.
Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.
HI all, I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks.
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this? newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam) ownrParam.Value = OwnerID colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close() colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close() and so on for 9 columns…
Hi, I'm running a CLR stored procedure through my web using table adapters as follows: res = BLL.contractRateAdviceAdapter.AutoGenCRA() 'with BLL being the business logic layer that hooks into the DAL containing the table adapters. The AutoGen stored procedure runs fine when executed directly from within Management Studio, but times out after 30 seconds when run from my application. It's quite a complex stored procedure and will often take longer than 30 seconds to complete. The stored procedure contains a number of queries and updates which all run as a single transaction. The transaction is defined as follows: ---------------------------------------------------------------------------------------------------------------------- options.IsolationLevel = Transactions.IsolationLevel.ReadUncommittedoptions.Timeout = New TimeSpan(1, 0, 0) Using scope As New TransactionScope(TransactionScopeOption.Required, options) 'Once we've opened this connection, we need to pass it through to just about every 'function so it can be used throughout. Opening and closing the same connection doesn't seem to work 'within a single transactionUsing conn As New SqlConnection("Context Connection=true") conn.Open() ProcessEffectedCRAs(dtTableInfo, arDateList, conn) scope.Complete() End Using End Using ---------------------------------------------------------------------------------------------------------------------- As I said, the code encompassed within this transaction performs a number of database table operations, using the one connection. Each of these operations uses it's own instance of SQLCommand. For example: ----------------------------------------------------------------------------------------------------------------------Dim dt As DataTable Dim strSQL As StringDim cmd As New SqlCommand cmd.Connection = conn cmd.CommandType = CommandType.Text cmd.CommandTimeout = 0Dim rdr As SqlDataReaderstrSQL = "SELECT * FROM " & Table cmd.CommandText = strSQL rdr = cmd.ExecuteReader SqlContext.Pipe.Send(rdr) rdr.Close() ---------------------------------------------------------------------------------------------------------------------- Each instance of SQLCommand throughout the stored procedure specifies cmd.CommandTimeout = 0, which is supposed to be endless. And the fact that the stored procedure is successful when run directly from Management studio indicates to me that the stored procedure itself is fine. I also know from output messages that there is no issues with the database connection. I've set the ASP.Net configuration properties in IIS accordingly. Are there any other settings that I need to change? Can I set a timeout property when I'm calling the stored procedure in the first place? Any advice would be appreciated.
I am new to ASP.NET so please excuse what may seem like a dumb question. I have a stored procedure that I need to run when the user clicks on our submit button. I am using Visual Studio 2005 and thought I could use the SqlDataSOurce Control. IS it possible to us the control or do I need to create a connection and call the stored procedure in the the button_click sub? Thanks in advance MF
Hi all, I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code: // Define data objects SqlConnection conn; SqlCommand comm; // Initialize connection string connectionString = ConfigurationManager.ConnectionStrings[ "pay"].ConnectionString; // Initialize connection conn = new SqlConnection(connectionString); // Create command comm = new SqlCommand("UpdatePaymentDetails", conn); //comm.CommandType = CommandType.StoredProcedure; // Add command parameters comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int); comm.Parameters["PaymentID"].Value = paymentID; comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewPayment"].Value = newPayment; comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewInvoice"].Value = newInvoice; comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money); comm.Parameters["NewAmount"].Value = newAmount; comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewMargin"].Value = newMargin; comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money); comm.Parameters["NewProfit"].Value = newProfit; comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime); comm.Parameters["NewEditDate"].Value = newEditDate; comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewQStatus"].Value = newQStatus; comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewStatus"].Value = newStatus; // Enclose database code in Try-Catch-Finally try { conn.Open(); comm.ExecuteNonQuery(); }
I have a table with information about a mobile account in one table, a table for mobile plans, and a table for planfeatures. Each mobile account is associated with a planid, and may be associated with any combination of the features associated with that plan. The plan features are stored in a bridgetable which contains 'invoicedate' (date stamped on the invoice in question), 'subaccountnumber' (the cell phone number), 'planid', and 'featureid'. I've tested the UpdateMobileFeature sp directly from the SQL Profiler--it works fine on it's own. I use a stored procedure to fill the mobile table (called from aspx page), and since I use three of the four columns written above for both the mobilesub and the mobilefeature tables, I tried just adding the parameter which holds the featureid's to the sp to update the mobilesub table. Then I call the UpdateMobileFeature sp from the updatemobile sp. (The code in mobilesub that is not calling UpdateMobileDetail works well). All seems to work fine--nothing crashes or anything, but nothing is being added to the mobilefeature table. Here is the code:CREATE procedure usp_updatemobile( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanId int, @FeatureList varchar(500) --added for UpdateMobileFeatures. In the form of a comma-seperated list, to imitate an array. --***irrelevant parameters removed***--) as exec dbo.UpdateMobileFeature '@InvoiceDate','@SubaccountNumber','@PlanId','@FeatureList' --the apparently nonfunctional call if exists ( //select for the mobile row in question ) Begin //update row End Else Begin //insert new row End GOCREATE PROC dbo.UpdateMobileFeatures( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanID int, @FeatureList varchar(500))ASBEGIN SET NOCOUNT ON CREATE TABLE #TempList ( InvoiceDate smalldatetime, SubaccountNumber varchar(50), PlanID int, FeatureID int ) DECLARE @FeatureID varchar(10), @Pos int SET @FeatureList = LTRIM(RTRIM(@FeatureList))+ ',' SET @Pos = CHARINDEX(',', @FeatureList, 1) IF REPLACE(@FeatureList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @FeatureID = LTRIM(RTRIM(LEFT(@FeatureList, @Pos - 1))) IF @FeatureID <> '' BEGIN INSERT INTO #TempList (InvoiceDate, SubaccountNumber, PlanID, FeatureID) VALUES (@InvoiceDate, @SubaccountNumber, @PlanID, CAST(@FeatureID AS int)) --Use Appropriate conversion END SET @FeatureList = RIGHT(@FeatureList, LEN(@FeatureList) - @Pos) SET @Pos = CHARINDEX(',', @FeatureList, 1) END END --SELECT o.FeatureID, CustomerID, EmployeeID, FeatureDate --FROM dbo.Features AS o -- JOIN -- #TempList t -- ON o.FeatureID = t.FeatureID
Insert Into MobileFeatures Select InvoiceDate, SubaccountNumber, PlanID, FeatureID From #TempList ENDGOHere is the method I used to call the first sp: (also with irrelevant stuff removed)public void saveCurrentMobile() { calculateTotals(); InvoiceDataSet dataset = InvoiceDataSet.GetInstance(); SqlConnection conn = (SqlConnection)Session["connection"]; SqlCommand cmdUpdateMobile; cmdUpdateMobile = new SqlCommand("usp_updatemobile", conn); cmdUpdateMobile.CommandType = CommandType.StoredProcedure; SqlParameter invoicedate = cmdUpdateMobile.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime); invoicedate.Value = DateTime.Parse(Request.Params["InvoiceDate"].ToString()); SqlParameter cellnumber = cmdUpdateMobile.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50); cellnumber.Value = txtCellNumber.Text.Trim(); SqlParameter plan = cmdUpdateMobile.Parameters.Add("@PlanId", SqlDbType.Int); plan.Value = planid; SqlParameter featurelist = cmdUpdateMobile.Parameters.Add("@FeatureList", SqlDbType.VarChar, 500); featurelist.Value = planform.FeatureList;
//Response.Write(isthirdparty.ToString()); Response.Write(thirdpartycompany); SqlParameter cycle = cmdUpdateMobile.Parameters.Add("@Cycle", SqlDbType.Int); cycle.Value = Convert.ToInt32(Request.Params["Cycle"]); int returnvalue = runStoredProcedure(cmdUpdateMobile); //the sp is called within this method.
I have a stored procedure being called from Visual Cafe 4.0 that takes over 30 minutes to run. Is there any way to backround this so that control returns to the browser that the JFC Applet is running in? The result set is saved to local disk and an email message sent to the user on completion. Thanks, Dave.
I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.
Can someone give me some insite why this stored procedure won't run as a scheduled job?
ALTER PROCEDURE tsul_insertintolinkedserver AS DECLARE @srvname varChar(20) SELECT @srvname = @@servername insert into THOMAS.tsnet.dbo.usagelog select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname from usagelog where id > ( select max(id) from THOMAS.tsnet.dbo.usagelog where hostserver = @srvname )
I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.
I've created a hierarchal listbox form that drills down From
Product - Colour - Year.
based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.
So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.
I want that product number to be used to populate the grid view. Is there away for me to do this?
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US
I have a stored procedure in SQL 2005 that purges data, and may take a few minutes to run. I'd like to report back to the client with status messages as the sp executes, using PRINT statements or something similar. I imagine something similar to BACKUP DATABASE, where it reports on percentage complete as the backup is executing.
I can't seem to find any information on how to do this. All posts on this subject state that it's not possible; that PRINT data is returned after the procedure executes. However it would seem possible since BACKUP DATABASE, for example, does this.
Is there any way to send status type messages to the client as the sp is executing??
Hi, I need to write a select query that will run all returned results through a separate stored procedure before returning them to me. Something like.... SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID() ......and run all the selected userids through a stored procedure like..... CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate()) Can anyone help me to get these working togethsr in the same qurey?
I've created this stored procedure to run two DTS packages which pull in data from two Excel files. I'd like to supress the Results tab since the results from the DTS packages are fed to it. I'd like to keep the Messages tab visible.
CREATE PROCEDURE [dbo].[ImportHelpDeskTickets] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON
[Code] ....
For those of you like me who will spend 8 hours trying to figure out how to get this working... Double up on the quotes as shown above. Also, be certain that NT SERVICEMSSQLSERVER has been granted appropriate rights to the folders with the the saved packages and the Excel files. You may also need to download the Access Database Engine.
Keywords: run dts package from stored procedure xp_cmdshell
'C:Program' is not recognized as an internal or external command, operable program or batch file.'
We try to run a stored procedure in management studio - and we see the following error - " Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[SQLSTATE HY000] (Error 0) The log for database 'Some-database' is not available.
Check the event log for related error messages.
Resolve any errors and restart the database.
[SQLSTATE HY000] (Error 9001) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE HY000] (Error 3314) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1).
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314). The step failed.
