Tag: sql-server-2008-r2

Collate while parsing xml

On same server I have few databases with diferent collation. I’m parsing xml as CREATE TABLE #tblDocuments ( Document varchar(5) COLLATE DATABASE_DEFAULT ,DocCode varchar(15) COLLATE DATABASE_DEFAULT ,Ammount decimal(18,2) ); IF @@ERROR <> 0 GOTO ErrorHandl; INSERT INTO #tblDocuments ( Document ,DocCode ,Ammount ) SELECT Tbl.Col.value(‘@DocType’,’varchar(3)’) AS Document ,Tbl.Col.value(‘@DocCode’,’varchar(15)’) AS DocCode ,Tbl.Col.value(‘@Ammount’,’decimal(18,2)’) AS Ammount FROM @xmlData.nodes(‘/Documents/document’) […]

SQL Server Trace file create unique identifier for field TextData of type NTEXT

To analyse my imported trace file it would like to have a unique value for Select Distinct TextData from myImportedTraceFile I tried using hashbyte although i am not sure if MD5 is the right tool to create a unique identifier. Even if this were the case (please tell me if it is so) then i […]

SQL String function to find each iteration of a specified value and return

I’m trying to parse a particular field and return me just a portion of the string. Example: My varchar field contains the following string. CN=PHLSERVER01,OU=Servers,OU=PHL,OU=Offices,DC=test,DC=test2,DC=office CN=NYCSFAX01,OU=Servers,OU=NYC,OU=Offices,DC=test,DC=test2,DC=office CN=KONGRDATA01,OU=ServersToDelete,DC=test,DC=test2,DC=office CN=LAXSERVER01,OU=Servers,OU=LAX,OU=Offices,DC=test,DC=test2,DC=office I just want to query and return the CN name (common name). So in this example I’d want to see: PHLSERVER01,NYCSFAX01,KONGRDATA01,LAXSERVER01

How to append constant value in parameter?

I have a query that uses one parameter. But I need to append some default constant value, something like this is footer. or any other parameter. Can you help me with that?

Rebuild indexes on replication server

I have 1 live server and 1 replication server, for reporting purpose we use the replication server and in replication server we have created index. So I want to know if I rebuild my index on replication server do I have to remove the replication first and then rebuild the index or can I directly […]

Recursive query from the same table

I have different product serial numbers in one table ProdHistory which contains, as the table name suggest, production history. For example I have product serial SER001 which uses parts with its own serial number. We also produce these parts thus uses the same table ProdHistory to track its subparts. The same goes with the subparts […]

Join tables and return data in comma separated

I have a requirement as mentioned below. Query needs to fetch data on SQL Server 2008 R2 database. Tried my best to fetch the data as mentioned below but couldn’t. Really appreciate your help. Product_table SKU UPC Details Weight Color 223 111 TShirt 25 White 224 114 Pants 25 Black 225 115 Abc 29 Yellow […]

SQL Server – Subquery returned more than 1 value

I have read some posts here about it but I do not understand at all. In my case I have a table, let’s say ‘Parent’ with two columns ID, DELETED. ID is int and DELETED is a bit column. I have another table, let’s say ‘Child’ with columns ID, ID_PARENT, NAME, DELETED as Parent Table. […]

SQL Server not geting rows from OraOLEDB linked server

I have two Windows 2008 Server 64x machines. One running Oracle 10.2.x.x.x (Express Edition) and other SQL Server 2008 R2 with ODAC 12c ( I had created linked server, tested connection and it passes. I can see all Oracle tables, but when I query them, for example: SELECT * FROM ORACLE..USER.PERSON All I get back […]

Get Target Framework Version of SQLCLR Assemblies on SQL Server via Query

We have a number of assemblies that are compiled with either .NET 2.0 or .NET 3.5. Is there a way, via an undocumented table, function, etc – that one could query the list of assemblies in a database and get the .NET version of the assembly? We have queried sys.assemblies which does contain a variety […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.