sql-server

Dense_Rank() alternative in sql server 2000?(Set based)

Actually, I have the task of finding the employees based on the salary rank. So I used Dense_Rank() and got the answer. Initially I was told to solve in SQL SERVER 2005. Later on they changed the requirement and is saying that the query should run in SQL SERVER 2000 also. I solved that using a while loop. But they are saying that...

Am i right to sacrifice database design fundamentals in this case for the sake of speed?

I work in a call centre that uses single table Access database for its calling, which I moved to a SQL server based system. There's a data list table (not normalized) and a calls table. This has about one update per second currently. All call outcomes along with date, time, and agent id are stored in the calls table. Agents have a predef...

strange SQL server report performance problem related with update statistics

I got a complex report using reporting service, the report connect to a SQl 2005 database, and calling a number of store procedure and functions. it works ok initially, but after a few months(data grows), it run into timeout error. I created a few indexes to improve the performance, but the strange thing it that it works after the inde...

What is wrong with the following statement?

When I do this: select col1,case when [pivot1]=1 then '-' else '' end [pivot1], case when [pivot2]=1 then '-' else '' end [pivot2] from (select col1,col2,col3 from tbl) as c pivot (sum(col3) for col2 in ([pivot1],[pivot2]))as pvt Everything works fine. When I do this: select col1,[pivot1],[pivot2] from (select col1,col2,col3 from tb...

Between SQL Database SNAPSHOT and SQL Database?

Hi, I'm just wondering what's the difference between a SQL database snapshot and a regular SQL database? Can someone out there would like to help me understand the difference between the two? Thanks in advance. ...

Visual Studio 2005 doesn't support Sql Server 2008

I'm developing Reporting services on VS2005 and have to connect to SQL Server 2008. The following Error occurs: "This server version is not supported. You must have Microsoft SQL Server 2005 Beta 2 or later." I found on the net a patch for it, but it doesn't work (link to patch) "The upgrade patch cannot be installed by the Windows I...

Enumerate all running databases

I'm writing a little Database administration program. It works fine if you give the db, but not when you don't know which db is installed. How can I enumerate all running databases? e.g. Output of the program: Port xy MS-SQL Server 2005 Port ab Postgre SQL Server Port cd MySQL Server Port ef MS-SQL 2008 Express Server Port gh Oracle...

UDF - Return a table from a custom sql string execution with "Exec 'select * from ....' "

Hello everyone, How can I return a table from an UDF that executes a custom query string? Eg: CREATE FUNCTION fx_AdvancedSearch ( @keywords varchar(255), (..... other params ....) ) RETURNS TABLE AS BEGIN DECLARE @SqlQuery varchar(1000) ..... SET @SqlQuery = 'my custom select string previously generated' EX...

Are there any programs that will shrink the size of a sql script file?

I have a SQL script which is extremely large (about 700 megabytes). I am wondering if there is a good way to reduce the size of the script? I know there are code minimizers for JavaScript and am looking for one to use with SQL scripts. I am not looking to get performance on the SQL script. I am trying to make the file size smaller. Rem...

How to search with multiple criteria from a database with SQL?

Hi. I am a newbie programmer. I have a page looks like below. I have a car database and i have to make a search from database with this page. There are many criterias that Users can enter and select values. But User doesn't have to fill or select all criterias. For example User can search a car that only depends on "Mark" or can search t...

tsql function split string

I wonder if anyone can help me. I need a tsql function to split a given value such as: 1) 00 Not specified 3) 01-05 Global WM&BB | Operations 2) 02-05-01 Global WM&BB | Operations | Operations n/a I need to get a result like this: cat1 cat1descr cat2 cat2descr cat3 cat3descr ---------------------------------...

Searching for a phrase using SQL Server Full text search

I want to use SQL SERVER 2008 Full text search to get the records that have a word or a phrase as the whole text within the field and not as part of the text within the field. For example: I have many records containing the Word 'Inheritance' as the whole text in the field and other fields containing the same word in between,at the begin...

SQL assign read/write access to all users on PC

I'm installing an SQL database and our application on a single PC. Under Vista this needs to be installed by an administrator account, but this means that the regular user account(s) can run our program, but it can't access the database it needs. What I need is a simple way of allowing all NT logins on the PC to have access to the datab...

Sql Connection String to named instance

I can connect to my sql server 2008 developer server using this in a sample code project: string connection = @"data source=.\SQLSERVER2008;Integrated Security=SSPI;Initial Catalog=RSINET.MVC"; When I try updating the web.config file like so, I get a sql exception: <add name="ApplicationServices" connectionString="data source=.\SQLS...

How tempDB works?

I am trying to understand the tempDB and following are the doubts popping in my mind. What is the lifetime of data in tempDB? Say a query is doing some Order By and uses tempDB for performing that. After this query finishes, someone else also executes a query which utilizes the tempDB. Will the second query find records written by firs...

Save MS Office files stored in SQL Server directly to server directory with ASP.Net

I am storing MS Office files such as PowerPoint (.ppt, .pptx) and Word (.doc, .docx) in SQL Server, and need to save these files to a temp file directory on the server through an ASP.Net application. I get the files up from the database and into the application as a Byte Array. But what I don’t know is how to then save the Byte Array as ...

SQL Server querying xml using a cursor

Hello, I currently have the following block of SQL 2005 code. What I need to do is import the XML as a bulk operation, however for each record I might need to do some additional processing (maybe insert into a separate table). At the moment I can only query the first item, how can I query the complete data in a cursor style where I look...

SQL update value to highest date in table

I have the following problem: Suppose I have a table with the following fields: [ID] [Start Date] [Status Date] [Status Description] [ID] is not unique, and so I may have: ID Start Date Status Date Status 123 01/01/2009 01/01/2009 Start 123 01/01/2009 01/02/2009 Change 123 01/01/2009 01/03/2009 Change 123 01/01/2009...

Using "FOR XML" with XML Serialization with DATETIME type

I'm having issues trying to add some dates to a pre-existing class that is loaded via XML Serialisation, and it's not doing what I thought it should do. I knocked up a basic test with SQL of (where EffectiveFrom and EffectiveTo are declared as DATETIME) SELECT o.EffectiveFrom AS [@EffectiveFrom], o.EffectiveTo AS [@EffectiveTo], FROM...

Local vs Global temp tables - When to use what?

I have a report which on execution connects to the database with my_report_user username. There can be many end-users of the report. And in each execution a new connection to the database will be made with my_report_user (there is no connection pooling) I have a result set which I think can just be created once (may be on the first run ...