SQL Server stored procedure
Is it possible to write stored procedure that works with a variable number of parameters? (in SQL Server 2005) ...
sql-server-2005
TSQL in SQL 2005: Query
I have 2 columns in my table, called TaskSet and SkillsSelected. The sample data as follow: TaskSet | SkillsSelected -------------------------------------------------- SK000001, SK000004, SK000002 | SK000001, SK000002, SK000003 SK000002 | SK000002, SK000003, SK000004 As you can see it's ...
SSIS Package failed
I am running Windows Server 2003 R2 Enterprise Edition SP 2 I have created an integration package in VS 2005 which extracts data from Excel and imports it into a table on SQL server 2005 running on this server. The package runs ok in Visual Studio 2005,I did a bit of research which suggests this is due to SQL 64 bit and Windows 64b bi...
Overwrite values in database by a unique column
I have a datatable comimg from code that contain set of columns (ID- primary key, and name varchar). I have my sql table that also contain same (ID- primary key, and name varchar) type of data. table has 200000+ rows. I want to compare that if sqltable id column contain same id in datatable means (datatable id column value matches with s...
identifying deleted columns
Hi, i am using sql server 2000 .I have a table with four columns ie. col1,col2,col3 and col4. I have records in the table . I used dbcc page command and i could see the values of all the column. I then deleted a column , col2, from the table and did dbcc page again. I could see the values of all the columns except for col2 which value "D...
Is Sql Server Express edition 2005 64 bit supported on Windows Server 2008 64 bit?
Couldn't find any reference to this online. Does any one know? ...
Difference between SQL connection string (local) and .\
Between SQL 2000 and 2005 MS changed the syntax to connect to a local database from (local) to .\instanceName. I have a client who has been using (local)\instanceName to connect to local SQL Server 2005 databases. Usually it works fine, but sometimes it doesn't and he has been using PCname\instanceName. Why does this sometimes fail? ...
Query on Sql Joins
I have three tables. tblLink: (LinkId, LinkName, GroupId, ,SubGroupId) GroupId and SubGroupId are foreign key in tblGroup and tblSubGroup tblGroup: (GroupId, GroupName) tblSubGroup: (SubGroupId, SubGroupName) in tblLink SubGroupId is allowed Null but GroupId is Mandatory. I want to fetch LinkName, GroupName, SubGro...
Request timed out when very low traffic over Christmas Holiday week
Over this past Christmas holiday week, when the website I work on was experiencing very low traffic, we saw several Request timed out exceptions (one on each day >> 12/26, 12/28, 12/29, and 12/30) on several pages that require user authentication. We rarely saw Request timed out exceptions prior to this very low traffic week. It is ...
Clone entire database with a SP
Hi guys, I'm trying to find out if this is possible, but so far I haven't found out any good solutions. What I would like to achieve is write a stored procedure that can clone a database but without the stored data. That means all tables, views, constraints, keys and indexes should be included but without any data. Can it be done? ...
Compare time part of DateTime data type in SQL Server 2005
How can I compare only the time portion of a DateTime data type in SQL Server 2005. For example, I want to get all records in which 'MyDate' field is after a specific time. The following example is a very long and probably not fast way of doing this. I want all date in wich time is greater than 12:30:50.400 SELECT * FROM Table1 WHERE ...
Preferred Method to Catch Specific OleDB Error
Ok - I have a situation in which I must execute a dynamically built stored procedure against tables that may, or may not be in the database. The data retrieved is then shunted to a VB.Net backed ASP based report page. By design, if the tables are not present in the database, the relevant data is automatically hidden on the report page....
Why can't INSERT EXEC Stored procedures be nested?
I found a pretty good article detailing how to go about passing table data around and it mentions that the INSERT EXEC style table data sharing (http://www.sommarskog.se/share_data.html#INSERTEXEC) has the drawback of not being allowed to be nested? In other words [in SQL Server 2005 at least], in the pseudocode below PROC1's INSERT EXE...
Arithmetic overflow problem
i have a table in my database as freespace... it has a column called freesize with its datatype as int. i have three valuse in it : 1065189988 1073741818 1073741819 now i try to get the total free space but i get an error. private void GetFreeSpace() { DataTable dt = new DataTable(); SqlConnection co...
SQL Server 2005 efficient delete
My issue at hand is that i need to remove about 60M records from a table with out causing deadlocks with other processes that use this table. At this point i'm almost done removing the records using a while loop that only processes about 1M records at a time however its taken all day. Q1: What is the optimal way to remove large quantit...
SQL Server cache question.
When I run a certain stored procedure for the first time it takes about 2 minutes to finish. When I run it for the second time it finished in about 15 seconds. I'm assuming that this is because everything is cached after the first run. Is it possible for me to "warm the cache" before I run this procedure for the first time? Is the ca...
INNER JOIN And Row_Num() Function Problem
Hi , I have a stored Procedure which do Sorting and Paging like the Sorting Custom Paged Results of of Scott Michell . I have two tables: Article and Category ,My StoredProcedure works fine for Article Table , But i want add a field of Category into queyr (Inner Join I mean ) Actually I can't do like the Scott Michell has done , Becau...
When should I create database indexes?
Hai guys, when to set index for a table (ie) during table creation or on performance tuning? What are the advantages and disadvantages of indexing? ...
Removing duplicates in SQL Server - Unusual Scenario
Hi Guys, The database I'm currently working on is for a scientific instrument that records data at certain points in time. My instrument records per second, but with a resolution of 5 seconds. ID Total Particles DateandTime 38313 602 2009-01-27 16:25:48.000 38314 602 2009-01-27 16:25:49.000 38315 ...
How to collate SQL_Latin1_General_CP1_CI_AS using IN operator
I want to filter records on 'Email' my query is like this. SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer) It gives me following error Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. I can use collate if there is equal op...