Sniffing queries to SQL Server
Is there a way to sniff SQL queries sent to a SQL Server db on any level (above transport level)? Perhaps there's some kind of a tracer in ASP.NET or built-in log in SQL Server ? ...
sql-server
Creating mdf file from sql script
I've created a database in Visual Studio 2008 in an App_Data folder of a MVC Web Application project. This results in an mdf file for the database that can be explored in the Server Explorer tab. You can create a SQL script for changes you do to the database. So I'm wondering how you run these sql change scripts to an mdf-file in Visual...
SQL Server - database size calculation difference question
I am attempting to programmatically monitor the size of a SQL Server database, so that my admin section of my web app can report it, and I can use that to execute some cleanup SPs, to clear log files, etc. I use the following code to calculate the size of the tables, per SO recommendation: CREATE TABLE #t (name SYSNAME, rows CHAR(11), ...
Start stored procedures sequentially or in parallel
We have a stored procedure that runs nightly that in turn kicks off a number of other procedures. Some of those procedures could logically be run in parallel with some of the others. How can I indicate to SQL Server whether a procedure should be run in parallel or serial ie: kicked off of asynchronously or blocking? What would be...
Execute a SQL stored proc from a SQL view
I am finding that calling a stored proc in Excel is not as easy as it should be, but calling a view, or a direct table is very easy. So, how can I create a view that will call a stored proc with no params? I know I won't be able to pass any values into the view, and I don't need or want to, Just want to wrap a stored proc in a view. so...
bulk insert with or without index
In a comment I read Just as a side note, it's sometimes faster to drop the indices of your table and recreate them after the bulk insert operation. Is this true? Under which circumstances? ...
How do I supply the FROM clause of a SELECT statement from a UDF parameter
In the application I'm working on porting to the web, we currently dynamically access different tables at runtime from run to run, based on a "template" string that is specified. I would like to move the burden of doing that back to the database now that we are moving to SQL server, so I don't have to mess with a dynamic GridView. I thou...
One 400GB table, One query - Need Tuning Ideas (SQL2005)
Hi, I have a single large table which I would like to optimize. I'm using MS-SQL 2005 server. I'll try to describe how it is used and if anyone has any suggestions I would appreciate it very much. The table is about 400GB, has 100 million rows and 1 million rows are inserted each day. The table has 8 columns, 1 data col and 7 columns us...
Testing for whitespace in SQL Server
I've got some blank values in my table, and I can't seem to catch them in an IF statement. I've tried IF @value = '' and if @value = NULL and neither one catches the blank values. Is there any way to test whether or not a varchar is entirely whitespace? AHA! Turns out I was testing for null wrong. Thanks. ...
SQL Server Template-Driven Data Entry Question
Based on the answers to this question about dynamically accessing tables, I've decided to take a couple steps back and get some advice on the larger picture. I am revisiting the database design of a Windows Forms application which I am rewriting for the Web using ASP.NET. Also, I have ported our database to Sql Server, so it can handle...
SQL Server Tool to script drop statements which have check existence statements before them?
I have tried SQL Server 2008 Management Studio and other third party tools to script all database objects (views, SPs & tables) and I can't get anything to generate a one file script which has a drop statement preceded with an "If exists.." statement for every object. I need the "if exists" statement so I don't get any errors if an obje...
How to migrate from sql server express to sql server?
I have been developing a web app that I wish to deploy on godaddy that is using a .mdf file for data. I would like to use a sql server database that comes with the account for my data. What is involved in setting up my config and how do I get the schema copied over? Here is the error I get: The 'System.Web.Security.SqlMembershipProvide...
Linq: multiple relationships to same table
I have the same problem as this guy: I have a table that has references my tblstaff table twice for two different people. Now that I have added this second reference neither of them work. What is up w/ that? ...
Database indexes: Only selects!
Good day, I have about 4GB of data, separated in about 10 different tables. Each table has a lot of columns, and each column can be a search criteria in a query. I'm not a DBA at all, and I don't know much about indexes, but I want to speed up the search as much as possible. The important point is, there won't be any update, insert or d...
What size int whould you use for foreign key fields?
I have a star schema type data base, with fact tables that have many foreign keys to dimension tables. The number of records in each dimension table is small - often less than 256 bytes, but always less than 64k. The fact tables typically have hundreds of thousands of records, so I want maximize join speed. I'd like to use tinyints and...
Best practices and anti-patterns in creating indexes in SQL Server?
What are the things that you would consider when defining indexes, clustered and non-clustered, for SQL Server? Are there any anti-patterns that DB newbies should be aware of? Please explain the "Why" or provide references if possible. ...
Using More than 2 Entity Relationships in Report Builder (SQL Server 2005)
I've created a model using SQL Server Reporting Services. When I try to create a report using the Report Builder, I can drag an entity to the report, followed by a nested sub-entity. I get the two-level grouping as expected. However, I cannot add a third level entity to the relationship, no matter which combination I try. What can I do...
SQLServer: How to sort table names ordered by their foreign key dependency
The following SQL separates tables according to their relationship. The problem is with the tables that sort under the 3000 series. Tables that are part of foreign keys and that use foreign keys. Anyone got some clever recursive CTE preferably or a stored procedure to do the necessary sorting?? Programs connectiong to the database are no...
MS SQL Server COLUMNS_UPDATED() Function
I have been researching audit triggers for some of my sql tables. I came across this article on simple-talk. Now I know that the COLUMNS_UPDATED() function used here is only supposed to be available on SQL Server 2005 and later but it appears to work under 2000 as well. Is this perhaps a classic Microsoft undocumented feature in 2000? O...
Sharing data between remote locations
I'm currently estimating how to best share data between offices at different geographical locations. My current preference is for using SQL Server Merge Replication and have a main database and handful of subscribers. The system will also need to allow a few work sites to work disconnected (no or little connectivity on construction site...