but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
I am running into an issue while executing a sproc from Excel VBA. Everything connects fine, and I am passing a parameter, however, after a few seconds, it seems like the connection receives a "completed" command and continues down it's code, but the sproc is still executing. The result is that I never receive the record set from the sproc.Here is the code snippet from VBA:
' Create Recordset objects. Dim cmd As New ADODB.Command Dim conn As ADODB.Connection Dim prm As ADODB.Parameter Dim sConnString As String Dim rs As ADODB.Recordset Dim strQry As String Dim rowCount As Long
[code]....
And here is the sproc that is being called. the first thing it performs after the "IF" block (there are multiple steps that would consecutively be called after this, but all of the data hinges on this first step working) is a TRUNCATE statement. After running a SQL profiler while executing the VBA code, I consistently see an "account log out" entry; almost as if the connection from the Excel workbook is sent a disconnect instruction. The sproc continues to run and perform the rest of the script in the "IF" block, but the returned recordset is never returned back to Excel.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET NOCOUNT ON GO
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET. Scenario: I have a loop through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables. I can not use the following syntax: cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dbo.storedprocedurename" With cmd .Parameters.Add(New SqlClient.SqlParameter("@param1", paramValue1)) .Parameters.Add(New SqlClient.SqlParameter("@param2", paramValue2)) End With What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET?
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
I'm trying to build ssis package that select rows from SQL Server table and
according to Conditional Split cube results I should execute oracle stored
procedure with in and out parameters.
The in parameters should come from the SQL select and the output parameters
should be inline parameter that I should declare on in the OleDB Command
selecet statement.
Here is the sql statement I wrote in the OleDB Command:
declare @out_return_status as int declare @out_return_msg as varchar (1000) begin exec clickace.cns_clk_sst_iw_pkg.update_instal_warranty_dates ?,?,?,?,?,
@out_return_status,@out_return_msg end
When I click on the Refresh button in the ssis GUI I get the following error:
An OLE DB error has occured. Error Code : 0x80040E51.
An OLE DB record is available. Source: OraOLEDB Hresult : 0x80040E51.
Description :"provider cannot provide parameter information and
I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.
CREATE PROCEDURE OfficeMove
-- Add the parameters for the stored procedure here
@UserName nvarchar(10),
@NewLocation nchar(5),
@NewCity nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Execute as user = '***'
DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
WHERE User_Name = @UserName
INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
I tried to use the "How to call a Parameterterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsoft.com/kb/308049 to learn "Use DataReader to Return Rows and Parameter" in my VB 2005 Express. I did the following things:
1) created a stored procedure "pubsTestProc1.sql" in my SQL Server Management Studio Express (SSMSE):
USE pubs
GO
Create Procedure TestProcedure
(
@au_idIN varchar (11),
@numTitlesOUT Integer OUTPUT
)
As
select A.au_fname, A.au_lname, T.title
from authors as A join titleauthor as TA on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where A.au_id=@au_idIN
set @numTitlesOUT = @@Rowcount
return (5)
2) created a project "pubsTestProc1.vb" in my VB 2005 Express and copied the following code from http://support.microsoft.com/kb/308049 (i.e. Added the code to the Form_Load eventQL_Client) :
Imports System.Data
Imports System.Data.Client
Imports System.Data.SqlType
Imports System.Data.Odbc
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection & _
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I compiled the above code and I got the following 15 errors: Warning 1 Namespace or type specified in the Imports 'System.Data.Client' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 2 9 pubsTestProc1 Warning 2 Namespace or type specified in the Imports 'System.Data.SqlType' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 3 9 pubsTestProc1 Error 3 Type 'SqlConnection' is not defined. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 10 25 pubsTestProc1 Error 4 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 15 30 pubsTestProc1 Error 5 Name 'testCMD' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 17 9 pubsTestProc1 Error 6 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 20 23 pubsTestProc1 Error 7 Name 'RetValue' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 21 9 pubsTestProc1 Error 8 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 23 23 pubsTestProc1 Error 9 Name 'auIDIN' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 24 9 pubsTestProc1 Error 10 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 26 28 pubsTestProc1 Error 11 Name 'NumTitles' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 27 9 pubsTestProc1 Error 12 Name 'auIDIN' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 29 9 pubsTestProc1 Error 13 Type 'SqlDataReader' is not defined. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 32 25 pubsTestProc1 Error 14 Name 'RetValue' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 39 47 pubsTestProc1 Error 15 Name 'NumTitles' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 40 52 pubsTestProc1 //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// First, I am completely lost here alreay. Second, I should have the following code from http://support.microsoft.com/kb/308049 too:
OLE DB Data Provider
Dim PubsConn As OleDbConnection = New OleDbConnection & _
("Provider=sqloledb;Data Source=server;" & _
"integrated security=sspi;initial Catalog=pubs;")
Dim testCMD As OleDbCommand = New OleDbCommand & _
("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As OleDbParameter = testCMD.Parameters.Add & _
Console.WriteLine("Number of Records: " & (NumTitles.Value)) ////////////////////////////////////////////////////////////////////////////////////////////////////// Now, I am completely out of touch with these two sets of the code from the Microsoft KB 308049 and do not know how to proceed to get the following output stated in the Microsoft KB 308049-see the below:
4. Modify the connection string for the Connection object to point to the server that is running SQL Server.
5.
Run the code. Notice that the ExecuteScalar method of the Command object returns the parameters. ExecuteScalar also returns the value of column 1, row 1 of the returned rowset. Thus, the value of intCount is the result of the count function from the stored procedure. //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Please help and tell me what I should do to get this project landed on the right track.
I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.
I want to run this top-level procedure using an account which can't do anything else.
Is there a simple way to give it all the permissions it needs without empowering it to do anything else?
I'm using Sql Server 2005, and I am receiving an error when I attempt to run a stored procedure and I have no clue why. Can someone please help?
I receive an error when I attempt to execute the following stored procedure: exec INSERT_OBJ 1234, 'Name', 123, NULL, GetDate(), 'system'
The error I receive is: "Incorrect syntax near ')'" All of the values are valid values and valid types.
Is it possible to be an error within the stored procedure itself? This error makes it sound like the syntax of my attempt is incorrect, thus it never gets ran.
I am trying to execute a "ServerB" Stored Procedure in "ServerA".This SP is in Multiple DB's in ServerB. I am trying to use Openquery and Dynamic SQL to do that.But I am having issues. Intially i am trying to pass just one DBname as parameter..if it returns values then i can use cursor or other options to retrieve for multiple DB's Please Help!!!
Hi, Can anyone tell me how to execute a stored procedure using vb6.0.I am able to connect to sqlserver from my application.Simple select queries are working.
The stored procedure contains a select statement at the end.I want to get that as the resultset in vb6.0.How do I do this.
Hi I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000. But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs. Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers? I'm using .NET Framework 1.1ODBC (SQL Native Client) Here is the Store procedure code:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[CreateProjectDataBase] @AFModelDatabaseProject varchar(200), @TemplateDbBackupFileAndName varchar(200), @DatabaseName varchar(200), @DataFilePathAndName varchar(200), @LogFilePathAndName varchar(200) AS
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName WITH INIT RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName, MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure; this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName; this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName; this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }"; this.odbcCreateDataBaseCommand.ExecuteNonQuery(); RegardsTomas
We have a test and production environment. After transfering some tables from test to prod and all stored procedures using those tables. We get an error when executing those stored procedures: " DB-library: Possible network error: Bad token from SQL Server: Datastream processing out of sync. Net-library error 0: DB-libray Process Dead - Connection Broken. " When we execute the stored procedure with 1 parameter less we get a parameter missing error. Then we execute the stored procedure again and everything is allright? Has anyone experienced this before? If so, please help. SQlServer 6.50.201
How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..
I got this results from running (sp_dropallindex) like this:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO DROP INDEX table1.index1 GO DROP INDEX table1.index2 GO
I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...
I tried something like this:
DECLARE @DROP AS VARCHAR(8000) SET @DROP='exec sp_drop_allindex' EXECUTE (@DROP)
and i see the same out , but my indexes and PK still there ... i'm confused about it ..
how to use opendatasource to execute a stored procedure remotely in MySQL with parameters..I am using sp_configure to enable and disable Ad Hoc Distributed queries as below before and after the open data source statement
sp_configure show,1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override go
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Hi everyone,first time here, so I'm sorry if this has been covered already agesago. :(I am trying to learn T-SQL and Stored Procedures and bought the bookon these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'malready stuck on my first Stored Procedure and getting error messagesthat I cannot understand. I've already tried Google and Microsoftonline to no avail. I do have the .NET Framework on my system and useit for programming in C# sometimes and by the looks of the error, it'ssomething to do with that? Please note I am only using SQL ServerExpress.Here is the first Stored Procedure:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE ap_HelloASBEGINSET NOCOUNT ON;SELECT 'Hello world'SELECT * from dbo.EqTypeENDGOThe error in its entirety is this:Msg 6522, Level 16, State 1, Procedure ctrd_DDL_PROCEDURE_EVENTS_vb,Line 1A .NET Framework error occurred during execution of user definedroutine or aggregate 'ctrd_DDL_PROCEDURE_EVENTS_vb':System.UnauthorizedAccessException: Access to the path 'c:server_login.log' is denied.System.UnauthorizedAccessException:at System.IO.__Error.WinIOError(Int32 errorCode, StringmaybeFullPath)at System.IO.FileStream.Init(String path, FileMode mode, FileAccessaccess, Int32 rights, Boolean useRights, FileShare share, Int32bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, StringmsgPath, Boolean bFromProxy)at System.IO.FileStream..ctor(String path, FileMode mode,FileAccess access, FileShare share, Int32 bufferSize, FileOptionsoptions)at System.IO.StreamWriter.CreateFile(String path, Boolean append)at System.IO.StreamWriter..ctor(String path, Boolean append,Encoding encoding, Int32 bufferSize)at System.IO.StreamWriter..ctor(String path, Boolean append)at VbTriggers.Triggers.trigger_DDL_PROCEDURE_EVENTS() ..The statement has been terminated.Can anyone assist please? :(Many thanks in advance,Lara
HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh
I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.
This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.
When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.
I have a maintenance stored procedure that executes a script using xp_cmdshell and sqlcmd. The stored procedure has one argument that is contains the name of the procedure to update.
The stored procedures retrieves the location of the stored procedures (stored in a table) and then builds a variable that contains the sqlcmd command. Note that the file name of the script is <stored procedure name>.sql
I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.
Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2 WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication AS BEGIN SET NOCOUNT ON; declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
Hi All, I have a peculiar problem with SQL2000. When i execute a Stored procedure in Demo & Production i get different outputs. But i copied the business logistics from the Sp and executed as a script in both the servers. Now Both the records are same. I WANT TO KNOW " WHETHER THERE IS ANY DIFFERENCE IN EXECUTION METHODOLOGY BETWEEN STORED PROCEDURE AND QUERY". NOTE: My stored procedure has 14 executable scripts. Upto 10 scripts no date comparisons were made. But at the 11th script the records differ. I DOUBT WHETHER THERE WILL BE ANY DATE RELATED ISSUE WHEN EXECUTING AS STORED PROCEDURE AND SCRIPT
END I'm then using a tableadapter to execute this from my ASP.NET page using the following code, Protected Sub ExecutePackage() Dim ExecuteAdapter As New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0) Dim strResult As String strResult = ExecuteAdapter.Execute() lblResult.Text = strResult End Sub If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code. When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @ReturnValue and SELECT @ReturnValue to no avail. Can someone suggest how I can get the value 0-6 to my code?
CREATE PROCEDURE SelectCostumers @name varchar(100) Declare @SQL = "SELECT Id, Name FROM Costumers" AS IF (@name IS NULL) @SQL ELSE @SQL += "WHERE Name LIKE @name"
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
