Hi, Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher priority should be gotten first. unfortunately MS SQL doesn't allow an order by clause in the up...
Using .NET's SqlBulkCopy, what permission do I need to give to the user in SQL Server 2008? ...
I have some data in string format that represents binary data (e.g. '0x0002'). Is there some function or trick where I can convert these from literal strings to binary? That is, I want '0x0002' to become 0x0002, and SELECT CAST('0x0002' AS BINARY(20)) obviously won't do that. I did come up with some painfully slow process that involve...
What is the best practice for right-justifying a numeric in TSQL? I have to format a fixed length extract file and need to have the Numeric fields right justified. (I am using SQL Server 2005) I found this, which seems pretty straight forward. right(' '+convert(varchar(20),a.num),12) Here is the full Select statement sel...
I have several tables with more than 1000 pages and fragmentation above 30% and commonly around 90%. ---- Edit: Thanks for the comments. It boils down to this - if a table is not being used in a relational context, what are the benefits of putting a clustered index on the table? I know it can save space, since it can now be defrag...
I have a DataSet defined and designed in visual studio. Who knows of a tool to generate tables in SQL Server to match the dataset. ...
from f in CUSTOMERS where depts.Contains(f.DEPT_ID) select f.NAME depts is a list (IEnumerable<int>) of department ids this query works fine until you pass a large list (say around 3000 dept ids) .. then i get this error: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameter...
I want to install sql server 2008 express on my laptop that has 1 GB memory, but my database contains lots of binary data that I don't want spending all my RAM. I would much rather sacrifice sql performance (make it page) in favor of other applications. Is it possible to limit the memory footprint of sql server? ...
I'm designing a database schema, and I'm wondering what criteria I should use for deciding whether each column should be nullable or not. Should I only mark as NOT NULL only those columns that absolutely must be filled out for a row to make any sense at all to my application? Or should I mark all columns that I intend to never be null...
Using T-SQL I've found that I can't use 'ALTER INDEX' with the table/index values in variables without getting a syntax error. Is there some way this could be done ? I'm on SQL Server 2005. My code looks like this : DECLARE @TABLENAME VARCHAR(256) DECLARE @IDXNAME VARCHAR(256) DECLARE @SCHEMAID INT SET @TABLENAME = 'T1' SET @IDXNAME = ...
Which of these have a better effective performance when used from ASP.NET SP1: Returns 100 params SP2: Selects 100 params from SP1 into a temp table by doing a EXEC SP1 Selects 20 params from temp table ...
Hi, I am generating a report that contains the version number. The version number is stored in the DB and retrieved/incremented as part of the report generation. The only problem is, I am calling SSRS via a web service call, which returns the generated report as a byte array. Is there any way to get the version number out of this gen...
SQL Server 2008 introduces the notion of sparse columns - where only a few of a very large number of rows have a value. We want to use this concept but the major use is to create a view where the sparse column IS NULL. Not the more usual - NOT IS NULL. Microsoft say the sparse column and associated index is optimized for retrieval o...
Hi i want ot restore master database . FOr that i try to start my instance in single user mode.... But give below error: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe - m -s TST_01 2009-03-18 13:17:35.18 Server Authentication mode is WINDOWS-ONLY. 2009-03-18 13:17:35.18 Server Error: 17112, Severity: ...
Are User Defined Data Types in MS SQL Server something that a intermediate SQL user should know and use? What are pros and cons of using UDTs? ...
How to get the results (pass parameter also) of a view of a remote server from a stored procedure? The view is in a separate server from the current server where the stored procedures exist. Thanks. ...
Hello, I am having some trouble dealing with an SQL table that needs to be queried and re-formatted into another table for reporting purposes. Here's the initial table: id int, logtimestamp datetime, serialnumber varchar(255), robotid int, amountconsumed float The robotid's are only from 1 to 4. Eve...
Lets say I have the following tables: Customers Products CustomerProducts Is there a way I can do a select from the Customers and Products tables, where the values are NOT in the map table? Basically I need a matched list of Customers and Products they do NOT own. Another twist: I need to pair one customer per product. So If 5 cus...
I am using sp_send_dbmail in SQL Server 2008 to send out the results of a query. I moved the query into a proc and am trying to use the proc in the sp_send_dbmail prcedure like so: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile', @from_address = '[email protected]', @reply_to = '[email protected]', @recipients ...
Hi, I am using the following code to check if the temp table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give error saying "invalid column". Please let me know what I am doing wrong. IF OBJECT_ID('tempdb..#Results') IS NOT NULL D...