Excel Import: March 2012

Thursday, March 29, 2012

Executable or Way for User to Launch an SQL Package?

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

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
>

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

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
>

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

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

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

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]

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
>

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
>

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)

(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

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

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
>

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
>

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
>

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

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.

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.

Exec time for a query to run in QA?

Hi All
I want to see how long it takes for my query to execute in QA. How do i do
that? Thanx in advanceThere is an Execution Time element at the lower right hand part of the
screen in QA. Optionally you could turn on Client Statistics, Statistics
Time or using GETDATE() before and after your query.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||When it's done, look in the lower right corner of the status bar, the third
box from the right shows elapsed time.
You can also do another tactic, like
SELECT @.dt = CURRENT_TIMESTAMP
-- query here
SELECT DATEDIFF(MS, @.dt, CURRENT_TIMESTAMP)
You can also look at SET STATISTICS TIME and SET STATISTICS IO topics in
books online to see how to return different stats about the query or
queries. Showing execution plan and server/client statictics can also be
useful.
http://www.aspfaq.com/2245
A
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||MittyKom wrote:
> Hi All
> I want to see how long it takes for my query to execute in QA. How do
> i do that? Thanx in advance
Best way is to use Profiler. But you can use SET STATISTICS TIME ON /
OFF from Query Analyzer to see the execution. STATISTICS IO is also
useful. Try this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT * FROM pubs.dbo.authors
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com

EXEC Strangeness

Hi all,

I would be very grateful if anyone can detect the problem with the following code. I have indicated the offending line. Basically if I hardcode in @.BelegId, it runs with no problems.

Declare @.SQL VarChar(1000)
Declare @.TableName VarChar(1000)
Declare @.Result Varchar(50)
Declare @.BelegId int
Set @.BelegId=447
Set @.ObjName='s_jps.ap_gr'

if (object_id (N'tempdb..#tempt') is not null) drop table #tempt;
create table #TempT (result varchar(1000))
--SELECT @.SQL='DECLARE @.RESULT VARCHAR(1000) '

SELECT @.SQL = 'SELECT CONVERT(VARCHAR(1000),' + @.ObjName + ') FROM '
SELECT @.SQL = @.SQL + 's_jps '
***PROBLEMATIC LINE FOLLOWS: IF I HARDCODE BELEGID, NO PROBLEMS***
SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + @.BelegId
SELECT @.sql = @.sql + ' and s_se_art_id=5'
INSERT #TempT Exec ( @.SQL )
SELECT * FROM #TempT
GO

He's a quick fix without knowing anything else about your environment...

Change Declare @.BelegId int to Declare @.BelegId varchar(5)

SQLDiva

|||SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + convert(varchar(10), @.BelegId)|||Please take a look at sp_executesql. It is safer to use sp_executesql than concatenating strings to execute in dynamic SQL. You need to protect the code against SQL injection attacks. And you should avoid dynamic SQL in this case if it is easier to just use a static SELECT statement specifying the table name. You probably don't need a temporary table also unless you are doing some complex processing.|||Thanks for the advice. I already looked into sp_executesql but had all sorts of problems with output parameters.

And I'm afraid that the unfortunate truth is that I do have to do some complex processing.

Exec StoredProcedure in Query?

I have two stored procedures that have a common column and I need to write a query like this:

pseudo code:

Code Snippet

select * from (exec firstProcedure 'argument') T where T.ID not in (exec secondProcedure 'arg') S;

What's the proper way to do this?

It is not possible, Convert your Sp as inline table or table valued function (UDF).

Sample,

Code Snippet

Create Function firstProcedure (@.arg int)

returns table

return

(

Select id from Sysobjects

)

Create Function secondProcedure (@.arg int)

returns table

return

(

Select id+10 id from Sysobjects

)

Select * from firstProcedure(1) Where id not in (Select id from secondProcedure(2))

|||Another alternative is to create temp tables and use INSERT INTO ... EXEC proc syntax to load the data into two different temp tables and then to join the temp tables. My first choice is normally to do as ManiD suggested and create either a function or a view; however, there are some procedures that simply cannot be converted. If this is the case, the temp table option might work best.|||

You can also use functions OPENQUERY (if you added a linked server) or OPENROWSET.

Example:

SELECT TOP 10 *

FROM OPENROWSET('SQLOLEDB', '(local)';'my_user';'my_pwd', 'EXEC Northwind..[Ten Most Expensive Products]') as t

AMB

sql

Exec Stored Procedures with high level user permissions

I need to run a stored procedure with admin rights inside the stored procedure like so

Exec xp_cmdshell "c:\start.exe"

i need this to have administrator access so when it is called upon by an asp.net web page that executes the stored procedure it is not run by Network Service user but Administrator. How would i do this.it's the account that runs sqlserver serice that you need|||i am sorry i still don't understand if you could give an example stored procedure or

are you stating that the user that stored the stored procedure is the user level it is run at.|||ohh i forgot to add thank you for replying so fast last time|||what i am saying is that you need to make sure that the account that is used to start sqlserver service is an account that is given enough priveleges to perform the task you need.|||so if the service is started by user Administrator which it alwasy is. That is the user used to start any programs in the stored procedure.

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>>
>

exec stored procedure without qualifying dbo

Exec Stored Procedure from command line?

Is it possible to execute a stored proc form the DOS command Line?Hi,
Yes , you could use the command line utility OSQL or ISQL to do execute a
procedure / tSQL from command line.
OSQL -Usa -Sserver -Ppassword -Qmaster..sp_help -oc:\result.txt'
Thanks
Hari
MCDBA
"Steve" <streesh@.bellsouth.net> wrote in message
news:zJ_0d.115942$0o5.2550@.bignews1.bellsouth.net...
> Is it possible to execute a stored proc form the DOS command Line?
>sql

Exec Stored Procedure from command line?

Is it possible to execute a stored proc form the DOS command Line?
Hi,
Yes , you could use the command line utility OSQL or ISQL to do execute a
procedure / tSQL from command line.
OSQL -Usa -Sserver -Ppassword -Qmaster..sp_help -oc:\result.txt'
Thanks
Hari
MCDBA
"Steve" <streesh@.bellsouth.net> wrote in message
news:zJ_0d.115942$0o5.2550@.bignews1.bellsouth.net. ..
> Is it possible to execute a stored proc form the DOS command Line?
>

EXEC stored procedure for every line of SELECT result table - how?

Hello,

Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.

I guess it's possible with cursors, but maybe it's possible to make it
easier.

Give an example, please.

Thank you in advance.

Hubert<hubert.trzewik@.gmail.com> wrote in message
news:1110986308.176019.292290@.l41g2000cwc.googlegr oups.com...
> Hello,
> Is it possible to EXEC stored procedure from a query?
> I want to execute stored procedure for every line of SELECT result
> table.
> I guess it's possible with cursors, but maybe it's possible to make it
> easier.
> Give an example, please.
> Thank you in advance.
> Hubert

A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:

http://www.sommarskog.se/share_data.html

After that, you can do something like this:

declare @.c1 int, @.c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()

open cur
fetch next from cur into @.c1, @.c2...
while @.@.fetch_status = 0
begin
exec dbo.MyProc @.c1, @.c2, ...
fetch next from cur into @.c1, @.c2...
end

close cur
deallocate cur

Simon|||Thanks. It was quite fast and easy to create such procedure.

exec stored procedure

Hi,

I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!

I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!

The ole db command calls a stored procedure with two input variables:

exec stp_updedgrsholds status, temp_cr_num

from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.

but nothing gets updated in the database.

If I call the stored procedure the following way

exec stp_updedgrsholds 'C', 87

It updates fine! I have tried a number of different way with @. symbols and assignment p_status = @.status

but nothing seems to work.

Any ideas are much appreciated.

Ninder Bassi

If you call the stored proedure and it succeeds, but then data is not updated, have you confused the target of your connections?

What is a data view?

How is the stored procedure being called, what task or component?

What are status and tep_cr_num, that syntax does not make sense. When passing in parameters with OLE-DB, it is usual to use ? as the placeholder, then in the task or component UI, use the "Parameters" screen to map a SSIS variable to the placeholder.

|||

told you i was new to it all!!

the stored procedure is being called from the ole-db command

i have tried the ?'s now and get the following error after mapping them in the "column mappings" tab.

[OLE DB Commmand [3310]]: Columns cannot convert between unicode and non-unicode string data types.

many thanks for the help.

|||

ot it. did a data conversion and the parameters mapped fine. and all worked fine.

many thanks DarrenSQLIS i just needed that nudge in the right direction.

Exec Stored Procedure

Hello

Which is faster :

to write a a big stored procedure with if conditions, or to separate them and call them using exec??

i.e:

if @.id=1
insert into ...
else if @.id=2
update...
--------
or
if @.id=1
exec InsertProcedure
else if @.id=2
exec UpdateProcedure

Please help

Its very difficult to jugde that which is faster inLine Query or Store procedure

Read What One of Microsoft Press books says about Store procedure

Stored procedures are executable server-side routines. They give you great power and performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures are allowed to have side effects. That is, they are allowed to change data in tables, and even the schema of objects. Stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects. You can perform input validation in stored procedures, and you can use stored procedures to allow activities only if they make sense as a whole unit, as opposed to allowing users to perform activities directly against objects.

Stored procedures also give you the benefits of encapsulation; if you need to change the implementation of a stored procedure because you developed a more efficient way to achieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure's interface remains the same, the users and the applications are not affected. On the other hand, if you implement your business logic in the client application, the impact of a change can be very painful.

Stored procedures also provide many important performance benefits. By default, a stored procedure will reuse a previously cached execution plan, saving the CPU resources and the time it takes to parse, resolve, and optimize your code. Network traffic is minimized by shortening the code strings that the client submits to Microsoft SQL Server—the client submits only the stored procedure's name and its arguments, as opposed to the full code. Moreover, all the activity is performed at the server, avoiding multiple roundtrips between the client and the server. The stored procedure will pass only the final result to the client through the network"

And About User defined Funciton Or Say inline Query Statement

User-defined functions (UDFs) are routines that perform calculations/computations and return a value—scalar (singular) or table. Microsoft SQL Server 2000 introduced UDFs, which you developed with T-SQL and could incorporate in queries, computed columns, and constraints.

|||

probably the big stored procedure with everything in it will be faster but only by a matter of milliseconds. I would choose which ever option you will find easier to maintain and develop.

|||

Are there any clues or proves ??

Please I need that urgenlty

|||

It depends on what you are doing in the IF Loops. If you are just doing INSERTs one big proc should be fine, as far as performance is concerned. If you split it into multiple smaller procs, you have the ability to call individual proc if you need to reuse the INSERT to one of the tables rather than call the giant proc.

|||

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Thank you

|||

In case of splitting sp , will SQL server builds and stores execution plans for exec called procedures??

Plz Advice

|||

JRICE:
If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Please re-read my earlier reply.

|||

Thanks for your reply,

I need an advice if Im concern about performance and I need reusability of my stored procedures, will it differ that much?

thank you in advance

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
Dave
Can you post the proc?
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>
|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>
|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>
|||I have a solution for you but unfortunately can't post it because it's
proprietary.
Just kidding. Try this and see if it works any better:
EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
Make sure the SP is the same version (accepts the same # of parameters) on
both servers.
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>
|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>
|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).
sql

Thursday, March 29, 2012

Executable or Way for User to Launch an SQL Package?

execure store procedure from Query Analizer

execure store procedure from Query Analizer

execure store procedure from Query Analizer

exectuing a .bat from cmdexec

exectuing a .bat from cmdexec

ExecSQL Task Truncates Ouput Parameter

execption with store procedure

ExecContextHit through SQL Profiler

ExecContextHit through SQL Profiler

Exec/Query across servers (unlinked)

EXEC('string') from a UDF?

exec(select...), how supress the output?

EXEC(@SQL) And Unicode Result Bug

exec xp_sendmail error on SQL Server 2000

EXEC WITH RECOMPILE

EXEC WITH RECOMPILE

EXEC WITH RECOMPILE

EXEC WITH RECOMPILE

Exec VBScript file from T-SQL?

Exec VBScript file from T-SQL?

Exec time for a query to run in QA?

EXEC Strangeness

Exec StoredProcedure in Query?

Exec Stored Procedures with high level user permissions

exec stored procedure without qualifying dbo

exec stored procedure without qualifying dbo

exec stored procedure without qualifying dbo

Exec Stored Procedure from command line?

Exec Stored Procedure from command line?

EXEC stored procedure for every line of SELECT result table - how?

exec stored procedure

Exec Stored Procedure

EXEC stored procedure

Excel Import

Blog Archive

About Me