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

Post New Web Links

SQL XML syntax

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server
I'm having some issues trying to write a select statement and get the results into the correct XML format.

need the format like this:

<MeasureGroup ID = "A">

View Complete Post

More Related Resource Links

Need Syntax To Make Results of LINQ Union Return Non-Generic Type



I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
            using (WorkbookDataContext dc = _conn.GetCont

T-SQL in Yukon: Powerful New T-SQL Syntax Gives SQL Server a Programmability Boost


The T-SQL language in the upcoming version of SQL Server will provide more power and flexibility than previous versions. Additions and enhancements include error handling with the TRY/CATCH construct, SNAPSHOT isolation, and WAITFOR enhancements. Also important are the BULK rowset provider, common table expressions, recursive queries, PIVOT and UNPIVOT operators, and much more. This article introduces these features so readers will be ready for the next version of SQL Server.

Itzik Ben-Gan

MSDN Magazine February 2004

Default values in SQL syntax / MVC


Hi guys, I have a little problem for which I need a little advice about.

I have a repository function called getList which retrieves certain information that gets posted to it from a form. My form has 6 fields, and a user can enter searvh criteria in any or all the fields. According to what he has entered, I must execute a SQL string to retrieve matching information.

I have the following:

public List<Client> getList(string clientSurname, string clientInitials, string clientName, string clientNumber, string clientReference, int divisionID, int clientStatus, int usrID)


var clientlist = from c in DB.Clients
                             where c.Surname.Contains(clientSurname)
                             && c.FullName.Contains(clientName)
                             && c.Initials.Contains(clientInitials)
                             && c.OldClientNo.Contains(clientNumber)
                             && c.Reference.Contains(clientReference)
                             && c.DivisionID == divisionID
                             && c.Status == clientStatus
                             && division.Contains(c.DivisionID)
                             orderby c.Surname,c.Initials

Syntax error (missing operator) in query expression

Good Day Gurus!

I have a Excel user application which has a user form (named 'Registo') that displays criteria and an image that has been entered in it's corresponding spreadsheet. This works the way it should.  There's also the ability to search the spreadsheet via a form (by clicking 'Pesquisar' button) this opens a search form. However, I having a bit of a problem with it. When I try to search for something it basically doesn't do anything at all. It just sits there. So I tried to debug it and I think I'm having a problem with either the JET db engine or somethign with teh query or maybe I don't have the correct reference.

I 'borrowed' this Excel application from another forum because it's exactly what I'm looking for! However, I suck at vb.

So I was hoping somebody could take a look at the code and see if I'm missing something?  I'm kinda' desparate to get this working because I'm been trying to figure it out for days and I'm running out of time.  Cry
Option Explicit

'constantes para auxiliar na verificação do código
Private Const Ascendente As Byte = 0

Incorrect syntax near 'GO' error while running a sql script ?


I am using Visual Studio 2008

I have one Database A

now i want to create Database B just like Database A

I generated a script of Database A

and created fresh Database B

Now, when i try to run the generated script against Database B, i get this error, why ? how to fix it ?

sql error

For your information:

I have checked in my script it is:


and i have also tried to save the sql script in notepad as: ANSI, UTF-8 and UNICODE

and then i copied from txt file to query but still doesn't work and getting the same error

and these are the option that i selected/used while creating my script:


LinQ Syntax of looping a collection


Hi, I would be glad is there is anyone can point/teach me the correct way of iterate a collection in LINQ. I attached part of my coding for reference. I wish to find out the location(s) of an Employee who can also happen to be a Supervisor. My model contains a Many-to-Many relationship between Location and Employee. Thanks in advance


Dim someDataSource = From _e As Employee In _context.Employees
                                  Where _e.Locations = location <---- _e.Locations return a collection 
                                  Select _e.FullName, _e.ID


Incorrect syntax near "("


Hi, below is a sub I wrote to run an SQLDataSource UpdateCommand. This is customized layer of a createUserWizard. I know I should probably have another table for the addt'l data and just FK the UserId...but I'd rather update the existing record and add the data to the existing row in the user (membership) table.

Can someone tell me what I'm doing wrong on this sub. The error is at the last line, "DataSource.Update()"


 Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)
        FormsAuthentication.SetAuthCookie(CreateUserWizard1.UserName, False)

        Dim UserNameTextBox As TextBox = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName"), TextBox)
        Dim DataSource As SqlDataSource = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("InsertExtraInfo"), SqlDataSource)

        Dim User As MembershipUser = Membership.GetUser(UserNameTextBox.Text)
        Dim UserGUID As Object = User.ProviderUserKey

        DataSource.UpdateParameters.Add("UserId", UserGUID.ToString())

End Sub



syntax error in calling mailhelper class??


I use to program with VB.NEt. I am just learning C#.  I need to learn C# b/c I am a SharePoint developer and Admin and thats what SharePoint is coded from.


so I am trying to create a simple form that sends an email but am getting an error with calling this function:

       mailHelper.SendMailMessage("admin@pointeightpower.com", "jstevens@pointeightpower.com", null, null, "August Heat Stress", String.Concat(txtSupervisorName.Text.Trim, "reviewed this month's training on ", System.DateTime.Now));


it says param string has some invalid arguements. I dont know why. it works in VB.Net. any help? Thanks

Razor syntax

While it has gnerally been advocated to separate code from mark up and all that , with the razor syntax and webmatrix all that is not valid anymore , I think.WebMatrix encourages code and mark up in the same page !!So what is the best practice as far as code and mark up is concerned.best rgds

Incorrect syntax near '<'

It was suggested that I use xmldocument.load to do a sql insert statement instead of BULK INSERT.  However when I run the Sub I am getting this error:  "Incorrect syntax near '<'.   "Here is my code, I have no clue what is causing this error, or how to fix it. Sub AO() Dim Z As String = Microsoft.VisualBasic.Format(Today.AddDays(-1), "MMddyyyy") Dim AO_Name As New String("\\Net\Import\AO-M_" & (Z) & ".xml") Console.WriteLine(AO_Name) Dim AOSQL As String Dim conn As New SqlConnection("Server=DEB\DEVDB;Database=MTrans;User Id=******;Password=**************************;") Dim cmd As New SqlCommand Dim x as New XMLDocument x.Load(AO_Namme) Dim xx As String xx=x.OuterXml AOSQL = "DECLARE @xml XML" + Environment.NewLine() + _ "SELECT @xml = " + xx + _ "Insert into AO (ASSET_OWNER_NAME,SCHEDULED_DATE,SETTLEMENT_CODE, INVOICE, OPERATING_DATE, DA_ADMIN, DA_ASM_REG, DA_ASM_SPIN)" + Environment.NewLine() + _ "SELECT ASSET_OWNER_NAME,SCHEDULED_DATE,CAST(SETTLEMENT_CODE AS smallint) AS SETTLEMENT_CODE, INVOICE, OPERATING_DATE, DA_ADMIN, DA_ASM_REG, DA_ASM_SPIN" + Environment.NewLine() + _ "FROM " + Environment.NewLine() + _ "(SELECT " + Environment.NewLine() + _ "summary.x.value('NAME[1]', 'VARCHAR(10)') AS ASSET_OWNER_NAME,summary.x.value('SCHEDULED_DAT

I need syntax help going from a URL to a Report Service Report

Here is the syntaxt I have tried.  It takes me to the Root folder page on the server,  how do I get to the report itself from an html link http://server.net/SSRS_Reports?/Folder1/Folder2/ReportName&rs:Command=Render&rc:LinkTarget=blank Thanks Dave  

Correct BCP syntax

I am a first time BCP user.  I have an XML file that I need to import into a new table in SQL Server.  This is using SQL Server 2008 and BCP version 10.50.16.  I read through the documentation but I get 370,000 errors!  (This is a very large file).  When I open the error log, I just see a bunch of question marks.  About this XML file: it does use a new line to delineate rows, however, it uses the space character to separate fields.  So I'm not sure if that is what causes it to fail.  The failure message is: BCP copy in failed I wish it were more descriptive.  Is there a way to get a more detailed error message? Otherwise, here is my command: d:\SQL Tables\data>bcp Development.dbo.wbl_zSkywardEnrollment2 in zSkywardEnroll ment.xml -fSE_format.fmt -m50 -eseErrorLog -b100 -t0x20 -T -F107 All of these files are in this same directory.  I know it says to specify full file path, but since they're all in this same directory and also I saw another example where they didn't specify directories I thought this would suffice. I have tried this without the format file also, doing it interactively but get same errors. And I have tried this starting with row 1.  I am curious which line should BCP start on?  Line 1 of this XML starts with Schema and ElementType info.  But on line 107 is where rs:data section

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

I need help with the syntax for passing a record field value from SSRS to an asp page

I have tried several variations without success.  Here is where I am now.  The goal is from inside Report Manager report, link to an asp page with a filed value. This is from the expression line of the Jump to URL entry. Also I am trying to open a new page http://www.whereever.com/CSSurvey01.asp?RecordID=" & Fields!respid.Value            

Syntax for joining the results of two UDF

I have a table UDF similar to proj.MyFunction(2010).  I would like to join the results from the select of each of the functions.  I tried a syntax like below but it does not work.  What is the correct syntax? proj.MyFunction(2010) (select fn1.ProjectID from proj.MyFunction(2010)) fn1 inner join (select fn2.ProjectID from proj.MyFunction(2010)) fn2 on fn1.ProjectID = fn2.ProjectID   Thanks, LLoyd

Sub Query Syntax

Hi All, I know this is a repost but I have struggled though it and managed to nut most of the issues from this query. An issue I am still getting are multiple values returned by a sub query... Which one SQL doesn't like to clue me into. Below is the stored procedure and I know it is mess, this is reverse engineering a terrible application.   USE [CMI] GO /****** Object: StoredProcedure [dbo].[CMIReport] Script Date: 09/02/2010 09:49:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[CMIReport] @startdate datetime, @enddate datetime as BEGIN ---- ~This drops the tables created by the report before recreating it~ ---- DROP TABLE CMI_Report DROP TABLE CMI_Report_Final_Data ---- ~This Query defines the dates early on in the procedure~ ---- SET NOCOUNT ON select * into CMI_Report from Case_Query_View where end_dt Is Null Or end_dt > @startdate and end_dt < @enddate --- ~This Query Replaces Name Veiw in the Report~ ---- select * into CMI_Report_Final_Data from Name_View ---- ~This is the defining query, This controls YTD and such! ---- SELECT nq.surname, nq.sw_ur, nq.MaxOfnext_rev_dt, cq.CTenddt, aq.LastOfadm_dt, aq.LastOfsep_dt, nq.end_dt, nq.clnt_id, nq.case_id, nq.HCPName, nq.hcp_id, cq.LastOfstart_dt, nq.MaxOfwellb_dt, (select MAX(dq.icd_desc) from Diagnosis_View dq where nq.clnt_id = dq.clnt_id) AS Diag

false sql mgmt studio syntax errors

after I run "use database" the sql mgmt studio query editor complains when I don't qualify table names.  But the statements run without error. Is there a way to help sql mgmt studio to know I am using a database?
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