How to import / export user account in Reporting Services?
There is a table [ReportServer].[dbo].[User]. But what is the relationship with other tables? And how can I generate the values inside? ...
sql-server
How do I handle an apostrophe (') in MS SQL 2008 FTS?
Hi, I have a website that utilizes MS SQL 2008's FTS (Full-Text Search). The search works fine if the user searches for a string with an apostrophe like that's - it returns any results that contain that's. However, it will not return a result if the user searches for thats, and the database stores that's. Also, ideally a search for tha...
T-SQL Grouping rows from the MAX length columns in different rows (?)
Hello, i'm trying to come up with a way to combine rows in a table based on the longest string in any of the rows based on a row key example CREATE TABLE test1 (akey int not null , text1 varchar(50) NULL, text2 varchar(50) NULL, text3 varchar(50) NULL ) INSERT INTO test1 VALUES ( 1,'Winchester Road','crawley',NU...
DateTimeOffset.Now in T-SQL
I'm executing a INSERT to a sql 2008 db. How do I specify in T-SQL to insert NOW in a DATETIMEOFFSET column? GETDATE()? ...
How to precompile stored procedures in SQL server?
Hi, Good morning ! Is there any way to pre compile stored procedures in SQL Server? My requirement goes like this.. I have some stored procedures, which take more time in compiling than executing. So I want to precompile all the stored procedures. It would be nice to precompile them when the db server is started and running. Any id...
Adding an identity to an existing column [SQL Server]
I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database. What's the SQL command to alter the column to have an identity property? ...
SQL objects that use an index
Is there a way to see what SQL objects from one data base use a certain index. But I do not want to have to see the execution plan for every object because I have a lot of stored procedures and views PS. It's for SQL 2005 ...
T-SQL 1=1 Performance Hit
For my SQL queries, I usually do the following for SELECT statements: SELECT ... FROM table t WHERE 1=1 AND t.[column1] = @param1 AND t.[column2] = @param2 This will make it easy if I need to add / remove / comment any WHERE clauses, since I don't have to care about the first line. Is there any performance hit when using this pat...
SQL Server stored procedure issue calling another stored procedure
Here's a issue I have with a stored procedure (using SQL Server 2005), inside this stored procedure it calls another stored procedure putting the data into a temp table. INSERT INTO #tmpTable (Column1, Column2, Column3) EXEC psp_rptInsideStoredprocedure 2 This inside stored procedure has a mode parameter that determines which columns...
SQL-How to Insert Row Without Auto incrementing a ID Column?
I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column. So, here is the problem. I have to insert into this table an ID for the column that has already been cre...
How do you access the Context_Info() variable in SQL2005 Profiler?
I am using the Context_Info() variable to keep track of the user that is executing a stored procedure and free-form sql. When troubleshooting issues on this server everyone session comes through. I would like to be able to bring in the value of the context_info() variable and filter based on it. ...
SQL Server Profiler - watch for permission denied on objects?
I'm using SQL Profiler to watch some database activity, and I'm interested in watching "Permission Denied" for various objects in the database. There are about 50 different items in the "Security Audit" category, but none that seem to display "Permission denied" for an object. I can see Login Failed, and a few other failures, but nothing...
Why Not Close a Database Connection in a Finally Block
Major Edit: I misread the article! The comment was in regards to the finalize method of the the class not the finally block :). Apologies. I was just reading that you should not close or dispose a database connection within a finally block but the article did not explain why. I can not seem to find a clear explanation as to why you w...
SQL server 2008 domain change
In the near future, my organisation will have to change both the machine name and the Windows domain for one of our SQL servers. The server is running SQL Server 2008 Enterprise Edition and the OS is Windows Server 2008 Standard edition. I have found good references to handle the change of machine name, but can anyone help with what sh...
SQL Server 2000: Limit number of rows
Hello sql gurus, Is there a way to limit the number of rows in a SQL Server 2000 database, such that older rows are deleted when new ones come in? I have some data that I'd like to keep around for a about thirty days - after that, I don't care whether the data lays around or is deleted - as long as the table doesn't become huge. Any o...
sql query - true => true, false => true or false
Hi, Simple query, possibly impossible but I know there are some clever people out there :) Given a boolean parameter, I wish to define my where clause to either limit a certain column's output - or do nothing. So, given parameter @bit = 1 this would be the result: where column = 1 given parameter @bit = 0 this would be the result: ...
Command line Script for cleaning VS project + backing up SQL + zipping??
Does anyone happen to have some sort of a nice command line script they use that could do something like: Clean VS project (delete \bin folder contents, etc) Backup SQL Server database Zip all files up ...
Simplest way of updating SQL Server 2005 from a REST web service
We have a job management application running (Access) with the database in SQL Server 2005. A third party is going to provide job information to us, which they make available as XML via an SSL web service using a REST style interface. The service needs to be polled automatically on a regular - 5 or 10 minutes - basis. The XML data nee...
How do I split an address string in T-SQL?
I want to split a column into 4 columns based on column. Eg: column value includes 'City_Name' , 'State' ,Zipcode' ,'Country' I want to split it into 4 different columns like City_Name, State, Zipcode, Country. How can I do this using T-SQL? ...
T-SQL User defined function overloading?
I understand that T-SQL is not object oriented. I need to write a set of functions that mimics method overloading in C#. Is function overloading supported in T-SQL in any way? If there is a hack to do this, is it recommended? ...