Does anyone have an example of a pivot using a table with an XML column in it - specifically turning some of the elements in the xml column into a column in the pivot table? I'm trying to build one, but the syntax is killing me; xml in sql is damn hard to work with, compared to xml in C#. I suspect this is impossible, but it wouldn't hur...
We have are relatively simple Reporting Services report that our users commonly export to Excel. I've noticed that the files produced by the Excel export seem unusually large. If I open one of these files and just click save, without making any changes, the file size reduces to about half of it's previous size. Has anyone else run into t...
SQL Server 2005: I have a SiteVisit row which contains information about a users visit, for instance HttpRefer, whether or not they placed an order, browser, etc. Currently for reporting I am joining this table with SiteEvent which contains information about each 'section' visited. This then produces a view which shows statistics about...
I have an XML column in a table; I want to "promote" a certain value in that XML as a computed column and index it for faster searching. I have a function that takes in the XML information and outputs the element of interest, like this: CREATE FUNCTION [dbo].[fComputeValue] (@data XML) RETURNS datetime WITH SCHEMABINDING AS BEGIN RETU...
Can you import (or migrate) SQL Server 2005 databases to SQL Server 2008? Is it possible to have the same 2005 and 2008 running on the same machine without complications? ...
One of the "best practice" is accessing data via stored procedures. I understand why is this scenario good. My motivation is split database and application logic ( the tables can me changed, if the behaviour of stored procedures are same ), defence for SQL injection ( users can not execute "select * from some_tables", they can only call ...
We have a production SQL Server 2005 database server with the production version of our application's database on it. I would like to be able to copy down the data contents of the production database to a development server for testing. Several sites (and Microsoft's forums) suggest using the Backup/Restore options to copy databases fr...
I have a table like as follows: SoftwareName Count Country Project 15 Canada Visio 12 Canada Project 10 USA Visio 5 USA How do I query it to give me a summary like... SoftwareName Canada USA Total Project 15 10 25 Visio 12 ...
I have a script to extract certain data from a much bigger table, with one field in particular changing regularly, e.g. SELECT CASE @Flag WHEN 1 THEN t.field1 WHEN 2 THEN t.field2 WHEN 3 THEN t.field3 END as field, ...[A bunch of other fields] FROM table t However, the issue is now I want to do other processing on the d...
There is small system, where a database table as queue on MSSQL 2005. Several applications are writing to this table, and one application is reading and processing in a FIFO manner. I have to make it a little bit more advanced to be able to create a distributed system, where several processing application can run. The result should be t...
What is the best way to avoid SQL injection on MSSQL Server from C# code using Linq? Should you use a function to strip of security issues or is it handled by the framework? ...
The application is planned to be built using ASP.NET, .NET Remoting & MS SQL Server. High availability is required at presentation layer, application layer and database. Does IIS 7.0 provide any advantages over IIS 6.0 in regard to the High availability aspect? ...
I just installed SQL Server Express 2008 because of intellisense feature. It worked at first but than it stopped working. Looking for the option to check and later consulting Google I have found that it looks like Microsoft disabled intellisense if you connect to SQL Server 2005 databases. Is this absolutely correct ? Is there any solu...
I have an archiving process that basically deletes archived records after a set number of days. Is it better to write a scheduled SQL job or a windows service to accomplish the deletion? The database is mssql2005. ...
I tried this: ALTER TABLE My.Table DROP MyField and got this error: -MyField is not a constraint. -Could not drop constraint. See previous errors. There is just one row of data in the table and the field was just added. EDIT: Just to follow up, the sql was missing COLUMN indeed. Now I get even more seriously looking errors though:...
Howdy, I have a long running insert transaction that inserts data into several related tables. When this insert is running, I cannot perform a select * from MainTable. The select just spins its wheels until the insert is done. I will be performing several of these inserts at the same/overlapping time. To check that the informa...
I have a user sign off report where it shows a users rights/access to the system which all fits nicely onto one page when I hit preview. However when I then go to print layout it shows me two pages with the second page only containg the footer. Any ideas please? ...
Using MSSQL2005, Can I truncate a table with a foreign key constraint if I first truncate the child table(the table with the primary key of the FK relationship)? I know I can use a DELETE without a where clause and then RESEED the identity OR Remove the FK, truncate and recreate but I thought as long as you truncate the child table you...
I have a Winforms application created in Visual Studio 2005 Pro, it connects to an SQL Server 2005 database using the SqlConnection / SqlCommand / SqlDataAdapter classes to extract data. I have stored procedures in my database to return the data to me. What is the best way to handle queries that take a "long time" to complete? (i.e long...
I have some complex stored procedures that may return many thousands of rows, and take a long time to complete. Is there any way to find out how many rows are going to be returned before the query executes and fetches the data? This is with Visual Studio 2005, a Winforms application and SQL Server 2005. ...