sql-server-2005

Using MS Team System 2008 Database Edition

Hi, guys! I'm wanting to start source controlling our DBs. I've read a lot of questions here and have found that MS Team System 2008 with Database Edition is what I need. So we get it and we are kind of lost really. We would like in first place, compare the different DBs we have (DEV, STA and PROD) because we had never done any source ...

SQL Server equivalent to Oracle's NULLS FIRST?

So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order: ORDER BY date_sent NULLS FIRST What is comparable in SQL Server? There are these alternatives, assuming the date values are NULL or in the past: ORDER BY ISNULL(date_sent, GETDATE()) DESC ORDER BY (CASE W...

Create an Error in an SSIS Package based on Return Value

If using SSIS to run a stored procedure in an EXECUTE SQL TASK and return a value. If the procedure returns any value besides 0, I want the DTSX package (and the job running it) to error out. How do I create an error based on this return vale? ...

StackOverflow Import Error: LOB beyond 2,147,483,647 bytes?

After downloading the September 2009 StackOverflow data-dump and running Brent's import query, I'm getting the following message: Msg 7119, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1 Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes. Msg 8179, Level 16, State 5, Procedure usp_ETL_Load_Posts, Line...

Reporting Services Interactive Sort problem

I have a set of reports defined for Reporting Services in SQL 2005. I'm rendering the reports using the ReportViewer control inside an ASP.NET 3.5 application. In all the reports I have a chart and a table where interactive sorting is implemented. The sorting works perfectly when I test it on the report server catalog page. But in the re...

Split Ms Sql 2005 Table to two or more parts

I need to split an existing table in to two or more tables on Sql Server 2005. The table already has more than a thousand of rows. For eg current table has cols A, B, C, D, E plus an id column. An I need to add A, B, C rows to another table in another database and add D, E to another table in another database. I know that it is weird. ...

LINQ to XML or SQL Server 2005 XML DML?

I'm writing an app that retrieves RSS feeds on a scheduled daily basis and saves the contents of each feed as XML Data in a SQL Server 2005 database. I want to display the aggregated items, sorted by date, for example, from the saved feed data in my app in Asp.Net GridViews. My question is: should I use LINQ to XML to query the feed da...

how to insert data into table from stored proc, manual values together in a single insert statement

Can anyone tell me how to insert a record in a table in the following case: I have 2 tables: create table #temp1(c4 int, c5 int,c3 int) ...and: create table #temp2(c1 int, c2 int) create procedure sptemp as begin select c1,c2 from #temp2 end Now I want to insert records into #temp1 table using the procedure as: insert in...

How to check if Current ID exits in that table or not

Hi, I am using VB.net and Sql server 2005. I have GridView in Application where I have my CPUserID. There can be thousands records in GridView with different CPUserIDs. Now I have a button "Allocate Token". But before allocating the token I want to check in my Token Table that if that CPUserID has already exists in table it should no...

SQL 2005 Query Optimisation

Hi everyone I have a SQL 2005 table consisting of around 10million records (dbo.Logs). I have another table, dbo.Rollup that matches distinct dbo.Logs.URL to a FileId column in a third table, dbo.Files. The dbo.Rollup table forms the basis of various aggregate reports we run at a later stage. Suffice to say for now, the problem I am h...

Collation conflict in stored procedure while assigning a variable

We are trying to create a stored procedure, however we run into the following error message: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. This error occurs at line 33, which reads as follows: SET @MINTIME = (SELECT CONVERT(varchar,DATEADD(MONTH,-1,G...

Changing INT to BigInt

I have a warehouse table with 16 tons of data in it. I have a few Integer columns in it. We have to cast these into BIGINT for every query we write, because the SUM is too large to fit in an INT. We now have a new datamart under development. So we thought, why not change all these columns into BIGINT and we have less to worry for the ne...

How to split data in SQL Server 2005?

Let's say....there is a column with data, "A, B, C" in SQL Server 2005. How can i split the data in that column into something like.. Column A -------- A B C ...

Convert existing database to Database project

I have a MS-SQL database on a server and have decided to play around with source control for the database. I want to create a database project and include that project within my solution. Is there any way to "import" an existing database into a database project in Visual Studio 2008? I have run a few searches but I haven't really found ...

Checking Data in a Large table against a smaller table

Hi, I have 2 tables one with a lot of records(table 1), and a second(table 2) with similar data but with far fewer records. On a regular basis i need to add a marker to records in the larger table where there is a corresponding record in the smaller table. For example this could be an email address. So if email address exists in the s...

Check constraint with only one value SQL Server 2005

I have a table with these fields: User_id, User_type, User_address Is it possible to add a constraint where only one record can exist where user_type = 'xyz' per user_id? There can be as many user_type = 'abc' as we wish but only one 'xyz'. I know that this is not the greatest design but it is what is there currently and I need to l...

How can I rotate the results of a query in SQL Server 2005?

I would like to be able to rotate the results of a SQL Query so that instead of the results being returned in a series of rows the results are returned as a single row with multiple columns. For example with the following table. Create Table TestData ( things varchar(25) ) Insert into TestData values ('Thing1') Insert into TestData ...

disable delete button if value in database table's--> column is true or checked

Ok... I have a database table called employees.. This has columns called ID, Name, datejoined and Cannotbedeleted (check boxes)... Now i want to add a delete column which deletes the rows when clicked. But there are some entries which cannot be deleted if the Cannotbedeleted field is true (checked)... so the delete button should be in...

authentication to sql 2005 using domain account from ASP

I cannot seem to use a Windows account to access my database from my ASP page. Here is my connection string: PROVIDER=SQLOLEDB;DATA SOURCE=NHA-SQL-I0;UID=DOMAIN\NHA-svcRequestForm;PWD=password;DATABASE=RequestForms I get the classic: Microsoft OLE DB Provider for SQL Server (0x80040E4D) Login failed for user 'NIRHB\NHA-svcRequestForm'. ...

How to optimize a table for fast inserts only?

I have a log table that will receive inserts from several web apps. I wont be doing any searching/sorting/querying of this data. I will be pulling the data out to another database to run reports. The initial table is strictly for RECEIVING the log messages. Is there a way to ensure that the web applications don't have to wait on thes...