Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts

Tuesday, March 27, 2012

@@identity

hi,

I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:


SELECT
@.OrderID = @.@.Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
OrderID,
ProductID,
Quantity,
UnitCost
)
SELECT
@.OrderID,
ShoppingCart.ProductID,
ShoppingCart.Quantity,
Prices.UnitCost
FROM
ShoppingCart INNER JOIN
Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
CartID = @.CartID

is there any way to rewrite this statement so that I can put it in the form insert()values(). ?is OrderID an autonumbering field?

if so - you shouldn't include it in your INSERT statement|||YEs it is ! Why do you not recommend putting it in the insert stetment ?

Anyways, I realized that my error wasn't really in the stored procedure, but in the function calling it.

Thanks.|||SQL will put the identity into the table automatically, what you're trying to do is over-write the value with the same number again...hence the duplicate key problem. So like the poster said, don't do it, get the help out and read what an identity column acutally is and how to use it.|||I'm not really overwriting the @.@.identity value, but assigning it to a variable. I then use the value of that variable in an insert statement on a totally separate table.
I solved my primary key issue and it wasn't related to the sql transaction i posted here, ....there was a logic error in my code.

So now even though its all working, you still think that the sql statement I'm using is incorrect ?|||Sorry I admit I was assuming the code that went before your example. The only suggest I would make would be to consider using SCOPE_IDENTITY rather than IDENTIY, tends to be safer in the long run.sql

@@FETCH_STATUS... one per simultaneous execute? Hope not!

Hello,
In another question i am using @.@.FETCH_STATUS . Below is an excerpt from SQL
Books Online.
"Because @.@.FETCH_STATUS is global to all cursors on a connection, use
@.@.FETCH_STATUS carefully".
Sounds silly but can anyone define a "connection" for me with respect to Sql
Server. I dont want to get caught out by using this construct. Does the
above mean "watch yourself if your doing things like nesting udfs and stored
procs etc" or does it mean "dont use this construct in high volume
environments like a web application".
In other words if my DAL has a 2 users calling the same procedure at
precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
right? Because they are on different connections.
cmdSelect.Connection.Open
Or does SQL Server "define" a connection differently?
TIA
Ian
> In other words if my DAL has a 2 users calling the same procedure at
> precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
> right? Because they are on different connections.
That's right... these are different connections.
HTH
Kalen Delaney
www.solidqualitylearning.com
"Ian Evitable" <whole@.hog.com> wrote in message
news:%238eKHzctFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> In another question i am using @.@.FETCH_STATUS . Below is an excerpt from
> SQL
> Books Online.
> "Because @.@.FETCH_STATUS is global to all cursors on a connection, use
> @.@.FETCH_STATUS carefully".
> Sounds silly but can anyone define a "connection" for me with respect to
> Sql
> Server. I dont want to get caught out by using this construct. Does the
> above mean "watch yourself if your doing things like nesting udfs and
> stored
> procs etc" or does it mean "dont use this construct in high volume
> environments like a web application".
> In other words if my DAL has a 2 users calling the same procedure at
> precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
> right? Because they are on different connections.
> cmdSelect.Connection.Open
> Or does SQL Server "define" a connection differently?
> TIA
> Ian
>|||Kalen
if you are executing two cursors at the same time, then you will definitely
face problem. if latest fetch status for the other cursor is 1 or 2, by the
time you are in cursor1 then latest fetch status for the cursor1 is 1 or 2
not zero(though actually not)
In otherwords it is like @.@.identity but specific to connection.
Regards
R.D
"Kalen Delaney" wrote:

>
> That's right... these are different connections.
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Ian Evitable" <whole@.hog.com> wrote in message
> news:%238eKHzctFHA.3500@.TK2MSFTNGP09.phx.gbl...
>
>|||That's true, but the OP just wanted to know about connections. He seemed
pretty clear that the docs said the @.@.FETCH_STATUS was the last fetch on the
connection, so if you have two or more on the SAME CONNECTION, you can have
problems.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:29EA91F8-AA2B-476F-BFBB-938840F57BF1@.microsoft.com...
> Kalen
> if you are executing two cursors at the same time, then you will
> definitely
> face problem. if latest fetch status for the other cursor is 1 or 2, by
> the
> time you are in cursor1 then latest fetch status for the cursor1 is 1 or 2
> not zero(though actually not)
> In otherwords it is like @.@.identity but specific to connection.
> Regards
> R.D
>
> "Kalen Delaney" wrote:
>
>|||Yes thanks RD but after Kalens' original clarification i now understand. The
idea of what constitutes a connection in Sql Server is the same as in
dotNet.
If however i want to something really fancy using multiple cursors on the
same connection then i should take extra special care that my use of
@.@.Fetch_STATUS is acting on the/signalling the status of the cursor at hand.
I thought that was the way it would be but i needed to be sure least i get
stuck debugging some wild goose chase.
Thanks again.
Ian
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23nBoMZjtFHA.1472@.TK2MSFTNGP15.phx.gbl...
> That's true, but the OP just wanted to know about connections. He seemed
> pretty clear that the docs said the @.@.FETCH_STATUS was the last fetch on
the
> connection, so if you have two or more on the SAME CONNECTION, you can
have
> problems.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:29EA91F8-AA2B-476F-BFBB-938840F57BF1@.microsoft.com...
2
the
>
>sql

@@FETCH_STATUS Reset

Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @.@.Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?

declare @.entityid int, @.datafrom nvarchar (255),

@.datato nvarchar (255), @.updatedate datetime

declare statustime1 cursor

local scroll static

for

select audit.entityid, audit.datafrom, audit.datato, audit.updatedate

from audit

, orders

where audit.entityid = orders.orderid

and audit.entitytypeid = 4

order by orders.orderid, audit.updatedate desc

open statustime1

While @.@.fetch_status = 0

Begin

fetch next from statustime1

into @.entityid, @.datafrom, @.datato, @.updatedate

print @.entityid

end

close statustime1

deallocate statustime1

go

The main thing that you are doing 'wrong' is using a CURSOR.

SQL Server operates most efficiently handling SET based data. (NOT row-wise data). A major mistake for most developers is keeping the 'recordset' mindset.

CURSORS are considered by some to be 'evil incarnate', whereas others recognize that there is a very small and highly limited use for them.

Most likely, your operation can be handled in a single set based query, with orders of magnitude imporvements in speed and reduced blocking.

If you want to post what you are attempting to accomplish, we may be able to better direct you to a set base solution.

But the main thing to understand is that in your learning, avoid using CURSORs. Push yourself to master set based logic. You will be much happier with the results.

|||

Thanks very much. I need to calculate the number of business days between two status events. Each is recorded in a row in the table, eg,

Order Event1 Date

1 Open June 1

1 Hold June 10

1Close June 30

2 Open July 1

2 hold July 10

I plan to fetch a row, save the values, fetch another row and calculate the days, continuining unitl a change in order and write output accordingly.

If you can suggest a set based approach I am all for it, however can anyone tell me why the @.@.fetch_status is not being reset.

|||

There are a few 'tools' that make the DBA's job much simplier. One of those tools is have a Calendar table in the database.

With a Calendar table, it is simple to JOIN against other tables with date values and derive the span of time between such dates. You may explore using a Calendar table with this resource.

With a Calendar table, you can easily meet your requiremens with a single query.

You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.

|||

I actually have a time dimension table so that will be a help, but can you show how we can get the data to do the subtraction between the two dates using set based approach. The trick is bringing together two rows that follow each other in sequence for the same order. I can't figure how to JOIN to do that.

I agree on fetch first outside the loop and so on, but that does not help with the question. Why does @.@.fetch_status still have a -1 even after the close and deallocate? It only seems to reset when I disconnect and that can't be right.

Thanks,

|||

You can use the function to fetch this data..

Sample.,

Code Snippet

Create function dbo.TotalBusinessDays

(

@.Start datetime,

@.End Datetime

)

returns int

as

Begin

Declare @.Result as int;

Set @.Result = 0;

While @.Start<=@.End

Begin

Select @.Result = @.Result + Case When datepart(weekday,@.Start) in (1,7) Then 0 Else 1 End

Set @.Start = @.Start + 1

End

return @.Result;

End

Go

Code Snippet

Create Table #events (

[Order] int ,

[Event1] Varchar(100) ,

[Date] datetime

);

Insert Into #events Values('1','Open','June 1 2007');

Insert Into #events Values('1','Hold','June 10 2007');

Insert Into #events Values('1','Close','June 30 2007');

Insert Into #events Values('2','Open','July 1 2007');

Insert Into #events Values('2','hold','July 10 2007');

Select

[Order]

,dbo.TotalBusinessDays([Open],[Hold]) OpenToHold

,dbo.TotalBusinessDays([Hold],[Close]) HoldToClose

From

(

Select

[Order]

, Max(case when [Event1] = 'Open' Then [Date] end) as [Open]

, Max(case when [Event1] = 'Hold' Then [Date] end) as [Hold]

, Max(case when [Event1] = 'Close' Then [Date] end) as [Close]

from

#events

Group bY

[Order]

) as Data

|||

I am impressed, looks pretty good. I may have oversimplied the example however,so how would it work for this data?

Order Event1 Date

3 Open July 1

3 Hold July 10

3 open july 20

3 hold Aug 1

3 open Aug 3

3 fill Aug 20

And I still would like to know why @.@.fetch_status stays -1 after I close and even deallocate the cursor.

|||

@.@.FETCH_STATUS is similar to a static variable with connection scope.

It holds the last know value.

If you try this, in a new connection window, without a CURSOR:

SELECT @.@.FETCH_STATUS

It will return [ 0 ].

It will reset on the next execution of FETCH.

|||

Bingo! You just solved it for me. I was being dense, and I was missing the point that @.@.fetch_status resets on the execution of fetch. It is not reset on open cursor. So I really need the first fetch outside the while loop and problem solved! My confusion is I am too familiar with that other DB (evil DB2) where SQLCODE would be zero after the open (lol) ..

Very interesting discussion and if you can think of set process approach to handle the more general case of multiple status changes during the order I would love to hear it.

|||

Here another sample,

Code Snippet

Create Table #orders (

[Order] int ,

[Event1] Varchar(100) ,

[Date] datetime

);

Insert Into #orders Values('3','Open','July 1 2007');

Insert Into #orders Values('3','Hold','July 10 2007');

Insert Into #orders Values('3','open','july 20 2007');

Insert Into #orders Values('3','hold','Aug 1 2007');

Insert Into #orders Values('3','open','Aug 3 2007');

Insert Into #orders Values('3','fill','Aug 20 2007');

Select * into #temp from #orders order By 1,3

Alter table #temp Add RowId int Identity(1,1)

select

[Pre].[Order],

[Pre].[Event1] +' to ' + [Post].[Event1],

dbo.TotalBusinessDays([Pre].[Date],[Post].[Date])

from

#temp [Pre]

Join #temp [Post] on [Pre].RowId = [Post].RowId-1

|||Since everyone was so nice to tell you why cursors are bad and give alternatives, and no one has answered your question:

The problem with your code is you need to change it to:

open statustime1

fetch next from statustime1

While @.@.fetch_status = 0

Begin

print @.entityid

fetch next from statustime1

into @.entityid, @.datafrom, @.datato, @.updatedate

end

In your current code you never did a fetch, so @.@.fetch_status is uninitiallized.

|||

Actually Tom, I think that I did answer that question.

Arnie Rowland wrote:

You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.

And

Arnie Rowland wrote:

@.@.FETCH_STATUS is similar to a static variable with connection scope.

It holds the last know value.

...

It will reset on the next execution of FETCH.

|||I am sorry Arnie, I miss that one. :< You did answer the question.

@@error for dynamic SQL

Does anyone know of a simple, correct way to retrieve the value of @.@.error from dynamic SQL? I can't use:SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

EXECUTE (@.cCmd)
SELECT @.@.error -- only shows locally reported error

EXECUTE sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported errorI can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?

-PatPI'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).

QUERY:
Set Concat_Null_Yields_Null Off

Declare @.cCmd nvarchar(4000)
Declare @.cServer nvarchar, @.cLogin nvarchar
, @.iError int

SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

Print @.cCmd + N' << The Command' + nchar(10)

EXECUTE (@.cCmd)
SELECT @.@.error As 'Error' -- only shows locally reported error

EXECUTE @.iError = sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported error

Select @.iError

RESULTS
EXECUTE @.i = [].master.dbo.sp_password NULL, 'junque', '' << The Command

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Error
----
1038

(1 row(s) affected)

Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

----
170

(1 row(s) affected)

What value do you expect for the error codes?|||I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.

When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @.@.error result.

At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.

-PatP|||Thanks - now I get it.

The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).

If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.

WRAPPER
create procedure my_sp_password
@.old sysname = NULL, -- the old (current) password
@.new sysname, -- the new password
@.loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @.self int, @.error int
select @.self = CASE WHEN @.loginame is null THEN 1 ELSE 2 END
, @.error = -1

-- RESOLVE LOGIN NAME
if @.loginame is null
select @.loginame = suser_sname()

-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @.self = 1
begin
dbcc auditevent (107, @.self, 0, @.loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
select @.error = 15210
return (@.error)
end
ELSE
begin
dbcc auditevent (107, @.self, 1, @.loginame, NULL, NULL, NULL)
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@.@.trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
select @.error = 15002
return (@.error)
end

-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @.loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@.loginame)
select @.error = 15007
return (@.error)
end

-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@.self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @.loginame and isntname = 0
AND sysadmin = 1) )
SELECT @.self = 1

-- CHECK OLD PASSWORD IF NEEDED --
if (@.self = 1 or @.old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @.loginame and
( (@.old is null and password is null) or
(pwdcompare(@.old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
select @.error = 15211
return (@.error)
end

-- CHANGE THE PASSWORD --
exec sp_configure 'allow', 1
Reconfigure
set @.error = @.@.error

if @.error = 0
begin
-- update master.dbo.sysxlogins
-- set password = convert(varbinary(256), pwdencrypt(@.new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
-- where name = @.loginame and srvid IS NULL
exec @.error = sp_password @.old, @.new, @.loginame
end

-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
if @.error = 0
begin
exec('use master grant all to null')
set @.error = @.@.error
end

-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @.error <> 0
return (@.error)
raiserror(15478,-1,-1)
set @.error = 15478
return (@.error) -- sp_password

EXECUTION
Declare @.error int
Select @.error = -1
exec @.error = my_sp_password 'fred', 'ted', 'sam'
Select @.error|||Or

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
GO

DECLARE @.cCmd varchar(8000), @.cServer sysname, @.cLogin sysname
SELECT @.cServer = @.@.SERVERNAME, @.cLogin = SYSTEM_USER

SELECT @.cCmd = 'DECLARE @.i int' + CHAR(13)
+ 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''test'', '''
+ @.cLogin + ''''+ CHAR(13)
+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
+ 'SELECT ' + CONVERT(varchar(15),@.@.SPID) + ', ''sp_password'''+', @.@.ROWCOUNT, @.@.ERROR, @.i' + CHAR(13)

SELECT @.cCmd

EXECUTE (@.cCmd)

SELECT * FROM ProcessLog o
WHERE Spid = @.@.SPID
AND Add_Dt IN ( SELECT MAX(Add_Dt)
FROM ProcessLog i
WHERE i.Spid = o.Spid)
GO

SET NOCOUNT OFF
DROP TABLE ProcessLog
GO|||Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.

-PatP|||How does your first one work?

Doesn't it return an error?

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.

?

I didn't test it remotely, are you saying it won't work?|||What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.

The first bit of logic works nicely when the code is being executed on the local server (in other words, when @.cServer = @.@.servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @.@.error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.

As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.

-PatP|||DECLARE @.j INT
CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
INSERT INTO #temp (result)
EXEC @.j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
SELECT @.j
IF @.j = -100
BEGIN
SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
FROM #temp
WHERE id = 1
END

DROP TABLE #temp

NOTE: I don't have a remote server to test it with at home.

Tim S|||I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:SET @.cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', '''
+ @.cLogin + ''' EXIT(SELECT @.@.error)" -s ' + @.cServer'

EXECUTE @.iRC = master.dbo.xp_cmdshell @.cCmdTo simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.

-PatP

Thursday, March 22, 2012

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')
help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')
|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
located[vbcol=seagreen]
on
>
>.
>
|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> you will have to use
> message
> located
> on
I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx
.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1ca6d01c452e8$ee0594d0$a501280a@.phx
.gbl...
located[vbcol=seagreen]
on[vbcol=seagreen]
>
>.
>|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> you will have to use
> message
> located
> on

Tuesday, March 20, 2012

Does anyone know how to:
When i execute a quesry, ex DBCC SHOWCONTIG, how do i edit
out the DBCC execution completed. If DBCC printed error
messages, contact your system administrator part? I am
running a cursor and i keep getting this answer back..If you don't want that line to show up, you can use the
with NO_INFOMSGS option. For instance:
DBCC SHOWCONTIG('authors') with NO_INFOMSGS
This option doesn't seem to be documented in the BOL for
DBCC SHOWCONTIG. But it works with SQL2000 SP3. I didn't
check any other version.
Linchi
>--Original Message--
>Does anyone know how to:
>When i execute a quesry, ex DBCC SHOWCONTIG, how do i
edit
>out the DBCC execution completed. If DBCC printed error
>messages, contact your system administrator part? I am
>running a cursor and i keep getting this answer back..
>.
>
I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
>> I created a store procedure in a database that is
located
>> on my machine. i want to execute that store procedure
on
>> another machine in which i have a linked server. Can
>> someone tell me how to execute it
>> ex: insert into #tmp openquery (link_server_name, 'exec
>> xpto')
>
>.
>|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx.gbl...
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> >--Original Message--
> >help
> >You have a good subject for this post
> >Try
> >EXEC sp_serveroption 'Server', 'Data Access', TRUE
> >GO
> >SELECT *
> > INTO tbl
> > FROM OPENQUERY('Server', 'EXEC usp') ;
> >
> >If you have #temp tables used in your stored procedure,
> you will have to use
> >SET FMTONLY OFF while calling the procedure like:
> >
> >SELECT * INTO tbl
> > FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
> >
> >
> >
> >"help" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> >> I created a store procedure in a database that is
> located
> >> on my machine. i want to execute that store procedure
> on
> >> another machine in which i have a linked server. Can
> >> someone tell me how to execute it
> >>
> >> ex: insert into #tmp openquery (link_server_name, 'exec
> >> xpto')
> >
> >
> >.
> >

/logger option

Hi,

I'm trying to use the below command to execute for generating the log file for SSIS package.

DTExec /FILE "C:\Documents and Settings\CP0808\My Documents\Test.dtsx" /logger "DTS.LogProviderTextFile;c:\log.txt"

But it got the error as below. Please advise.

Started: 10:03:20 AM
Error: 2006-10-06 10:03:21.15
Code: 0xC001000E
Source: Test
Description: The connection "c:\log.txt" is not found. This error is thrown b
y Connections collection when the specific connection element is not found.
End Error
Error: 2006-10-06 10:03:21.18
Code: 0xC001000E
Source: Test
Description: The connection "c:\log.txt" is not found. This error is thrown b
y Connections collection when the specific connection element is not found.
End Error
Error: 2006-10-06 10:03:21.21
Code: 0xC02020EA
Source: Test Log provider "{1AEAB490-1124-4A84-981F-7C1FDD80A721}"
Description: The connection manager "c:\log.txt" is not found. A component fa
iled to find the connection manager in the Connections collection.
End Error

Hi,

the logger option in DTEXEC specifies an already existing connection within the package. YOu will have to create a file location within the package to reference it.

Have a look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72104

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

I still got the error as below.

DTExec /FILE "C:\Documents and Settings\CP0808\My Documents\Test.dtsx" /CONNECTION DestinationConnectionFlatFile;"c:\test.csv" /CONNECTION SourceConnectionOLEDB;"Data Source=test;User ID=user;Initial Catalog=DATA_CONV;Provider=SQLNCLI;Auto Translate=false;PASSWORD=password" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /l "DTS.LogProviderTextFile;log.txt" /Set "\package.Connections[log.txt].Properties[ConnectionString];c:\log.txt"

Error: 2006-10-10 09:57:21.07
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by C
onnections collection when the specific connection element is not found.
End Error
Error: 2006-10-10 09:57:21.10
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2006-10-10 09:57:21.14
Code: 0x8001F02F
Source: Test
Description: Cannot resolve a package path to an object in the package ".Connections[log.txt].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2006-10-10 09:57:21.17
Code: 0x80012017
Source: Test
Description: The package path referenced an object that cannot be found: "\package.Connections[log.txt].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \package.Connections[log.txt].Properties[ConnectionString]
value to c:\log.txt.
Started: 9:57:20 AM
Finished: 9:57:21 AM
Elapsed: 0.5 seconds

Friday, February 24, 2012

.exe from stored procs

Hey guys.

I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

Thanks
tiboryou can launch an exe from a proc with xp_cmdshell. you can't display any of it's output though, except for whatever it writes to stdout.

if it's a GUI app, there's no point to it. Also, remember that this app would have to be located on the server.

Be very careful with this. If you enable xp_cmdshell, it opens up a fairly large security hole on your db server. Make sure only appropriate logins have perms to execute it. don't give any clients permissions to execute it directly or you may find that a criminal has stolen all your data.|||It's not advisable

You could bring down the server from what I understand

But if you want to try you need to use master..xp_cmdshell

But if you don't know about the sproc, then I'd really advise against it|||Thanks guys

Turns out, I may be able to avoid the project in its entirety but this is good info to know for the future.

Again, very much appreciated :)|||just be careful if you ever decide to use it. xp_cmdshell is a cross between a loaded gun and a vial of nitroglycerin.|||just be careful if you ever decide to use it. xp_cmdshell is a cross between a loaded gun and a vial of nitroglycerin.

pffffftttttt

The real danger is trying to launch an application with it

Hey DROP Database is kinda dangerous as well|||the real danger is that you can use xp_cmdshell to do anything you could do from a command prompt, which is pretty much anything.

if the wrong people have rights to execute xp_cmdshell, you are in trouble. so best left disabled.|||if the wrong people have rights to execute xp_cmdshell, you are in trouble.

Of course, would you give a gun to a toddler?

so best left disabled.

Couldn't disagree more|||Tibor, don't listen to Brett.

he's a wild vocalist (http://www.rockdetector.com/artist,5489.sm) for an all-girl 80's hair-metal band. everyone knows such types can't be trusted.

:)|||belly aching laughter. on the floor. verge of tears.

but really if your shop is not locked down with regards to db server security, shame on you. Although I have been somewhere that refused to be locked down. So I left.

I find xp_cmdshell extremely useful especially combined with osql. I do my database builds that way by doing a fresh dump from source control and letting those rip with a little cursor and some logging. A little Redgate afterwards to be sure and everything is right as rain.|||Hi,

I've quite the same Problem as Tibor.
I also try to call a .exe from a trigger.
The .exe is a VB.Net command line application, that shall invoke a ETL script on a third party BI Application. The VB application first calls the getObject Function and then calls the Reload Function of the BI application.
This application works fine, when I start it in the command line.
But when I try to call it with the xp_cmdshell proc the execution stucks and after a while an exception is thrown ("ActiveX component couldn't be started" or so on)
Until now, I haven't found any way to fix this problem.
Now I'm searching for another way of calling an .exe from a trigger. I don't know, if this helps. Maybe you have any suggestions to deal with this problem
Thanks in advance

Gojo|||you want to call an exe from a trigger? pardon me, but that's a less than good idea. It's quite bad actually.

triggers need to have a very constrained scope - they shouldn't be launching other processes, sending emails, writing data to remote servers, calculating your horoscope, or anything like that.

if you do somehow figure out how to launch an exe from a trigger, the perf or your application will suffer big time.

what are you trying to achieve with this? why do you think launching an exe is the way to do it?|||triggers need to have a very constrained scope - they shouldn't be launching other processes, sending emails, writing data to remote servers, calculating your horoscope, or anything like that.

If you're using SQL Server 2005, you can use the Service Broker to do such "crazy" stuff without ending up with long running transactions.|||Thanks for the fast replies

@. jezemine
I know, the idea sonds weird. My problem is, that I want to keep my data in the dashboard event triggered.
In this case a new DB entry is a event. But when I'm constantly polling the table (as I do now) the solution isn't event triggered. The only way to achieve this, is to call the .exe (which calls the load script), when there is a new entry in the table (therefore the trigger).
Do you have another idea how to accomplish that (I mean how to tell my .exe that there's a new entry, so that the .exe can call the load script)?

@.kaffenils
Unfortunately I have to use the 2000 version

thanks
gojo|||you could write an app that polls the table every so often, and when there's a new row then it executes your load script.

I don't understand what you mean by "the solution isn't even triggered" btw.|||Currently I poll the table every 5 secs. If there's a new row, I execute my script. This works fine, but as I wrote before, this solution is not what I actually want.
I don't want a permanent polling. I want the new Table entry to be the "initiator" of the script execution. When I poll the table, the poll query is the "initiator".
I hope I could make clear, what I want.

thanks
gojo|||Maybe my Problem could be solved another way.
Is there a possibility in SQL Server 2000 to access a class which is written in .NET and which is "stored" in the Global Assembly Cache (GAC)?
Then I could access the reload command.

Thanks gojo|||I'd probably create a SQL Agent job that did whatever "heavy lifting" was required (running the ETL process, etc), then use either sp_start_job (http://msdn2.microsoft.com/En-US/library/aa260289(SQL.80).aspx) or some closely related form of trickery to actually start the job itself.

-PatP|||note that would fail if the job is already running. you can't have two instances of the same job running at once.|||Thanks for the replies.
I've found an interesting site.
http://sqljunkies.com/Article/C5A500EB-B8BE-42C0-B23B-258A342CAAAB.scuk
I think that's exactly what I want.
I hope it works.

thanks gojo|||invoking that kind of thing from a trigger still counts as a no-no in my book.|||I agree, that this shouldn't be implemented in practice.
In my case this solution is more a feasibility study, if that kind of idea (new entry as trigger of a new Data Load) can be implemented in a technical way.|||note that would fail if the job is already running. you can't have two instances of the same job running at once.Failure to start the second instance would be a bad thing? I can't imagine a case where you'd want two separate ETL loads running at once. I suppose that you could queue the subsequent jobs somehow if there was any chance that you really wanted that to happen.

-PatP|||I have no idea if that would be a bad thing or not - depends on OP's requirements. I just stated is as a fyi for the OP.|||Ok so I have been playing around some with xp_cmdshell(yes, I know...not good, lol) but when I run the .exe, my query analyzer hangs up for about 10 minutes.

exec master..xp_cmdshell 'C:\test.exe'

It is just a little vb app i created to test with but it hangs up and does not execute.

I know that I have permissions and such as i can do a simple dos directory seach with it.

Any ideas as to why it hangs up?

Thanks|||It seems to be running it in the processes list of task manager but not actually running the application. Is it possible to actually run the app?|||If your application is expecting someone to click an OK button, you will be waiting a long time.|||Yeah, I just found out that it wont allow it...so i withdraw my question. heh|||If your application is expecting someone to click an OK button, you will be waiting a long time.. ;)|||If your application is expecting someone to click an OK button, you will be waiting a long time.

really? whenever I write a trigger, i have it pop a message box to ask the user if they really want to commit the data. it works great!

:)|||In order to pass a moderate SOX audit, you would have to ask that question twice. "Are you sure you want to commit that data?" "Really sure?"

Thursday, February 16, 2012

*.sql

I want to setup a weblog and I us .Text for it. It includes several .sql files and they are there for making my database (I think). How can I execute them so they make my database?? Thanks, Roelif the db is sql server, you can run the sql scripts from the *.sql file in QA...

HTH|||To elaborate further, if you have Query Analyzer, you can do a File...Open...and open the .SQL file. This will open up the script in a readable format. Then you do Query...Execute to run it.

If you do not have Query Analyzer, you can use the command-line OSQL utility.

osql -S (local) -E -i c:\SQLScript.sql

Terri|||If I do so then there are no tables created but there are stored procedures made. Is this okay??

Roel|||Well, the stored procedure won't be able to run without the tables being there, so no, in that sense it is not OK. But both QA and OSQL will let you create the stored procedures without the tables.

How are you planning on creating the tables? Didn't they supply scripts for that, too?

Terri|||I don't know. I think you must create tables wich match witch the stored procedures. But I will go with my question to the .Text department of this forum. Thanx

Thursday, February 9, 2012

(RtlTraceDatabaseEnumerate) error

hello my friends,

I am with a problem when I try to use a software in the platform Win NT 4 WKS and Win2K PRO.
When I execute software as administrator it functions well, but when I execute as default user it of the following error in the EventViewer:

The applicatory one, generated an error of applicattion. The error occurred in 05/23/2003 to the 17:01:36.755. The generated exception was c0000025 in address 78491678 (RtlTraceDatabaseEnumerate)

Somebody knows what it could be ?

If possible, will be able to order the reply for this email rafael@.astranet.com.br I is thankful

Since already I am thankful.

Rafael T. Hirano
rafael@.astranet.com.brRefer to one of this Archive posting (http://dbforums.com/arch/29/2002/8/485860) under this forum for information.