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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

using a sql query in a variable

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server


I'm using a stored procedure in Execute SQL task which return a query something like

select * from store where store_id in (3,6)

I then assigned this output to a variable Variable and try to run this query using OLE DB Source with the option SQL command from variable.

But i get an error

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Variable": "Exception from HRESULT: 0xC0015005".

Where am i going wrong? please guide me.


View Complete Post

More Related Resource Links

query string or session variable or neither for this scenario?

Hello,example scenario: user login has a list of associated 'accounts' it has access to... this list of accounts is stored in a database table, say tblAccountAccessControl, something like:login, accountID-----------------------userA, account123userA, account456userA, account798userB, accountABCuserB, accountDEFetc..this info is used throughout the web application and determines the 'domain' of information the particular login has access to. For example there are many other tables that have the 'accountID' field and whenever a page pulls data from the table it only pulls data that the currently logged in user should be allowed to see, based on that tblAccountAccessControl data.the security question:1) do I need to query the database every time I need to get this list of allowed accountID's? I was about to use a plain old query string to pass a particular accountID to another page but quickly caught myself as I realized that would be a major security flaw (the receiving page was going to use that accountID to grab info from the database, and there would be nothing preventing a user from simply typing in a url manually to get data from an accountID they are not supposed to be allowed to view). So I then thought I'll just store the list of accountID's in a session state variable so they will be carried with the user throughout the sessi

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

Query returns correct rows but I am not able to concatenate the rows into a variable :(


Hi all,

Why in the code below I am able to list all rows and display the field 'stepID' but I am not able to concatenate them all in the variable @stepIds?

declare @date as datetime
set @date = '2010-09-20 23:01:20:000'
declare @instance as uniqueidentifier
set @instance = '04658F34-CBF1-4CCD-A683-F23BDB669D35'

  Declare @CrLf char(2)
  Set @CrLf = Char(13) + Char(10)
	Declare @stepIds varchar(8000)
	Set @stepIds = ''
		--@stepIds = @stepIds + ' - Step: ' + CONVERT(varchar(4), stepID)

How To Map A GUID Variable And Use It In An OLE DB Source Data Flow Select Query


I have a simple SSIS package that does the following. 

On the control flow tab, there is an Execute SQL task that calls a stored procedure and returns a GUID. 

I have a package scoped variable that stores a UNIQUEIDENTIFIER output from the stored procedure. It appears that the package variable data type does not include a GUID data type so I tried to use string instead.

Next, the Execute SQL task is mapped to a data flow task. 
Within that data flow task I have an OLE DB Source which connects to a SQL Server Destination task.

On the OLE DB Source Editor screen I have a query that performs the following:

SELECT ID, SessionKey, ....
FROM dba_DroppedLoginsLog
WHERE SessionKey = ?

I have clicked on the parameters button to map the Parameter to my variable called User::SessionKey. This is the output variable from my Execute SQL Task component on the Control Flow tab. This value should be a GUID, but again, I don't see a data type for that.

When I execute the package it fails on the OLE DB Source with the following error:
[OLE DB Source [1908]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE D

how to store query result in variable


hello experts..

i am wishing to store the query result in any variable, please have look at below code.


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim conn As New SqlConnection(GetConnectionString())
Dim str As String = System.Web.HttpContext.Current.Request.Url.AbsolutePath
Dim previous = "SELECT views FROM Articles WHERE ([url]=@str)"
previous = previous + 1
Dim sql As String = "INSERT INTO Articles (views) VALUES (@previous)"
Dim cmd As New SqlCommand(sql, conn)
Dim pram As SqlParameter() = New SqlParameter(1) {}
pram(0) = New SqlParameter("@previous", SqlDbType.VarChar, 50)

pram(0).Value = previous

For i As Integer = 0 To pram.Length - 1

cmd.CommandType = CommandType.Text

how to Capture query result in a variable?


hi i have written a store proc to send a e-mail after my package is executed For the body of the mail i have another store proc called which give a result set as a table and that has to be displayed in the mail . the hilighted part of the store proc is where i have to do some thing for this to happen below is my store procs  

Any help is greatly appreciated !!??

***************This is the main Store proc****************


ALTER Procedure [dbo].[Send_Email_Summary_DART_File_Download]


@Status INT





/**** Declare the variables used by SP internally ****/



DECLARE @DMRrecipients NVARCHAR(500);


DECLARE @DMRimportance NVARCHAR(500);

-- DECLARE @FileSize INT ;





how to pass variable into SQL query in aspx



custom function which stores the current DATE into todayDate. I use this function  to convert system date into SQL friendly date. 



I used Data Grid and SQL Data source. 

SelectCommand="select * from sales where aab = 110026"


I want my select statement should take the date from todayDate.

ie: SelectCommand="select * from sales where aab = todayDate"

Help me to write this statement in correct  syntax 

How to map a datetime variable into an access ole db query


I am extracting data from an access 2003 database by date. I have built a query in the oledb editor. If I hardcode the date, the query work as such

SELECT t_PlanActuals.pa_Date, t_PlanActuals.pa_Day, t_PlanActuals.pa_DailyPlanParkers, t_PlanActuals.pa_DailyPlanMovers
FROM t_PlanActuals
WHERE (((t_PlanActuals.pa_Date)=#12/20/2002#));

I have defined a user variable called sweepdate but I cannot get the access syntax to accept it in the query string.

Can anybody tell me the correct way of passing the syntax? I tried using ? but that did not work when I tried the preview

Variable names must be unique within a query batch or stored procedure. ???


Hi i am using sql data source to delete first from Child Table and second check if there is no child  go and delete the primary key in Parent table

this is my code

<asp:SqlDataSource ID="SDSOrdersGrid0" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            DeleteCommand="Declare  @OrderIDinWeek
Set @OrderIDinWeek=(Select order_dtl_id from Weeks where IdWeek=@IDweek)
IF (SELECT count(*) FROM  weeks WHERE order_dtl_id =@OrderIDinWeek) =0
   delete from order_dtl where order_dtl_id= @OrderIDinWeek

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

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.

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Retrieving a variable from a previous page



I'm looking to have a search page where a user will select an item from a listview, then then taken to another page that wil display data based on their original selection.

A user will type in a string and be presented with a list of usernames that are like that string, when they select a user name who's profile they would like to view I would like the page that they're taken to, to be that users profile.

How would I transfer the variable from the search page to the profile page, and when I work on the profile page, how can I access that variable?


Embeb a variable number of videos


I need to provide some alternate content for my non-Flash users.

I have a directory of video files that is scanned using the DirectoryInfo() function, and I want to use the results to embed all files ending in ".mov" in QuickTime players on a web form.

How would you approach this? Right now I am using the count on the array of videos to determine how many times to repeat the embed code.  But this seems inelegant.

Is there a way to have a single QuickTime player.ascx control that gets duplicated according to the number of videos available for display?

How to take value of bound field of grid in a variable or in a textbox.


How to take value of bound field of grid in a variable or in a textbox.

 I m using this code in .aspx page for grid.

<asp:GridView ID="Grd_Mprocess" runat="server" AutoGenerateColumns="False"

                      Width="100%" AllowPaging="True"


                      AllowSorting="True" PageSize="5"  >

                 <RowStyle CssClass="odd" />


<asp:BoundField HeaderText="DOC_TYPE_PK" DataField="DOC_TYPE_PK"  Visible="true" >


  <asp:TemplateField HeaderText="DOC TYPE ID" Visible="true">  &nbs

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