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


Post New Web Links

how to Capture query result in a variable?

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

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

 

 AS

 BEGIN

 

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

 

DECLARE @DMRbody NVARCHAR(max);

DECLARE @DMRrecipients NVARCHAR(500);

DECLARE @DMRSubject NVARCHAR(500); 

DECLARE @DMRimportance NVARCHAR(500);

-- DECLARE @FileSize INT ;

-- DECLARE @FileId INT

--

 

/


View Complete Post


More Related Resource Links

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)"
Try
conn.Open()
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.Parameters.Add(pram(i))
Next

cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
<

Use DB query result to generate Jquery required lines of code

  

Hello

I 'm gonna query database for photo records. each record include "photo path" where imagesURL  stored in string format

 I Choses my favorite Juqery plugin to show images. Plugin needs some lines of code like below.

How could I  generate these lines

<script>
$(function() {
$('#slideshow').crossSlide({
sleep: 2,
fade: 1
}, [
{ src: 'picture1.jpg' },
{ src: 'picture2.jpg' },
......
.....
{ src: 'picture4.jpg' }
])
});
</script>

Pass the server variable to search core result web part

  

I'd like to custom the search core result web part to filter results based on the log on user name.

It can easily be done in Dataview web part.

However the search core web part doesn't accept "<parameterbining>" to pass in the server variable [logon_user]. I can't figure out how to use <xsl:param> to get this server variable either.

 

Any ideas? Thanks a lot!


If you get confused, listen to the music play...

On New Item form, select item on choice dropdown based on query result.

  

I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

Dynamic Class with Linq query Result

  
Hi, var   query = db.Customers.Where( "City == @0 and Orders.Count >= @1", "London", 10). OrderBy( "CompanyName"). Select( "New(CompanyName as Name, Phone)");    I am using this query as a sample which returns a dynamicclass type, and i need this should to converted to a list collections or datatable etc., As i am holding it in my wcf service with linqtosql as dataaccesslayer. Trying to send the results to the UI which i have consumes this service. Kindly help me in this. Thanks query = db.Customers

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

Need to add hard coded values to LINQ query result

  
I am running a LINQ query which populates a list used by a DropDownList, I need to insert an "Unassigned" value to the list. The function below queries the values correctly from the db but does not insert the "Unaasinged" value to the list. Please help.   public List<KeyValuePair<int, string>> GetTestList() { KeyValuePair<int, string> item = new KeyValuePair<int, string>(0, "Unassigned"); using (DataClassesDataContext db = new DataClassesDataContext()) { var Query = from records in db.TEST_SUITEs orderby records.TEST_SUITE_ID select new KeyValuePair<int, string> ( records.TEST_SUITE_ID, string.Format(records.TEST_SUITE_ID + " " + records.SUITE_TYPE + " " + records.SUITE_NAME) ); //Query.ToList().Insert(0, item); Query.ToList().Insert(0,item); return Query.ToList(); } }  

Check if Linq query return result

  
HelloI have Linq query likeDim Prood= (From p InProdutcts _                                          where p.ProductID="InputInteger"                                                         ..... ) .SingleOrDefaultI could Use Count if I Use .ToList Instead of .SingleOrDefault , But now that I use 'SingleOrDefault', how Could I find if Prod contains any record or it's Empty.

SQL server management studio 2008 freezes when running a query to Grid result

  
Hello, I've got problems when I run a Query to a grid result using SQL server management studio 2008 under W7 pro. When I execute the query, the first part of the grid returns, but then it freezes. The query should present a double resultset and contains approx 6100 records, devided over 2 grids. Strange enough, the query runs fine when I export to TXT or to a file. It seems though that the problem is building the grid. When it occurs I cannot end the application bij shutting down de SSMS window, but have to kill the Ssms.exe. In task manager I see only 25% CPU usage and approx 20% memory usage. Nothing exiting there I guess. Suggestions anybody? Microsoft SQL Server Management Studio      10.0.2531.0 Microsoft Analysis Services Client Tools      10.0.3798.0 Microsoft Data Access Components (MDAC)      6.1.7600.16385 Microsoft MSXML      3.0 4.0 5.0 6.0 Microsoft Internet Explorer      8.0.7600.16385 Microsoft .NET Framework      2.0.50727.4952 Operating System      6.0.6001 regards, Pim

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

Analysis Services - Query returns a #error result

  
Hi, I have a cube - which i had backed up and just retored it. After the restoration, when i try to reply the cube e.g: select [Time].&[200503] on 0 , {[Process].[All process]} on 1 from [myConsolidations] - i recieve an a result showing #error only. What could be the cause of this?

Exporting my query result to .CSV or .Txt fomat?

  
Hi everyone, i would like to ask if there is way on how to export my query result into .csv or .txt file?   Thanks

How to setup Query Result Shaping for LinQ to SQL instead of Entity Framework?

  
Hi, I am new to this, and I am trying to implement a linq query similar to db.Genres.Include("Albums") command from mvc music store using my current DB setup. I realize that the tutorial mentions Checlking the "Include foreign key columns in the method" when creating the tables in ADO.NET, but if I am using LINQ to SQL classes, then how can I obtain the same effect. And how do I use db.Genres.Include("Albums") to simplify my life? Thanks

Unable to store for xml Xpath result in a variable

  
HI ALL, Unable to store the results of xml  reults into a varible , it  throws an error declare   @r varchar(1000) set   @r =( WITH   XMLNAMESPACES (     'http://schemas.microsoft.com/analysisservices/2003/engine/2/2' as ddl2_2   , 'http://schemas.microsoft.com/analysisservices/2003/engine/2' as ddl2   ,'http://www.w3.org/2001/XMLSchema-instance' as xsi   ,'http://www.w3.org/2001/XMLSchema' as xsd ) select   DatabaseID "Object/DatabaseID" , CubeID "Object/CubeID" , MeasureGroupID "Object/MeasureGroupID" , PartitionID "Object/PartitionID" , DimensionID "Object/DimensionID" , ProcessType "Type" , WriteBackTableCreation "WriteBackTableCreation" , Parallel "parallel"   from #ega WHERE   ProcessingGroupID = 3 FOR   XML PATH('Process'))
Categories: 
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