sql-server

What changes should be made to SQL Server’s locking architecture to make it more developer friendly?

I’ve lately come across a rather frustrating situation where SQL server refuses to issue locks only against a primary key when a statement like this select * from table with (rowlock updlock) where key=value is executed against it. Now don’t get me wrong here, it does lock the row but it goes one step farther and locks the table too. ...

How do I suppress empty namespaces with FOR XML in Sql Server

We are encountering a strange problem with SQL Server 2005/2008 using the FOR XML with fragments of xml and namespaces. Here is the query in question. WITH XMLNAMESPACES ( DEFAULT 'http://tempuri.org/newincomingxml.xsd', 'http://tempuri.org/newincomingxml.xsd' as [xsi], 'http://tempuri.org/newincomingxml.xsd' as [a] ) SELECT [@a:Sou...

SQL Query: DELETE all rows except for the xx most-recent

I'm writing a script to check RSS feeds on regular intervals and need to prune out old articles. I came across this answer which seems really close to what I'm looking for: http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n I need a similar solution that works the same way, except it k...

sql server conditionals

I am getting an error when I run this query because the data type money can't be implicitly converted to a varchar. However, I am using an if statemnt to make sure the data type is not money before I try the conversion. Clearly, the conversion is being executed anyways. Anyone know why? table: BBH_NEW col: rebate2 datatype: money if...

Can I set VARCHAR size larger than 8k but less than MAX?

Starting in SQL 2005, VARCHAR(MAX) is no longer limited to 8000 bytes, it instead can go up to 2GB using "overflow" pages. But what if I want to limit this column to say, 10k bytes? It seems I get an error if I try to put anything in the size parameter above 8000. Which is odd because MAX is the same as asking for a 2GB limit. Seems ...

Need help with Select Into

I'm trying to create my first data mart. I am creating my dimension table with a "Select Into" The code is as follows: select distinct fpc_number, fpc_desc into m2mdata01dw..ProdClass from m2mdata01..INPROD How can I set up a autonumber primary key in this situation? ...

Connecting to SQL Server in Php - Extension Err

<html> <head> <title>Connecting </title> </head> <body> <?php $host = "*.*.*.*"; $username = "xxx"; $password = "xxx"; $db_name = "xxx"; $db = mssql_connect($host, $username,$password) or die("Couldn't Connect"); $selected = mssql_select_db($db_name, $db) or die("Couldn't open database"); ?>...

How do i transform rows into columns in sql server 2005

There is a question here in stackoverflow with the same title but that is not what I am looking for. I have a table like the one below Name | Count ---------------- Chery | 257 Drew | 1500 Morgon | 13 Kath | 500 Kirk | 200 Matt | 76 I need to trasform this result set into something like this Chery | Drew...

Building a connection string from a user's input in a WPF application

If we want in our WPF application to let a user make a SQL Server db connection settings through dialog window, what approach we should use in implementing this. Should we programmatically make changes to the app.config file? Or, some another approach exists? Or, maybe, in WPF there is some prebuilt tools for doing this? ...

SQL Server: How to ignore referential integrity until COMMIT?

i have a process to move rows from one database to another. Because of some circular foreign key reference chains i cannot remove the rows from the old database, nor can i insert them into the new database. Since the entire operation happens in a transaction1, i want SQL Server to ignore referential integrity failures until i call COMMI...

Is it possible to have a varchar(max) column for an ActiveRecord object type?

I really want to have a column of inifinite length for one of the properties of my ActiveRecord object of type string, but I don't know how to set it. When I set the length to -1 (the number for MAX in SQL server) I get an error. Can anyone help? EDIT: I meant to say Castle ActiveRecord. ...

alternative to ancestor-or-self ( or select all nodes in the tree with a specific child node)

I am trying to identify all the nodes in a tree that lead to a specific node. I am trying to accomplish this through either MSSQL XML (2005) or by the Microsoft.XMLDOM in ASP classic. I know the logic with XPATH but SQL Server does not support the ancestor-or-self axis and XMLDOM seems to choke on the :: notation.. The xpath that work...

SQL to skip running Create Partition Function under SQL Server Express

Our database schema is stored in a single file that is intended for production use but we also use when running functional tests on our local boxes. We drop the database and rebuild it from this file and then add a known set of test data. All was working well. Recently we added some performance improvements. The trouble came when we ...

SQL Server day datatype?

I am creating a table for an application that handles scheduling and deals with recurring events. I need to indicate which days and times an event may recur on (eg. every Monday and Wednesday at 1pm). Is there a datatype that can handle only days of the week without inputting specific dates, or will I need to create another table conta...

pick a number 1 to 10 on web page

Hi All, I want to create a web page where a registered user picks a number from 1 to 10. When a user picks a number that number is removed from the available list. Is there a way to guaranty that no 2 or more users can have the same number? Thanks, rod ...

What is wrong in this sql select statement?

Here is my select statement, SELECT TOP 1 EmpId, RemainingAdvance FROM SalaryDetails WHERE EmpId IN (SELECT Emp_Id FROM Employee WHERE Desig_Id='27') ORDER BY CreatedDate DESC When i executed SELECT Emp_Id FROM Employee WHERE Desig_Id='27' the results were Emp_Id 16 17 But when i execute my...

SQL Server 2008 SSMS connection and http connection string?

I have a SQL Server 2008 on a server which goes out on the internet through a domain name computer.example.com, I want to develop a .net app on my PC and to connect to that database through SSMS - to create tables and so on... And I still want a connection string which can be accessed from anywhere - I'm thinking at a http ...

sql server 2008 - sqlcachedependency without trigger?

I am so puzzled. I am using this open-source snippet. I have a table without any triggers in SQL Server, and yet the SQLCacheDependency is working fine. I thought you needed triggers on the table for it to work?! DateTime localFileTimeStamp = DateTime.Now; DateTime fileTimeStampInDB; ...

quering remote database using sql server ?

I have used sp_addlinkedserver to access the remote machines db now i am writing queries explicitly on database like, select * from [server\instance].database.owner.tablename Now with this, [Server\instance] : this has to be provided explicitly [database] : can we find databases on specified instance using query like ms_ForEachDB ? ...

How to add a new row to a datagrid(WPF toolkit) when click on a button in the outside of datagrid

I want to add a new row in the datagrid when click on a button in the outside of a datagrid. The datagrid is bound with SQL Server database, and it have some data at runtime I want to add new data to the database through the database I tried a lot, but it was unsuccessful Anyone reply me it will be very helpful for me... Advance thank...