MS SQL Server :: Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio

Management Studio Express && SQLcmd: Syntax Errors In Executing A MS-DOS Batch File && A Microsoft SQL Server Query File?
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.

In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
    sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
     "Shippers" -i c:prosseappschapter02ListListColumnVales.sql   -o
     c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
     USE $(Northwind)
     GO
     SELECT $(CompanyName) FROM $(Shippers)
     GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:

'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.

I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.

Thanks in advance,
Scott Chang

View Replies !

SQL Server Management Studio - New Query Window
I'm trying to write a query with named parameters/variables in the Management Studio - New Query pane. Where should I post a question about how to create parameters/variables?

Thanks,
Gregory

View Replies !

Problem With Executing Query Or Stored Procedure
Hi all
i am facing problems in executing stored procedures.
Sometimes the connection to server fails.
all the query, sp working fine few minutes back fails.
the sp executes and executes and does not get results but hangs later a get an error.

Any ideas please let me know it is quite urgent. i am at the point of testing
and only now facing such problems

thanks
with regards
roopa

View Replies !

What User Is Used When Executing An SSIS Package From Management Studio?

All:

I am working with a package that executes a stored procedure. This stored procedure makes use of a Linked Server. When I execute it in debug mode in VS 2005, it works flawlessly. However when I deploy it to the production server and execute it by going to Management Studio, connecting to Integration Services, navigating to MSDB, right clicking and choosing "run package" I get the infamous:

Login failed for user '(null)'. Not associated with a trusted SQL Server Connection. OLE DB Provider SQLNCLI for linked server [name] returned message "Communication Link Failer"

error. If I execute the stored procedure by itself on the production server, it works flawlessly. It is only when this stored procedure gets executed via the above mentioned way inside of an SSIS pack that I get this error. To me this suggest it is being executed as a different user, but I don't know which one.

Any help would be wonderful.

Thank you in advance...

AccDev

View Replies !

Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second
Hello to all,
I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha
My Procedure Codes are here:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )
AS
BEGIN

if ( @Level = 1)
begin
select @Path = convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @IDMember
and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0
end
if (@Level = 2)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0
end
if (@Level = 3)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0
end
if ( @Level = 4)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0
end
if (@Level = 5)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0
end
if (@Level = 6)
begin
select top 1 @Path = '' from wtcomValidRelationships
end
END

View Replies !

Trigger Executing Linked Server Stored Procedure
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.

Thank you

View Replies !

SQL 2005 Management Studio: Line Numbers In Query Window.
Can anyone tell me if you can display line numbers in the query windowof SQL 2005 Management Studio and if so how do I go about doing it?Thanks a bunch. TFD

View Replies !

Error With Executing A Stored Proc From C# Code Using SQLHELPER Class Of Data APplication Block Of MSDN
We are executing a stored procedure, where one of the parameters is a NTEXT data type.

While executing the procedure from SQLHELPER class of MSDN Data Application block,
we get the following error:

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@INPUTXML"): Data type 0xE7 has an invalid data length or metadata length.

Please help to resolve this issue.

Regards
NS RAJAGOPAL

View Replies !

Executing DTS From Stored Procedure
Hi,

I have create a DTS package "test"

i see the name is stored in msdb.sysdtspackages

how do i run this "test" package from stored procedure

thx
vik

View Replies !

Executing A Stored Procedure
Hello,

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.

Thank you ,
Crystal

View Replies !

Need Help In Executing A Stored Procedure Using Op
Hi All,

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!!!

Ex:

DECLARE @TSQL varchar(8000), @DBNAME char(20)
SELECT @DBNAME = 'DB1'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(serverB'+','+''exec '' + @DBNAME + ''.dbo.sp_StoredProcedure''+')'
EXEC (@TSQL)

Thanks in Advance!!!

View Replies !

Executing Stored Procedure Using VB6.0
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.

Keerthi

View Replies !

Executing Stored Procedure... Hangs...
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

View Replies !

Stored Procedure, Error When Executing (
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

View Replies !

Timer For Executing Stored Procedure
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

View Replies !

Executing A Stored Procedure Result
Hi Guys..

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 ..

PLEASE HELP ME OUT :D

View Replies !

Trigger Executing A Stored Procedure
I am having a issue with executing a stored procedure from a trigger. I have two tables, a staging table, and a live table. The staging table has a FOR INSERT trigger on in that executes a stored procedure, and also uses some error handling. However, I have placed a primary key on the live table, and I'm inserting data into the table that will violate that primary key. This is to test that the transaction gets rollback correctly. However, the data never gets inserted into the staging table, nor will any raiserror kick off. The stored procedure gets called, and errors out with out calling my if @@ERROR <> 0 statement, or inserting data into my staging table. How can I still insert data into the staging table and call the @@ERROR statement? Can I check the constraints of the table called by the stored procedure before actually calling the stored procedure? I have also tried the INSTEAD OF INSERT as well, that doesn't work either. Thanks.

View Replies !

Executing Stored Procedure From Powerbuilder 9
I'm trying to execute simple stored procedure on SQL Server from Powerbuilder 9 development environment and procedure returns 0 in output parameter, not what I was expecting.
Stored procedure is:

create procedure manta_sequence_proc
@sequence_name varchar(30),
@sequence_id numeric(19,9) OUTPUT
as
begin

SET NOCOUNT ON
Begin Tran seq

update manta_squrence set sequence_id=sequence_id+1 where sequence_name = @sequence_name

Commit Tran seq
select @sequence_id = sequence_id from manta_sequence where sequence_name = @sequence_name

-- print 'name' + @sequence_name
-- print 'id' + str(@sequence_id)

SET NOCOUNT OFF /* Enable all rows affected messages */

return @sequence_id

END
go

Power builder call to procedure is:

long ll_sequence_id
string ls_sequence_name

ls_sequence_name = 'TEST'

declare manta_sequence procedure for manta_sequence_proc
@sequence_name = :ls_sequence_name,
@sequence_id = :ll_sequence_id output;
exec manta_sequence;

messagebox('TEST', STRING(ll_sequence_id))
// it shows 0 not en expected number

Can anyone help?
Thanks

View Replies !

Problem Executing A Stored Procedure From VB5
I would like to execute a Stored Procedure that contains many remote server EXECUTE`s, using VB5 SP3, SQL Server 6.5 SP4,
SQL Server ODBC Driver version 3.70, and RDO 2.0:

VB5 Code:

Set db = rdoEnvironments(0).OpenConnection("", Connect:="SERVER=NT40;DRIVER={SQL SERVER};DATABASE = RyDConversion;UID=" & strUser & _
";PWD=" & strPassword & ";DSN=``", Prompt:=rdDriverNoPrompt, ReadOnly:=True)
db.Execute "{CALL ConvertirIncidentes}" `, rdExecDirect

Server 1:

CREATE PROCEDURE ConvertirIncidentes AS
EXECUTE NT40B.RyDConversion.dbo.ConvertirIncidentes
GO

Server 2:

CREATE PROCEDURE ConvertirIncidentes AS
BEGIN
BEGIN TRANSACTION
DECLARE @UltimoIncidente VARCHAR(12)

SELECT @UltimoIncidente = CONVERT(VARCHAR(12),ISNULL(MAX(I.Numero_de_inciden te),1))
FROM DIN.dbo.Incidentes I

EXECUTE(" INSERT DIN.dbo.Incidentes
EXECUTE NT40.RyDConversion.dbo.ObtenerIncidentes " + @UltimoIncidente + " , 1 ")

IF @@ERROR <>0
BEGIN
ROLLBACK TRANSACTION

SELECT STATUS = 1,
DESCRIPCION = "Error inserting in table `Incidentes`"
RETURN
END

/*Repeat this for 5 more tables*/

COMMIT TRANSACTION
END

raises the error:

S1000: [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream

I tried with DAO ODBCDirect & ADO and it works wrong the same

(only from VB, when executes from Enterprise manager works ok)

This is the only SP that raises the error, the others (including with remote calls to SP) executes perfectly.

Looking forward your answer as soon as possible
Thank you

View Replies !

Bad Token When Executing Stored Procedure
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

Kees Visser

View Replies !

Executing A Stored Procedure From An ASP Page
I am trying to execute a stored procedure from an ASP page.

Does anyone know what the syntax would look like?
I have done several queries to get recordsets but I have not executed a stored procedure.

The queries typically go something like this;
.
…
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open MYCONNECTIONSTRING

Set rs = PPS.Execute("My SQLstring”)

To execute the stored procedure would you need a recordset object?

Would the stored procedure be put in a variable and the used as a parameter in the execute method?

Sqlstring = “MyStoredProcedure GO”:

cnn.execute(Sqlstring)

Any help would be appreciated

Thanks

View Replies !

Error Executing Stored Procedure
When I execute a stored procedure, I'm receiving errors such as:

Changed language setting to us_english.
.
.
.
.

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionRead (recv()).
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.

Connection Broken

Has anyone seen these errors before? Any suggestions for how to fix it?
I would appreciate it.
Thanks!
Toni

View Replies !

Error Executing A Stored Procedure
To run this stored procedure we have to logon as user:fxweb01. For this user we have granted role to execute the XP_CMDSHELL extended procedure. But when we run this we get the following error.

Msg 50001, Level 1, State 50001

xpsql.c: Error 1314 from LogonUser on line 476

We are able to run the same when we log on as SA.

Are we missing anything.

Help.

-Rajesh

View Replies !

Executing A Script From A Stored Procedure

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

Basically stored procedure is ...

select @cmd = 'sqlcmd -E -d ' + @dbname + ' -i "' + @filelocation + @proc + '.sql" '
exec master..xp_cmdshell @cmd

Is there a way to execute sql scripts within a stored procedure without using xp_cmdshell?

Thanks!!

View Replies !

Get Records After Executing A Stored Procedure
Hi All,

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.

EXEC [dbo].[ETLloadGROUPS]
  @countRun =?,
  @startTime =?,
  @endTime = ?

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.

Am I missing something here ? Please Advice

Thanks in Advance

View Replies !

Executing Stored Procedure With Variables
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.

Public Sub Main()

Dim vars As Variables

Dim DropVariable As String

Dim CreateVariable As String

Dim InsertVariable As String

DropVariable = "Execute dbo.[sp_DropTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

CreateVariable = "Execute dbo.[sp_CustomTables] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

InsertVariable = "Execute dbo.[sp_InsertTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

Dts.VariableDispenser.LockOneForWrite("varDropTable", vars)

Dts.VariableDispenser.LockOneForWrite("varCreateTable", vars)

Dts.VariableDispenser.LockOneForWrite("varInsertTable", vars)

Dts.Variables("varDropTable").Value = DropVariable

Dts.Variables("varCreateTable").Value = CreateVariable

Dts.Variables("varInsertTable").Value = InsertVariable

'MsgBox(Dts.Variables("varDropTable").Value)

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

View Replies !

Executing SP_ADDLINKEDSRVLOGIN From A Stored Procedure
I have connected Oracle to SQLExpress manually using Linked Servers. The linked server name is DELTA. It is working perfect. Of course the login-mapping has been defined manually.

I need to automate LOGIN and USER creation (database USER_CODE = LOGIN_CODE) based on a table where I will store the USER_CODE and PASSWORD. I managed to automate the LOGING and USER creations thanks to a friend from this forum but I'm trying to user SP_ADDLINKEDSRVLOGIN inside a stored procedure to automate login_mapping creation but it is not working inside a transaction.

Help

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

USE [mydelta]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[CREATE_NEW_ACCESS]

@NEW_ACCESS_CODE VARCHAR(255),

@NEW_ACCESS_PASSWORD VARCHAR(255)

AS

DECLARE

@SQL_SCRIPT VARCHAR(255);

BEGIN

SET @SQL_SCRIPT = 'CREATE LOGIN ' + @NEW_ACCESS_CODE + ' WITH PASSWORD =''' + @NEW_ACCESS_PASSWORD + ''' ,DEFAULT_DATABASE = mydelta ,CHECK_POLICY = OFF';

EXEC (@SQL_SCRIPT);

SET @SQL_SCRIPT = 'CREATE USER ' + @NEW_ACCESS_CODE + ' FOR LOGIN ' + @NEW_ACCESS_CODE;

EXEC (@SQL_SCRIPT);

-- EXEC SP_ADDLINKEDSRVLOGIN 'DELTA' , 'false' , @NEW_ACCESS_CODE , 'orasysremote' , 'orasysremote';

SET @SQL_SCRIPT = 'EXEC SP_ADDLINKEDSRVLOGIN '+'''DELTA'''+','+'''false'''+','+''''+@NEW_ACCESS_CODE+''''+','+'''orasysremote'''+','+'''orasysremote''';

EXEC (@SQL_SCRIPT);

END
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

View Replies !

Executing Stored Procedure In A Select
i wanna execute a stored procedure in a select and use its return type
i.e

select name from table1 where id = sp 1,1

i executed it and occurred an error

help me pls

View Replies !

Rename A Stored Procedure In Server Management Studio
How do you rename a stored procedure in SQL Server Management Studio? I tried right clicking on the stored procedure and I don't see a Rename option.

View Replies !

Executing SSIS From Stored Procedure - Need To Get Return Value Into ASP.NET
I'm executing an SSIS package using the following stored procedure
ALTER PROC [dbo].[SSISRunBuildSCCDW] AS

BEGIN

DECLARE @ServerName VARCHAR(30), @ReturnValue int, @Cmd varchar(1000)

SET @ReturnValue = -1
SET @ServerName = 'myserver'

SET @Cmd = 'DTExec /SER ' + @ServerName + ' ' +
' /SQL ' + 'BuildSCCDW ' --Location of the package stored in the mdb
--' /CONF "\ConfigFilePath.dtsConfig" ' +
--' /SET Package.Variables[ImportUserID].Value; ' +
--' /U "LoginName" /P "password" '

EXECUTE @ReturnValue = master..xp_cmdshell @Cmd, NO_OUTPUT
RETURN @ReturnValue
--SELECT @ReturnValue [Result]

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?

View Replies !

Diff B/w Executing As Stored Procedure And Script
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

View Replies !

Executing A Variable Inside A Stored Procedure
Hello :) I need to do something like this:

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.

Thank you

View Replies !

Executing An MS SQL Stored Procedure From A Java Servlet
I'm trying to use a servlet to process a form, then send that data toan SQL server stored procedure. I'm using the WebLogic 8 App. server.I am able to retrieve database information, so I know my applicationserver can talk to the database.I've determined the failure occurs when the the following statement isexecuted: cstmt.execute(); (due to the failure of println statementsplaced afterwards). I get the following error after trying to executethe stored procedure call:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find storedprocedure 'insertTheForm'The username and password i'm using to connect is a Windows user withadmin rights. It is also associated with the Odbc connection--and ofcourse is a database user..with full rights. I have executablepermissions on the stored procedure set up as well. I did a microsoftrecommended registry fix as well (for a previouserror:http://support.microsoft.com/defaul...;en-us;Q238971).Am I missing something? I posted my servlet code below.Thanks for any help!DineshformHandlingServlet.class-------------------------package showme;/** formHandlingServlet.java** Created on July 6, 2003, 7:01 PM*/import javax.servlet.*;import javax.servlet.http.*;import java.io.*;import java.sql.*;import java.text.DateFormat;/**** @author Administrator*/public class formHandlingServlet extends HttpServlet {private static final String email1 = "email";private static final String password1 = "password1";private static final String password2 = "password2";private static final String displayname = "displayname";Connection dbConn = null;// create a persistent conneciton to the SQL serverpublic void init() throws ServletException{String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";String dbURL = "jdbc:odbc:Con2";String usernameDbConn = "dinesh";String passwordDbConn = "werty6969";try{Class.forName(jdbcDriver).newInstance();dbConn = DriverManager.getConnection(dbURL, usernameDbConn,passwordDbConn);}catch (ClassNotFoundException e){throw new UnavailableException("jdbc driver not found:" + dbURL);}catch (SQLException e){throw new UnavailableException("error: " + e);}catch (Exception e){throw new UnavailableException("error: " +e);}}public void doPost(HttpServletRequest request, HttpServletResponseresponse) throws ServletException, IOException{response.setContentType("text/plain");PrintWriter out = response.getWriter();//extract parameter information from register.jspString email1 = request.getParameter("email1");String password1 = request.getParameter("password1");String password2 = request.getParameter("password2");String displayname = request.getParameter("displayname");try{//make a callable statement for a stored procedure.//It has four parametersCallableStatement cstmt = dbConn.prepareCall("{call insertTheForm(?, ?, ?, ?)}");//set the values of the stored procedure's input parametersout.println("calling stored procedure . . .");cstmt.setString(1, email1);cstmt.setString(2, password1);cstmt.setString(3, password2);cstmt.setString(4, displayname);//now that the input parameters are set, we can proceed to execute theinsertTheForm stored procedurecstmt.execute();out.println("stored procedure executed");}catch (SQLException e){throw new UnavailableException("error: " + e);}}}

View Replies !

Access Not Executing Stored Procedure Correctly
Hi guys I cant seem to get my stored procedure to execute properly through Access Xp. Do you think there is something wrong with my stored procedure??

CREATE PROCEDURE [insert_ConditionalLicense_UpdateFromTerms]
(@TM_# [int],
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@SS# [nvarchar](50),
@Birthdate [nvarchar](50),
@reasonforconditional [ntext],
@Notes [ntext])

AS INSERT INTO [GamingCommissiondb].[dbo].[ConditionalLicense_View]
( [TM #],
[FirstName],
[LastName],
[SS#],
[reasonforconditional],
[ConditionalStart Date])

SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATION-COND'
IF @@Error <> '0'
RETURN

when I execute it through a command button this is the message I get "paramater" not quite sure why I am getting this message

View Replies !

Occasional Error When Executing CLR Stored Procedure
This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:
<code>
Private Shared Sub GetReportParameters()
        Dim drReportParameters As SqlDataReader

        Try
            ' set the parameters for the command request.
            _SQLCommandRequest = _SQLConnection.CreateCommand()

            _SQLCommandRequest.CommandText = _spReportParms

            _SQLCommandRequest.CommandType = CommandType.StoredProcedure

            ' get the information for the data reader request.
            drReportParameters = _SQLCommandRequest.ExecuteReader

            ' make sure that we have something first.
            If Not drReportParameters Is Nothing Then

                ' find out if we have any rows returned.
                If drReportParameters.HasRows Then

                    ' read each of the rows looking for the respective value.
                    While drReportParameters.Read

                        ' make sure that we can get the appropriate code id.
                        If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

                            ' set the parameters for each of the following internal variables.
                            Select Case drReportParameters.Item("Code_ID").ToString
                                Case Is = "PDFP Path"
                                    _FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
                                Case Is = "PDF Temp Path"
                                    _FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
                                Case Is = "LogFile Path"
                                    '''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
                                    _FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
                                Case Is = "LogFile Flag"
                                    _swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
                                Case Is = "Delete PDF"
                                    _DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
                                Case Is = "PrintTool"
                                    _PrintTool = drReportParameters.Item("Display_Text").ToString
                                Case Is = "BPP Path"
                                    _BppPath = drReportParameters.Item("CharVar1").ToString
                                Case Is = "SendToPrinter"
                                    _SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
                                Case Else
                                    ' ignore it.
                            End Select
                        End If
                    End While
                End If
            End If
        Catch ex As Exception
            ' throw a new exception to trip up the PrintReport() function.
            Throw New Exception(ex.Message)
        Finally
            ' make sure that we close out the datareader, regardless.
            If Not drReportParameters Is Nothing Then
                If Not drReportParameters.IsClosed Then drReportParameters.Close()
            End If
        End Try
    End Sub
</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

View Replies !

Executing Stored Procedure Impersonation Question
As a bit of background first, I'm trying to write a CLR stored proc that will start/stop a Windows Service using the ServiceController class.

The problem I'm having is that the stored proc gets run as NT AUTHORITYNETWORK SERVICE - ie the user the SQLServer Windows Service runs as. This user doesn't have adequate permissions to start/stop a Windows Service (the user only has permission to view the service's status).

The Window's user who is connected to the db - executing the stored proc, does however have adequate permission to start/stop the Windows Service. I'd like to have someway of running the code in the stored proc as if it were this user. If someone could point me in the right direction I'd appreciate it.

View Replies !

Stored Procedure To Executing An SSIS Package
In SQL Server 2005 I need a stored procedure that will execute an SSIS Package for me. There is some earlier stuff on the board but I don't understand it. I don't want to create a Job to do it if I don't have to.

Thanks,

George Cooper

View Replies !

Executing SSIS Package From Stored Procedure
Hey guys,
I've got a problem here. I need to send the query result to a csv file then transfer the file to a website. I thought this is a good candidate for a SSIS package. The package is ready now but I don't know how can I execute it from within a stored procedure.
I thought sp_OA family of extended procedure would be helpfull. After following steps:
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT

EXEC @hr1 = sp_OAMethod @oPKG, 'LoadFromSQLServer("foo", ,, 256, , , , "foo1")', NULL

EXEC @hr1 = sp_OAMethod @oPKG, 'exec'
EXEC @hr1 = sp_OADestroy @oPKG
it tells me command execute successfully. But no package actually gets executed and I can see no results
Thanks

View Replies !

Problems Executing Stored Procedure OLEDB
<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

View Replies !

Executing An Oracle Stored Procedure From SSIS
Hello,

Is there an oracle provider out there that will let me invoke a parameterless stored procedure that is in a package in my Oracle source?

Better could that stored proc receive a prameter?

Still better, could I use a stored proc in a OLEDB source component and get the resutls from its only out variable (ref cursor) into my SSIS dataflow?

I haven't been able to get any of these basic functionalities working with either the Oracle OLEDB or the Microsoft OLEDB for Oracle provider...

If not, are there any plans to enahnce the MS provider to handle that?

A more tricky question :
Why does the ReportingService data processing extension for Oracle sources allow such things and not the .NET provider in SSIS?

Thanks

View Replies !

Executing A Job From A Stored Procedure Stopped Working

I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:

CREATE PROCEDURE xxxxx
WITH EXECUTE AS 'domainusername'

AS

EXEC msdb.dbo.sp_start_job B'jobname' ;

RETURN

The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.

This worked for the past several months.

Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'

I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.

Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.

Thanks - SM

View Replies !

Issue Executing Stored Procedure Using Constraints

I have been uisng the latest MSSQL JDBC jar.
I have a Stored procedure which inserts data into RTFTickerData table which had the following type of constraint 'Check_RTFTickerData' defined in it.

Constraint details: (len(ltrim([TickerID])) > 0 and len(ltrim([PrimaryFeedID])) > 0 and len(ltrim([PrimaryServiceProvider])) > 0 and len(ltrim([LastUpdateBy])) > 0 and len(ltrim([LastUpdateApp])) > 0 and len(ltrim([LastUpdateFrom])) > 0 and [ScalingFactor] <> 0.0)

Now when i try to execute this SP I get an error

To call this stored procedure I use Callable statement.If I use a simple Statement it works fine.I want it to work using CallableStatement only.

UPDATE statement conflicted with TABLE CHECK constraint 'Check_RTFTickerData'. The conflict occurred in database 'PJ_Adaptiv', table 'RTFTickerData'.

If I try using older version of the JDBC driver it works fine.

Please help me out solving this issue using the latest jar?Is there anything that I have lto set in the code ?

View Replies !

Executing A Stored Procedure In ActiveX Script
Hi!

How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.

Thanks.

$3.99/yr .COM!
http://www.greatdomains4less.com

View Replies !

Different Results When Running Procedure From Management Studio Vs Application Code
I'm updating a process that recreates a large table every night. The table is the result of a bunch of nightly batch processes and holds a couple million records. In the past, each night at the end of the batch jobs the table would be dropped and re-created with the new data. This process was embodied in dynamic sql statements from an MFC C++ program, and my task is to move it to a SQL Server 2000 stored procedure that will be called from a .Net app. Here's the relevant code from my procedure:
sql Code:

Original
- sql Code

-- recreate new empty BatchTable table
print 'Dropping old BatchTable table...'
exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table

-- validate drop
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'Old BatchTable dropped.'
End

print 'Creating new BatchTable...'
SELECT TOP 0 *, cast('' as char(3)) as Client, cast('' as char(12)) as ClientDB
INTO dbo.BatchTable
FROM differentDB.dbo.BatchArchives

--validate create
If Not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'New BatchTable Created.'
End

-- recreate new empty BatchTable table print 'Dropping old BatchTable table...' exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table -- validate drop IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'Old BatchTable dropped.' END print 'Creating new BatchTable...' SELECT TOP 0 *, CAST('' AS CHAR(3)) AS Client, CAST('' AS CHAR(12)) AS ClientDB INTO dbo.BatchTable FROM differentDB.dbo.BatchArchives --validate create IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'New BatchTable Created.' END

The print statements are there because the .net app will read them in and then write them to a log file. Some of the other mechanics are there to mimic the old process. The idea is to duplicate the old process first and then work on other improvements.

This works in Management studio. The .Net App reports that the old table was dropped, but when it tries to create the new table it complains that "There is already an object named 'BatchTable' in the database." I have verified that the old table is removed.

Any ideas on how to fix this?

View Replies !

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window