sql-server

Good books or sites for advanced SQL queries or puzzles?

I work with SQL almost on a daily basis and want to improve my SQL skills. I am looking for online resources or books for advanced queries for real life problems. I liked Joe Celko's books and I am looking for similar resources. I am also interested in SQL puzzles but ones which solve real life business apps. I am not into brain teasers,...

How to execute stored procedure without exception thrown by CRecordset::Open?

I'm trying to execute a stored procedure on SQL Server from an MFC based C++ program. The stored procedure does get executed properly, but an exception is thrown in my program, a CDBException with a message of "Function sequence error". Tracing through the MFC source code for CRecordset::Open, the exception is thrown from MoveNext - th...

Setting the minimum column width in SSRS

Greetings. For the life of me, I can't remember how to set the minimum width on an SSRS column. When I render the report in 'Preview' mode it looks fine, but when I set it up as a subscription to go out via email it gets scrunched together. Appreciate any help. Thanks! ...

Stored Procedure To Table Variable Issue

Okay, what I'm trying to do is take the results from a stored procedure and put them into a temp table... Looks like this: DECLARE @Table TABLE( [ID] int, [CostA] real, [CostB] real, [CostC] real ) INSERT INTO @Table EXECUTE [dbo].[CostProcedure] @RootId = 123 @Rate = 20 --THEN: SELECT * FROM @Table -- Gives Me...

Very slow connection to SQL Server 2005 only if using ADO.NET with SqlClient

I have a brand new server with Windows 2008 Server 64 bit + SQL Server 2005 Standard Edition SP3. When I try to open a connection to this server from a client in the same domain (Windows XP, .Net 3.5), it takes around 20 seconds to open the connection. After opening the connection, everything is fast as usual. When using a MDAC 2.8 co...

Help with sql server query

Hi I have this table: proj - nun - X aaa - 1 - 0 aaa - 2 - 0 bbb - 3 - 0 bbb - 4 - 0 bbb - 5 - 0 bbb - 6 - 0 ccc - 7 - 0 i need to get this proj - nun - X aaa - 1 - 1 aaa - 2 - 2 bbb - 3 - 1 bbb - 4 - 2 bbb - 5 - 3 bbb - 6 - 4 ccc - 7 - 1 how I can do it with sql query ? (sq...

SQL Server How Do You Iterate Though a Cursor Loop Once When a Specific Condition Occurs

I have a SQL Server cursor. I want to skip one iteration of the loop when a specific condition occurs. Break takes you out of the cursor loop and continue does not appear to be doing anything. Is there a command that says "hey this record is no good so let go ahead and skip it and work on the next one". By the way I know cursors are e...

How to find the type, length, nullable or not given a table and a field name

I have a table that has data that maps to fields in other tables in the database. Something like this: FieldName isNeeded CUSTNAME 0 PRODQTY 1 The MYFIELD1 and MYFIELD2 maps to fields in other tables. In a SQL Script, I would like to get the field definition such as type, length (info that we get we run a sp_help ...

Trimming alphanumeric characters from a strings of varying lengths in T-SQL

I have a result set that I want to trim a 2 digit suffix from. The strings will always be of varying lengths, but the suffixes will always be two digits separated by '-'. Example: APPTR-W302-01 NRSB-8920-09 Right now I am using the following. This is a hack because the '20' parameter is arbitrary. REVERSE(SUBSTRING(REVERSE(COURSENAME...

SQL Efficiency: WHERE IN Subquery vs. JOIN then GROUP

As an example, I want to get the list of all items with certain tags applied to them. I could do either of the following: SELECT Item.ID, Item.Name FROM Item WHERE Item.ID IN ( SELECT ItemTag.ItemID FROM ItemTag WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55) Or SELECT Item.ID, Item.Name FROM Item LEFT JOIN ItemTag ON It...

.NET hosting for demo projects.

I am a Java programmer looking to learn .NET, particularly C# and F#, to improve my employability. Toward this goal I am creating a website which will have a few demo projects. My hope is to learn C#/F# and the .NET framework by creating this website and then have a finished product to self-advertise and to show potential employers. W...

Linked SQL Server database giving "inconsistent metadata" error

I am currently running a third-party software suite, which uses SQL Server as its database. I have a second instance of SQL Server running in a different location, and some apps that I am building in that instance SQL Server needs to access some data in the third-party software. So, I created an ODBC connection between the boxes, and s...

Function vs. Stored Procedure in SQL Server

Hi, I've been learning Functions and Stored Procedure for quite a while but I don't know why and when exactly I should use functions or stored procedure instead of one another. They look same to me maybe because I am kinda newbie about that. Can some one tell me why ? Thanks in advance. ...

How do I limit the acceptable values in a database column to be 1 to 5?

I have a table in a database where one of the columns should have a value from 1 to 5. How can I write this limitation into the database? Do I use a constraint? What's the best practice say about this kind of thing? I am using SQL Server 2005 ...

What SQL is required to put SQL Server 2005 in single user mode?

I would like to know how (and by extension if it's possible) to put SQL Server 2005 in single user mode using SQL statements? I found these instructions on the MSDN, but alas they require SSMS. http://msdn.microsoft.com/en-us/library/ms345598(SQL.90,loband).aspx *To set a database to single-user mode In Object Explorer, connect to...

SQL Concat field from multiple rows.

I would like to create a function that returns a concatinated string of a given field of given query. Here is what I did. And this one gives me an error. Must declare the table variable "@qry". CREATE FUNCTION dbo.testing ( @qry varchar(1000), @fld varchar(100), @separator varchar(15) = '; ' ) RETURNS varchar AS...

whitespace UNION bug

This one got me the other day. What would you expect the following to return? SELECT 'X' AS line UNION SELECT 'X ' AS line Notice the space in the second SELECT. Well apparently SQL 2000 and 2005 both return 1 result. Even though its a UNION (and not a UNION ALL). There is nothing I can see in Books on line about this. Why does it ha...

When do I need to use Begin / End Blocks and the Go keyword in SQL Server?

Can someone tell me when and where I need to use begin and end blocks in SQL Server? Also, what exactly does the Go keyword do? Thanks in advance... ...

Addressing cursors using a field.(SQL Server)

I have a table that I process using a cursor. Lets say it's structure is like this: RID | School | Order | Text Now, I filter out the other schools(so only mine is shown) and then I ORDER BY order, so that I get the text arranged how I want. Now, my problem is, the order isn't straight incrementing(though all of them are unique per scho...

Sql Query for Commissions (Decision?) Table

SQL 2008 I have a commission table that looks like this: Comm% | ProfitStartRange | ProfitEndRange 0.01 | 0.00 | 100.99 0.02 | 101 | 500.99 0.03 | 501 | 1000.99 etc... Basically I want create a query that returns the appropriate Comm% based on a value. I would like to do this inline and not in a user defined function if possible ...