sql-server

Can an Excel Data Connection lock a DB table?

Hi, If I add a simple Sql Server table data connection to an Excel worksheet, can it lock that table if the connection is never closed? ...

How to turn one column of a table into a csv string in SQL Server without using a cursor

I want to return the results of select Column from Table into a comma separated string using SQL Server. The column in question is rather large (nvarchar(2000)) so the solution has to be able to handle very large result values. ...

SQL Server 2005 Backup strategy

I manage a web application for a client with the following specs: ASP.net 3.5 running on a Virtual Windows 2003 Web Server SQL Server Standard hosting the database Database current size of 6Gb, with 1Gb/month growth rate One single table is responsible for 98% of the size, holds the most critical data for the client Log is not kept for...

Does index_id=0 in sys.indexes refer to the table itself?

Unfortunately the BOL is a little vague on this, but index_id = 0 on sys.indexes or sys.partitions appears to refer to the table itself where there is no clustered index on the table. True - or am I missing something? ...

C#: Exception "Input string was not in a correct format." after Row.Delete in DataSet

I am getting a strange exception when trying to update a DataTable after flagging a row for deletion. It looks like it's validating the data in the row I'm trying to delete. I was able to save the same row successfully, why won't it let me delete it? Using C# in VS 2008 and SQL Server CE. The essential bits (I hope) of my code: var li...

Usage history of Stored Procedures in SQL Server 2008

I work with legacy systems that have tens of thousand of lines of stored procedure code, where many of the stored procedures are obsolete and not used anymore. There doesn't seem to be a way to check execution history, so my question is if it might be a good idea to start each stored procedure by inserting a row into a table that keeps r...

TSQL to grant db_datareader and db_datawriter for MS SQL Server

What is the exact SQL to assign db_datareader and db_datawriter roles to a user in SQL Server? Say the user name is MYUSER and the DB in question is MYDB. Thanks! ...

Full text search on calculated columns

I need to allow full-text searching on two fields that are concatenated together. In this case, FullName = FirstName + ' ' + LastName. The simple approach seems to be to create a FullName calculated column then include that as a column in the full-text indexing. Is this an acceptable approach? How would you do it? UPDATE and INSERT happ...

C# Application - Should I Use Stored Procedures or ADO.NET with C# Programming Techniques?

I have a C# Application I am creating that stores all data in SQL Server. Sometimes it's easier for me to make data changes programmatically and sometimes it's easier to have stored procedures and functions in the SQL Server database and call them. I am rather new to programming and I don't know what the subtle advantages and/or disa...

Learning project: Please help me setup a server capable of the following:

Hi, I'd really like some help with this as I have hardly any idea where to start. I'm fed up of not having somewhere to properly test any web code and the free hosting sites I've found only support half of what I want. I want to setup my own server capable of dishing out webpages, running a database and handling WCF services. I'll be ...

Is SQL Server Management Studio as bad as it seems? How to view relatiosnip attributes????

So, I'm using SQL Server Management Studio Express,it has been working not bad so far, ignoring some of the weirdness. But I just happened to get a whole shwack of data deleted when I happened to delete a row in a parent table. So I thought I must have cascade deletes enabled in this relationship. But, how does one determine this?? ...

Code reuse and modularity in SQL

Is code reuse and modulatiry a good idea for SQL Stored Procedures programming? And if so, what's the best way to add these features to a SQL stored procedure code base? I usually create scalar valued functions for tasks that are common and repeated. I find that it eases both development of new procedures similar to existing ones, but ...

Problem with code syntax : Stored Procedure for Generic Insert

I have problem compilin this code..can anyone tell whats wrong with the syntax CREATE PROCEDURE spGenericInsert ( @insValueStr nvarchar(200) @tblName nvarchar(10) ) AS BEGIN DECLARE @insQueryStr nvarchar(400) DECLARE @insPrimaryKey nvarchar(10) DECLARE @rowCountVal integer DECLARE @prefix nvarchar(...

How do I determine a public holiday in Sql server?

I have an application written in c# that cannot run on a public holiday or a weekend. I've looked around a bit and haven't found anywhere (official) that provide all the public holidays for the next say 50 years. If I can get these, I will simply bulk insert them into my sql server Holidays table and use that. However, I can't find ...

What are the advantages of having typed XML columns in SQL server?

From what I've read an XML column in SQL server can be typed (having a schema) or untyped. What are the advantages of using typed XML? Is it just that the XML is validated? ...

Maintaining audit log for entities split across multiple tables

We have an entity split across 5 different tables. Records in 3 of those tables are mandatory. Records in the other two tables are optional (based on sub-type of entity). One of the tables is designated the entity master. Records in the other four tables are keyed by the unique id from master. After update/delete trigger is present on ...

Syntax Prob: Using Variables in Stored Procedures

Can anyone tell how correct the following code below. Iam tryin to create a stored procedure that returns the rowcount of a table whose name is passed to it. CREATE PROCEDURE spROWCOUNTER ( @tablename nvarchar(20) @rowCountVal int OUTPUT ) AS DECLARE @strQuery nvarchar(300) SET @strQuery = 'SELECT @rowCountVal=COUNT(*) FROM '...

test ssis transformation expressions in management studio and creating an expression

I am trying to remove part of a string from another string, for example: declare @url varchar (20) set @url = 'www.test.com~~34235645463544563554' select @url, substring(@url,1,CHARINDEX('~~',@url)-1) I am trying to remove '~~34235645463544563554' I am use to using the built in tsql functions (as shown above) to do this but trying t...

ADO.Net : Get table definition from SQL server tables

I am using C# to write a method that returns the following information about a table: column names, column types, column sizes, foreign keys. Can someone point me in the right direction on how to accomplish this ? ...

Generic Insert stored proc : Runtime error

The following code generates the primaey key for the new record to be inserted and inserts the record into a table, whose name and the values to be inserted are given as parameters to the stored procedure. I am getting a runtime error. I am using Visual Studio 2005 to work with SQL Server 2005 Express Edition ALTER PROCEDURE spGenericIn...