Hi All, I was wondering how I can pass either an ArrayList, List<int> or StringBuilder comma delimited list to a stored procedure such that I find a list of IDs using IN(): @myList varchar(50) SELECT * FROM tbl WHERE Id IN (@myList) In C# I am currently building the list as a string which is comma delimeted; however when using...
In my last job we worked on a very database heavy application and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards but these are more platform specific so I'll not go into them here. I'm interested to know what other people use for SQL formatting standards. Unl...
I have a stored procedure that returns an object as XML. How do I handle the case where the object doesn't exist? I can't return null or empty string as the XmlReader complains that it is not valid XML. If I return an empty Tag, how do I tell if it is just an empty object or no object? ...
Hi, further to these two questions, is there a way to disable the messages that may get sent along with the resultset in SQL2008? (please note this is nothing to do with the ANSI_WARNINGS setting. Or NOCOUNT.) Thanks for any help. Edit: It's not a problem with compatibility settings or table owners. And it's nothing to do with NOCOUNT...
What's the recommended way to create a type in MSSQL 2005 with a default value and a check constraint to be used in several columns? I don't want to add a check constraint and a default value for every single column. My first idea was to create a default and a rule to bind to this type but books online clearly states that we should avo...
I was once told that instead of having to have my host register a SQL Server database file + user/password it is possible instead to put my MDB file into my website's APP_DATA and connect to it directly. How do I do this? I tried using the SqlConnection's connection wizard and set DataSource : Microsoft SQL Server Database File (SqlCli...
i Dispose an SqlConnection object, but of of course it isn't really closed. i need closed connection to not hold locks on database objects. How can i prevent closed connections from holding locks? Explanation of the above sentance for those who don't know: When you close an ADO or ADO.NET connection, you aren't actually severing the...
I'm working with a set of data from SQL Server that I'd like to get into a group of Excel files. This task needs to be automated to run on a monthly basis. The data looks like Site ID FirstName LastName ------ ------- --------- --------- North 111 Jim Smith North 112 Tim Johnson North 113 Sachin Tedulkar South 201 Horatio...
i backed up a data: BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.bak' WITH INIT --overwrite existing And then tried to restore it: RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE --force restore over specified database And now the database is stuck in the restoring state. Some people have theorized ...
I would like to run a search with MSSQL Full text engine where given the following user input: "Hollywood square" I want the results to have both Hollywood and square[s] in them. I can create a method on the web server (C#, ASP.NET) to dynamically produce a sql statement like this: SELECT TITLE FROM MOVIES WHERE CONTAINS(TITLE,'"holly...
Ok so say I have 100 rows to insert and each row has about 150 columns (I know that sounds like a lot of columns, but I need to store this data in a single table). The inserts will occur at random, (ie whenever a set of users decide to upload a file containing the data), about a 20 times a month. However the database will be under conti...
I have an SSIS Mulitcast object that splits my flow into 2 paths. In the first path I insert the flow into another database. In the second path I update the rows of the flow to show that they were inserted. I need a way to make one path wait until the other path has finished. (So I can handle any insert errors and not update the ...
Ok so I realize that this is a pretty vague question, but bear with me. I have experienced this problem on numerous occasions with different and unrelated queries. The query below takes many minutes to execute: SELECT <Fields> FROM <Multiple Tables Joined> LEFT JOIN (SELECT <Fields> FROM <Multiple Tables Joined> ) ON <Condition> ...
For my current project we want to present statistical data and rank it. For my case I'm talking about "Favouriting" of an artist, counting the times an artist's track has been played, displaying a count of how many playlists an artists track has been added to a playlist... These are all very domain specific issues, but it's a concrete ex...
I would like to know the best way to transfer data from a web service to an excel worksheet (office 2007). I am not interested in outputing a report to excel from a web site. In our scenario a user will be using excel (with a number of macros and such) and will request about 11000 rows of data from the server to a worksheet. Anyone ha...
The architecture of one of our products is a typical 3-tier solution: C# client WCF web service SQL Server database The client requests information from the web service. The web service hits the database for the information and returns it to the client. Here's the problem. Some of these queries can take a long, long time, and we do...
Today, for the first time in 10 years of development with sql server I used a cross join in a production query. I needed to pad a result set to a report and found that a cross join between two tables with a creative where clause was a good solution. I was wondering what use has anyone found in production code for the cross join? Up...
Imagine you sell an application that stores its data in a Microsoft Sql Server database. Some customers are large companies with existing Sql installations and staff to maintain them. Other customers are small companies who get the SQLEXPRESS version installed by a setup program. The database will soon contain a lot of important data an...
I used an Update statement inside a procedure to update a table which has an update trigger. Does the update statement complete after the trigger completes or what? ...
I have a job that runs every night. I do my own logging in my stored procedure so that I can tell what was going on if something fails. Last night the job failed and I know where. When I ran the job manually it succeeded. So I went to the job history to see what I could find. I selected the "Show Step Details" and can see the message...