sql-server

Select "where clause" evaluation order

In Sql Server 2005 when I have multiple parameters do I have the guarantee that the evaluation order will always be from left to right? Using an example: select a from table where c=1 and d=2 In this query if the "c=1" condition fails the "d=2" condition will never be evaluated? PS- "c" is an integer indexed column, d is a large ...

MS SQL Server: Check to see if a user can execute a stored procedure

How can you check to see if a user can execute a stored procedure in MS SQL server? I can see if the user has explicit execute permissions by connecting to the master database and executing: databasename..sp_helpprotect 'storedProcedureName', 'username' however if the user is a member of a role that has execute permissions sp_helprot...

Two datasets with same parameters in SSRS

Hi, I am trying to create two tables in my SSRS report. I have used 2 different packages with same parameters for the dataset creation. while executing the dataset2 (manually from data tab in the designer) it works fine and gives me field results. But while executing from report (clicking the view report) the second table , which uses ...

TSQL: Howto add a char to a select statement.

I have the following statement SELECT id, descr from mytable which returns 1, 'Test1' 4, 'Test4' 6, 'Test6' 8, 'Test8' 22, 'Test22' Now I want the display to Add a sequential character to the first 4 results... 'A', 1, 'Test1' 'B', 4, 'Test4' 'C', 6, 'Test6' 'D', 8, 'Test8' '',22, 'Test22' Thoughts? Edit: Would prefer a SQL Ser...

Rename a column or append a column? that is the question...

I am about to alter the several tables in a massive system which I probably only understand around 10%. I want to add three columns. One of these is just a rename of an existing column. Part of me wants to :- Rename the column but worried about the impact on unknown parts of the system that use the old name. Append the three columns...

SQL Server FTS performance after adding many records

We have a web application that allows clients to manage large lists of names. For searching on these lists, we use SQL Server 2008's FTS, which usually works well. Our largest client has 900,000 names and enjoys sub-second search times. For another new client, however, I recently imported 150,000 names, and performance is terrible (as i...

Can select into add a new field to the resulting table?

I've got a SQL statement in SQL Server 2005 that looks something like this: SELECT * INTO #TempTable FROM FirstTable WHERE <complex where clause> What I would really, really like is to have the resulting temp table have an extra field that is essentially an integer field counting from 1 up in the order the where clause returned the re...

How can I parse Serv-U FTP logs with SSIS?

A while back I needed to parse a bunch of Serve-U FTP log files and store them in a database so people could report on them. I ended up developing a small C# app to do the following: Look for all files in a dir that have not been loaded into the db (there is a table of previously loaded files). Open a file and load all the lines into ...

Best Sql Server IDE for scripting / development needs

I am looking for the best Sql Server IDE for scripting / development needs. Must haves: Visual GUI for creating complex queries Intellisense for all db objects Auto-format capability to format SQL script to a particular format / coding standard. SELECT Sql output to .xls, .txt with custom choices in delimiters / format Cost is not an...

T-SQL: How do I create a unique key that is case sensitive?

How do I create a unique constraint on a varchar field that is case sensitive (SQL Server 2005)? Currently my constraint looks like this: alter table MyTable add constraint UK_MyTable_MyUniqueKey unique nonclustered (MyCol) When I try to insert the following two values, I get a "Violation of UNIQUE KEY constraint..." error. insert i...

Generating a histogram from column values in a database

Let's say I have a database column 'grade' like this: |grade| | 1| | 2| | 1| | 3| | 4| | 5| Is there a non-trivial way in SQL to generate a histogram like this? |2,1,1,1,1,0| where 2 means the grade 1 occurs twice, the 1s mean grades {2..5} occur once and 0 means grade 6 does not occur at all. I don't mind if the...

Are CLR UDTs commonly used in enterprise applications?

I'm investigating some of the newer technologies available in SQL Server 2005/2008. Most of my applications are written in C# and generally have a database component. Most of what I find on Google are the basic, 'This is how you set up a CLR UDT'. I have a few general questions on their real-world application and use. Are CLR hosted...

Connection String to Local DB file is not working.

connectionString="AttachDbFilename=C:\Documents and Settings\nmartin\My Documents\PS_Upload\TimeTrack\src\TimeTracker\TimeTrack\App_Data\ASPNETDB.MDF;Integrated Security=True; User Instance=True" providerName="System.Data.SqlClient" /> This is the connection string that is provided to me from the Server Explorer for my local MDF f...

SQL Stored-Proc using parameter for server name?

I have a stored procedure that gets all the non-system database names from a SQL Server: select name from MySQLServer.master.sys.databases where name not like ('master') and name not like ('tempdb') and name not like ('msdb') and name not like ('model') and name not like ('Admin') What I would like to do is pass the server name as a p...

FInd out the last write date in a SQL Server database

Folks, Assume you receive a disconnected backup of a SQL Server database (2005 or 2008) and you restore that to your SQL Server instance. Is there a way, is there a system catalog or something, to find out when the last write operation occured on that particular database? I'd like to be able to find out what day a particular database ...

View with a read only column

Hi I would like to design a query where a column is not updateable, but the rest of them yes. How can I do this? Regards ...

OPENQUERY with a variable in a cursor

How can I return a OpenQuery in SQL Server including a variable to a cursor? DECLARE curMyCursor CURSOR FOR EXEC('SELECT * FROM OPENQUERY(SYBASE, ''SELECT * FROM MyTable WHERE MyPrimaryKey=''''' + @Variable + ''''''')') OPEN @ResultCrsr ...

SQL Server 2000/5 - Script to get list of Active or Inactive databases (except system ones)

I need help to script all the databases on an SQL 2005 server and possibly view their activity (specifically the date of last activity). I need to drop all databases that are NOT active on the server. ...

SQL Server Script Wizard

Hi I am using SQL Server 2005. If I try to generate scripts for the dB using the Generate Scripts wizard in the management studio (Right click dB-> Tasks-> Generate Scripts) I get no option like IF EXISTS DROP condition in the list. Whereas some of the SQL Servers installed on different machines have this option enabled. Is the serv...

SQL Server, Converting NTEXT to NVARCHAR(MAX)

I have a database with a large number of fields that are currently NTEXT. Having upgraded to SQL 2005 we have run some performance tests on converting these to NVARCHAR(MAX). If you read this article: http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx This explains that a simple ALTER COL...