sql-server

Using a trigger to record audit information vs. stored procedure

Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving...

"FirstName" and "LastName" store in database USER or USER_PROFILE table?

Currently I store the user's "FirstName" and "LastName" in the USER table, but i also have a USER PROFILE table (which provides additional information about the user) and it would makes sense to have the user's "FirstName" and "LastName" in the USER PROFILE table aswell. So what do i do? make two copies of "FirstName" and "LastName"? ...

Need compare tables utility for SQL Server

Command line or library "compare tables" utility for SQL server with comprehensive diff output to a file in .Net I can't find anything like that. Commercial or free ( XSQL Lite is suitable for my case and ) tools show diffs in grids with possibility to export to CSV. Also they generate sync SQL scripts when run from command line. Wha...

How can I search for numbers in a varchar column

I've got a simple nvarchar(25) column in an SQL database table. Most of the time, this field should contain alphanumeric text. However, due to operator error, there are many instances where it contains only a number. Can I do a simple search in SQL to identify these cases? That is, determine which rows in the table contain only digits in...

Dynamic select clause in SQL Server from XML

I have to query a view and include only those columns which are defined in the XML which comes as a parameter to my SP. Can i include that XML in select clause and extract all columns defined in that XML. Please tell a way to do this. XML format is <Columns> <Column Name="CustomerID"/> <Column Name="CustomerName"/> <Column N...

Attaching databases of sql 2000

I have few databses of sql 2000 on windows 2000. Can I attach these databases to another instance of SQL Server 2000 on anothermachine having windows 2003 installed?? Does attach and detach of databases are platform independent?? ...

Deploy/run database changes before automatic build

I am using SQL Server and Visual studio. I have an automatic build via CCNET. When I commit my code in SVN the automatic build runs and all unit tests runs with mocked/stubbed data. The only thing that not goes automatic now is running the database changes/scripts, I do that by hand. Anybody got tips how this can be autometed? ...

How can we secure our data from DBA?

Hi guys, I have very confidential data in my database. I am trying to secure my data from dba. I am a member of development team. We develop our software and delpoy in a server which has its own dba. We have limited control over the server. In this scenario how can i deny dba of the server to lookup my data and deny making changes to ...

sql rowlock on select statement

I have an ASP.Net webpage where the user selects a row for editing. I want to use the row lock on that row and once the user finishes the editing and updates another user can edit that row i.e. How can I use rowlock so that only one user can edit a row? Thank you ...

How do I limit the permissions of a C# program to read only on an SQLServer Database?

Hi Guys, I have written some C# which connects to a live production database. I want to give my application read only access to the DB but am unsure how to achieve this. Is there any trivial way to get this done by amending the connection string? My understanding is that the application will logon with the credentials of the person ru...

How can I select values from different rows depending on the most recent entry date, all for the same employee ID?

Basically I have a table which is used to hold employee work wear details. It is formed of the columns: EmployeeID, CostCentre, AssociateLevel, IssueDate, TrouserSize, TrouserLength, TopSize & ShoeSize. An employee can be assigned a pair of trousers, a top and shoes at the same time or only one or two pieces of clothing. As we all k...

TSQL to insert an ascending value

I am running some SQL that identifies records which need to be marked for deletion and to insert a value into those records. This value must be changed to render the record useless and each record must be changed to a unique value because of a database constraint. UPDATE Users SET Username = 'Deleted' + (ISNULL( Cast(SELECT RIG...

T-SQL Where statement - OR

Sorry for the vague title but I really didn't know what title to give to this problem: I have the following result set: ID Field FieldValue Culture 1 Color Groen nl-NL 2 Taste Kip nl-NL 3 Color Green en-GB 4 Taste Chicken en-GB 5 Color Green en 6 Taste Chicken ...

Help understand difference in sql query

Select user_name [User Name], first_name [First Name], last_name [Last Name] From tab_user ORDER BY user_name Select user_name [User Name], first_name [First Name], last_name [Last Name] From tab_user ORDER BY User Name Above are the two queries, Is there any difference because of the user_name used instead of U...

What are naming conventions for SQL Server ?

I need to create database for SQL Server, what kind of naming convention I sohuld use? 1) Table names could be : customer, Customer, CUSTOMER 2) Field names could be : customer_id, CustomerId, CustomerID, CUSTOMER_ID, customerid, CUSTOMERID and so on... Is there any official suggestion for naming conventions or what is msot common way...

"Cannot open user default database" error with "User Instance=True"

I have a desktop application that uses Sql user instancing. This is my connection string: "Data Source=.\SqlExpress; AttachDbFilename=C:\path\file.mdf; Integrated Security=True; User Instance=True; Connect Timeout=100;" My application creates this DB, downloads a load of data into it from a web service and then does a lot of actions w...

What is the best way to store a date without a year in SQL database?

I am building an app that has a daily quote that should be stored in the database. Each quote is assigned with a day of the year, including one for Feb 29th. Since the quote only cares about the day not the year should I still use smalldatetime type? Please let me know your opinions, thanks!! ...

Get the unique constraint columns list (in TSQL)?

I can get a list of unique constraints fairly easily with the following query: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='UNIQUE' But how do I get a list of the columns that each unique constraint applies to? ...

Sharing activerecord classes between oracle and ms sql server.

I have primary key sequence for oracle in my Castle ActiveRecord class: [PrimaryKey(PrimaryKeyType.SeqHiLo, "UserId", SequenceName = "users_seq")] public int Id { get; set; } Now i need to configure ActiveRecord to use Sql Server, but it does not support sequences, but it's not possible, since oracle-specific mapping is used. I don't...

How to run sql from a .net application against sqlserver and get output like with SQL Management Studio?

I'd like my app to run sql files and receive the results output as if the sql was run from SQL Management Studio with results to text. i.e. nicely formatted recordsets, printed messages, rows affected, errors, running multiple batches separated by GO statements, etc. Any suggestions how to do this without building it from scratch on top ...