sql-server

Passing an "in" list via stored procedure...

Duplicate of: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767 How can I construct a stored procedure that will allow me to pass (for example) an @IDList so that I can write: Select * from Foo Where ID in @IDList Is this doable? TIA! Kevin ...

Why am I getting this error on my local database and not on my live database?

when I am querying the local database instead of live database I get the following error: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Shop' because it is not full-text indexed. Why is this happening? ...

SPF compliant message with "reply-to" header inside of T-SQL

I've got the following T-SQL code below that's working from inside of SQL Server 2005. I'm looking to add a "reply-to" header so that any reply will go to the "reply-to" address, NOT the FROM address. Yes, I know about setting this up in Database Mail, but I'm looking to just add some code to my existing solution. I don't want to use ...

User authentication when using single database per client?

My company is building an ASP.NET HR application and we have decided to create one database per client. This ensures that clients cannot accidentally view another client's data, while also allowing for easy scalability (among other benefits, already discussed here). My question is - what is the best way to handle security and data acce...

Locking in SQL Server

I have 2 batch programs, 1 is a program that sends email and another one sends fax. They both access a table named QUEUE. In the email sender program, this is what happens in relation to QUEUE. For each record in QUEUE that satisfies criteria: Locks record 1 in QUEUE table: select 1 from QUEUE with (UPDLOCK) where id = 1 Process s...

Unable to debug SQL Server 2005 stored procedures in Visual Studio Team System 2008

I have been trying to debug SQL Server 2005 stored procedures, in Visual Studio Team System 2008. I connected to the database server and did a right-click "Execute", on the stored procedure. I even tried "Step Into Stored Procedure", with no luck. The IDE shows it is running, but I can not seem to break or step into the stored proce...

Should transactions be specified outside a stored procedure or inside?

We can wrap a call to a stored procedure in a transaction and specify an isolation level. Or we can put the transaction inside the stored procedure specify an isolation level there. Which is it better to do? ...

Restore individual tables on Microsoft SQL Server 7?

Hi, is it possible to restore individual tables from a full backup file of Microsoft SQL Server 7 (yes, I know this is really old, but our client can't upgrade for various reasons). The total backup file is about 180GB in size and restoring the whole database once a week to a development server is not pratical, as it takes several days...

how to create a mssql view for getting last state information

lets say, i have two tables, one for object records and one for activity records about these objects. i'm inserting a new record in this activity table every time an object is inserted or updated. for telling it in a simple way, assume i have four fields in activity table; objectId, type, status and date. when an object is about to be...

Can I append to a BLOB in MSSQL database from C# without reading the original data?

Hi, I'd like to be able to append some data to a binary field in a MS SQL 2005 server from C# without reading the original data, concatenating it, and then setting it all back. Is this possible? Cheers! Steve ...

SQL Server 2005 Full Text forum Search

I'm working on a search stored procedure for our existing forums. I've written the following code which uses standard SQL full text indexes, however I'm sure there is a better way of doing it and would like a point in the right direction. To give some info on how it needs to work, The page has 1 search text box which when clicked will ...

Remove duplicate from a staging file

I have a staging table which contains a who series of rows of data which where taken from a data file. Each row details a change to a row in a remote system, the rows are effectively snapshots of the source row taken after every change. Each row contains meta data timestamps for creation and updates. I am now trying to build an update ...

Are table indexes getting replicated to my sqlce database?

Hi i have a ms sql database that is getting replicated to mobile sqlce databases. My question is the following: Are the indexes getting propagated to the sqlce database tables as well? ...

.Net inserting NULL values into SQL Server database from variable values

Hi. There have been similar questions but the answers weren't what I was looking for. I want to insert the a value of NULL into the SQL Server database if the reference is NULL or a value has not yet been assigned. At the moment I am testing for null and it looks like String testString = null; if (testString == null) { command.Para...

How to run an SSIS package from Visual Basic 6?

Can anyone provide some information on how to run an integration services package, on an SQL server 2005 instance, from Visual Basic 6? Any help at all is much appreciated. ...

Simplest way to output XML from SQL Server to client

I need to return contents of an xml column in sql server to client by simply clicking a URL. The xml has processing instruction to open the correct application (Infopath in this case). I'm a bit confused as to all the various xmlreader writers and streams available. I suppose I want to send the contents of SQLs ExecuteXmlReader to Resp...

SQL Optimization

Is there a performance difference between these two queries. Apparently WHERE should be reserved for querying values on the table to be SQL 92 compliant, is there a performance reason? SELECT Foo.FooId ( SELECT Bar.BarId FROM Bar WHERE Bar.FooId = Foo.FooId FOR XML PATH('Bar'), TYPE ) As 'Bar' ...

CPU USAGE IN SQL BOX

Hi friends, Is there a best number that a CPU usage be for a SQL BOX. I just want to make it clear that what is the preferable number. Please, suggest. Thanks, ...

How to use a single SqlTransaction for multiple SqlConnections in .NET?

I have SQL Server 2000, it doesn't support MultipleActiveResults. I have to do multiple inserts, and it's done with one connection per insertion. I want to begin a transaction before all insertions and finish it after all insertions. How do I do it? ...

Creating a new text column in a SQL Server Table: which type should I choose?

I have a text field that can be 30 characters in length, however it can achieve around 2.000 characters. For "future safety", it would be nice if this new column could support until 3.000 characters. I think the average size of this field is 250 characters. Which SQL Server 2000 data type is better to increase performance and disk spac...