exec sql

I need help understanding the syntax of the "exec sql" statement.

i am looking at code that build an sql string such as

sql="exec SOMETHING Session("id")"

or something like that.

then, there is

conn.execute(sql)

My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.

i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.

in particular, the code i am examining is

sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)

i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?I would wager that SurveyDelete is the name of a stored procedure in the database. Have a look there.|||i bet your right. thanks.

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florenceSQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florence
SQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

Exec @string

I am trying to do an insert statement utilizing a variable string.

something like:

Set @.cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @.lServer + ';User ID=' + @.user + ';Password=' + @.pword + char(39) + '
).myServer..

Insert into #Temp_table (field1, field2)
select exec @.cString

--What is the syntax for this?as answered in previous post. you can like so:

insert tb
exec(@.sql)|||When I do something like:

Insert into #tACCOUNT (ACCOUNTID, ACCOUNT, TYPE)
exec (@.cString)

I get:

MSDTC on server 'myServer' is unavailable.|||dtc is needed to ensure data integrity between 2 sites. when you do any dml, the transaction is implicitly promoted to a distributed transaction.

EXEC / Sp_executesql with Dynamic SQL string exceeding 4000 - SQL Server 2000

This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.

I have my SQL string exeeding more than 4000 characters.
The sp_executesql expects its parameters to be declared as nvarchar/ntext.
ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.

The following was the original approach.
===============
DECLARE @.sql nvarchar(4000),

--
select @.sql=@.sql+'select.................'
--
--
INSERT INTO #Temp
EXEC sp_executesql @.SQL,@.paramlist

===============================

The above sql was split into two sql variables and tried in the following way.
==============================
DECLARE @.sql nvarchar(4000), @.sql1 nvarchar(4000),@.paramlist nvarchar(4000)

--
select @.sql=@.sql+'select.................'
select @.sql1=@.sql1+'from table1, table2.........'
--
--

INSERT INTO #Temp
exec('EXEC sp_executesql ''' + @.sql + @.sql1 + ''',@.paramlist')
====================

However, it compiles correctly but during the execution, the error indicates as syntax error.
I ran the resulting SQL string (@.sql + @.sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through
sp_executesql, it gives me error

Appreciate if anyone can suggest a workaround for this situation.

Try the other version of executing dynamic SQL the simple one....

EXEC(@.sql + @.Sql1)

Because i have used this 2 one place where my Dynamic SQl even exceeds 1lakh characters....|||

Thanks Mandip. It's working. Appreciate your help.

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

Excessively long string

On my form I have a web control that shows my report services report. When I
click on the printer button I get the error "An error has occurred trying to
get the current window.", then the next error, "A Runtime Error has occurred.
Do you wish to Debug? Line: 25 Error: Marshaler restriction: Excessively long
string."Hello Mike,
Before we go further on this issue, I'd like to know which web control you
use to show reports? Is it Web browser control? Does the issue occurs on
different reports? If you use a simple report with some textboxes, can you
reproduce the issue? Do you have RS 2005 sp1 installed?
Based on my research, you may have encountered the following known issue
913771 FIX: You may receive an error message when you try to print a SQL
Server
2005 Reporting Services report from a WebBrowser control
<http://support.microsoft.com/default.aspx?scid=kb;EN-US;913771>
This issue is fixed in SRS 2005 sp1. If you did not have applied sp1,
please go to the following link to get it
http://www.microsoft.com/downloads/details.aspx?FamilyId=CB6C71EA-D649-47FF-
9176-E7CAC58FD4BC
If you need any further help, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||SP fixed it. Thanks.
"Peter Yang [MSFT]" wrote:
> Hello Mike,
> Before we go further on this issue, I'd like to know which web control you
> use to show reports? Is it Web browser control? Does the issue occurs on
> different reports? If you use a simple report with some textboxes, can you
> reproduce the issue? Do you have RS 2005 sp1 installed?
> Based on my research, you may have encountered the following known issue
>
> 913771 FIX: You may receive an error message when you try to print a SQL
> Server
> 2005 Reporting Services report from a WebBrowser control
> <http://support.microsoft.com/default.aspx?scid=kb;EN-US;913771>
> This issue is fixed in SRS 2005 sp1. If you did not have applied sp1,
> please go to the following link to get it
> http://www.microsoft.com/downloads/details.aspx?FamilyId=CB6C71EA-D649-47FF-
> 9176-E7CAC58FD4BC
> If you need any further help, please feel free to let's know. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Exception handling

it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.

publicvoid get_issid(string cse_email,string tech_email,string subject,string issue_details,string response,string solv_date,outint issid)

{

// Establish Connection

SqlConnection oConnection = GetConnection();

// build the command

SqlCommand oCommand =newSqlCommand("get_issid", oConnection);

oCommand.CommandType =CommandType.StoredProcedure;

// Parameters

SqlParameter paracse_email =newSqlParameter("@.cse_email",SqlDbType.VarChar, 50);

paracse_email.Value =cse_email;

oCommand.Parameters.Add(paracse_email);

SqlParameter paratech_email =newSqlParameter("@.tech_email",SqlDbType.VarChar,50);

paratech_email.Value = cse_email;

oCommand.Parameters.Add(paratech_email);

SqlParameter parasubject =newSqlParameter("@.subject",SqlDbType.VarChar, 50);

parasubject.Value = subject;

oCommand.Parameters.Add(parasubject);

SqlParameter paraissue_details =newSqlParameter("@.issue_details",SqlDbType.VarChar, 500);

paraissue_details.Value = issue_details;

oCommand.Parameters.Add(paraissue_details);

SqlParameter pararesponse =newSqlParameter("@.response",SqlDbType.VarChar, 500);

pararesponse.Value = response;

oCommand.Parameters.Add(pararesponse);

SqlParameter parasolv_date =newSqlParameter("@.solv_date",SqlDbType.DateTime);

parasolv_date.Value = solv_date;

oCommand.Parameters.Add(parasolv_date);

SqlParameter paraissid =newSqlParameter("@.issid",SqlDbType.Int);paraissid.Direction =ParameterDirection.Output;

oCommand.Parameters.Add(paraissid);

try

{

oConnection.Open();

oCommand.ExecuteNonQuery();

issid =int.Parse(paraissid.Value.ToString());

}

catch (Exception oException)

{

throw oException;

}

finally

{

oConnection.Close();

}

}

the stored procedure is:

create proc [dbo].[get_issid](@.tech_emailvarchar(50), @.cse_emailvarchar(50),@.subjectvarchar(50),@.issue_detailsvarchar(500),@.responsevarchar(500),@.solv_datedatetime, @.issidintoutput)

as

select @.issid=tech_response.issue_idfrom tech_response,issue_detailswhere tech_response.tech_email=@.tech_emailand tech_response.cse_email=@.cse_emailand tech_response.subject=@.subjectand tech_response.issue_details=@.issue_detailsand response=@.responseand solv_date=@.solv_dateand tech_response.issue_id=issue_details.issue_id

requested to help in this

Use ExecuteScalar or ExecuteReader .

ExecuteNonQuery can be used only on DDL statements such as Insert and Delete statements.

|||

Hi,

Thank u for your reply. but the error is coming again same as earlier.

pls help me in this regard.

your amibly,

nagireddy

excel source numeric/string data problem

i've been reading some problems with excel source data being force as

numeric type when there are string/numeric type in the data, but adding

IMEX=1 to the extended properties will fix this problem...this is true

but not in my case...

say my excel file have about 40 rows..if row 1-39 in column A are all

NULL and row 40 has a string in it, the string in row 40 will not be

converted and the excel source is forcing this column A data type to be

numeric..having IMEX=1 in there does not work..however..if i add a

string anywhere in row 1-8 in column A, the the string in row 40 will

be converted because the external data type now is a string..

anyone know how to solve this problem?solved this problem by going into the excel file and format first first 8 rows as text|||

An alternative is to change the datatype for the column using the "Show Advanced Editor" menu on the Excel Data Source. Select the "Input and Output Properties" tab, expand the Excel Source Output and the the "Output Columns" (Note: not the External Columns). Now you can select change the DataType property of some \ all columns.