I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?Howdy
If its through a web or VB type app, let the web app execute a stored procedure called by the app.
Cheers,
SG.|||Hey,
from a vb app or vb script you can do this function:
Public Sub ExecuteEDIPackage(FileName As Variant)
Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String
Set oPKG = New DTS.Package
' Set Parameter Values
sPackageName = "EDIPackage"
' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName
' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
' Execute
oPKG.Execute
' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next
oPKG.UnInitialize
Set oStep = Nothing
Set oPKG = Nothing
End Sub
Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts
Thursday, March 29, 2012
execure store procedure from Query Analizer
Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.com
Hi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor
|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com
|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.com
Hi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor
|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com
|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
execure store procedure from Query Analizer
Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
--
Message posted via http://www.sqlmonster.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrés Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
--
Message posted via http://www.sqlmonster.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>sql
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
--
Message posted via http://www.sqlmonster.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrés Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
--
Message posted via http://www.sqlmonster.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>sql
execure store procedure from Query Analizer
Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
---
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example belo
w.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
---
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example belo
w.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
exectuing a .bat from cmdexec
All,
sql 2000 sp3
I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
data in the files that I am creating. Below is the contents of the .bat. I
have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
connection, any other ideas would be a great help.
db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
d:\wip\ds2_wrs_ind_employee_db2.txt
db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
d:\wip\ds3_wrs_clr_employee_db2.txt
db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
d:\wip\ds4_wrs_client_db2.txt
db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
d:\wip\ds5_wrs_diary_db2.txt
db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
d:\wip\ds6_wrs_dry_history_db2.txt
db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
d:\wip\ds7_wrs_folder_control_db2.txt
--
Thanks,
SnyperSorry, the step does fail. I see it in the appliction log as a failure. I do
not know how to get the .bat to execute properly. Any ideas?
--
Thanks,
Snyper
"snyper" wrote:
> All,
> sql 2000 sp3
> I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
> data in the files that I am creating. Below is the contents of the .bat. I
> have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> connection, any other ideas would be a great help.
> db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> d:\wip\ds2_wrs_ind_employee_db2.txt
> db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> d:\wip\ds3_wrs_clr_employee_db2.txt
> db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
> d:\wip\ds4_wrs_client_db2.txt
> db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
> d:\wip\ds5_wrs_diary_db2.txt
> db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> d:\wip\ds6_wrs_dry_history_db2.txt
> db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> d:\wip\ds7_wrs_folder_control_db2.txt
> --
> Thanks,
> Snyper|||Snyper
Although i know nothing of the detail of what you are trying to do here ...
If running the indiviual commands are being created but not populted then
perhaps it might be worth looking at it being a permissions error in creating
the information or permisiions on population the file.
when you run one of these commands what permissions context is it running
under
under, your id, under the os id or under the applicaion you are running the
commands from.
Are the drive letters in the contect of the user and location you are
running them from ie if runnind this on a remote serverits D: will not be the
same as you d:
perhap you need to qualify the files such \\<severname>\<sharename>
Can you break these commands down
a simpler command to say display a directory listing work via this method ie
the cmdexec works but not he commnd you are running...
"snyper" wrote:
> Sorry, the step does fail. I see it in the appliction log as a failure. I do
> not know how to get the .bat to execute properly. Any ideas?
> --
> Thanks,
> Snyper
>
> "snyper" wrote:
> > All,
> >
> > sql 2000 sp3
> >
> > I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
> > data in the files that I am creating. Below is the contents of the .bat. I
> > have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> > connection, any other ideas would be a great help.
> >
> > db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> > db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> > d:\wip\ds2_wrs_ind_employee_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> > d:\wip\ds3_wrs_clr_employee_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
> > d:\wip\ds4_wrs_client_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
> > d:\wip\ds5_wrs_diary_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> > d:\wip\ds6_wrs_dry_history_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> > d:\wip\ds7_wrs_folder_control_db2.txt
> > --
> >
> > Thanks,
> >
> > Snyper
sql 2000 sp3
I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
data in the files that I am creating. Below is the contents of the .bat. I
have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
connection, any other ideas would be a great help.
db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
d:\wip\ds2_wrs_ind_employee_db2.txt
db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
d:\wip\ds3_wrs_clr_employee_db2.txt
db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
d:\wip\ds4_wrs_client_db2.txt
db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
d:\wip\ds5_wrs_diary_db2.txt
db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
d:\wip\ds6_wrs_dry_history_db2.txt
db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
d:\wip\ds7_wrs_folder_control_db2.txt
--
Thanks,
SnyperSorry, the step does fail. I see it in the appliction log as a failure. I do
not know how to get the .bat to execute properly. Any ideas?
--
Thanks,
Snyper
"snyper" wrote:
> All,
> sql 2000 sp3
> I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
> data in the files that I am creating. Below is the contents of the .bat. I
> have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> connection, any other ideas would be a great help.
> db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> d:\wip\ds2_wrs_ind_employee_db2.txt
> db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> d:\wip\ds3_wrs_clr_employee_db2.txt
> db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
> d:\wip\ds4_wrs_client_db2.txt
> db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
> d:\wip\ds5_wrs_diary_db2.txt
> db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> d:\wip\ds6_wrs_dry_history_db2.txt
> db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> d:\wip\ds7_wrs_folder_control_db2.txt
> --
> Thanks,
> Snyper|||Snyper
Although i know nothing of the detail of what you are trying to do here ...
If running the indiviual commands are being created but not populted then
perhaps it might be worth looking at it being a permissions error in creating
the information or permisiions on population the file.
when you run one of these commands what permissions context is it running
under
under, your id, under the os id or under the applicaion you are running the
commands from.
Are the drive letters in the contect of the user and location you are
running them from ie if runnind this on a remote serverits D: will not be the
same as you d:
perhap you need to qualify the files such \\<severname>\<sharename>
Can you break these commands down
a simpler command to say display a directory listing work via this method ie
the cmdexec works but not he commnd you are running...
"snyper" wrote:
> Sorry, the step does fail. I see it in the appliction log as a failure. I do
> not know how to get the .bat to execute properly. Any ideas?
> --
> Thanks,
> Snyper
>
> "snyper" wrote:
> > All,
> >
> > sql 2000 sp3
> >
> > I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
> > data in the files that I am creating. Below is the contents of the .bat. I
> > have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> > connection, any other ideas would be a great help.
> >
> > db2cmd /c /w /i db2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> > db2cmd /c /w /i db2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> > d:\wip\ds2_wrs_ind_employee_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> > d:\wip\ds3_wrs_clr_employee_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds4_wrs_client_db2_query.sql -r
> > d:\wip\ds4_wrs_client_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds5_wrs_diary_db2_query.sql -r
> > d:\wip\ds5_wrs_diary_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> > d:\wip\ds6_wrs_dry_history_db2.txt
> > db2cmd /c /w /i db2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> > d:\wip\ds7_wrs_folder_control_db2.txt
> > --
> >
> > Thanks,
> >
> > Snyper
exectuing a .bat from cmdexec
All,
sql 2000 sp3
I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
data in the files that I am creating. Below is the contents of the .bat. I
have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
connection, any other ideas would be a great help.
db2cmd /c /w /i DB2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
db2cmd /c /w /i DB2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
d:\wip\ds2_wrs_ind_employee_db2.txt
db2cmd /c /w /i DB2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
d:\wip\ds3_wrs_clr_employee_db2.txt
db2cmd /c /w /i DB2 +o -tf ds4_wrs_client_db2_query.sql -r
d:\wip\ds4_wrs_client_db2.txt
db2cmd /c /w /i DB2 +o -tf ds5_wrs_diary_db2_query.sql -r
d:\wip\ds5_wrs_diary_db2.txt
db2cmd /c /w /i DB2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
d:\wip\ds6_wrs_dry_history_db2.txt
db2cmd /c /w /i DB2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
d:\wip\ds7_wrs_folder_control_db2.txt
--
Thanks,
SnyperSorry, the step does fail. I see it in the appliction log as a failure. I do
not know how to get the .bat to execute properly. Any ideas?
--
Thanks,
Snyper
"snyper" wrote:
> All,
> sql 2000 sp3
> I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is
no
> data in the files that I am creating. Below is the contents of the .bat. I
> have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> connection, any other ideas would be a great help.
> db2cmd /c /w /i DB2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> db2cmd /c /w /i DB2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> d:\wip\ds2_wrs_ind_employee_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> d:\wip\ds3_wrs_clr_employee_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds4_wrs_client_db2_query.sql -r
> d:\wip\ds4_wrs_client_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds5_wrs_diary_db2_query.sql -r
> d:\wip\ds5_wrs_diary_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> d:\wip\ds6_wrs_dry_history_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> d:\wip\ds7_wrs_folder_control_db2.txt
> --
> Thanks,
> Snyper|||Snyper
Although i know nothing of the detail of what you are trying to do here ...
If running the indiviual commands are being created but not populted then
perhaps it might be worth looking at it being a permissions error in creatin
g
the information or permisiions on population the file.
when you run one of these commands what permissions context is it running
under
under, your id, under the os id or under the applicaion you are running the
commands from.
Are the drive letters in the contect of the user and location you are
running them from ie if runnind this on a remote serverits D: will not be th
e
same as you d:
perhap you need to qualify the files such \\<severname>\<sharename>
Can you break these commands down
a simpler command to say display a directory listing work via this method ie
the cmdexec works but not he commnd you are running...
"snyper" wrote:
[vbcol=seagreen]
> Sorry, the step does fail. I see it in the appliction log as a failure. I
do
> not know how to get the .bat to execute properly. Any ideas?
> --
> Thanks,
> Snyper
>
> "snyper" wrote:
>
sql 2000 sp3
I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is no
data in the files that I am creating. Below is the contents of the .bat. I
have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
connection, any other ideas would be a great help.
db2cmd /c /w /i DB2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
db2cmd /c /w /i DB2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
d:\wip\ds2_wrs_ind_employee_db2.txt
db2cmd /c /w /i DB2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
d:\wip\ds3_wrs_clr_employee_db2.txt
db2cmd /c /w /i DB2 +o -tf ds4_wrs_client_db2_query.sql -r
d:\wip\ds4_wrs_client_db2.txt
db2cmd /c /w /i DB2 +o -tf ds5_wrs_diary_db2_query.sql -r
d:\wip\ds5_wrs_diary_db2.txt
db2cmd /c /w /i DB2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
d:\wip\ds6_wrs_dry_history_db2.txt
db2cmd /c /w /i DB2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
d:\wip\ds7_wrs_folder_control_db2.txt
--
Thanks,
SnyperSorry, the step does fail. I see it in the appliction log as a failure. I do
not know how to get the .bat to execute properly. Any ideas?
--
Thanks,
Snyper
"snyper" wrote:
> All,
> sql 2000 sp3
> I have a .bat file at d:\wip\queries.bat, the step succeeds, but there is
no
> data in the files that I am creating. Below is the contents of the .bat. I
> have 'd:\queries.bat' in the cmdexec step. I have verified the odbc
> connection, any other ideas would be a great help.
> db2cmd /c /w /i DB2 +o -tf ds1_query.sql -r d:\wip\ds1_query.txt
> db2cmd /c /w /i DB2 +o -tf ds2_wrs_ind_employee_db2_query.sql -r
> d:\wip\ds2_wrs_ind_employee_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds3_wrs_clr_employee_db2_query.sql -r
> d:\wip\ds3_wrs_clr_employee_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds4_wrs_client_db2_query.sql -r
> d:\wip\ds4_wrs_client_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds5_wrs_diary_db2_query.sql -r
> d:\wip\ds5_wrs_diary_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds6_wrs_dry_history_db2_query.sql -r
> d:\wip\ds6_wrs_dry_history_db2.txt
> db2cmd /c /w /i DB2 +o -tf ds7_wrs_folder_control_db2_query.sql -r
> d:\wip\ds7_wrs_folder_control_db2.txt
> --
> Thanks,
> Snyper|||Snyper
Although i know nothing of the detail of what you are trying to do here ...
If running the indiviual commands are being created but not populted then
perhaps it might be worth looking at it being a permissions error in creatin
g
the information or permisiions on population the file.
when you run one of these commands what permissions context is it running
under
under, your id, under the os id or under the applicaion you are running the
commands from.
Are the drive letters in the contect of the user and location you are
running them from ie if runnind this on a remote serverits D: will not be th
e
same as you d:
perhap you need to qualify the files such \\<severname>\<sharename>
Can you break these commands down
a simpler command to say display a directory listing work via this method ie
the cmdexec works but not he commnd you are running...
"snyper" wrote:
[vbcol=seagreen]
> Sorry, the step does fail. I see it in the appliction log as a failure. I
do
> not know how to get the .bat to execute properly. Any ideas?
> --
> Thanks,
> Snyper
>
> "snyper" wrote:
>
ExecSQL Task Truncates Ouput Parameter
I am successfully passing several parameters into a SP using the ExecSQL Task. I have a single parameter that is an OUTPUT parameter in the SP.
I always recieve the first character of the OUTPUT string. I have tried chaning the data type of the parameter mapping [AnsiString, String, etc.] and I have tried changing the data type of the OUTPUT parameter [varchar, char, nvarchar, nchar]
In each case, only the frist character of the output value is returned and subsequently passed to the next SSIS component / task.
Any ideas why this value is truncated?It is a String.
One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.
John|||
jloper wrote:
One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.
John
Ignore that, I was wrong. Sorry!
Note to self: Check out your answers before posting them!
-Jamie
execption with store procedure
HI!
how do i make the store procudere only exec when new data inserted in the other table? recently i hav use sp_procoption to make an autoexecution.... but it will insert the new data together with the old data
any suggestion?
CREATE PROCEDURE [P1] AS
INSERT INTO TABLE1
SELECT sa.NoMatriks, sa.SesiSem,
SUM(Nilai * KreditSubjek) / SUM(KreditSubjek) AS CPA,
SUM(KreditSubjek) AS JumKredit,
p.Agama, p.Aliran, p.Negeri, p.Kaum
FROM TblSubjekAmbil sa, TblSubjek s, TblGred g, TblPelajar p
WHERE sa.NoMatriks = p.NoMatriks AND
sa.KodSubjek = s.KodSubjek AND
sa.Gred = g.Gred
GROUP BY sa.NoMatriks, sa.SesiSem,
p.Agama, p.Aliran, p.Negeri,
p.Kaum (SELECT sa.NoMatriks, t1.NoMatriks
FROM TblSubjAmbil sa, TABLE1 t1
WHERE t1.NoMatriks <> sa.NoMatriks)
exec sp_makestartup N'P1'
exec sp_makestartup N'P1'
exec sp_makestartup N'P1'Databases have a great concept, called trigger. Just define an INSERT trigger for your table, and let that trigger execute your code or procedure.sql
how do i make the store procudere only exec when new data inserted in the other table? recently i hav use sp_procoption to make an autoexecution.... but it will insert the new data together with the old data
any suggestion?
CREATE PROCEDURE [P1] AS
INSERT INTO TABLE1
SELECT sa.NoMatriks, sa.SesiSem,
SUM(Nilai * KreditSubjek) / SUM(KreditSubjek) AS CPA,
SUM(KreditSubjek) AS JumKredit,
p.Agama, p.Aliran, p.Negeri, p.Kaum
FROM TblSubjekAmbil sa, TblSubjek s, TblGred g, TblPelajar p
WHERE sa.NoMatriks = p.NoMatriks AND
sa.KodSubjek = s.KodSubjek AND
sa.Gred = g.Gred
GROUP BY sa.NoMatriks, sa.SesiSem,
p.Agama, p.Aliran, p.Negeri,
p.Kaum (SELECT sa.NoMatriks, t1.NoMatriks
FROM TblSubjAmbil sa, TABLE1 t1
WHERE t1.NoMatriks <> sa.NoMatriks)
exec sp_makestartup N'P1'
exec sp_makestartup N'P1'
exec sp_makestartup N'P1'Databases have a great concept, called trigger. Just define an INSERT trigger for your table, and let that trigger execute your code or procedure.sql
ExecContextHit through SQL Profiler
Hello,
Hopefully someone can answer this question. I am running a procedure which u
ses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to up
date a table. When I run this sp in a query window it runs much faster then
when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:Ex
ecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is r
ecompiling is there anyway to prohibit that?
Thanks !!ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dyn
amic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. W
hen I run this sp in a query window it runs much faster then when it is called from
a J
ob. I ran Profiler traces with both methods and noticed that there were SP:E
xecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is t
here anyway to prohibit that?
>Thanks !!|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
>
Job. I ran Profiler traces with both methods and noticed that there were SP:
ExecContectHit entries in the Traces. Does this mean that at the point that
I see these statements that there is an sp_recompile occurring within the sp
? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is
there anyway to prohibit that?[vbcol=seagreen]
>|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
>
a Job. I ran Profiler traces with both methods and noticed that there were S
P:ExecContectHit entries in the Traces. Does this mean that at the point tha
t I see these statements that there is an sp_recompile occurring within the
sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling i
s there anyway to prohibit that?[vbcol=seagreen]
Hopefully someone can answer this question. I am running a procedure which u
ses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to up
date a table. When I run this sp in a query window it runs much faster then
when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:Ex
ecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is r
ecompiling is there anyway to prohibit that?
Thanks !!ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dyn
amic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. W
hen I run this sp in a query window it runs much faster then when it is called from
a J
ob. I ran Profiler traces with both methods and noticed that there were SP:E
xecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is t
here anyway to prohibit that?
>Thanks !!|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
>
Job. I ran Profiler traces with both methods and noticed that there were SP:
ExecContectHit entries in the Traces. Does this mean that at the point that
I see these statements that there is an sp_recompile occurring within the sp
? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is
there anyway to prohibit that?[vbcol=seagreen]
>|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
>
a Job. I ran Profiler traces with both methods and noticed that there were S
P:ExecContectHit entries in the Traces. Does this mean that at the point tha
t I see these statements that there is an sp_recompile occurring within the
sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling i
s there anyway to prohibit that?[vbcol=seagreen]
ExecContextHit through SQL Profiler
Hello,
Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
Thanks !!
ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
Thanks !!
ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
Exec/Query across servers (unlinked)
Can I exec/query across unlinked servers that are on the network but
physically different server instances? If so, how, please?
TIA!
RobertTake a look at OPENROWSET and OPENDATASOURCE in Books On line
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You can use OPENROWSET
SELECT *
FROM OPENROWSET('SQLOLEDB','otherinstance';'u
ser';'pwd',
'SELECT * FROM somedb.dbo.mytable')|||My bad...
I wasn't using fully qualified naming. I was using something like
ABC123.dbo.TableName instead of DEV.ABC123.dbo.TableName.
Thanks!
Robert
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:OKtDvGkXGHA.3660@.TK2MSFTNGP04.phx.gbl...
> Can I exec/query across unlinked servers that are on the network but
> physically different server instances? If so, how, please?
> TIA!
> Robert
>
physically different server instances? If so, how, please?
TIA!
RobertTake a look at OPENROWSET and OPENDATASOURCE in Books On line
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You can use OPENROWSET
SELECT *
FROM OPENROWSET('SQLOLEDB','otherinstance';'u
ser';'pwd',
'SELECT * FROM somedb.dbo.mytable')|||My bad...
I wasn't using fully qualified naming. I was using something like
ABC123.dbo.TableName instead of DEV.ABC123.dbo.TableName.
Thanks!
Robert
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:OKtDvGkXGHA.3660@.TK2MSFTNGP04.phx.gbl...
> Can I exec/query across unlinked servers that are on the network but
> physically different server instances? If so, how, please?
> TIA!
> Robert
>
EXEC('string') from a UDF?
Can I not use an EXEC command from with in a UDF? I am looking through Book
s
online, but can't find anything that says I can't.
In my UDF I have
Exec('Select fields fromTABLE Where this=that')
I use this in stored procs alot when the table name is a variable.
When trying to do it from a UDF, I get:
"Invalid use of 'EXECUTE' within a function"
Thanks,
SteveHi
UDF's can contain certain functions like EXECUTE. Basic rule of the UDF.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||You cant execute q sqlstring in a UDF, try to code a stored prcoedure for
that.
HTH, Jens Suessmeyer.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
[shudder] Parameterizing table names is a very bad idea and with good design
it shouldn't be necessary. Why would you want to do this in a function
anyway? (BTW, you can't)
David Portas
SQL Server MVP
--|||Thanks for all the responses.
I use a UDF so I can use it to return a table variable to base a MS ACCESS
form off of. The table name is passed in cause it is a ##table, and will be
different for different users. The UDF gathers info from different tables,
including the ##table, then puts it all in a table variable to return to the
form.
Steve
"David Portas" wrote:
> [shudder] Parameterizing table names is a very bad idea and with good design
> it shouldn't be necessary. Why would you want to do this in a function
> anyway? (BTW, you can't)
> --
> David Portas
> SQL Server MVP
> --
>
>|||Local temp tables are scoped to a session anyway so there's no need to
parameterize the name. On the other hand, why are you using temp tables to
return data to the client?
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
There may be plenty of reasons for this. Say for instance you result
set includes a column that is an expression. Assume further that you
also use the recordset client-side to keep new and updated data, and
that you write data back through stored procedures. Problem is that
this field becomes read-only if you are in ADO. So our application
has it's fair share of temp tables to work around this brain-deadness
in ADO.
And then there are of course plenty of procedures where a temp table
is used as a work table, and this is where data is returned from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David,
Say the ACCESS application deals with orders. You enter the order section,
in the background, the code takes your userid and gathers lots of info from
different places and puts it into ##DavidTable. Your ACCESS form is based
off that table. When you leave that section, you apply any updates from the
temp table to the normal data structures. While in the section, you may nee
d
a report. The UDF reads the ##useridTable and creates a table variable with
info from that, and from other sources. Say another user (me) gets in while
you are there to work with orders also, they will get ##SteveTable to work
with.
That is what I am working with.
Thanks
"David Portas" wrote:
> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
> --
> David Portas
> SQL Server MVP
> --
>
>|||If you use local temp tables instead of global (prefix with a single #
instead of ##) then the table is scoped to the connection. That way you can
use the same name for each user and you won't have to parameterize the name.
In ADO.NET you could use a disconnected recordset for this but I don't know
what other options exist in Access. I suspect there ought to be a method not
using temp tables. You might want to ask the question in Access forum.
David Portas
SQL Server MVP
--|||Steve
Have tried adp of access where you can dynamically do all this stuff with
sql server
Regards
R.D
"Erland Sommarskog" wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> There may be plenty of reasons for this. Say for instance you result
> set includes a column that is an expression. Assume further that you
> also use the recordset client-side to keep new and updated data, and
> that you write data back through stored procedures. Problem is that
> this field becomes read-only if you are in ADO. So our application
> has it's fair share of temp tables to work around this brain-deadness
> in ADO.
> And then there are of course plenty of procedures where a temp table
> is used as a work table, and this is where data is returned from.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
s
online, but can't find anything that says I can't.
In my UDF I have
Exec('Select fields fromTABLE Where this=that')
I use this in stored procs alot when the table name is a variable.
When trying to do it from a UDF, I get:
"Invalid use of 'EXECUTE' within a function"
Thanks,
SteveHi
UDF's can contain certain functions like EXECUTE. Basic rule of the UDF.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||You cant execute q sqlstring in a UDF, try to code a stored prcoedure for
that.
HTH, Jens Suessmeyer.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
[shudder] Parameterizing table names is a very bad idea and with good design
it shouldn't be necessary. Why would you want to do this in a function
anyway? (BTW, you can't)
David Portas
SQL Server MVP
--|||Thanks for all the responses.
I use a UDF so I can use it to return a table variable to base a MS ACCESS
form off of. The table name is passed in cause it is a ##table, and will be
different for different users. The UDF gathers info from different tables,
including the ##table, then puts it all in a table variable to return to the
form.
Steve
"David Portas" wrote:
> [shudder] Parameterizing table names is a very bad idea and with good design
> it shouldn't be necessary. Why would you want to do this in a function
> anyway? (BTW, you can't)
> --
> David Portas
> SQL Server MVP
> --
>
>|||Local temp tables are scoped to a session anyway so there's no need to
parameterize the name. On the other hand, why are you using temp tables to
return data to the client?
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
There may be plenty of reasons for this. Say for instance you result
set includes a column that is an expression. Assume further that you
also use the recordset client-side to keep new and updated data, and
that you write data back through stored procedures. Problem is that
this field becomes read-only if you are in ADO. So our application
has it's fair share of temp tables to work around this brain-deadness
in ADO.
And then there are of course plenty of procedures where a temp table
is used as a work table, and this is where data is returned from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David,
Say the ACCESS application deals with orders. You enter the order section,
in the background, the code takes your userid and gathers lots of info from
different places and puts it into ##DavidTable. Your ACCESS form is based
off that table. When you leave that section, you apply any updates from the
temp table to the normal data structures. While in the section, you may nee
d
a report. The UDF reads the ##useridTable and creates a table variable with
info from that, and from other sources. Say another user (me) gets in while
you are there to work with orders also, they will get ##SteveTable to work
with.
That is what I am working with.
Thanks
"David Portas" wrote:
> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
> --
> David Portas
> SQL Server MVP
> --
>
>|||If you use local temp tables instead of global (prefix with a single #
instead of ##) then the table is scoped to the connection. That way you can
use the same name for each user and you won't have to parameterize the name.
In ADO.NET you could use a disconnected recordset for this but I don't know
what other options exist in Access. I suspect there ought to be a method not
using temp tables. You might want to ask the question in Access forum.
David Portas
SQL Server MVP
--|||Steve
Have tried adp of access where you can dynamically do all this stuff with
sql server
Regards
R.D
"Erland Sommarskog" wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> There may be plenty of reasons for this. Say for instance you result
> set includes a column that is an expression. Assume further that you
> also use the recordset client-side to keep new and updated data, and
> that you write data back through stored procedures. Problem is that
> this field becomes read-only if you are in ADO. So our application
> has it's fair share of temp tables to work around this brain-deadness
> in ADO.
> And then there are of course plenty of procedures where a temp table
> is used as a work table, and this is where data is returned from.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
exec(select...), how supress the output?
Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)
(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow
Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?
EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)
> (2) Can I use the result of this query in another query somehow? e.g.
> select
INSERT #tmp (...)
EXEC('...')
> (3) Can I control the size of the columns in the output somehow
This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.
But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)
worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway
> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')
sorry, don't understand this? Can you elaborate?
> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.
create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable
I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?
You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.
>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser
I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.
But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
EXEC(@SQL) And Unicode Result Bug
I've got a dynamic SQL query that is generated inside a SP, and is run using
the
EXEC (@.SQL)
or
EXEC sp_executesql @.SQL
This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.
All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data i
s
return '?' instead of unicode charachters.
This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!
Cheers
TrisTris (Tris@.discussions.microsoft.com) writes:
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.
As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.
I like to stress that this is all guessworks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@.microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
> Cheers
> Tris|||Hi, thanks for the responses.
Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.
Cheers
T
the
EXEC (@.SQL)
or
EXEC sp_executesql @.SQL
This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.
All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data i
s
return '?' instead of unicode charachters.
This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!
Cheers
TrisTris (Tris@.discussions.microsoft.com) writes:
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.
As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.
I like to stress that this is all guessworks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@.microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
> Cheers
> Tris|||Hi, thanks for the responses.
Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.
Cheers
T
exec xp_sendmail error on SQL Server 2000
I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
So I try to have it executed in a trigger but it failed.
Here is the trigger creation script and error message
use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO
CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @.email_subject varchar(100),
@.email_content varchar(4000),
@.email_recipients varchar(50)
set @.email_subject='SQL Mail test mail'
set @.email_recipients='some@.world.com.tw'
set @.email_content='this is a test mail, don't reply this mail'
exec master.dbo.xp_sendmail @.recipients=@.email_recipients,@.subject=@.email_subj ect,@.message=@.email_content
GO
Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.
Appreciate any prompt reply.
JDbefore we get to your problem, let's talk about this for a second.
is your logic valid if more than one record is inserted at a time?
have you thought about the associated overhead for each transaction here?
have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?
I am nearly certain your problem is permissions related. which is another can of worms.|||before we get to your problem, let's talk about this for a second.
is your logic valid if more than one record is inserted at a time?
have you thought about the associated overhead for each transaction here?
have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?
I am nearly certain your problem is permissions related. which is another can of worms.
Thanks very much for your reminders
I really didn't think about these.
Only one record is inserted at a time.
There is no much transaction, just something like error notification.
mmmmm, I didn't know unavailable exchange server will cause such issue.
So how can I avoid it?
How to fix the permission issue?
Thanks for your help!!
JD|||Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).
Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.
That said,
Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.
Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.
Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?
Regards,
hmscott|||hi hmscott,
Thanks for helping me away such dangerous condition.
It is really bad hear that. I thought I am almost there.
I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.
Can you provide any reference for SMTP usage?
But I am still want to know how to solve the issue I have right now.
I can run the script out of trigger.
I did exec master.dbo.xp_startmail.
master.xp_startmail was in the error message when firing the trigger.
Best Regards,
JD|||My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...
As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.
Also, did you check for the existence of the master.dbo.sp_startmail proc?
Regards,
hmscott
So I try to have it executed in a trigger but it failed.
Here is the trigger creation script and error message
use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO
CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @.email_subject varchar(100),
@.email_content varchar(4000),
@.email_recipients varchar(50)
set @.email_subject='SQL Mail test mail'
set @.email_recipients='some@.world.com.tw'
set @.email_content='this is a test mail, don't reply this mail'
exec master.dbo.xp_sendmail @.recipients=@.email_recipients,@.subject=@.email_subj ect,@.message=@.email_content
GO
Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.
Appreciate any prompt reply.
JDbefore we get to your problem, let's talk about this for a second.
is your logic valid if more than one record is inserted at a time?
have you thought about the associated overhead for each transaction here?
have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?
I am nearly certain your problem is permissions related. which is another can of worms.|||before we get to your problem, let's talk about this for a second.
is your logic valid if more than one record is inserted at a time?
have you thought about the associated overhead for each transaction here?
have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?
I am nearly certain your problem is permissions related. which is another can of worms.
Thanks very much for your reminders
I really didn't think about these.
Only one record is inserted at a time.
There is no much transaction, just something like error notification.
mmmmm, I didn't know unavailable exchange server will cause such issue.
So how can I avoid it?
How to fix the permission issue?
Thanks for your help!!
JD|||Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).
Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.
That said,
Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.
Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.
Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?
Regards,
hmscott|||hi hmscott,
Thanks for helping me away such dangerous condition.
It is really bad hear that. I thought I am almost there.
I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.
Can you provide any reference for SMTP usage?
But I am still want to know how to solve the issue I have right now.
I can run the script out of trigger.
I did exec master.dbo.xp_startmail.
master.xp_startmail was in the error message when firing the trigger.
Best Regards,
JD|||My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...
As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.
Also, did you check for the existence of the master.dbo.sp_startmail proc?
Regards,
hmscott
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
That's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David
|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
That's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David
|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
Exec VBScript file from T-SQL?
Is there a way to run a VBScript file from a T-SQL script (SQL 2000)?
Yes, it requires using xp_executeSQL.
That is a 'MAJOR' security issue and should not be done lightly -if at all. It also requires admin permissions in the server (or a proxy).
Perhaps if you were to provide more detail about your needs, folks here would help come up with 'safer' alternatives -if possible.
|||The root of the problem is that CDOSYS email has stopped working from SQL. It still works from VBScript, though. We rebooted the server (Win 2000 SP 3) and that fixed the problem, but only for a few days. Someone suggested converting our CDOSYS emails to VBScript. I am hoping that someday CDOSYS works again from SQL so I wanted to not change the job too much if possible. However, if it's a big security issue, I may change the steps that generate emails from T-SQL to OS Command line steps and exec the VBScript that way.|||If you are wanting to send email from SQL Server 2000, then I recommend that you examine xp_smtp_email. It is more reliable and less of a security issue than using a mapi client on the server.
Email for SQL Server 2000
http://www.sqldev.net/xp/xpsmtp.htm
Also, click here for a similar forum thread.
sql
Subscribe to:
Posts (Atom)