Does Linq to SQL or Linq to Entities 4.0 support the hierarchyid datatype
Is their a way to use linq to SQL/Entities 4.0 to work with the hierarchy datatype? ...
sql-server-2008
Dynamic select clause in SQL Server from XML
I have to query a view and include only those columns which are defined in the XML which comes as a parameter to my SP. Can i include that XML in select clause and extract all columns defined in that XML. Please tell a way to do this. XML format is <Columns> <Column Name="CustomerID"/> <Column Name="CustomerName"/> <Column N...
Is there any way to debug SQL Server 2008 query ?
Hi is there any way to debug a SQL Server 2008 query ? Thanks in advance ...
How to use correctly the Query Window in SQL Server 2008
Hello, What should I do to avoid that commands be executed each time I hit 'Execute !. icon' I mean this USE master; GO CREATE DATABASE Sales GO USE Sales; GO CREATE TABLE Customers( CustomerID int NOT NULL, LName varchar (50) NOT NULL, FName varchar (50) NULL, Status varchar (10), ModifiedBy varchar (30) NULL ) GO When...
Bug in SQL Server 2008 XML support ?
Hi, I have sql server 2008 with sp1. I use XML features of the sql server and I have some xml schema attached to the xml column. The issue is I have some element in the xml schema defined like below: <xs:element name="BodyBack" type="xs:base64Binary" minOccurs="0" /> When I try to insert some xml content with base64 encoded value, th...
SQL Server 2008 View Columns match to underlying Table Columns
I've been attempting to write some SQL code that when provided with a view will locate the columns that the view columns reference and work out if there are any indexes on those columns. The end aim is to provide users with a list of columns it would be possible to use when querying the view. Currently though I can only find the column...
Find out how much storage a row is taking up in the database
Is there a way to find out how much space (on disk) a row in my database takes up? I would love to see it for SQL Server CE, but failing that SQL Server 2008 works (I am storing about the same data in both). The reason I ask is that I have a Image column in my SQL Server CE db (it is a varbinary[max] in the SQL 2008 db) and I need to k...
Easiest way to make copy SQL Server DB to test DB?
What is the easiest way to take a SQL Server database and make a test copy of it? I have looked through some existing topics but not sure if there is an easier way. I have database publisher. I want to be able to keep both database on the same server, potentially. Update: I used the Microsoft SQL Server Publishing Wizard to script to f...
How to call a scalar function in a stored procedure
I am wacking y head over the problem with this code. DECLARE @root hierarchyid declare @lastchild hierarchyid SELECT @root = NodeHierarchyID from NodeHierarchy where ID = 1 set @lastchild = getlastchild(@root) it says it does not recognize getlastchild function. What am I doing wrong here ...
C# SqlDataAdapter not populating DataSet
I have searched the net and searched the net only to not quite find the probably I am running into. I am currently having an issue getting a SqlDataAdapter to populate a DataSet. I am running Visual Studio 2008 and the query is being sent to a local instance of SqlServer 2008. If I run the query SqlServer, it does return results. Code...
Why is ISql100DatabaseEncryptionKey blocking Schema Compare
I am trying to do a Schema Compare in Visual Studio 2010 on some SQL Server 2008 databases. But I get the following error. What does it mean and how can I ignore? An error was received from SQL Server while attempting to reverse engineer elements of type Microsoft.Data.Schema.Sql.SchemaModel.ISql100DatabaseEncryptionKey: The user does n...
what the true nature of @ in Transct-SQL
Hello, I reading some old ScottGu's blogs on Linq2SQL. Now I'm doing the SPROC part. I'd like to know what's the exact meaning of @variable. See this from ScottGu's Blog ALTER PROCEDURE dbo.GetCustomersDetails ( @customerID nchar(5), @companyName nvarchar(40) output ) AS SELECT @companyName = CompanyName FROM Customers WHERE Custo...
PIVOT / UNPIVOT in SQL Server 2008
Hello I got child / parent tables as below. MasterTable: MasterID, Description ChildTable ChildID, MasterID, Description. Using PIVOT / UNPIVOT how can i get result as below in single row. if (MasterID : 1 got x child records) MasterID, ChildID1, Description1, ChildID2, Description2....... ChildIDx, Descriptionx Thanks ...
Getting clusters of rows close together in time
I have a table basically like so ID | ItemID | Start | End | --------------------------------------------------------------- 1 234 10/20/09 8:34:22 10/20/09 8:35:10 2 274 10/20/09 8:35:30 10/20/09 8:36:27 3 272 10/21/09 12:15:00 10/21/09 12:17:...
What is a good reason to use SQL views
I am reading though the sql server 2008 bible and I am covering the views section. But he really dont explain the purpose of views. What is a good use for views. Should I use them in my website and what are the benefits of them. ...
Copying large data from result of query in MS SQL Server Management Studio
Hello, I have a query that returns a large 'ntext' result. I want to copy this over to a plain text editor (Notepad), but only a part gets copied over. I tried increasing Query Options -> Results -> Text, but the max seems 8192, which is insufficient for me. Any ideas on how this can be achieved? I'm using SQL Server Management Studi...
ALTER TABLE SWITCH Partition Failing In SQL Server 2008
I have a staging table (stage_enrolments) and a production table (enrolments). The staging table isn't partitioned, the production table is. I'm trying to use the ALTER TABLE SWITCH statement to transfer the records in the staging table to production. ALTER TABLE dbo.stage_enrolments SWITCH TO dbo.enrolments PARTITION @partition_num; ...
Best Way to call a Web Service from TSQL
Hello, What is the best way to call a Web Service from TSQL? I would Like to write some triggers that call out to a web service. Is there a generally used best practice for this? Implementations would need to be handled in SQL Server 2005 and 2008 ...
Passing a Table Valued parameter to a stored procedure
Here's an example of how to use table-valued parameters in a SQL Server 2008 stored procedure using .NET. And here's the list of parameter types in CF9. Q: Is it possible for ColdFusion to pass a table-valued parameter to a Microsoft SQL Server 2008 stored procedure? ...
Change Data Capture or Change Tracking - Same as Traditional Audit Trail Table?
Before I delve into the abyss of Microsoft documentation any deeper, I'd like to know if someone experienced with Change Data Capture and Change Tracking know if one or both of these can be used to replace the traditional ... "Audit trail table copy of the 'real table' (all of the fields of the original table, plus date/time, use...