Help with Pivot / Unpivot
I have a table as below Name Priority Date ------------------------- A 2 d1 B 3 d2 How to write a query to achieve the below output ColumnNames d1 d2 -------------------------- Name A B Priority 2 3 Thanks ...
sql-server
Paginate rows from SQL
I am trying to finish writting my news/blog ASP.net C# application and I am getting stuck on how to return only 10 items at a time after filtering items based on tag or keyword. This is an example of what I am looking for: User queries sql by browsing to ?tag=Pizza Backend finds all tags that equal the query string (sent over as a @ pa...
Stored Procedure error
I am testing something. I want to write a statement in my stored procedure that raises an exception/error. I do not want the dividebyzero exception. Any other statement that raises an exception will work. Any help? ...
Date formats mismatch between different servers (XML column)
Hi, I am facing a problem for past two days with regard to the date format in SQL. I have developed a application in ASP.NET and have deployed it in production server. I am persisting data into a table which has an xml column with date feilds like ---some date time--- When I run the application from localhost ( or deploy the applica...
Delete SQL Server 2005 records without logging
How to delete records from SQL Server 2005 tables without logging them into transaction logs. I do not wish to log because once deleted, those records will never be needed again. Currently the various deletes take too much of time. Are there any other options to improve performance of delete statements? I can not use truncate since t...
How do you have 2 order by in SQL Server
Hi, Can you have a single SQL that order events by current day then future dates and within current day and future dates in alphabetic order? Please show me an example? Thanks :) ...
Do DB indexes take same amount of disc space as column data?
If I have a table column with data and create an index on this column, will the index take same amount of disc space as the column itself? I'm interested because I'm trying to understand if b-trees actually keep copies of column data in leaf nodes or they somehow point to it? Sorry if this a "Will Java replace XML?" kind question. U...
Selecting max/min value from more than one fields
In the following query the start/finish columns are datetime fields. How should I modify this query to get two more columns, one with the min date and one with the max date (of all the 6 datetime fields and all the rows) repeated in each row. Alternatively how could I create a new query returning only these 2 (min/max) dates, for the...
SQL Server 2000, Get COUNT(DISTINCT ID) with a condition that I can't write to my WHERE ?
Hello all, First of all, I don't want to use a "join" because that will make my query longer and difficult to read. So what I need to do must be withing the same SELECT statement. My columns in myTable are A, B , C , D , time, ID and H H columnd tells if a record is 'Open' or 'Close', here how my query looks like. SELECT A, B, C, ...
If I partition a table, can I replace the file for a partition without telling SQL Server?
I would like to be able to swap one table partition for another, just by replacing partitionN.ndf before starting up the server. The general aim is to be able to split out some sets of table rows into different files so that when the app is installed, it only goes with one set. There are some rows that are always needed, so Scenario A ...
Why calling a user defined function needs the owner name when calling a stored procedure doesn't ?
Why calling a user defined function need the owner name when calling a stored procedure doesn't ? Please help! ...
Organize stored procedures, views, functions etc with sql management studio.
Hi I'm using Microsoft SQL Server Management Studio to work with a database. I'm only a .Net developer with limited experience in managing sql server, I'm far from a real DBA. In next few days, the number of stored procedures are going to increase very much. Also more views and functions will be created. Are there any way to organize ...
SQL Server Query Performance - Clustered Index Seek
Excuse the long post, but below I have included a full script to generate and populate my test harness. My test harness has the following tables |--------| |-------------| |-----| |--------------| |Column | |ColumnValue | |Row | |RowColumnValue| |--------| |-------------| |-----| |--------------| |ColumnId| |ColumnValueId|...
How can I ensure one-at-a-time processing in SQL Server?
I have a data-upload function that load some data into several tables and processes them. I want my users to be able to initiate two uploads at once (though this will be the exception rather than the rule), but in that instance I want the first upload to complete before the second upload begins. (The whole process should take around 20 s...
SQL Server SMO Database Enumeration returns inexisting database
I'm enumerating all databases of an SQL Server 2005 instance using SMO like as shown below and it returns a non existing database (it was existing in the past). Using conn As SqlConnection = New SqlConnection(_connectionString) conn.Open() Dim smoConnection As New ServerConnection(conn) Dim srv As Server = New Server(smoCo...
Is it possible to switch to a database on a linked server using a 'USE' statement in SQL Server 2005?
I've tried the obvious: USE linkedServerName.databaseName Which gives me the error: `Could not locate entry in sysdatabases for database 'linkedServerName'. If something like this were possible, it'd save me a bunch of clicking around in management studio! ...
Basic Data View Setup Throws Error When Trying to Edit or Insert SQL Server Data on SharePoint Page
Using: - MOSS 2007 - SQL Server 2005 (and Management Studio Express) - SharePoint Designer 2007 - Northwind Database (dbo.categories) First, I am a total novice, so I am fully expecting a user error answer ;) Here is what I did: - Opened a blank Web Part page in SharePoint Designer - Inserted a Data View - Set up a connection to the No...
PHP / SQL Server which adapter to use and general questions
I beginning a project which requires me to use php with a SQL Server database. Although I am using mySQL for years this will be my first project using SQL Server. Is it better to use PDO than MMSQL. Are there any "Gotcha"s I should be aware when using SQL Server. Does SQL Server have any UTF-8 problems? ...
Connect to SQL Server 2008 with TCP/IP
I get the following error when I try to connect with TCP/IP from SQL Server Management Studio. I want a step by step description to solve my problem I don`t know what´s wrong? Cannot connect to =================================== A network related or instance specific error when a connection to SQL Server... (provider: Named pipe-prov...
What is the best way to perform a manipulation with huge amounts of data in SQL Server ?
We need to perform the following operation in our database : There is a table A which has column B_ID that is a foreign key to the table B. There are many rows in the table A that have the same value of B_ID and we want to fix this by cloning the corresponding rows in B and redirecting the rows from A to them. All this is relatively si...