.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

Query on XML datatype in SQL Server 2008

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi All, In table I have column with XML datatype. I can able to read the below XML tag. SET   @Title = CAST(@XMLContent.query('data(root/chemicalName)') AS VARCHAR(50)) <root><chemicalName>Sulphuric acid</chemicalName></root> but how can I read (in SQL Server 2008) with multiple rows in it, and how to know the list of child nodes and data of it dynamically? <Root> <Row> < CurrencyCode>GBP</CurrencyCode> < CurrencyName>POUND (STERLING)</CurrencyName> </ Row> <Row> < CurrencyCode>INR</CurrencyCode> < CurrencyName>Rupee</CurrencyName> </ Row></

View Complete Post

More Related Resource Links

SQL server 2008 - Unable to tune a query with DTA

We are on:Verison: SQL 2008 [10.0.2531] on windows server 2008. Both are enterprise editions.When I highlight the query and right-click, then select Analyze Query in Database Engine Tuning Advisor, then click "Start Analysis",  I get the following error in the "Message" column of "Progress" tab of DTA: Cannot initialize tuning. [.Net SqlClient Data Provider] If I click on this, it displays this message: "Cannot find either column 'msdb' or the user-defined function or aggregate 'msdb.dbo.fn_DTA_unquote_dbname', or the name is ambiguous. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count=0, current count=1. (Microsoft SQL Server Error: 4121)Please let me know if you guys have any ideas on this error. Thanks,Sam. 

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

query with locks not working in sql server 2008

I have a select query with join on some view and locks as (readpas,updlock) after executiong this query it executes the same query with little modification( no locks etc). This sequence of query executes fine in SQL server 2000/2005.   When I am executing same seq in sql server 2008 through application, I am getting timeout for second query. Can someone please suggest what can be the reason? I cannot post the query.Cheers!!! Vatsa

Looking for Client Query Analyzer (SQL Server 2008) or Equivalent Reporting Tools


I've recently upgraded from SQL Server 2000 to SQL Server 2008.  My accounting and marketing team is skilled and has been using SQL Query Analyzer for years to run smaller queries and build small Crystal Reports.  Unfortunately Query Analyzer is now bundled into the management studio.  That's a bit too much software for this level of power users.

Is there a client-side query analyzer for SQL Server 2008?  I'd rather use a Microsoft product than a 3rd party tool, but I suppose I could do that too.  Looking for something simple that will:

1.  Show users the database tables and/or views

2.  Run queries

Perhaps query analyzer isn't the way to go and my team needs a different reporting system.  If this is the way MS is heading, could you direct me towards a product or service that allows power users to get the information they need?  They will still need to look at raw data to build Crystal Reports though.

Query execution failed using SQL Server 2008 R2 (RTM)

I have a reporting services application that is returning an error running a report which uses an MDX query. This same report is running successfully on other systems using Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64). But this system returning the error is using this version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). I'm inclined to think that R2 is using different rules for the parameter because the position indicated below (4,33) is date parameter (@Date), and the same report is running fine in SP1.

When I run the report via the report manager, this syntax below returns the error shown at position (4,33) which is the position of @Date.


linkmember(StrToMember(@Date).parent.lag(4).firstChild,[Origination Date].[Date])


ApplicationException: Error processing alert report ...: System.Web.Services.Protocols.SoapException: An error has occurred durning report processing. --> Microsoft .ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. --> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset '...'. --> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseExceition: Query(4,33) Parser: The syntax for 'Date' is incorrect.

At Microsoft.ReportingServi

SQL Server 2008 query execution time is different if i execute it in management studio or from code

When I execute query from software which i wrote in c# it required lots of time to complete but if I execute exactly the same query from management studio it will be done in seconds. In both cases execution plan is the same. 

SQL Server 2008 R2 Report builder - Cannot update a list of fields for the Query



I have existing report connected to SSAS cube. When I try to change the query and click OK, i get this message "Could not update a list of fields for the query. Verify that you can connect to the data source and  your query syntax is correct"

My Query syntax and access to data source dont have any problem.  Because fo this issue, i cannot update the query used in the report. How do i resolved this issue?



How to Import Excel Data into SQL Server 2008(64 bit) Using SQL Query (in Windows Server 2008 64bit

I am developing VB .Net application in Visual Studio 2008(64bit), SQL Server 2008(64bit) and Windows Server 2008(64bit) environment.

I need to import an Excel and Flat files data into SQL Server database using SQL Query. I tried a lot with the following SQL Queries directly with SQL Server Management Studio,

1. SELECT * INTO  dbo.ExcelImportTest FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\PTPLDCT\DCT Test Datasource\DCT_Excel_Test_Sample_1.xls',
'select * from [Sheet1$]')

While executing i got an error as below,

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

2. I have also tried with MSDASQL provider,

Select * from openrowset('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};
Dbq=DCT_Excel_Test_Sample_1.xls;DefaultDir=D:\PTPLDCT\DCT Test Datasource\', 'Select * from [Sheet1$]')

Then i encountered the following error,

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

3. Finally i have tried the Query with Linked Server,

EXEC sp_addlinkedserver @server = '

SQL SERVER 2008 Query Performence


HI All,

The below mentioned Query was take too much time nealy (3 mins) how reduce the time



Distinct SUBSTRING(c.page,12,3)

Cannot generate table scripts with DateTime2 datatype in SQL Server 2008


When trying to generate a "CREATE TO" script from a SQL Server 2008 table containing either DATE or DateTime2 data types,  I get the following error:

Script failed for Table 'dbo.tMyTable'.  (Microsoft.SqlServer.Smo)
Column SnapshotDate in object tMyTable contains type DateTime2, which is not supported in the target server version, SQL Server 2005. (Microsoft.SqlServer.Smo)

However, I've checked all my databases (including master, tempdb, etc.) and ALL are cmptlevel=100.


SOAP Error 500 when query in Sql Server 2008 using for reporting a Sharepoint list 2007


I'm doing the reporting services for a SharePoint list, the datasource is configured as a connection with http://<Servername>/_vti_bin/lists.asmx and I add the parameters listName , rowLimit and viewName with the

SQL Server 2005/2008 Query Monitoring via DMVs


My understanding is that sys.dm_exec_query_stats is for archive aggreated query statistics so I’ve been using the query below to monitor active long running queries and it works fine for single statements – however it seems to return hugely inflated total_elapsed_time values with some procs or functions where multiple statements are run. I’m also interested if anyone know how to find query completion times from DMVs?


Select …<some columns>
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
INNER JOIN sys.dm_exec_sessions AS es
ON es.session_id = er.session_id
INNER JOIN sys.dm_exec_connections AS ec
ON ec.session_id = es.session_id
where er.total_elapsed_time / 1000.0 > @ElapsedThreshold
ORDER BY er.total_elapsed_time DESC;

SQL 2008 to iSeries via linked server (IBMDASQL) - TOP 10 * SQL query returns ALL records


I am querying a iSeries - AS400 database from SQL server 2008 server 64 bit,  via a linked server (using OLE DB Provider - IBMDASQL)

The SQL is select top 10 * FROM <linked Svr>.<Dest server>.<DBName>.<TableName>

Instead of returning top 10 rows, the query returns ALL the rows. I use the top 10 sql query in my "Heart beat monitor" to check the viability of the linked server after a server reboot.  If the test query work

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.

Sql Server 2008 Tutorials

This Link Provides total Sql Server 2008 Tutorials

Seven reasons to care about SQL Server 2008 R2

Microsoft begins to fully realize its vision of SQL Server as an information platform and not "just" a database. Hence the main theme for this release -- at least according to Microsoft -- is self-service BI. The PowerPivot plug-ins for Excel 2010 and SharePoint 2010 are easily going to make the biggest splash of all the new features, not least because they're the most complete. But then, SQL Server 2008 R2 isn't strictly necessary for PowerPivot for Excel, which works with plenty of other data sources.

Error upgrading SQL Server 2008


i was normally using VS2005 and SQL server 2005...

i upgraded to SQL Server 2008... and then...

i could not build my project anymore... because of this error:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.IO.FileNotFoundException: The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Source Error: 
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