Query XML creating field names whithout knowing node names

If I have a SQL SERVER 2012 table containing an XML field type. The records it could contain are as follows. I have simplified my problem to the following. Record 1: ID_FIELD=’nn1′ XML_FIELD= <KNOWN_NAME_1> <UNKNOWN_NAME1>Some value</UNKNOWN_NAME1> <UNKNOWN_NAME2>Some value</UNKNOWN_NAME2> … Maybe more … </KNOWN_NAME_1> Record 2: ID_FIELD=’nn2′ XML_FIELD= <KNOWN_NAME_2> <UNKNOWN_NAME1>Some value</UNKNOWN_NAME1> <UNKNOWN_NAME2>Some value</UNKNOWN_NAME2> … Maybe more […]

Foreign key relation ship between two databases in SQL Server 2008

I have two databases, is it possible to create a foreign key between two tables in different databases in SQL Server 2008? thanks

Get table ID after insert with ColdFusion and MySQL

My normal process for inserting into one table and then getting the ID back so that I can insert into another table is like this in MSSQL: DECLARE @transactionKey uniqueidentifier SET @transactionKey = NEWID() INSERT INTO transactions(transactionKey, transactionDate, transactionAmount) VALUES(@transactionKey, ‘#transactionDate#’, ‘#transactionAmount#’) DECLARE @transactionID int SELECT @transactionID = transactionID FROM transactions WHERE transactionKey = @transactionKey […]

How to declare a variable in SQL Server and use it in the same Stored Procedure

Im trying to get the value from BrandID in one table and add it to another table. But I can’t get it to work. Anybody know how to do it right? CREATE PROCEDURE AddBrand AS DECLARE @BrandName nvarchar(50), @CategoryID int, @BrandID int SELECT @BrandID = BrandID FROM tblBrand WHERE BrandName = @BrandName INSERT INTO tblBrandinCategory […]

Same query – different execution plans

SQL 2008. I have a test table: create table Sale ( SaleId int identity(1, 1) constraint PK_Sale primary key, Test1 varchar(10) null, RowVersion rowversion not null constraint UQ_Sale_RowVersion unique ) I populate it with 10k test rows. declare @RowCount int = 10000 while(@RowCount > 0) begin insert Sale default values set @RowCount -= 1 end […]

How to stop the “Changed database context to …” message

Is there some way to stop the Changed database context to … message when the piece of SQL has a USE database in it ?

Invalid cursor state, SQL state 24000 in SQLExecDirect

I need to call two stored procedures in sequence via ODBC in PHP: #run stored procedure 1 $query = “Shipped_Not_Shipped_Rep “.$_GET[‘rep_id’]; $result = odbc_exec($dbh, $query); odbc_result_all($result); #run stored procedure 2 $query = “Shipped_Not_Shipped_Account “.$_GET[‘account_id’]; $result = odbc_exec($dbh, $query); odbc_result_all($result); I’m getting this error in PHP after the second stored procedure call: Warning: odbc_exec() [function.odbc-exec]: SQL […]

SSRS 2008 Hidden Columns Should NOT Export to CSV

When I hide columns in SSRS they still appear in the CSV export. I have to hide columns, not the entire tablix. This is what I have tried already: The filters in the tablix hide rows not columns. The DataElementOutput per column can not be set using an expression.

How do I fix 'Invalid character value for cast specification' on a date column in flat file?

I have a CSV file with a {LF} delimiting each row and a date column with the date format as “12/20/2010” (including quotation marks) My destination column is a SQL Server 2008 database table of type date (not datetime) In my Flat File Connection Manager, I have configured the date column to be data type […]

Cannot connect to remote SQL server

A client of mine gave me a server name which is fully qualified [servername].somedomain.net to try to connect to their SQL Server instance over VPN. So I tried, using the username and password they gave me. No luck. I can ping it but cannot connect, I get the error that the username and password is […]

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