Edit a step from a job

I can't edit a step (or create a new one) form an Agent job.
I get General acces denied error.
On details I see at program location
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfos()
Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
I use a client station to acces the server
On server works fine
Any ideas ?Does the logon details you're using from the client have rights to change
the job details?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sorin Popescu" <spopescu@.nospam.com> wrote in message
news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> I can't edit a step (or create a new one) form an Agent job.
> I get General acces denied error.
> On details I see at program location
>
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
> I use a client station to acces the server
> On server works fine
> Any ideas ?
>|||How can I verify that please ?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>> I can't edit a step (or create a new one) form an Agent job.
>> I get General acces denied error.
>> On details I see at program location
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
> s()
>> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
>> I use a client station to acces the server
>> On server works fine
>> Any ideas ?
>>
>|||It's the owner of job
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>> I can't edit a step (or create a new one) form an Agent job.
>> I get General acces denied error.
>> On details I see at program location
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
> s()
>> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
>> I use a client station to acces the server
>> On server works fine
>> Any ideas ?
>>
>sql

ECall Methods

When trying to Connect to MS SQL Server 2005 through SQL Server management Studio I get an error message as follows:

ECall methods must be packaged into a system module. (mscorlib)

Visual Studio .NET 2005 is installed on the same system as this is a development workstation.

I have no idea what is causing this and any help would be much appreciated.

If you require any further details as to the nature of this, please let me know.

Thanks

Brendan

Hi Brendan,

I have exactly the same error wen trying to connect SQL Server 2005 in SQL Server management Studio.

Did you find the solution Brendan?

Thanks,

Vlad

|||

Brendan, Vlad,

Ditto for me. I was just upgrading to SP2 and now I have this problem. I'm working with tech support to resolve it. I've already removed the Workstation Components (as they suggested), but that did not help.

I'll post any solution that I find - assume you'll do the same

Coston

|||

Just to let you guys know the solution I used in resolving this problem.

I had to uninstall and reinstall .NET 1.1 and 2.0 frameworks.

Then I applied their service packs and ran the add and remove program to

repair the .NET 2.0 framework.

I can now connect successfully to all the sql server databases using management studio.

ECall Methods

When trying to Connect to MS SQL Server 2005 through SQL Server management Studio I get an error message as follows:

ECall methods must be packaged into a system module. (mscorlib)

Visual Studio .NET 2005 is installed on the same system as this is a development workstation.

I have no idea what is causing this and any help would be much appreciated.

If you require any further details as to the nature of this, please let me know.

Thanks

Brendan

Hi Brendan,

I have exactly the same error wen trying to connect SQL Server 2005 in SQL Server management Studio.

Did you find the solution Brendan?

Thanks,

Vlad

|||

Brendan, Vlad,

Ditto for me. I was just upgrading to SP2 and now I have this problem. I'm working with tech support to resolve it. I've already removed the Workstation Components (as they suggested), but that did not help.

I'll post any solution that I find - assume you'll do the same

Coston

|||

Just to let you guys know the solution I used in resolving this problem.

I had to uninstall and reinstall .NET 1.1 and 2.0 frameworks.

Then I applied their service packs and ran the add and remove program to

repair the .NET 2.0 framework.

I can now connect successfully to all the sql server databases using management studio.

ECall Methods

When trying to Connect to MS SQL Server 2005 through SQL Server management Studio I get an error message as follows:

ECall methods must be packaged into a system module. (mscorlib)

Visual Studio .NET 2005 is installed on the same system as this is a development workstation.

I have no idea what is causing this and any help would be much appreciated.

If you require any further details as to the nature of this, please let me know.

Thanks

Brendan

Hi Brendan,

I have exactly the same error wen trying to connect SQL Server 2005 in SQL Server management Studio.

Did you find the solution Brendan?

Thanks,

Vlad

|||

Brendan, Vlad,

Ditto for me. I was just upgrading to SP2 and now I have this problem. I'm working with tech support to resolve it. I've already removed the Workstation Components (as they suggested), but that did not help.

I'll post any solution that I find - assume you'll do the same

Coston

|||

Just to let you guys know the solution I used in resolving this problem.

I had to uninstall and reinstall .NET 1.1 and 2.0 frameworks.

Then I applied their service packs and ran the add and remove program to

repair the .NET 2.0 framework.

I can now connect successfully to all the sql server databases using management studio.

|||Repair install of .NET framework 2.0 solved this issue for me as well.

ECall Methods

When trying to Connect to MS SQL Server 2005 through SQL Server management Studio I get an error message as follows:

ECall methods must be packaged into a system module. (mscorlib)

Visual Studio .NET 2005 is installed on the same system as this is a development workstation.

I have no idea what is causing this and any help would be much appreciated.

If you require any further details as to the nature of this, please let me know.

Thanks

Brendan

Hi Brendan,

I have exactly the same error wen trying to connect SQL Server 2005 in SQL Server management Studio.

Did you find the solution Brendan?

Thanks,

Vlad

|||

Brendan, Vlad,

Ditto for me. I was just upgrading to SP2 and now I have this problem. I'm working with tech support to resolve it. I've already removed the Workstation Components (as they suggested), but that did not help.

I'll post any solution that I find - assume you'll do the same

Coston

|||

Just to let you guys know the solution I used in resolving this problem.

I had to uninstall and reinstall .NET 1.1 and 2.0 frameworks.

Then I applied their service packs and ran the add and remove program to

repair the .NET 2.0 framework.

I can now connect successfully to all the sql server databases using management studio.

sql

Easy way to sync

If you want help interpreting the error messages, then
please post them up. There's a lot of experience on this
newsgroup, and you might find that there's no need to
purchase a 3rd party tool at all.
SQL Datacompare would be an alternative if you want to go
down that path, or DataMirror.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Thanks for your reply. Is there are any free good utilities available for
this ?
Thanks again,
-Aruna
"Paul Ibison" wrote:

> If you want help interpreting the error messages, then
> please post them up. There's a lot of experience on this
> newsgroup, and you might find that there's no need to
> purchase a 3rd party tool at all.
> SQL Datacompare would be an alternative if you want to go
> down that path, or DataMirror.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||SQL Server Replication
(AFAIK there aren't any other data-synchronization free
utilities.)
Rgds,
Paul Ibison (SQL Server MVP)

Dyncamic SQL

Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>

Dynamic Sql: Truncate Table: Trap @@ERROR

Greeting All, I have a stored proc that dynamically truncates all the
tables in my databases. I use a cursor and some dynamic sql for this:

.....
create cursor
Loop through sysobjects and get all table names in my database.

...
exec ('truncate table ' + @.TableName)

Now, I want to be able to determine if an error occurred or not nad log
that error to a table in another database.

However, when I try to trap the value of @.@.ERROR after the
exec ('truncate table ' + @.TableName) when an actual error occurs it
fails. My error was synthetically created by placing a foreign key on
the table which precludes the option of truncation:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.

The actual relevant code snippet is:

BEGIN
BEGIN
SET @.v_RowCount = (
SELECT rowcnt
FROM sysindexes
WHERE id = (
SELECT id
FROM sysobjects
WHERE name = @.v_Name
)
AND indid IN (0,1)
)
EXEC('truncate table ' + @.v_Name)
-- If there was an error truncating the current table.
-- Write the event to the MessageLog table.
IF (@.@.ERROR <> 0)
BEGIN
SET @.v_OutputMessage = ('There was an error ' + @.v_name)
INSERT INTO MessageLog (message) values (@.v_outputmessage)
RETURN (-1)
END

Like I was saying, when the error is generated because of the foreign
key the variable @.@.error is never set to 4712, in fact if I were to put
a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
would never be executed. The only thing that would show up in
Enterprise Manager would be the:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.
Any ideas as to what is going on here?

Thanks, TFD."LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1106171149.461401.53660@.f14g2000cwb.googlegro ups.com...
> Greeting All, I have a stored proc that dynamically truncates all the
> tables in my databases. I use a cursor and some dynamic sql for this:
>
> ....
> create cursor
> Loop through sysobjects and get all table names in my database.
> ...
> exec ('truncate table ' + @.TableName)
> Now, I want to be able to determine if an error occurred or not nad log
> that error to a table in another database.
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> The actual relevant code snippet is:
> BEGIN
> BEGIN
> SET @.v_RowCount = (
> SELECT rowcnt
> FROM sysindexes
> WHERE id = (
> SELECT id
> FROM sysobjects
> WHERE name = @.v_Name
> )
> AND indid IN (0,1)
> )
> EXEC('truncate table ' + @.v_Name)
> -- If there was an error truncating the current table.
> -- Write the event to the MessageLog table.
> IF (@.@.ERROR <> 0)
> BEGIN
> SET @.v_OutputMessage = ('There was an error ' + @.v_name)
> INSERT INTO MessageLog (message) values (@.v_outputmessage)
> RETURN (-1)
> END
>
> Like I was saying, when the error is generated because of the foreign
> key the variable @.@.error is never set to 4712, in fact if I were to put
> a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
> would never be executed. The only thing that would show up in
> Enterprise Manager would be the:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> Any ideas as to what is going on here?
> Thanks, TFD.

That particular error terminates the current batch, which is why the rest of
the code doesn't execute - Erland has a detailed article about error
handling in MSSQL, in which he points out that there is unfortunately no
real consistency about which errors will do this:

http://www.sommarskog.se/error-hand...#statementbatch

If your goal is to delete all rows from all tables, then truncation won't
work anyway, so you would have to look at something else. Cascading foreign
keys with DELETE would work, but of course it wouldn't perform as well (and
it wouldn't reset any identity seeds, if that's relevant). Rebuilding the
tables (and constraints and indexes) from scripts or restoring an 'empty'
backup would be other options.

Simon|||LineVoltageHalogen (tropicalfruitdrops@.yahoo.com) writes:
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.

This error can be avoided before-hand by

IF NOT EXISTS (SELECT * FROM sysforeignkeys
WHERE rkeyid = object_name(@.tbl))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and Simon, thank you for your responses. The example I showed
you was created so that I could create an error (testing phase of a big
project) during the truncation operation, it was the only way I knew of
that would guarentee an error. This database actually has no foreign
keys in it, it is a persistent store which feeds a dimensional data
store for an OLAP server. This small snippet I brought forth is just a
tiny piece of a much larger data load. I just wanted to be able to
trap an error during the truncation process if one should arise, I
can't imagine any such thing happening but I just wanted to be able to
log it should it occur.

Regards, TFD.

Dynamic SQL with Datetime variables

I am having difficulty using dynamic SQL with datetime variables. A code
snippet like this gives me an error about converting a string to datetime:
...
WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
AND clutr_nm LIKE ' + @.Market + '
AND Convert(datetime, W) >= ' + @.startdate + ' '
@.startdate is the datetime variable. Using '' + @.startdate + '' prints the
literal variable @.startdate, while using ''' + @.startdate + ''' gives me the
same conversion error. How do I solve this?
Larry Menzin
American Techsystems Corp.The string concatination looks correct
probable you are looking for:
WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
AND clutr_nm LIKE ' + @.Market + '
AND W >= ' + datepart(w,@.startdate)
is this the one you are looking for? what should be the result of the query
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Larry Menzin" wrote:

> I am having difficulty using dynamic SQL with datetime variables. A code
> snippet like this gives me an error about converting a string to datetime:
> ...
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND Convert(datetime, W) >= ' + @.startdate + ' '
> @.startdate is the datetime variable. Using '' + @.startdate + '' prints th
e
> literal variable @.startdate, while using ''' + @.startdate + ''' gives me t
he
> same conversion error. How do I solve this?
> --
> Larry Menzin
> American Techsystems Corp.|||Dont no whether you defined the datetime as ISO (then the implicit
conversion will to add a number to a string which wouldnt be that nice).
How did you dfine your Datetime variable ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:40CFE544-2384-4BAD-9FB3-339A5D10BB88@.microsoft.com...
>I am having difficulty using dynamic SQL with datetime variables. A code
> snippet like this gives me an error about converting a string to datetime:
> ...
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND Convert(datetime, W) >= ' + @.startdate + ' '
> @.startdate is the datetime variable. Using '' + @.startdate + '' prints
> the
> literal variable @.startdate, while using ''' + @.startdate + ''' gives me
> the
> same conversion error. How do I solve this?
> --
> Larry Menzin
> American Techsystems Corp.|||Correction:
datepart(ww,@.startdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> The string concatination looks correct
> probable you are looking for:
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND W >= ' + datepart(w,@.startdate)
> is this the one you are looking for? what should be the result of the quer
y
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Larry Menzin" wrote:
>|||Doesn't work.
When using dynamic SQL, '+@.variable+' is used for numbers, '''+@.variable+'''
is used for strings, but what syntax is used for dates? I can't seem to find
anything that works.
Larry Menzin
American Techsystems Corp.
"Chandra" wrote:
> The string concatination looks correct
> probable you are looking for:
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND W >= ' + datepart(w,@.startdate)
> is this the one you are looking for? what should be the result of the quer
y
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Larry Menzin" wrote:
>|||DECLARE @.startDate datetime
SET @.startDate = '4/1/2005' /* for testing */
I still can't get dynamic SQL to work using date variables embedded in the
SQL string.
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> Don′t no whether you defined the datetime as ISO (then the implicit
> conversion will to add a number to a string which wouldn′t be that nice).
> How did you dfine your Datetime variable ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:40CFE544-2384-4BAD-9FB3-339A5D10BB88@.microsoft.com...
>
>|||Like mentioned in my previous post, the "Date" you are pasting in is in
some numeric format, so that SQL Server implicitly converts it to a number,
if you wanna add a number to a string (which cant be converted to a number)
the query parser will punish you with an error.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Larry,
What data type is column [W]?
Datetime values should be concatenated as characters values.
Example:
use northwind
go
declare @.i int
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
set @.sd = '19970101'
set @.ed = '19971231'
set @.sql = N'select @.i = count(*) from dbo.orders where orderdate > ''' +
convert(char(8), @.sd, 112) + N''' and orderdate < ''' + convert(char(8),
dateadd(day, 1, @.ed), 112) + N''''
print @.sql
exec sp_executesql @.sql, N'@.i int output', @.i output
print @.i
go
AMB
exec
"Larry Menzin" wrote:
> Doesn't work.
> When using dynamic SQL, '+@.variable+' is used for numbers, '''+@.variable+'
''
> is used for strings, but what syntax is used for dates? I can't seem to fi
nd
> anything that works.
>
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Chandra" wrote:
>|||Ok. I got it to work by converting datetime variables to character strings
and then comparing strings. Dates apparently cannot be used directly in
dynamic SQL. What is the performance hit from all these date to string
conversions?
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> Larry,
>
> What data type is column [W]?
> Datetime values should be concatenated as characters values.
> Example:
> use northwind
> go
> declare @.i int
> declare @.sql nvarchar(4000)
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '19970101'
> set @.ed = '19971231'
> set @.sql = N'select @.i = count(*) from dbo.orders where orderdate > ''' +
> convert(char(8), @.sd, 112) + N''' and orderdate < ''' + convert(char(8),
> dateadd(day, 1, @.ed), 112) + N''''
> print @.sql
> exec sp_executesql @.sql, N'@.i int output', @.i output
> print @.i
> go
>
> AMB
> exec
> "Larry Menzin" wrote:
>|||> dynamic SQL. What is the performance hit from all these date to string
> conversions?
Can you show the code?. I have not idea what conversions are you talking
about.
AMB
"Larry Menzin" wrote:
> Ok. I got it to work by converting datetime variables to character strings
> and then comparing strings. Dates apparently cannot be used directly in
> dynamic SQL. What is the performance hit from all these date to string
> conversions?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>

Dynamic SQL stops responding with no error message

On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls another
SP (sp_BuildPayments) which has among several lines of code one specific lin
e
that cancel the process with no error message. The line is a dynamic SQL tha
t
follows below:
execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
I've tried a lot of things with no sucess to find out the problem. Acctually
the code stop running in this line but @.@.error is 0. Does anyone know how to
solve this?
Thanks in advance.
Cheers.
JorgeJorge Luis Ribeiro wrote:
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
> another SP (sp_BuildPayments) which has among several lines of code
> one specific line that cancel the process with no error message. The
> line is a dynamic SQL that follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' +
> @.Account_Value + ' from [PRM].[dbo].[' + @.TableName +
> ']' + ' where datepart(month, Event_Date) = ' +
> @.Event_Month + ' and datepart(year, Event_Date) = ' +
> @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
> Acctually the code stop running in this line but @.@.error is 0. Does
> anyone know how to solve this?
> Thanks in advance.
> Cheers.
> Jorge
Do the users have UPDATE rights on the underlying table? The update is
going to require a table scan every time because you do not have and
SARGable clauses in the query. It's possible you are running into a
locking/blocking issue. You should be able to see the error value
outside the EXEC, unless there is a trigger on the table which is
resetting the value. Do you have a trigger on the table?
David Gugick
Imceda Software
www.imceda.com|||Hi
Print ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
Run the output in QA and see if it does the job.
"Jorge Luis Ribeiro" <Jorge Luis Ribeiro@.discussions.microsoft.com> wrote in
message news:B259EFD8-FECF-4652-844B-1AF42FE3F697@.microsoft.com...
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
another
> SP (sp_BuildPayments) which has among several lines of code one specific
line
> that cancel the process with no error message. The line is a dynamic SQL
that
> follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
> ' from [PRM].[dbo].[' + @.TableName + ']' +
> ' where datepart(month, Event_Date) = ' + @.Event_Month +
> ' and datepart(year, Event_Date) = ' + @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
Acctually
> the code stop running in this line but @.@.error is 0. Does anyone know how
to
> solve this?
> Thanks in advance.
> Cheers.
> Jorge|||Actually there is a trigger that calls the sp_CloseBusinessAccount that call
s
the other one.
I've tried to check the @.@.error in both procedures with no sucess.
As you mentioned the lock issue, this table @.TableName is a table that has
been created by the same procedure I intend to update.
"David Gugick" wrote:

> Jorge Luis Ribeiro wrote:
> Do the users have UPDATE rights on the underlying table? The update is
> going to require a table scan every time because you do not have and
> SARGable clauses in the query. It's possible you are running into a
> locking/blocking issue. You should be able to see the error value
> outside the EXEC, unless there is a trigger on the table which is
> resetting the value. Do you have a trigger on the table?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
Rea
Try using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
=========== step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam = MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

Dynamic SQL Question

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting
the varchar value 'SELECT ' to a column of data type int.):
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
EXEC (@.SQL)
How can I make this work?
Thanks,
Ninelhi ninel,
Please, post DDL instructions but at first, try to sustitute this line for
the another one:

> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)

> set @.sql = ('
SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''')

> EXEC (@.SQL)
"ninel" wrote:

> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||I see no need for dynamic SQL here:
DECLARE @.sCalldate varchar(10)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.RC=Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x'
Razvan|||Hi!
declare
@.sql varchar(8000),
@.sCalidate varchar(10),
@.RC int;
set @.sql = 'select @.RC = count(*) from from tmPunchTime where scalldate =
@.sCallDate and is null(sRawLogout, ''x'') = ''x''';
exec sp_executesql @.sql, N'@.RC int out, @.sCalidate varchar(10)', @.RS out,
@.sCalidate = @.sCalidate;
select @.RC
Micle.
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:orOdnbUkTPNtF33fRVn_vA@.giganews.com...
>I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||Hi Ninel,
I agree with Razvan Socol that there is no need for Dynamic SQL.
Please visit this link and view When not to use Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html ( An authority on Dynamic
SQL)
If in your case you donot know TableName or Parameters are changing
then Micle approach is what you need
Please let me know if it clear your doubts.
With warm regards
Jatinder|||No need to use dynmaic sql to get the results you want. Please see example
below.
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
DECLARE @.sEmployeeNameId int
DECLARE @.sAgentid int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
CREATE TABLE #tmPunchTime
(
Scalldate datetime,
sRawLogout sysname
)
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
SELECT @.RC =(SELECT Count(*)
FROM #tmPunchTime
WHERE convert(nvarchar,scalldate,112) = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x')
SELECT @.RC
DROP TABLE #tmPunchTime
"ninel" wrote:

> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>

Dynamic SQL question

i am using Dynamic SQL .
print @.sql <- It will show the statment if there is an error , Now, How can
i show the statment even there is an error
EXEC (@.sql)
Thanks a lotDont understand exactly your question, but you can print it before
executing the @.Sql, so you get the information in the messages pane (QA).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23pMdy9LSFHA.1176@.TK2MSFTNGP12.phx.gbl...
>i am using Dynamic SQL .
> print @.sql <- It will show the statment if there is an error , Now, How
> can i show the statment even there is an error
> EXEC (@.sql)
> Thanks a lot
>|||Thanks Jens,
How can I print it before excuting the @.sql ?
Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:e$pfxEMSF
HA.3444@.tk2msftngp13.phx.gbl...
> Dont understand exactly your question, but you can print it before
> executing the @.Sql, so you get the information in the messages pane (QA).
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:%23pMdy9LSFHA.1176@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
Print @.sql is good enough. Could you please post your entire part of dynamic
SQL. The error might be because of some other issues in declaration or
asssignment.
Thanks
Hari
SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23$b8JcMSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Thanks Jens,
> How can I print it before excuting the @.sql ?
> Thanks
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:e$pfxEMSFHA.3444@.tk2msftngp13.phx.gbl...
>|||Print @.sql --> Messages Pane
or Select @.sql --> Result Pane
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23$b8JcMSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Thanks Jens,
> How can I print it before excuting the @.sql ?
> Thanks
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:e$pfxEMSFHA.3444@.tk2msftngp13.phx.gbl...
>

Dynamic sql problem

Hello: I am tearing my hair out over this.

I am using sql server 2005 and am getting the error "arithmetic overflow error converting nvarchar to numeric." The sp is below. It works fine when I do not include the numeric variable @.z and only insert the other 3 fields. The varbinary(MAX) is not the problem. The problem is syntax error mixing numbers and strings. How do I put the numeric @.z in the @.query string so I can insert a value into the FileID numeric field?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER off

ALTER PROCEDURE [dbo].[uspInsert_Blob] @.x as nvarchar(60), @.y as nchar(5), @.z as numeric(18, 0)

as

DECLARE @.QUERY VARCHAR(2000)

SET @.QUERY = "INSERT INTO myTable(FileID, FileName, FileType, Document)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " +@.z+ " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

EXEC (@.QUERY)

cast the variable to a nvarchar(18)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " + cast(@.z as nvarchar(18)) + " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

|||

Yes that works. I thank you so much.

When the exec query runs, does sql server recast it as numeric since the parameter and the field are numeric?

|||

Yes, it does an implicit conversion based on datatype conversion precedence:

You can see them listed here:
http://msdn2.microsoft.com/en-us/library/ms190309.aspx

All lower precedence types get implicitly converted to the highest precedence type if it is possible.

Louis

Dynamic SQL Problem

I'm getting "Invalid Column" error with below code. Can anyone Help?
CODE:
USE [Northwind]
GO
declare @.SQL varchar(1000), @.debug int
declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
int
set @.sTable="Orders"
set @.sField="OrderDate"
set @.sField2="employeeID"
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
--EXEC(@.SQL)Your @.variables are not passed into the Exec(), nor should they be. The
Exec() is run separately from the stored proceedure.
Here is a simple example of how I circumvent this:
declare @.SQL varchar(1000)
declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
set @.getTable='Orders'
set @.getField='OrderDate'
set @.getFilter='employeeID = 3'
SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
Set @.SQL = Replace(@.SQL,'getField',@.getField)
Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
EXEC(@.SQL)
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||that's fine except i need a @.employeeID variable. i hardcoded 3 just for
this simple example and will actually be passing 2 WHERE variables in the
production code. can you modify your code?
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:145010B5-D41D-4184-BE78-7F239164BD44@.microsoft.com...
> Your @.variables are not passed into the Exec(), nor should they be. The
> Exec() is run separately from the stored proceedure.
> Here is a simple example of how I circumvent this:
> declare @.SQL varchar(1000)
> declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
> set @.getTable='Orders'
> set @.getField='OrderDate'
> set @.getFilter='employeeID = 3'
> SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
> Set @.SQL = Replace(@.SQL,'getField',@.getField)
> Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
> Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
> EXEC(@.SQL)
>
> "Scott" wrote:
>|||My goof. Your doing pretty much what I am. However, you have double quotes
around you @.variables.
Also watch out for empty space, by declaring as char rather than varchar,
your string is longer than it needs to be.
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||You main problem is that you delimited your strings with double quote marks.
In T-SQL, strings are delimited with single quotes. i.e. set @.sTable='Order
s'
For readability of the print @.sql, I changed your variables from char(40) to
varchar(40). The code will execute fine with char(40), it just has a lot of
extra spaces.
If you change the variables to varchar or you happen to have a table name of
40 characters, the WHERE clause will fail because there will be no space
between them. I suggest you put a space in front of the word WHERE as I've
done below.
Finally, EXEC (@.SQL) is no longer the recommended best practices. You
should be using exex sp_executesql which requires unicode input so I changed
@.SQL from varchar(1000) to nvarchar(1000).
Hope that helps,
Joe
Here's the corrected code:
USE [Northwind]
GO
declare @.SQL nvarchar(1000), @.debug int
declare @.sTable varchar(40), @.sField varchar(40), @.sField2 varchar(40),
@.employeeID int
set @.sTable='Orders'
set @.sField='OrderDate'
set @.sField2='employeeID'
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + ' WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
exec sp_executesql @.sql
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||Parameterised dynamic queries are best done using the sp_executesql system
procedure. No hassle, no fuss - pure execution.
ML
http://milambda.blogspot.com/

Dynamic SQL Job

I am trying to create a sql job that has multiple steps, emails out the error on a failure, and will continue to the next step. We can not put Outlook on our servers so we can not use SQL Operators.

Basically I would like every step in the job to jump to a failure step that emails out an error message and returns to the step after the step that failed. I could have a failure step for each actual step, but this job has a number of steps and being the anal programmer I am, I don't want to have multiple steps that do the same thing.

I have figured out how to Dynamically update the current sql Job by using sp_update_jobstep and job tokens [STEPID] and [JOBID]. For example the following code will update the job so it will quit job with failure if the step finishes successfully.

declare @.tmpJobID as uniqueidentifier
declare @.tmpStepID as integer

set @.tmpJobID = (convert(uniqueidentifier, [JOBID]))
set @.tmpStepID = [STEPID]

exec sp_update_jobstep @.job_id=@.tmpJobID, @.step_id=@.tmpStepID, @.on_success_action=2

The problem is that the job is not updated until it is completed. So, the changes will not take affect until the next time it is ran. Is there a way to reload the job into memory, or force the job to use the updates?

I have also tried directly updating the sysjobsteps table but that didn't work either.

Thanks much.Well, does anyone know how to get a job to dynamically jump to a specified step?

Thanks much|||Built-in step dependency has limited complexity capabilities. You'll probably haveto merge the steps and...code away!

Dynamic SQL Issue

When I execute the following stored procedure I get the
error: 'Invalid operator for data type. Operator equals
subtract, type equals varchar.'
SQL Server thinks I'm trying to subtract the mobile_phone
instead of adding dashes between the numbers.
Here is my stored procedure:
---
create PROCEDURE SelectSortedUsers
@.SortColumn varchar(70)
, @.SortDirection char(4)
AS
declare @.sqlstring varchar(2000);
set @.sqlstring = 'select u.last_name
, u.logon , territory
, r.short_description as Role
, r.role_key
, u.active
, CONVERT(varchar,u.last_login_dt,101) as
last_login_dt
, u.email
, substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) as mobile_phone
from users u
inner join roles r
on u.role_key = r.role_key
order by u.' + @.SortColumn + ' ' + @.SortDirection
exec (@.sqlstring);
=========================================================
How can I add dashes for mobile phone?
Thanks.
DarinYou need to surround strings with single quotes (you will probably see the
problem if you use PRINT @.sql instead of EXEC(@.sql))
, ''' + substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Darin Browne" <db@.nospam.com> wrote in message
news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
> When I execute the following stored procedure I get the
> error: 'Invalid operator for data type. Operator equals
> subtract, type equals varchar.'
> SQL Server thinks I'm trying to subtract the mobile_phone
> instead of adding dashes between the numbers.
> Here is my stored procedure:
> ---
> create PROCEDURE SelectSortedUsers
> @.SortColumn varchar(70)
> , @.SortDirection char(4)
> AS
> declare @.sqlstring varchar(2000);
> set @.sqlstring = 'select u.last_name
> , u.logon , territory
> , r.short_description as Role
> , r.role_key
> , u.active
> , CONVERT(varchar,u.last_login_dt,101) as
> last_login_dt
> , u.email
> , substring(u.mobile_phone, 1, 3) + '-' +
> substring(u.mobile_phone, 4, 3) + '-' +
> substring(u.mobile_phone, 7, 4) as mobile_phone
> from users u
> inner join roles r
> on u.role_key = r.role_key
> order by u.' + @.SortColumn + ' ' + @.SortDirection
> exec (@.sqlstring);
> =========================================================> How can I add dashes for mobile phone?
> Thanks.
> Darin|||Aaron, thanks for your quick reply.
Applying your suggestion, I get an error because Server
doesn't know what table 'u' is aliasing because it's now
outside the dynmaic string where 'u' is aliased.
I've tried moving the 3 quotes around to find the perfect
spot but to no avail.
Any ideas?
Thanks.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>|||It's working!
Thanks for your help.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>