column default value or including in insert script?
which is better, is there any performance difference? setting datetime column's default value to getdate() or using getdate() with the insert t-sql script. ...
sql-server
Timeout when I call a stored procedure in SQL Server 2008
From C# with EF, I call a long stored procedure with ExecuteStoreCommand 30 sec after the procedure starts, I have a timeout exception. How can I configure timeout ? On the server or in my C# client ? Thanks ...
Reasons not to have a clustered index in SQL Server 2005
I've inherited some database creation scripts for a SQL SERVER 2005 database. One thing I've noticed is that all primary keys are created as NON CLUSTERED indexes as opposed to clustured. I know that you can only have one clustered index per table and that you may want to have it on a non primary key column for query perfoamce of searc...
What is the fastest code to call multiple stored procedures?
Solution 1 : foreach (var item in itemList) { myContext.ExecuteStoreCommand("EXEC MyProc {0};", item); // Insertion } or Solution 2 : StringBuilder sb = new StringBuilder(); foreach (var item in itemList) { sb.AppendLine(String.Format("EXEC MyProc {0};", item)); // Insertion } myContext.ExecuteStoreCommand(sb.ToString()); ...
how to script non-default collation and skip explicit scripting for default collation?
In SSMS 2008 R2 I create the table aTest(Albnian varchar(10), Dflt varchar(10)) In SSMS table designer both column properties have Collation: < database default > (under Column Properties--> "Table designer") I change collation of column Albnian to a non-default, for ex., to Albanian_CI_AS. If I script the table in SSMS (right-cli...
bulk insert datetime data
Hi, I try to bulk insert some datetime values in this format: 31/12/2005 00:00:00 using something like this: create table Seed ( StartDate datetime not null ) BULK INSERT Seed FROM 'd:\dump\Seed.txt' WITH ( firstrow=2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ) But I get this: Bulk load data conversion erro...
easy button for adding column
Hi All, I right click on my table in ssms 2008 and select Script Table as / Drop and Create Table to new window and I try to run the script but get an error: Could not drop table because it is referenced by a foreign key constraint What was the point of the Drop and Create generate script then? Thanks, rod. ...
Import Excel file to SQL Server row-by-row
I'm importing an Excel file (about 1000 records only) to a dedicated SQL Server database. As I need to work on the incoming data from Excel (add a GUID per row, some data conversions) I want to do it row by row and don't want to bulk import (I have nothing against transactions, though). I'm confused on how to do it correctly. I can eith...
Sql Server Triggers
I have a table which have 4 fields. Col1 Guid Primary-Key (FK to another table) Col2 Guid Primary-Key (FK to another table) Col3 Int Primary-Key Col4 Int Now i want to do this: when a new record inserted by user which have same value as a record exist before on the table like below: There was this Record: X Y Z 2 New record X Y ...
How to create a UDF in the master database that references the correct sys.objects table?
Using SQL Server 2008, I'd like to create a UDF that gives me the create date of an object. This is the code: create function dbo.GetObjCreateDate(@objName sysname) returns datetime as begin declare @result datetime select @result = create_date from sys.objects where name = @objname return @result end go I'd like to put t...
basics of SQL Server 2008 backups
We don't have a DBA and I have to do backups. We have only one database that is 3gb (the others are less than 100mb). The 3gb database is write-heavy, and has very important data. I set up daily backups for all the databases but i think that might not be enough. What are transactional log backups? what is my appropriate backup action...
How to get previous row's specific value to compare with next row value in a gridview
I have a GridView which binds data from DB, and using rowboundevent, I want to get the second cell value of each row and compare it with the second cell value of next row. If they are the same then the cell should be empty, else the value should be written in the cell. ...
How to write a report with multiple filters for the same field in SSRS
The below report is currently written using linq to entities to get the data and building an html table in code. I'd like to rewrite it using SSRS and need some advice. All of the data is in the same table. There will also be date range parameters. Here is the basic template for the report that is needed. Categories 0-30 31-60 ...
SQL Backup to Isolated storage from ADO.NET
Hi All, Is it possible to create an SQL server database backup using c# ADO.net and outputting the .BAK file to Isolated storage? Thanks ...
Transforming string data in a SQL Server database
I need to convert some data stored as varchar in a SQL Server database that I want to change from CamelCase to delimiter-separated. I know how to do this trivially in C# but not T-SQL. Any ideas? ...
Debugging stored procedures in Visual Studio 2008 Pro
I'm trying to setup debugging of stored procedures in VS 2008 Pro, when I right-click on the connection in the Server Explorer I don't see the Allow SQL CLR Debugging option at all. The SQL Server is remote and running the 2005 express edition, so I'm not sure if I have to enable something on the remote server itself. Any help is appre...
preventing a specific record from being deleted
I want to prevent a specific record from being deleted. This trigger works fine for that specific record. However, other records still remain when they're being deleted. Why? ALTER TRIGGER [Globalization].[CountriesTracker] ON [Globalization].[Countries] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; IF ((Select COUNT(*) from [Delet...
SQL Server 2005 backup and restore
Hi folks, I have two backup files 1) is named 'backup.sql' with a bunch of SQL defining TABLES 2) is named 'backup' with a bunch of encoded data, which I believe are the ROWS I need to restore these TABLES + ROWS, but all I am able to figure out is how to restore the tables. Any tips on dealing with these files? It's the first tim...
To CTE or not to CTE...
Having been stuck with SQL2000 for far too long, I've not really had a lot of exposure to Common Table Expressions. The answers I've given here (#4025380) and here (#4018793) have gone against the flow in that they didn't use a CTE. I appreciate that for recursion they are the beez kneez, and there are a few queries that can be greatl...
SQL Server 2008 - basic question on shredding XML
Hello - I'm somewhat new to using XML in sql server, and am having a problem getting the syntax right - and for that matter, probably have some basic confusion about referencing XML elements. Anyway, I'll need to shred XML like the below out into columns. Seems simple, but I can't seem to get the nodes right - or for that matter I do...