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

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

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

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!
DaveCan 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...
>|||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).

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!
DaveCan 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...
>>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
>|||> 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...
>> 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
>>
>|||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...
>>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
>|||"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).

EXEC Command problem

Tried to do a search on EXEC and got a lot of hits, but none that I saw
addressed my problem.
I'm trying to move a DB, but when I type,
EXEC sp_detach_db 'database'
I get the unrecognized command error.
I did a search for EXEC, but couldn't find it.
Any suggestions/assistance?
Thanks.
hi,
"BobCo" <BobCo@.discussions.microsoft.com> ha scritto nel messaggio
news:3B1E774A-0376-4CE3-B7D3-6BF054155724@.microsoft.com
> Tried to do a search on EXEC and got a lot of hits, but none that I
> saw addressed my problem.
> I'm trying to move a DB, but when I type,
> EXEC sp_detach_db 'database'
> I get the unrecognized command error.
> I did a search for EXEC, but couldn't find it.
> Any suggestions/assistance?
> Thanks.
EXEC is just execute the stored procedure (the system stored procedure, in
this case), and is required when the call to stored procedure is not the
very first line of the current Transact-SQL batch
so the problem is not EXEC ...
actually you usually should get
Server: Msg 229, Level 14, State 5, Procedure sp_detach_db, Line 50
EXECUTE permission denied on object 'sp_detach_db', database 'master', owner
'dbo'.
if your current user has not been granted membership to sysadmins server
fixed role, as only relative members are allowed to execute that system
stored procedure... further details at
http://msdn.microsoft.com/library/de...da-di_83fm.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi ,
The Exec command is just optional and the call to the stored procedure
sp_detach_db command completes even if the exec prefix is ommitted.
Also you can also try using the Enterprise manager to do the same task.
Girish Sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||hi Girish,
"Girish Sundaram" <girishs@.microsoft.com> ha scritto nel messaggio
news:MjgS8Dl7EHA.2600@.cpmsftngxa10.phx.gbl
> Hi ,
> The Exec command is just optional and the call to the stored procedure
> sp_detach_db command completes even if the exec prefix is ommitted.
> Also you can also try using the Enterprise manager to do the same
> task.
> Girish Sundaram
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
actually you can't... AFAIK, EM is not allowed to be used in conjunction
with MSDE instances..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

EXEC Command in all existing connections

If there any way to execute t-sql command in all connections in one time.
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>

Excuting SQL in a stored procedure

I know that you can execute a stored proc with the "execute" command, but is there any way to execute a string that is an sql string?

On a related note, is there a better way than building a string like "select * from table where" and then tacking on "value="+@.somevalue to do a search if your search is based on multiple (possibly null) variables? If you can answer the first question, dont bother with the second. =P

Its called dynamic SQL (or D-SQL).

declare @.sql varchar(max)

SET @.sql = 'SELECT * FROM TABLE WHERE NAME LIKE @.searchstring ORDER BY NAME' [or something like that]

EXEC sp_executesql @.sql, N'@.searchstring varchar(50)', @.searchstring

Now you have to be carefule about SQL Injection, which means that someone out there may try to insert SQL of their own like -- or WHERE 1=1 or other things that can force errors in your D-SQL, which report errors to the user which include things like table and column names. Which they can the use to get to your security tables or what have you. It can be very dangerous.

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

|||

Dynamic sql can be executed with thesp_executesql command however, if you know what possible columns the user could search on then it's best to use a CASE statement and avoid dynamic sql. If multiple columns may or not be searched, you can also useoptional parameters.

|||

There was a really ugly thread on that a few days ago and in the end the guy used D-SQL. Do you have a functioning example?

|||

I've never encountered a situation where I've needed to use D-SQL. Just start with the idea that you CANNOT use D-SQL. Like was said above, you can find a way using CASE statements in most cases. Why don't you post the stored procedure and maybe people can help you so you don't need to use D-SQL?

|||

Its not mine.

http://forums.asp.net/t/1163791.aspx

I think that there is a time and place for D-SQL. If you have a system that stores credit card numbers and stuff like that, well, yeah, no way. But if you don't have sensitive information in your DB, or if you can isolate your sensitive information on a seperate DB and maybe do joins through objects, D-SQL is safe enough. Starting with the idea that you can't use D-SQL no matter what is similar to a physician being told he can't use a particular medicine because some folks have died from overdoses of it.

I work for a gov't contractor on secure systems, and most of our work is framework development, so I haven't had to deal with the issue of the best and safest way to write a full text indexed search function. Its coming up though. But if I had the choice of writing 1 D-SQL routine and locking it down or writing 1,000 stored procedures that were table-specific, I think I'll go with D-SQL. Of course, I have the luxury of security testers who can be assigned to try to crack my code, and I realize that many of the folks here are one-man shows.

Now, it occurs to me that if hackers didn't get feedback through the .Net framework on the client when they forced SQL errors, SQL injection would be almost impossible. Is there some way to use compiler directives or attributes or something to prevent unhandled exceptions from bubbling up? Maybe replace it with a nice uninformative dialog box telling folks not to format their search strings in naughty ways and if they do it again they get their IP blocked?

|||

I learned to love dynamic SQL after I wrote a bunch of stored procedures using D-SQL and then ran into a problem that I had to try to track down and troubleshoot.. It's worth the extra time to spend to learn to do things the right way.

|||

I have seen a lot of novices fal into the trap. Things that are easiest to learn can get you into trouble that quickly too. D-SQL can be effective if used wisely. Same goes with CURSORs, TRIGGERs. It is very easy to write up a CURSOR than to figure out how to write a loop without CURSOR so novices fall into the trap and start using CURSOR for the simplest of the loops. I have seen people asking how to execute DTS package via TRIGGER. That was a new high for me.. There is always more than one way to skin a cat. Its worth the extra time to read up the best practices (even a quick google search brings up plenty of articles) and implement them and pays in the long run.

|||

Do you think we should start a D-SQL forum here? It seems like there's a lot of people wanting to know more about it and some people who are downright alarmed by it. Like fire, it is a wonderul servant and a terrible master...

|||

Erland has an excellent article:The Curse and Blessings of Dynamic SQL. Its long but worth reading.

|||

Yeah, you sent that to me 2 weeks ago. It was very good. Thanks again.

sql

except command not working

When I use EXCEPT in sql server 2005 (like union, union all), I am getting the following error. Did any body used this command in sql server 2005. (select * from t1 except select * from t2)

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.

Quote:

Originally Posted by sajithamol

When I use EXCEPT in sql server 2005 (like union, union all), I am getting the following error. Did any body used this command in sql server 2005. (select * from t1 except select * from t2)

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.

Select * from table1 except select * from table2 works perfectly fine for me, on SQL 2005