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

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

Pipe SQL Query function into Variable with no column headings

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


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

SSRS 2005 SP3 - Instr function with Global Variable fails in Subscriptions

Hi I have a very simple expression in a text box in my report:  INSTR(Globals!ReportServerUrl,"Report").  This expression should return the integer value of where the string "Report" appears in the Global variable for the ReportServerUrl.  Simple enough.  This works fine for generating the report directly through report manager, and in my case returns a value of 20. However when run as a subscription and emailed to myself the value returned is 0. This suggests that the search string does not appear in the Global variable. In the reportserver config file the URLroot tag contains the correct server address - http://myserver/reportserver. Does anyone know why i should be experiencing this issue with my subscriptions?  Your help would be much appreciated! Thanks Guy  Guy

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

Scalar Function Variable length return value

Would like to write a Scalar Function that returns a variable length string of spaces. Length being determined by an input parameter (int) Maybe I am missing the obvious but it seems impossible because you have to declare the size  of the varchar return variable I know there is a performance hit using scalar functions but we are generating a view that emulates an old fixed file format with lots of unused fields. So it will make the resultant view readable. So I am looking to do select goodfield1,fnSpace(5) as Unusedfield1,goodfield2,fnSpace(10)... from MyView thanks Bob

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

How do I get column values in Sharepoint designer so I can use them as a variable in code block?

The code below is in my EditForm.aspx and it works but instead of it outputting "This is just a test!" I need get a number that is stored in a column named Phone Number that has 5555555555 stored in it and have the line that says TD2.innerHTML = "This is just a test!"; ouput what ever number is stored in the Phone Number column. Here is the code that is in my EditForm.aspx <script type="text/javascript">function DisplayItemID(){       var TD1 = document.createElement("TD");       TD1.className = "ms-formlabel";       TD1.innerHTML = "<h3 class='ms-standardheader'>Test</h3>";       var TD2 = document.createElement("TD");       TD2.className = "ms-formbody";       TD2.innerHTML = "This is just a test!";       var IdRow = document.createElement("TR");       IdRow.appendChild(TD1);       IdRow.appendChild(TD2);       var ItemBody = GetSelectedElement(document.getElementById("idAttachmentsRow"),"TABLE").getElementsByTagName("TBODY")[0]; 

How to filter managed meta data column using page query string?

I have two managed term sets as follows: Articles:Downloads Projects:Downloads I am trying to use a content query web part to filter a list based on the above manged meta data column. I want to filter out only "Articles:Downloads" Scenario 1: When I edit the web part and directly set the filter option with the managed meta data column to "Articles:Downloads" it works fine. When I export the web part, in the .webpart file, the "FilterValue1" property has a value "Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910". Scenario 2: I want the web part to get the filter information from query string [PageQueryString:pageType]. So I tried using these options: a) pageType=Downloads      This works but returns downloads under 'projects' as well. (i want downloads only under 'articles')  b) pageType=Articles:Downloads     Does not work and shows no records c) pageType = Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. (This value is the same as one found in 'FilterValue1' of the static web part). c) pageType = Articles:Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. d) pageType = 9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work.   What am I missing here? Can some one please help?

Query DB and update Check Box List on JS/jQuery Function

Hey guys.  I'm not sure if this is the right forum to post this in but I've come to a roadblock in my app and I'm not sure which way I should go.  Currently, I have a slider control and two text boxes used to set the Start and End times of a section of a video.  Let's say the video is of some Music Awards.  In my database, I've tagged the start and end time of each celebrities on-screen appearance.  When the user adjusts their start and end times on the page, I want to query the database table and pull back all celebrities within that range.  The problem is I don't know how to do this in JS.Can I use the Check Box List and bind the values to it?  Do I need to render a table of Check Boxes in JavaScript?  Do I call the Server-Side code via Ajax and then partially render the page?Any help, comments or suggestions would be great.

Return linq query in function

I try to return a var query from a function, but i'll get the error: Error:Error 41 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Xml.XmlElement>'. An explicit conversion exists (are you missing a cast?)  Code: protected static IEnumerable<XmlElement> GetModels(byte MerkID) { string rawXML = AutoDisk.GetModels(12345, true, MerkID, "2010", ""); XmlDocument xml = new XmlDocument(); xml.LoadXml(rawXML); var query = from feed in xml.DocumentElement.ChildNodes.Cast<XmlNode>() select new { nModelID = feed.SelectSingleNode("nModelID").InnerText, Modelnaam = feed.SelectSingleNode("Modelnaam").InnerText }; return query; } Whats wrong and how can i make this working ?  

How to copy column headers along with the query result in SS00

HiIs it possible to copy the column headers along with the query result from SQL Server 2000 Query Analyser.Thanks in advance.fjz

Schema comparer mistake in table-valued function (Nullability in column definition)

Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

How to create nonclustered index which uses function on column?

Hi, Is there any way I can perform following. The ID column is of varchar type.   CREATE NONCLUSTERED INDEX idxID ON Table_1(CAST(ID AS BIGINT) );   Regards, Jigs

How to enable delete column at Gridview when having specify query.

Hello:  I have trouble to add delete column to gridview when using specific query. I have PK set up at the table, I need add specific query to display data at gridview:   SELECT [PID], [GID], [TID], [SID], [NEWSID] FROM [TableOne] WHERE ([NAME] = ?)   After builder this query, I click advanced button. However, generate insert, update, and delete Statements is not enabled.   How to add delete column to this gridview?   Your help is highly appreciated.

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

Column names in each view or function must be unique. Any Suggestions Please!!!!!!!!

I am getting this error when I am executing this view. But I am not getting this Error when parsing the query!!! Can some one help me with the issue. Create   view [PosSales_Transactions_Tenders_Extract] AS SELECT RT .RTL_TRN_ID, RT .STORE_NO, RT .WORKSTATION_NO, RT .RTL_TRN_NO, RT .RTL_TRN_TYPE_CODE, RT .AMOUNT, RT .OPERATOR_NO, RT .CUSTOMER_NO, RT .EMPLOYEE_NO, RT .BUSINESS_DATE, RT .BEGIN_DATETIME, RT .END_DATETIME, TLI .LINE_ITEM_NO, TLI .TENDER_TYPE_ID, TLI .TENDER_TYPE_CODE, TLI .TENDER_AMOUNT, TLI .CHANGE_FLG, TLI .SERIAL_NO, TLI .KEYED_FLG, TLI .EXCHANGE_RATE, TLI .FOREIGN_TENDER_AMT, TE .TENDER_TYPE_ID, TE .TENDER_TYPE_DESC FROM   dbo.RETAIL_TRANSACTION RT JOIN   dbo.TENDER_LINE_ITEM TLI ON   RT.RTL_TRN_ID = TLI.RTL_TRN_ID JOIN   dbo.TENDER TE ON   TLI.TENDER_TYPE_ID = TE.TENDER_TYPE_ID WHERE   RT.VOID_FLG = 0 AND RT .VOIDED_FLG = 0 AND RT .VOIDING_FLG = 0 AND RT .SUSPENDED_FLG = 0 AND RT .TRAINING_FLG = 0 Msg 4506, Level 16, State 1, Procedure PosSales_Transactions_Tenders_Extract, Line 4 Column names in each view or function must be unique. Column name 'TENDER_TYPE_ID' in view or function 'PosSales_Transactions_Tenders_Extract' is specified more than once.

Steps to alter function used as computed column

What is the best way to change a function that is currently being referenced from a calculated column in a table?  I get the following error when I try to alter it.   Msg 3729, Level 16, State 3, Procedure fn_sdtAcctJoin2, Line 232 Cannot ALTER 'dbo.fn_sdtAcctJoin2' because it is being referenced by object 'tblMembers'.
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