.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Assign a local variable a value via dynamic sql

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

declare @myint int,

@query = varchar(4000)

set @query = "select max(id) from[" + dbo.fnGetDBName()  + "].dbo.mytable"

set @myint = exec @query  -- fails

DBCC Checkident reseed(mytable,reseed,@myint)

--more processing statements--

trouble is I cannot get @myint to be asigned.

I can take the long route and create a temp table and assign the value after selecting the first row. I tried sp_executesql which works, but nothing else is processed after that.


View Complete Post

More Related Resource Links

Local variable scoping in C#

In my previous post, Compiler-generated scopes for local variable declarations, I briefly touched on the issue of multiple meanings applied to the same name. In this post, I'll aim to flush out the compiler's rules with regards to binding names in their local scopes.

Compiler-generated scopes for local variable declarations

I was tasked with understanding and fixing a bug on error reporting with foreach iteration variables the other day, and it got me thinking about local variable scoping rules in C# in general. First, the bug.

SSIS Deployment Reads local Environment Variable and not Server

Hi All, I have an SSIS package that uses an Environment variable to hold the connection string for the local SSISConfiguration table.  This works properly on my local machine.  However, when I import the package to Integration Services(through SSMS) it reads the environment variable from the local machine and not the server it is deployed to.  If I log on to the server remotely and do the same process it uses the the correct environment varible... (SQL Server 2005) Is there a way to deploy the package to the server and get it to use the correct Env variable without having to log-in to the server and import it?  Let me know if anyone is looking for additional information.   Thanks!

How to declare variable and assign value in xslt

Hi, I am writing xslt to create HTML by using input xml data. The input XML looks like this ID Name  Description 1 Name1 Description Something1 Name1 Description another text2 Name2 Some text2 Name2 Some text22 Name2 Some text2 Here Id is primary, where Name is unique as per ID. I want to show the output the following way Name1   * Description Something   * Description another textName2   * Some text   * Some text2   * Some text2 So I want to declare a variable in XSLT and make a loop in records and store the previous record ID. IF the previous record id is same, then no need to show the name. But when I declare in the following xslt, I am getting 'The variable or parameter 'xxx' was dublicated within the same scope' error<xsl:output method ="html" media-type="text/html" encoding="iso-8859-1" /> <xsl:template match="Details"> <xsl:variable name="counter" select="0"/> <xsl:for-each select="XMLParentNode/XMLDetailNode/XMLSubDetailNode"> <xsl:if test="$counter = 1"> <p> <b style='text-color:red;'> <xsl:value-of select="NAME" disable-output-

What type variable can I use to assign a possible Null or a string to?



I am converting a VB6 app to VB2008 and are having problems with replacing Variant variables.

I used Variant in cases where the data might be string or Null.

Is there a way I could check for Null and just set the string to zero lenght?

Any help would be appreciated.



can dynamic sql be used against a table variable?


This Works

create table #tmp1(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into #tmp1(rowID) SELECT ' + @rowID)

This does not work

Declare @tmp1 table(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into @tmp1(rowID) SELECT ' + @rowID)

Error Msg:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tmp1'.

Is there a way to use Dynamic Sql against a Table var -- @tmp1?
How to do this?

The goal is to be able to pass in a string param like this to a proc

s1 = 'aa'',''bb'',''cc'

declare @s1 varchar(20)

Begin As

declare @tmp1(...)
exec('Insert Into ' + @tmp1 + ' (...) Where fldx In (''' + @s1 + ''')')


Assign guid to variable in control flow


How do I assign a script task generated GUID to an ssis variable and then pass it to sql server via a stored proc? I've only been able to find info on how to do it in a data flow task (derived column)

There doesn't seem to be a GUID type in the Data Type drop down in the Variables tab

I get the following error whether I create the variable as an object or string & setting the data type of the variable to either object,guid or string in the parameter mapping tab of the execute sql task don't do much either

Error: 0xC002F210 at Log Fail, Execute SQL Task: Executing the query "EXEC dbo.spLogGenericExtractControlOnComplete
    ..." failed with the following error: "Conversion failed when converting from a character string to uniqueidentifier.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Log Fail


What magic combination of settings do I have to use to make it work?

need to store the value of Execute SQL(dynamic sql) into variable.


Hi guys, I need to store the value of my dynamic sql query into a variable

Basically i am passing a table name and then doing a count(*) to get the number of rows but having difficulty in storing the value of the Exec(@sql) into a variable.

Pls help


Select @sql = 'Select' + CAST(@TRCnt AS Varchar) + ' = count(*) From [' + @TTableName  + ']'



Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




Problem passing local variable for Approvers email in customised Approval workflow


I am building an Approval workflow using SPD and have used the Start an Approval Workflow task action.

The workflow works on a change register and looks up different list of approvers assigned to change items.

Using Local Variables, the look up assignment works fine.

My problem is that when I pass the Approvers variable to the email at the end of the workflow (the on Current Item with... part) it appears the variable in empty?

I have used the approach of adding a variable check in the content of the emails - as the workflow sends the emails anyway. The lookup works fine - it’s just the email at the end of the process that does not seem to be able to see the contents of the variables that are shown to be set earlier.

assign default value to dynamic data field



I have the script below and I want to apply a default value to the DynamicField StudentId (please see line in bold) below. Can anyone tell me how to do so?




        <asp:GridView ID="GridViewGuardians"  runat="server" SelectedIndex="1"
            AutoGenerateColumns="false" DataKeyNames="StudentGuardianId" DataSourceID="LinqDataSourceGuardians" ShowHeader="true" ShowFooter="True">
                   <asp:LinkButton  ID="lbInsertGuardianG" runat="server" CommandName="Insert" ValidateionGroup="addValidationGuardian" OnClick="lbInsertGuardianG_Click" >Insert</asp:LinkButton>

Adding multiple attachment (files) to a dynamic variable so it can be send to a email. VB 2010


Hello !

I need some advice on what to use for adding multiple attachment to a email. I'm using VB 2010.

First, I have a Form in my company web site to fill a reclamation ..... when you fill and send it, it convert the form into a PDF, save it in the client side "TEMP" and finally get the PDF and send it to a email..... that's perfectly  fine. I add to the form the capability of attach multiple files with file upload to the same "TEMP" and displayed in a Grid View to remove or add files and send everything to a email, the attachment capability is working. Everything is working with the PDF not the files(attachments). 

There is a way to get the multiple files from the "TEMP" folder and add it to some dynamically variable (array list, attachment collection, list collection, etc.) so all can be send to the email. I already know the way to put it into the email with the PDF. The problem is to get the files and add it to the dynamic variable and that it work with the email.

If the code is need it, let me know.


Copying rows using a dynamic SQL Command variable


I have a table with the Source TableName and Source ColumnName. I am trying to use SSIS to extract data from my source based on the tables and columns. I am using a Script Task to dynamically create the SELECT statement, no problems with that.

Next, I have a data flow task with a Source OLE DB Connection and the Data Access mode is using this dynamically created select statement. I am however unable to use this because this SELECT statement variable is not populated until runtime.

Basically I want to COPY ALL ROWS from the source to a destination. 

Is this possible? What is wrong with my assumptions? Any suggestions?


You are so wise...like a miniature budha covered in fur. -Anchorman

Dynamic Variable Names to RETURN value set.


Here is an example of the concept I am used to in Coldfusion using EVALUATE and DEEVALUATE. Trying to accomplish something dynamic code side using ASP.NET C# .NET 4.0 and VS2010. The example should explain what I am trying to do... should be simple, reflaction, dynamic, eval of some kind? I thought I was close with Boolean.TryParse() but no luck even though logically it accepts a string why not convert to references variable?


bool test1 = true;

string dynboolname = "test" + 1;

if(dynboolname == true)




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend