sql-server

Repairing an MDF file

Can somebody in this forum recommend a tool/procedure to repair or recover a corrupt SQL Server 2000 database file? ...

"Similar Posts" like functionality using MS SQL Server?

I have lots of article store in MS SQL server 2005 database in a table called Articles- "Articles (ArticleID, ArticleTitle, ArticleContent)" Now I want some SP or SQL query which could return me similar Article against any user's input (very much like "Similar Posts" in blogs OR "Related Questions" in stackoverflow). The matching shou...

Query to identify the number of revisions made to a table

Is there any query which can return me the number of revisions made to the structure of a database table? Secondly, how can I determine the number of pages (in terms of size) present in mdf or ldf files? ...

Developer moving from SQL Server to Oracle

We are bringing a new project in house and whereas previously all our work was on SQL Server the new product uses an oracle back end. Can anyone advise any crib sheets or such like that gives an SQL Server person like me a rundown of what the major differences are - Would like to be able to get up and running as soon as possible. ...

Use of .net Assemblies in SQL Server 2005

I've recently discovered that it's possible to place .net assemblies on SQL Server >=2005 servers so that .net functions can be called in T/SQL statements. I wondered what uses people found for these and how they perform? ...

Adapt Replace all strings in all tables to work with text

I have the following script. It replaces all instances of @lookFor with @replaceWith in all tables in a database. However it doesn't work with text fields only varchar etc. Could this be easily adapted? ------------------------------------------------------------ -- Name: STRING REPLACER -- Author: ADUGGLEBY -- Version: 20.05.2008 (1.2)...

How Do I Check Job Status From SSIS Control Flow?

Here's my scenario - I have an SSIS job that depends on another prior SSIS job to run. I need to be able to check the first job's status before I kick off the second one. It's not feasible to add the 2nd job into the workflow of the first one, as it is already way too complex. I want to be able to check the first job's status (Failed, Su...

SQLServer Native Client Error "Connection Busy With Results From Another Command" in SSIS

I'm getting a "Connection Busy With Results From Another Command" error from a SQLServer Native Client driver when a SSIS package is running. Only when talking to SQLServer 2000. A different part that talks to SQLServer 2005 seems to always run fine. Any thoughts? ...

How do I create a database programatically in SQL Server?

I would like to create a new database from my C# application. How can I do that? I'm assuming once I create it, I can simply generate a connection string on the fly and connect to it, and the issue all the CREATE TABLE statements. ...

Is it okay to have a lot of database views?

I infrequently (monthly/quarterly) generate hundreds of Crystal Reports reports using Microsoft SQL Server 2005 database views. Are those views wasting CPU cycles and RAM during all the time that I am not reading from them? Should I instead use stored procedures, temporary tables, or short-lived normal tables since I rarely read from m...

GUIDs as Primary Keys - Offline OLTP

We are working on designing an application that is typically OLTP (think: purchasing system). However, this one in particular has the need that some users will be offline, so they need to be able to download the DB to their machine, work on it, and then sync back once they're on the LAN. I would like to note that I know this has been do...

I can't see "Report Builder" button in the Report Manager on SQL Server 2005

I am a member of all the roles (Browser, Content Manager, My Reports, Publisher, Report Builder). If I login with a Local Administrator account, I can see and use it fine. Any ideas? ...

What's the best way to work with SQL Server data non-programmatically?

We have a SQL server database. To manipulate the data non-programmatically, I can use SQL Server Management Studio by right-clicking a table and selecting "Open Table". However this is slow for very large tables and sorting and filtering is cumbersome. Typically what we have done until now is to create an Access database containing link...

What is the command to truncate a SQL Server log file?

I need to empty an LDF file before sending to a colleague. How do I force SQL Server to truncate the log? ...

How do I execute a large SQL script (with GO commands) from c#?

I need to execute a large set of sql statements (creating a bunch of tables, views and stored procedures) from within a c# program. These statements need to be separated by "GO" statements, but SqlCommand.ExecuteNonQuery() does not like "GO" statements. My solution, which I suppose I'll post for reference, was to split the sql string on...

SQL Server Merge Replication Schedule

We're replicating a database between London and Hong Kong using SQL Server 2005 Merge replication. The replication is set to synchronise every one minute and it works just fine. There is however the option to set the synchronisation to be "Continuous". Is there any real difference between replication every one minute and continuously? ...

I get a "An attempt was made to load a program with an incorrect format" error on a SQL Server replication project

The exact error is as follows "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format." I've recently started working on this project again after a two month move to ...

Cost of Inserts vs Update in SQL Server

I have a table with more than a millons rows. This table is use to index Tiff Images. Each image have fields like date, number, etc. I have users that index this images in batch of 500. I need to know if it is better to insert first 500 and then 500 updates or when the user ends do the 500 insert with all the data. A very important thin...

Is SQL Server Bulk Insert Transactional?

If I run the following query in SQL Server 2000 Query Analyzer: BULK INSERT OurTable FROM 'c:\OurTable.txt' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWS_PER_BATCH = 10000, TABLOCK) On a text file that conforms to OurTable's schema for 40 lines, but then changes format for the last 20 lines (lets say t...

Non-Clustered Index on a Clustered Index column improves performance?

In SQL Server 2005, the query analyzer has told me many times to create a non-clustered index on a primary ID column of a table which already has a clustered index. After following this recommendation, the query execution plan reports that the query should be faster. Why would a Non-Clustered index on the same column (with the same sor...