List which columns have a full-text index in SQL Server 2005
How do I list all tables / columns in my database that have a full-text index? ...
sql-server
Scripting individual database objects
I have a database I have inherited, and my best practice is to create the scripts to create the objects (tables, stored procedures and views) and then version control these. It would like to have all the objects in this database scripted out, and put into source control; but on SQL Server 2005 (SQL Server Management Studio) it appears -...
Determine SQL Server version of linked server
Does anyone here know how i can determine the version of SQL running on my linked server through use of TSQL statements? I am running SQL2005 my linked servers are running a mix of sql2000, 2005 and 2008. ...
SQL Server Express Performance breaks with large Logfiles
Hello all, we run since 2 years a small application on SQL Server 2005 Express Edition the Database has gown from 75 MB up to nearly 400MB within this time, the there isn't a big amount of data. But the log file has been arrived at 3,7GB now without changing Hardware, table structure or Program code we noted that the Import processes wh...
How to Recover or Reset SSIS Package Password?
I have a few SSIS packages that were password-protected (their protection level is apparently EncryptAllWithPassword) by a developer who left the company and can't be reached anymore, and trying to open them gives the following error since the password can't be supplied: Error loading 'Package.dtsx' : Failed to remove package protect...
sql server 2005 - select records from tbl A contained WITHIN a text field of tbl B
Hi, I'm trying to work out a SQL Select in MS SQL 2005, to do the following: TABLE_A contains a list of keywords... asparagus, beetroot, beans, egg plant etc (x200). TABLE_B contains a record with some long free text (approx 4000 chars)... I know what record within TABLE_B I am selecting (byID). However I need to get a shortlist of ...
Determine version of SQL Server from ADO.NET
Hi, I need to determine the version of SQL Server (2000, 2005 or 2008 in this particular case) that a connection string connects a C# console application (.NET 2.0). Can anyone provide any guidance on this? Thanks, MagicAndi Update I would like to be able to determine the SQL Server version form the ADO.NET connection object if poss...
Why does my cursor stop in the middle of a loop?
The code posted here is 'example' code, it's not production code. I've done this to make the problem I'm explaining readable / concise. Using code similar to that below, we're coming across a strange bug. After every INSERT the WHILE loop is stopped. table containst 100 rows, when the insert is done after 50 rows then the cursor sto...
Updating SQL Server database from SQL Scripts on installation
Our current scenario is like this: we have an existing database that needs to be updated for each new release that we install we have to do this from individual SQL scripts (we can't use DB compare/diff tools) the installation should run the SQL scripts as automatically as possible the installation should only run those SQL scripts, th...
ADO.NET Query optimisation
I have a bit of a strange one happening. The first query I got from running a profiler on a C# ADO.NET application. What it is doing is not as interesting as the way the parameters are being passed - This query is taking 250+ seconds to complete, but when I modify it (Query 1) by changing the way the parameters are passed (see Query 2), ...
How to execute 3GB SQL file (Micrsoft SQL Server)?
I have a big SQL file that does not fit into memory and that needs to be executed against Microsoft SQL Server 2008. It seems that the sqlcmd.exe tool always loads it into memory first which is impossible in this case. Any ideas? unfortunately, i cannot split the script because it is generated by Redgates excellent SQL Data Compare. the...
Can I get a list of rows with varchar values that aren't castable to datetime?
I have a legacy SQL Server 2000 database that has one column that has a date that is stored as a varchar instead of a datetime for whatever reason. I want to make a view against this data and use a datetime field instead of a varchar to make making a report against the field easier. The good news is that most of the values look like th...
Background Threading in ASP.NET and Impact on Performance
My current site is set up to leave messages for certain people the first time anyone logs into the app. The number of messages left could be anywhere from 1000 to 10000, with each being a query, and all queries in a transaction statement. Yes, I know this seems like poor design, that is not my question. Currently, when a user logs in,...
Prevent a large textbox from getting put on the second page in SSRS.
Greetings. I've got a large textbox in SSRS, with a few textboxes under it. When I view the report in Preview mode, or from the Report Manager it looks fine. But when I actually render it from code (into a PDF), it puts my large textbox on the second page (and leaves what's below on the first page). There's a Connect request here: ...
How do I create a view off a property value table in SQL
I have a table in SQL that has four columns. Two of these are property and value columns that store performance metrics for one of our servers. We're constantly coming up with new performance metrics and we didn't want to keep redesigning our schema, so thats why we designed the table this way. Trouble is, when I create a view to look a...
Detecting Correlated Columns in Data
Suppose I have the following data: OrderNumber | CustomerName | CustomerAddress | CustomerCode 1 | Chris | 1234 Test Drive | 123 2 | Chris | 1234 Test Drive | 123 How can I detect that the columns "CustomerName", "CustomerAddress", and "CustomerCode" all correlate perf...
Alternative to Notification Services
What's a good alternative to Notification Services now that Microsoft has removed it from Sql Server 2008. I don't want to use the 2005 version or build my own from scratch. What third party tools fill this niche? Some of the NS functionality is now available through RS. However, our system requires "on demand" triggering of sending not...
Stored proc to copy relational data (SQL Server 2000)
I've got the following tables (only key columns shown): Order OrderItem OrderItemDoc Document ======= =========== ============ ========== OrderId OrderItemId OrderItemId DocumentId --etc-- OrderId DocumentId --etc-- --etc-- I'm writing a stored procedure to 'clone' an Order (takes an exist...
How to store many years worth of 100 x 25 Hz time-series - Sql Server or timeseries database
I am trying to identify possible methods for storing 100 channels of 25 Hz floating point data. This will result in 78,840,000,000 data-points per year. Ideally all this data would be efficiently available for Web-sites and tools such as Sql Server reporting services. We are aware that relational databases are poor at handling time-ser...
Simplifying (aliasing) T-SQL CASE statements. Any improvement possible?
As you can see, this sucks big time. Any alternative? I've tried using the column alias in the group by clause to no avail. select count(callid) , case when callDuration > 0 and callDuration < 30 then 1 when callDuration >= 30 and callDuration < 60 then 2 when callDuration >= 60 and callDuration < 120 then 3 ...