When should I use semicolons in SQL Server?
While checking some code on the web and scripts generated by SQL Server Management Studio I have noticed that some statements are ended with a semicolon. So when should I use it? ...
sql-server
Unit Testing TSQL
Is there anybody out there writing unit tests for their TSQL stored procedures, triggers, functions ... etc. I've recently started making database and restores and installs part of our automated Cruise Control build process. Now I'm thinking about taking it to the next level where we do the install, then run through a list of stored pr...
Sql Server installation error
Hi. While i'm installing SQl server 2005 on my system i'm getting an eroor message like this."Setup cannot statrt sqlserver services, just refer hot ststrt services by manually" I'm using windows seven Beta as OS ...
SQL Server XML Query Advice
I'm writing a user-defined function to extract values from an XML column in SQL Server which represents a simple dictionary of string key-value pairs. The only way I've made it work so far seems overly complex. Do you have any simplifying suggestions or tips for the DictValue function below? IF EXISTS (SELECT * FROM sys.objects WHERE...
Database replication from SQLserver 2000 to SQLserver 2008
I'm trying to replicate a rather large database from SQLServer 2000 to SQLServer 2008, located on two different servers. I found an article about attempting this and have been trying to follow its direction. Here is the article. Mixed Mode Bi-Directional Transactional Replication between SQL 2000 and SQL 2008 Here is the part I'm s...
SQL Server (2005+) query to return the base table and base column (field) for each column (field) in a view
I want a query that will return a row for each column in a view, and a row for the view itself. There should be a column basetable in the result that gives the base table for the column in the current row, and a column basefield in the result that gives the name of the column in the underlying query (for renamed columns). It would be a ...
SQL 2005 CSV Import Quote Delimited with inner Quotes and Commas
I have a CSV file with quote text delimiters. Most of the 90000 rows are fine, but I have a few rows that have a text field that contains both a quote and a comma. For example the fields value would be: AB",AB When Delimited this becomes "AB"",AB" When SQL 2005 attempts to import this I get errors such as... Messages Error...
Setting up a linked server from SQL Server 2005 to PostgreSQL
Since I struggled a bit with this one and couldn't find a good online source with simple steps, here it is. ...
Database permanent identificator
Does anybody know if there is any unique identificator for a database that I could use to identify a database even it has been renamed or restored/copied from different server? This kind of id could be created by server when a database is created but I cannot find any. ...
InnerXML from an element selected by SQL Server's OPENXML command
As a follow-up to this question - Is there a way to get the raw XML inside an element that you select using OPENXML? Should I be using an XML datatype instead? Is there a way to get the raw XML with the XML datatype? ...
How do you find the last time a database was accessed?
In SQL Server 2005, can you easily determine the last time someone queried a database. ...
Can someone explain my Windows/SQL Server memory usage
I'm running Windows Server 2003 x64 with 8GB RAM and SQL Server 2005 64 bit. I have SQL set to use loads of memory, but the SQL process only ever takes under 100 MB RAM. Is this normal? It is accessing data with indexes many GBs in size. Moreover, no process is taking over 100MB, yet there is only a minimal (<100MB) amount ofree memory....
Throwing Exceptions in SQL CLR Stored Procedures
Is it good design to throw exceptions from SQL CLR stored procedures? Since we are in the context of SQL Server, do any special considerations need to be made? Is this bad design? [Microsoft.SqlServer.Server.SqlProcedure] public static void MyStoredProcedure(string foo) { if (string.IsNullOrEmpty(foo)) { throw new Argum...
Anybody using SQL Server Spatial in a production environment?
I'm looking for some spatial database features but can't upgrade to SQL Server 2008 at the moment. I've come across this open source mssqlspatial package which would provide most of the features I need however I'm hesitant to deploy it without knowing if it's production ready. I was wondering if anybody out there had deployed this pack...
Delphi: "Parameter object is improperly defined. Inconsistent or incomplete information was provided."
I'm trying to insert a record into a table in a 3-tier database setup, and the middle-tier server generates the error message above as an OLE exception when it tries to add the first parameter to the query. I've Googled this error, and I find the same result consistently: it comes from having a colon in a string somewhere in your query,...
Need Help of Sql Database Administrator
I have a database by name database1 and it contain a table by name of employee In employee table have many fields like employee, name, salary etc etc. by mistake i am change salary value 0 for all employee. I am restore database from backup by name database2 Now i need assistance to update my current database which is database1 I wan...
Using temp tables in IF .. ELSE statements
Why does SQL Server insist that the temp table already exists! one or the other will happen!! , so it will never be the case. declare @checkvar varchar(10) declare @tbl TABLE( colx varchar(10) ) set @checkvar ='a' INSERT INTO @tbl (colx) VALUES('a') INSERT INTO @tbl (colx) VALUES('b') INSERT INTO @tbl (colx) VALUES('c') INSERT IN...
Using custom codification scheme instead of GUID as Primary Key
I'm upsizing a backend MS Access database to SQL Server. The front-end client will remain an Access application for the time being (has about 30k lines of code). The aim is to eventually allow synchronisation of the database accross multiple servers (not using replication but probably the sync framework). Currently, all Primary Keys i...
ADO.NET SQL Client Provider default database connection
Does the .NET SQL Client DataProvider always connect using the master database. I've noticed that when I connect to the database to execute an ADO.NET query and then look at the active connections in SQL Management studio, the SPID for the .NET SQL client provider always uses master DB The initial catalog in my connection string is tha...
Date Time Formating
Hi i need to wirte sql query in such a way that it will retrive the date in the following format dd MM yyyy HH mm ss AM(or PM) eg 31 12 2009 12 45 06 AN ...