sql-server

Modify XML in SQL server to add a root node

To give some background to this problem first, I am rewriting some code that currently loops through some xml, doing an insert to a table at the end of each loop - replacing with a single sp that takes an xml parameter and does the insert in one go, 'shredding' the xml into a table. The main shred has been done successfully,but curren...

SQL Server 2000 Server Errors

The same database and application acts weirdly on our test machine, but it works nice on other computers. On the test machine: We get SSL error exception. We fixed that based on an MS KB article, but after that it said "Server error" or "General network error" and slowed down to 1-2 stored procedures/second. The profiler said that we...

Is it possible to pass db name as a parameter

I have about 100 sites built in a cms, each with its own database. Each database has the same tables. My stored procedure needs to select all of the pages in a database given a sitename. Below I am trying to pass the database name as a parameter, but it doesn't seem to work. ... @site nvarchar(250) AS SELECT * FROM @site..cmsDocumen...

outer query to list only if its rowcount equates to inner subquery

Need help on a query using sql server 2005 I am having two tables code chargecode chargeid orgid entry chargeid itemNo rate I need to list all the chargeids in entry table if it contains multiple entries having different chargeids which got listed in code table having the same charge code. data : code 100,1,100 100,2,100 100...

A database design question

Hi! I have to store on a database four kinds of object on a database. These objects have the same atributes: Name (varchar2). Description (varchar2). File (Binary blob). Maybe I can use a table to store these objects adding a column to identify the kind of object but I need to store a lot of objects (> 1,000,000 or much more). My q...

Canceling a programatically started SQL Server Express Install

During my application setup, the user has the option of installing a new SQL Server Express 2005 instance in the local machine if they don't want to use an already existing SQL Server 2005 in their network for whatever reason. As the SQL Server installation is optional, I don't have it as a prerequisite of my installer. Instead, I just ...

Temporary storage for cleaned data in Integration Services

I have an Excel file that I need to process three times in integration services, once for projects, once for persons and once for time tracking data. At each step I have the excel source and I do need to do some data clean up and type conversions (same in all three steps). Is there an easy way of creating a step that does all this and ...

SSIS Analysis Services Connection Error In The Transport Layer of initializing provider

I'm trying to connect to an analysis services database in SSIS using a specific user name and password. I'm getting the message error in initializing provider. AN error was encountered in the transport layer. The peer prematurely closed the connection. Any ideas? ...

Analysis Services Only Windows Authentication

Is it true that Analysis Services ONLY supports windows authentication? ...

How to determine SQL Server version on a reporting server

All of our production instances of reporting services are split into the web server components and the reports database components. I know that you can detect the instance of SQL Server on a database server by the following TSQL: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') How...

Memory leak when using time in NHibernate SQL query

I use NHibernate in my ASP.NET application to connect to an MS SQL Server 2005 database. In some cases I need to write my own SQL queries. However, I noticed that the SQL server thread leaks about 50 KB of memory every time I execute the following piece of code: NHibernate.ISession session = NHibernateSessionManager.Instance.GetSession(...

How to transfer a ssis package from Dev to Prod?

I'm trying to move my packages to production using a configuration file, but file is changed only partly and the results go still to DEV server. Does anybody know what to do? ...

Correlated query vs inner join performance in SQL Server

let's say that you want to select all rows from one table that have a corresponding row in another one (the data in the other table is not important, only the presence of a corresponding row is important). From what I know about DB2, this kinda query is better performing when written as a correlated query with a EXISTS clause rather than...

Is Linq-To-SQL getting scrapped?

Duplicate: Is LINQ to SQL DOA? I read somewhere on the 'net that Microsoft is considering moving away from LINQ-To-SQL in its current form as it wasn't very successful. I want as many opinions on this as possible as I'm new to .NET and want to choose the best possible route for where I work. ...

Guide to reading, comparing, and interpretting Execution Plans in SQL Server

I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others. If you want to add your own summary here that would be welcome, though I expect the topic is large enough that links to pre-written articles will be req...

Group data when shredding xml to multiple tables in SQL server

This follows on from this question: http://stackoverflow.com/questions/624206/modify-xml-in-sql-server-to-add-a-root-node/ In addition to the problem posed and now fixed in that question, I am also splitting the existing table into 2, where the 2nd table is a sub-table of the first, with a foreign key back to the first. So currently ...

T-SQL GROUP BY: Best way to include other grouped columns

I'm a MySQL user who is trying to port some things over to MS SQL Server. I'm joining a couple of tables, and aggregating some of the columns via GROUP BY. A simple example would be employees and projects: select empID, fname, lname, title, dept, count(projectID) from employees E left join projects P on E.empID = P.projLeader group by...

SQL to return list of years since a specific year

I need a list of years as a recordset starting with 2004 to current year (in desc order), without writing a stored procedure. Is this possible? (SQL Server 2005). So it should return: 2009 2008 2007 2006 2005 2004 ...

SQL HELP - Conditional where clause based on a BIT variable - SQL Server

I need help writing a conditional where clause. here is my situation: I have a bit value that determines what rows to return in a select statement. If the value is true, I need to return rows where the import_id column is not null, if false, then I want the rows where the import_id column is null. My attempt at such a query (below) doe...

How do you change the datatype of a column in MS SQL?

I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table? ...