In SQLServer is there a DMV to get the dependencies of an object?
I'm looking for something that would get me the same information that is displayed when I select "View Dependencies" + "Objects on which [some_table] depends" ...
sql-server
Is it possible to SELECT multiple constants into multiple resultset rows in SQL?
I know I can "SELECT 5 AS foo" and get the resultset: foo 5 (1 row) ...is there any way to "SELECT 5,6,7 AS foo" and get the resultset: foo 5 6 7 (3 rows) ...I'm well aware that this is not typical DB usage, and any conceivable usage of this is probably better off going w/ a more ordinary techniq...
Executing Stored Procedure via sp_executesql
Good morning, I am attempting to execute a Stored Procedure within another stored procedure. The catch is that the stored procedure name is dynamically built within the first procedure. Here is an example of what I am trying to do ... CREATE PROCEDURE SPINSVALUE_12345 @guid uniqueidentifier AS DECLARE @returnValue bit DECLA...
SQL Service Broker and .NET Windows Service - Best practices?
I currently have a database that gets updated from a legacy application. I'd like to utilize a SQL Service Broker queue so that when a record is updated, a message is placed in the queue (using a trigger or something). I would then like to have a long running application (Windows service written in .NET) that is constantly "listening" ...
Locking in a SQL Server Stored Procedure
I have a stored procedure that manipulates an integer table field (which represents a sequence number) based on some criteria - the criteria can reset the field back to zero. In a multiuser environment there is a possibility that the field may be referenced by 1 user before it is updated by another user so I would like to prevent this by...
sql primary key and index
Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key. Edit: an additional question - will it do any ...
Is a VFP uniqueidentifier the same as a Guid?
Hi I'm converting a foxpro database to SQL Server and some of the keys are VFP-generated unique identifiers - GUIDs I assume. Are these valid uniqueidentifiers? Can I just use the bytes and convert them easily into a uniqueidentifier in SQL Server? Thanks, Craig ...
How do I remove offline Files from a SQL Server 2005 database
I have a database that was restored from a SQL 2000 instance to a SQL 2005 instance some time ago. The SQL 2000 instance had old, unused Full Text Searches defined. It appears that, when the file was restored, the full text searches were not restored but just deleted. This left the database in a funny state, with the FTS files still as...
Getting odd error on .net ExecuteNonQuery
I'm working in .NET with SQL server on the backend I have a database that I create a record in using a web control - then I need to update some of the fields. I can trap the sql statement and run it in sql server successfully - however, when I try to run execute non-query I get the following error: Unhandled Execution Error Incorrect...
InvalidOperationException
Has anyone encountered this error using SQL Server 2005 and Data access application blocks in a failover configuration? "InvalidOperationException Framework Data Provider Error 6" It seems to occur in our server application at irregular intervals and more frequently when SQL Server is in failover config? While doing some research I s...
How can I get a list of all of the user databases via t-sql?
I want to get a list of all of the user databases from an mssql server instance. What's the best way to do this? I know I can select from sys.databases, but I don't see any way to filter out system databases besides hardcoding a list of names to exclude. I need the script to work on 2000/2005 and 2008. If the approach I listed above...
Is this an efficient sql server query?
Hi, If I write a sproc in this format, will sql server do this effeciently or should the sql 'weaving' be done on the server side (.net)? Note: this is just a rough idea of what my query looks like, there are more 'if' clauses the build up my query that I execute using 'exec' declare @sql nvarchar(4000) declare @order nvarchar(4000) ...
Using ORDER BY on SELECT DISTINCT in TSQL
I am trying to retrieve a list of date strings ordered by date like this... SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate FROM dbo.ProviderProcessGeneratedDate ORDER BY GeneratedDate This orders by the varchar that I converted the dates to. example... 02/01/2008 02/15/2008 02/21/2007 02/23/2007 02/29/2008 ...
How to apply the DRY principle to SQL Statements that Pivot Months
I'm wondering how others handle this situation... and how to apply the Don't Repeat Yourself (DRY) principle to this situation. I find myself constantly PIVOTing or writing CASE statements in T-SQL to present Months as columns. I generally have some fields that will include (1) a date field and (2) a value field. When I present this bac...
SQL Join Not Returning What I Expect
I have a temp table I am creating a query off of in the following format. That contains a record for every CustomerID, Year, and Month for several years. #T Customer | CustomerID | Year | Month ex. Foo | 12345 | 2008 | 12 Foo | 12345 | 2008 | 11 Bar | 11224 | 2007 | 7 When I join this temp table to another table of the followin...
Linked servers and SQL Server 2005 on localhost
Is it possible to run SQL Server 2005 on localhost, with a schema of it's own, and then use data from another remote server? Can this be achieved with linked servers? Im trying to set up a proper sandbox on localhost. ...
What's the best solution for file storage for a load-balanced ASP.NET app?
We have an ASP.NET file delivery app (internal users upload, external users download) and I'm wondering what the best approach is for distributing files so we don't have a single point of failure by only storing the app's files on one server. We distribute the app's load across multiple front end web servers, meaning for file storage we...
How do you manage your run once sql install scripts in subversion?
I'm working at a company that does several releases to production every year and during the build up to each release we gather up a collection of 1 time sql install scripts like table creation and dataports. The way things currently work is that after the release to production, we branch, tag then we delete all 1 time scripts from subv...
There are a method to paging using ANSI Sql only?
I know: Firebird FIRST and SKIP; MySql LIMIT; Sql Server ROWNUMBER(); Does someone knows a SQL ANSI way to perform result paging? ...
What the best resource to learn ANSI SQL?
I don't mean "Basic SQL", but strongly the specs and the difference between the specs and the implementations between great databases (like SQL Server, Oracle, etc). ...