FAQ: SQL Server query errors and explanations

Frequently Asked Questions: SQL Server query errors and explanations

How can I fix bad coding within a SQL Server query? Can I modify a query to show the navigation path? How can I requery a stored procedure from ADO.NET? What is Query Analyzer's replacement in SQL Server 2005? Is there a query to return the name of the database? How can I get data from two servers using the same query? What can I do if there is a long delay when running a query? How do I query current and previous outstanding reserves? How do I use SQLMail to send query results? How is SQLCMD used as tool for querying? How do you use DISTINCT in a SQL Server query? Can I speed up a composite nonclustered index-based query?

1. How can I fix bad coding within a SQL Server query?

I am having a problem with the following SQL query.

(configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Image Amplification', 'IMAGE_AMPLIFICATION', 'false', 'Enable to display image amplification and not popup windows. Good for popup blockers.', '4', '9', 'tep_cfg_select_option(array('true', 'false'), ', now()); (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Image Amplification Factor', 'IMAGE_AMPLIFICATION_FACTOR', '2.5', 'The image amplification factor to big image size devided by small image size', '4', '10', now());

My Internet service provider tells me that there is a programming issue within my code. Can you help me fix this error?

It's a bit hard to tell from this script but my guess is that you are trying to have the value tep_cfg_select_option(array('true','false') as a string value.

If so, you'll need to make the single quotes double quotes, so instead of:

'tep_cfg_select_option(array('true','false')'

You'll need:

'tep_cfg_select_option(array(''true'',''false'')'
—Greg Low, Development Expert

Return to query errors FAQ

2. Can I modify a query to show the navigation path?

I viewed your article on the Common Table Expression that will recursively query itself. How can I modify this query to show the navigation path down the tree to each node returned in the result set, in a different format? For example, if the data is:

ID ParentId
A null
B A
C A
D B
E C

I want the result from your query to be:
ID Level Path
A 1 A
B 2 A.B
C 2 A.C
D 3 A.B.D
E 3 A.B.C

CREATE TABLE #x
(
 ID CHAR(1),  
 ParentID CHAR(1)
)

INSERT #x (ID, ParentID)
SELECT 'A', NULL
UNION ALL
SELECT 'B', 'A'
UNION ALL
SELECT 'C', 'A'
UNION ALL
SELECT 'D', 'B'
UNION ALL
SELECT 'E', 'C'

;WITH FullPath AS
(
 SELECT 
  ID, 
  1 AS Level,
  CONVERT(VARCHAR(MAX), ID) AS Path 
 FROM #x
 WHERE ParentID IS NULL

 UNION ALL

 SELECT
  x.ID,
  FP.Level + 1 AS Level,
  FP.Path + '.' + x.ID AS Path
 FROM #x AS x
 JOIN FullPath FP ON x.ParentID = FP.ID
)
SELECT * 
FROM FullPath

Return to query errors FAQ

3. How can I requery a stored procedure from ADO.NET?

Each execution of a stored procedure is unrelated to any other execution of that procedure. Requerying a procedure is then just another execution of it.
—Greg Low, Development Expert

Return to query errors FAQ

4. What is Query Analyzer's replacement in SQL Server 2005?

Both Query Analyzer and Enterprise Manager are gone from SQL Server 2005. In their place is a single tool, SQL Server Management Studio. This tool has most of the features of its predecessors, but with an upgraded UI and a lot of improved functionality. I think that most DBAs will find it to be a good upgrade.

If you'd like more information on some of the new features offered by SQL Server Management Studio, please read my article: SQL Server Management Studio client tool enhancements.
—Adam Machanic, SQL Server 2005 Expert

Return to query errors FAQ

5. Is there a query to return the name of the database?

You can use the DB_NAME() global function in either SQL Server 2000 or SQL Server 2005 to get that data. Use something like the following query:

SELECT DB_NAME() AS DatabaseName

This query will return the name of the database that the session is currently connected to. Note that in SQL Server, unlike Oracle, the "DUAL" table is unnecessary for selecting scalar values from functions or other non-table sources.
—Adam Machanic, SQL Server 2005 Expert

Return to query errors FAQ

6. How can I get data from two servers using the same query?

You simply refer to the data in the linked server using fully-qualified names ie:

SELECT Columns FROM ServerName.DatabaseName.dbo.TableName

Return to query errors FAQ

7. What can I do if there is a long delay when running a query?

Whenever I see 40 or 50 second delays, it suggests name resolution problems on the network, particularly if this happens when the database hasn't been accessed for a few minutes. The default DNS resolution normally times out after about 40 seconds and then tries to resolve the name via broadcast. After that, it's cached for a little while and seems to work fine.
—Greg Low, Development Expert

Return to query errors FAQ

8. How do I query current and previous outstanding reserves?

I need to write a query that gives both the current outstanding reserve and the prior outstanding reserve. The answer is derived from the same column only different line items. For example:

Claim #: 1, Date: 6/12/05, Seq. #: 1, OutRes $1500
Claim #: 1, Date: 10/1/05, Seq. #: 2, OutRes $2000
Claim #: 1, Date: 10/19/05, Seq. #: 3, OutRes $0

Required result:
Claim #1, Prior Reserve 2000, Current Reserve 0.

I can't seem to get the syntax right. Any thoughts on what I'm missing?

I suspect you're after:

SELECT TOP 2 OutRes,Seq FROM Claim ORDER BY Date DESC,Seq DESC

That'll return 2 rows, the first is the current reserve. The second (if there is one) will return the prior reserve.
—Greg Low, Development Expert

Return to query errors FAQ

9. How do I use SQLMail to send query results?

You will need SQL Agent. I would recommend that you integrate SQL Server with Exchange or another MAPI email system. If your email system is Exchange your task is easy. SQLMail is essentially the integration of SQL Server & Exchange. You can set it up by following the 'SQL Mail, Setting up' link in Books Online (BOL). Once this is done, you can send mail using operators you define under SQL Server Agent or you can send it as Transact-SQL steps in your DTS package using xp_sendmail. —Joe Toscano, Integration Services/Development Expert

Return to query errors FAQ

10. How is SQLCMD used as tool for querying?

SQLCMD is the SQL Server 2005's command-line query tool. It replaces the older isql and osql command-line tools, both of which are deprecated. isql is no longer shipped with SQL Server, and osql is scheduled for future removal from the product. Organizations using ISQL in SQL Server 7 or 2000 should switch to osql before upgrading to SQL Server 2005. After upgrading, try to switch to SQLCMD as soon as possible in order to avoid future problems.

SQLCMD provides a large number of scripting options that allow for a great amount of flexibility for dynamically running scripts. For instance, scripting variables can be embedded in scripts, which can be used as substitution parameters at runtime, thereby allowing generic database scripts to be written for large installations.

There is not enough space here to properly cover this tool; please consult SQL Server 2005 Books Online for a complete rundown.
—Adam Machanic, SQL Server 2005 Expert

Return to query errors FAQ

11. How do you use DISTINCT in a SQL Server query?

I'm not sure what the limitation here that you're asking about is. DISTINCT simply limits the returned rows to avoid having duplicate entries. If you are sending a query to SQL Server from VBA, you can specify the DISTINCT modifier in a query the same way you would in a SQL query tool ie: SELECT DISTINCT SomeColumns FROM SomeTable
—Greg Low, Development Expert

Return to query errors FAQ

12. Can I speed up a composite nonclustered index-based query?

Here's the situation: A composite nonclustered index is created with XYZ table with the columns ordered, seqno,del_date to improve the query below:

SELECT * FROM XYZ WHERE del_date BETWEEN @datefrom AND @dateto AND orderid=@orderid AND seqno=@seqno ORDER BY orderid, seqno,del_date

Now, after implementing the index, the query still responds very slowly. What other steps are needed to resolve the problem?

Based on this information, you have a few different options that I think should be tested thoroughly in your development and test environment by reviewing the query plans. Here are a few high level thoughts:

• Evaluate building a clustered index with the same column definition
  
• Evaluate building a clustered index based on an identity and separate non-clustered indexes for each column
  
• Evaluate building three separate non-clustered indexes for each of the columns

Return to query errors FAQ

Didn't find what you were looking for? Pose a question to anyone of our SQL Server experts. You can also browse our SQL Server Topics section for more advice.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server performance and tuning Using full-text search for symbols in SQL Server Monitor database mirroring and replication after a SQL Server upgrade How to use the SELECT statement in SQL Translating information requests into SQL SELECT statements Using DISTINCT in SQL to eliminate duplicate rows SQL SELECT statement and SELECT query samples Using the ORDER BY clause of the SELECT query in SQL How to configure Database Mail in SQL Server 2005 to send mail SQL Server stored procedures tutorial: Write, tune and get examples Virtual database storage for SQL Server: Friend or foe? SQL Server stored procedures SQL and SQL Server Tutorial and Reference Guide SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands Stored procedure to monitor long-running jobs in SQL Server 2000

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary