sql-server

Import ‘xml’ into Sql Server

by

Try this: DECLARE @XML XML = ‘<EventSchedule> <Event Uid=”2″ Type=”Main Event”> <IsFixed>True</IsFixed> <EventKind>MainEvent</EventKind> <Fields> <Parameter Name=”Type” Value=”TV_Show”/> <Parameter Name=”Name” Value=”The Muppets”/> <Parameter Name=”Duration” Value=”00:30:00″/> </Fields> </Event> <Event Uid=”3″ Type=”Secondary Event”> <IsFixed>True</IsFixed> <EventKind>SecondaryEvent</EventKind> <Fields> <Parameter Name=”Type” Value=”TV_Show”/> <Parameter Name=”Name” Value=”The Muppets II”/> <Parameter Name=”Duration” Value=”00:30:00″/> </Fields> </Event> </EventSchedule>’ SELECT EventUID = Events.value(‘@Uid’, ‘int’), EventType = Events.value(‘@Type’, … Read more

How to fix “Must declare the scalar variable” error when referencing table variable?

by

This is a long standing parser issue. You need to get rid of the table prefix or wrap it in square brackets. i.e. delete from @CompanyGroupSites_Master where CompanyGroupID = @CompanyGroupID or delete from @CompanyGroupSites_Master where [@CompanyGroupSites_Master].CompanyGroupID = @CompanyGroupID Not delete from @CompanyGroupSites_Master where @CompanyGroupSites_Master.CompanyGroupID = @CompanyGroupID

How to let SQL Server know not to use Cache in Queries?

by

DBCC FREEPROCCACHE Will remove all cached procedures execution plans. This would cause all subsequent procedure calls to be recompiled. Adding WITH RECOMPILE to a procedure definition would cause the procedure to be recompiled every time it was called. I do not believe that (in SQL 2005 or earlier) there is any way to clear the … Read more

Executing set of SQL queries using batch file?

by

Save the commands in a .SQL file, ex: ClearTables.sql, say in your C:\temp folder. Contents of C:\Temp\ClearTables.sql Delete from TableA; Delete from TableB; Delete from TableC; Delete from TableD; Delete from TableE; Then use sqlcmd to execute it as follows. Since you said the database is remote, use the following syntax (after updating for your … Read more

Defining a one-to-one relationship in SQL Server

by

One-to-one is actually frequently used in super-type/subtype relationship. In the child table, the primary key also serves as the foreign key to the parent table. Here is an example: CREATE TABLE Organization ( ID int PRIMARY KEY, Name varchar(200), Address varchar(200), Phone varchar(12) ) GO CREATE TABLE Customer ( ID int PRIMARY KEY, AccountManager varchar(100) … Read more

Convert NULL to empty string – Conversion failed when converting from a character string to uniqueidentifier

by

SELECT Id ‘PatientId’, ISNULL(CONVERT(varchar(50),ParentId),”) ‘ParentId’ FROM Patients ISNULL always tries to return a result that has the same data type as the type of its first argument. So, if you want the result to be a string (varchar), you’d best make sure that’s the type of the first argument. COALESCE is usually a better function … Read more