sql-server

TSQL foreign keys on views?

I have a SQL-Server 2008 database and a schema which uses foreign key constraints to enforce referential integrity. Works as intended. Now the user creates views on the original tables to work on subsets of the data only. My problem is that filtering certain datasets in some tables but not in others will violate the foreign key constrain...

How To Query for Specific User Access Rights

I have an old database that I am inheriting. The access rights are not clearly defined anywhere and I'm looking for a quick way to get them for everyone. Let's say I have a user in my database that does not belong to any membership roles. However, they have been given access to do specific things to specific tables. For example, they can...

Backup SQL Server database in ASP.NET

Dear guys I want to backup a SQL Server database with C#. I have written a stored procedure which backup the database: CREATE PROCEDURE WINC_BackupDatabase AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here ...

SQL Server 2005 SSIS Checksum Package

Folks, We're building an ETL process to load mid-size dimensional data warehouse using SQL Server 2005 SSIS on 64bit OS. We're planning to use SSIS's Checksum package to manage SCDs (Slowly Changing Dimensions). Even though we're doing a proof of concept using SSIS Checksum package, I'm not comfortable using it in real production scen...

Delete a record and all the record that precede it

I have a table that has a primary key based on GUIDs. I need to find a record in this table based on the primary key and then delete this record and all the record that appear before it. Is there a command to delete preceding records or can anyone give me some example SQL on how I could do this. I am using SLQ server 2008 ...

SQL Insert multiple rows using stored procedure and xml parameter?

I've used a table valued parameter before, but I'm not sure how to use xml. I do not know the best way to format my xml but I think I would try this: <Car> <Name>BMW</Name> <Color>Red</Color> </Car> Then I would pass the xml (one or more car) to the stored procedure and it would insert one row for each car I pass (with ...

Help with SQL 2005 and inner join

I am trying to inner join 2 temp tables I know this can be done, I have done this before but i completely forgot how to do it Please advise meBelow is the query that I try to execute select tmp1.*, tmp2.cnt from ( select 1 as ClassificationType, tblMatches.IdGame, tblMatches.IdPlayer, sum(Score) as Score, sum(...

How can I check if SQL server is alive?

I have a method to check and make sure my SQL server is online, which I use in some connection sensitive parts of my code. Although it works fine, I notice it takes upto 20ms to run, and I was wondering if anyone knows of a better way of checking SQL server to ensure its up and kicking. Here is my existing code. [System.Diagnostics.Co...

Can I share a single database connection between ODBC and ADO?

Is it possible to have an ODBC connection and an ADO connection share the same underlying SQL Server connection, so that both are using the same SPID? Currently I am using SQLDriverConnect and ADODB::_ConnectionPtr->Open. I can make these connections in either order, so perhaps it's possible to open one making use of the other? (Langua...

Fine control over SQL Sort Order

I have a SQL Report driven by a query that sorts it in ascending order of some numerical value. But the user wants two particular rows of data, which happen to appear at different ordinal positions in the query results, to be juxtaposed. Is there a way to do this, either through the report's driving SQL or the .rdl file itself? ...

Error trying to delete database user account schema

I'm trying to delete a user's schema from a database and I'm getting the following error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for Schema 'ext_owner'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;ProdVer=9.00.4035.00&amp...

DB Agnostic ASP.Net?

Hello, we are making an ASP.Net application. We would like to have our application to be at least sorta DB agnostic, most notable to be compatible with both SQL Server and PostgreSQL. What is the best way of doing this? What are some of the common pitfalls? Also is there a class or something that already abstracts away the difference bet...

Best practices for stored procedure API?

We are adding some stored procedures to our product that can be called by 3rd-party clients. Are there best practices for parameter validation, return values, RAISERROR, etc? 3rd-party clients will not have direct table access, only to certain sprocs. The table touched by the sprocs is well-constrained but we want to be as user-friendly...

Is there a tool to generate a full database DDL for SQL Server? What about Postgres and MySQL?

Using Toad for Oracle, I can generate full DDL files describing all tables, views, source code (procedures, functions, packages), sequences, and grants of an Oracle schema. A great feature is that it separates each DDL declaration into different files (a file for each object, be it a table, a procedure, a view, etc.) so I can write code...

SQL Select Condition Question

I have a quick question about a select statement condition. I have the following table with the following items. What I need to get is the object id that matches both type id's. TypeId ObjectId 1 10 2 10 1 11 So I need to get both object 10 because it matches type id 1 and 2. SELECT ObjectId FROM Table WHERE TypeI...

does money map to float well?

does the sql data type money map to c# float? ...

can postgresql scale to the likes of sql server? is it easy to tune?

hoping someone has experience with both sql server and postgresql. Between the two db's, which one is easier to scale? Is creating a read only db that mirrors the main db easier/harder than sql server? Seeing as sql server can get $$, I really want to look into postgresql. Also, are the db access libraries written well for an asp.net ...

Are statistics automatically updated when a new index is created?

Is there any benefit to running an UPDATE Statistics after you create an index or is it done automatically for you? ...

SQL Exam - estimating table size problem

I'm preparing to the SQL Server exam (70-431). I have the book from Sybex "SQL Server 2005 - Implementation and Maintenance". I'm little confused about estimating a size of a table. In the 2nd chapter there is explained how to do this: Count the row size from the formula: Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +...

How do I identify a blank uniqueidentifier in SQL Server 2005?

Hello I'm getting a uniqueidentifier into a Stored Procedure that looks like this "00000000-0000-0000-0000-000000000000". This seems like a simple thing, but how can identify that this is a blank uniqueidentifier? If I get a value like this "DDB72E0C-FC43-4C34-A924-741445153021" I want to do X If I get a value like this "00000000-0...