Solution Center

Database Solutions for SQL Server DBAs and Developers

Problem:

Removing a parameter from a stored procedure or function is often necessary during normal database maintenance. However, safely removing a parameter requires updating all calls to the procedure or function, often making this a complex and time consuming task. Extra arguments in unrevised function and procedure calls cause argument count errors. Calls that don’t explicitly name arguments can pass arguments in the wrong order, cause data type errors, or improperly executed the function or procedure.

Solution:

ApexSQL’s Remove Parameters refactor, (available in both ApexSQL Edit and ApexSQL Refactor), makes the process of removing parameters and updating dependent objects quick, simple, and accurate. Our Remove Parameters refactor takes advantage of ApexSQL’s proprietary parser, perfected with 3 years worth of development, to find and update all execution calls referencing the modified function or procedure. With a single process you can safely remove parameters without directly scripting.

Labels: SQL Development and Coding

posted by Martin Locklear on Thursday, May 27, 2010  0 comments
 

Problem

Small typos and simple scripting errors are a common source of frustration and lost efficiency for developers. A small error can occur early in the script and not be noticed until the script is executed, and fails. Finding small errors like this in large scripts, especially if there are multiple typos, can be a huge drain on time, since it requires going through the script line by line.

Solution

ApexSQL’s Syntax Error Highlighting feature, (available in both ApexSQL Edit and our SSMS add-in, ApexSQL Refactor), provides a quick and easy way to find syntax errors in real time. By underlining syntactic and semantic errors in red, similar to spell check, ApexSQL’s Syntax Error Highlighting helps prevent errors at runtime, and increases developer efficiency.

Labels: SQL Development and Coding

posted by Martin Locklear on Thursday, May 27, 2010  0 comments
 

Integrating SQL Server with business infrastructure often requires developers to prepare T-SQL scripts for use within various client codes, (such as C#, PHP, and Perl). However, there isn’t an easy way to simply cut and paste T-SQL from a query editor and have the script refactored for the syntax of the destination language.

Solution

ApexSQL’s Copy SQL Code As refactor, (available in both ApexSQL Edit and our SSMS add-in, ApexSQL Refactor), provides a simple way to convert your script into the syntax of client languages. Simply cut and paste your script, and ApexSQL’s refactor will automatically translate the script to the appropriate syntax for the target language. In addition to the predefined languages, our Copy SQL Code As refactor allows you to create custom language templates, allowing support for any coding language.

Labels: SQL Development and Coding

posted by Martin Locklear on Thursday, May 27, 2010  0 comments
 

Problem:

Database development best practices suggest that implicitly referencing columns using SELECT * in queries should be avoided, due to potential problems associated with code readability, script robustness and query performance. SELECT * forces SQL Server to reconcile the referenced columns, creating an extra step in the query execution process that can result in as much as a 50% decrease in performance. In addition, wildcards can result in queries that return excessive data, returning unneeded columns in the result set. You can read more about this issue here, in an article from the MSDN library.

In addition to potential performance problems, SELECT * obscures the specific list of columns that have been referenced, which can result in unanticipated errors in calling code (i.e. client code) that are difficult to diagnose.

Explicitly referencing columns can avoid these performance issues, but implementing this practice by manually typing each column name is time consuming and error prone.

Solution:

ApexSQL's Expand Wildcards refactor, (available in both ApexSQL Refactor and ApexSQL Edit), takes advantage of the power of our proprietary parser to quickly and accurately replace the asterisk in SELECT * with the explicit column list, allowing developers to code both quickly and correctly.

Labels: SQL Development and Coding

posted by Martin Locklear on Thursday, May 27, 2010  0 comments
 

Problem:

Changing the name of an object, column, or parameter is a simple procedure, significantly complicated by the presence of referencing objects. A single name change can cause a cascade of broken dependencies, potentially compromising the structure and integrity of your entire database. Unless all dependent objects are updated, foreign key references to dependent columns will be broken, views and stored procedures that explicitly reference the old name will fail causing application breakage, and many other problems can be created.
SQL Server’s own dependency tracking systems (sysdepends, sp_msdependencies),are often incomplete, inaccurate, or both. You can read more about limitations of SQL Server’s dependency tracking system here, or on Microsoft’s own website. You can spend large amounts of time hunting down dependencies manually, which is at best a tedious process, and at worst an impossible one, if you are working on legacy databases created by other developers.

Solution:

ApexSQL has spent the equivalent of 3 years developing and testing our own proprietary parser that monitors dependencies in real time, quickly and accurately.Our Smart Rename refactor harness the power of this proprietary parser to easily propagate name changes throughout databases, consistently preventing broken dependencies and related errors. The Smart Rename refactor enables you to implement a name change without modifying a single line of script. This refactor is included in both ApexSQL Edit, and our SSMS add-in, ApexSQL Refactor.

Labels: SQL Development and Coding

posted by Martin Locklear on Thursday, May 27, 2010  0 comments
 

A database release management automates the process of applying SQL code updated by developers to your production database. Use the links to several videos to review how this process can look like if you use ApexSQL Diff and ApexSQL Edit.

Step 1: How to create a initial project for your database in a Source Control system using ApexSQL Diff.


The step shows an architecture used in this example and explains how to export your current database structure to a Source Control system.
Click the link to review this step.


Step 2: How to create a developer's database based on the current state of the Source Control project using ApexSQL Diff.


The step shows how developers can synchronize their local, empty databases with a database structure stored in a Source Control system.
Click the link to review this step.

Step 3: How developers can change their local databases' structure using ApexSQL Edit.


The step shows how developers can add their local databases to the previously defined Source Control project, how to change definitions for database objects and saves changes in a Source Control system.
Click the link
to review the step.

Step 4: How to update a production database structure with a Source Control system using ApexSQL Diff.


The step explains how to propagate changes saved in your Source Control system project to your database by using ApexSQL Diff and its synchronization feature.
Click the link to review the step.

Labels: Source Control Integration

posted by Anna Lesniak on Sunday, January 31, 2010  0 comments
 

As you can see from above, all of the following errors are underlined:

1) the extra “OF” beside “SET QUOTED_IDENTIFIER”
2) TABLES is misspelled (should be “TABLE”)
3) The last select statement has an extra word “TABLE”

Here are some items that will be checked by this Syntax Error Highlighter refactor:
Note! There are more syntax checking rules then mentioned here.

Syntax Errors

- Unclosed quotation mark after the character string
select name as 'alias from sys.objects
- Missing end comment mark '*/'
select * from sys.objects/*

Semantic errors – SELECT Statement Errors

- The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
- ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
- The column prefix does not match with a table name or alias name used in the query.

SELECT table1.*
FROM   sys.objects table2


- The ORDER BY position number %ld is out of range of the number of items in the select list.

SELECT   *
FROM     sys.objects
ORDER BY -1,
         100


- A COMPUTE BY item was not found in the order by list. All expressions in the compute by list must also be present in the order by list.

SELECT   employeeid,
         orderdate,
         freight
FROM     northwind.dbo.orders
ORDER BY customerid,
         orderdate
COMPUTE SUM(freight) BY employeeid;


- ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

(All columns in ORDER BY should be present in select list if SELECT DISTINCT is used.)

SELECT   DISTINCT name
FROM     sys.objects
ORDER BY object_id


- A column has been specified more than once in the order by list. Columns in the order by list must be unique.

SELECT   *
FROM     sys.objects
ORDER BY object_id,
         2

SELECT   *
FROM     sys.objects
ORDER BY object_id,
         sys.objects.object_id


- The IDENTITY function can only be used when the SELECT statement has an INTO clause.

select identity(int,1,1) as id, * from sys.objects

- ORDER BY must not be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

select object_id from sys.objects order by object_id
union
select object_id from sys.objects
order by object_id

- The correlation name '%.*ls' is specified multiple times in a FROM clause.
(The same aliases can’t be used inside single SELECT statement)

SELECT *
FROM   (SELECT object_id,
               name
        FROM   sys.objects) AS orders(customerid,nbr)
       JOIN (SELECT object_id,
                    name
             FROM   sys.objects) AS orders(customerid,nbr)
         ON orders.customerid = orders.customerid


Other semantic errors

- There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number

INSERT INTO t
           (id,
            name)
VALUES     (1,
            'name',
            'comment')


- A TOP N value may not be negative.

SELECT TOP ( -10 ) *
FROM   northwind.dbo.orders


- Subqueries are not allowed in this context. Only scalar expressions are allowed.
(Sub-queries are not allowed for some statements)

CREATE TABLE #t (
  id CHAR(10)    NULL,
  c1 TEXT CONSTRAINT df_working DEFAULT (SELECT *
   FROM   sys.objects))


- A GOTO statement references the label, but the label has not been declared.

GOTO label2
LABEL1:


- Variable should be declared before usage.

SET @a = 1


- Cannot use a BREAK statement outside the scope of a WHILE statement.

CREATE PROCEDURE P1(
                @id INT)
AS
  BREAK
  
  RETURN 2


- Cannot use a CONTINUE statement outside the scope of a WHILE statement.

CREATE PROCEDURE P1(
                @id INT)
AS
  CONTINUE
  
  RETURN 2


- Rule does not contain a variable.

CREATE RULE R1 AS 2>3;


- The definition for column '%.*ls' must include a data type.

CREATE TABLE t (
  c1 )


- Percent values must be between 0 and 100.

SELECT   TOP 150 PERCENT *
FROM     northwind.dbo.orders
ORDER BY orderid


- The number of rows in the TOP clause must be an integer.

SELECT TOP ( 0.25 ) *
FROM   sys.objects

DECLARE  @p  AS FLOAT;

SELECT @p = 10

SELECT TOP ( @p ) *
FROM   sys.objects


Other Errors

Other Errors that will be checked, but don’t have example (this list is not complete).

1. Query not allowed in WAITFOR.
2. Variables are not allowed in the statement.
3. Browse mode is invalid for a statement that assigns values to a variable.
4. The FOR UPDATE clause is invalid for statements containing set operators.
5. Batch/procedure exceeds maximum length of %d characters.
6. CREATE PROCEDURE contains no statements.
7. Case expressions may only be nested to level %d.
8. Correlation clause in a subquery not permitted.
9. Incorrect syntax for definition of the constraint.
10. Time value used with WAITFOR is not a valid value. Check date/time syntax.
11. Invalid money value.
12. The same large data placement option has been specified twice.
13. Incorrect syntax near the keyword.
14. Rule does not contain a variable.
15. Invalid expression in the TOP clause.
16. Cannot use SELECT INTO in browse mode.
17. Cannot use HOLDLOCK in browse mode.
18. The FOR BROWSE clause is no longer supported in views. Set the database compatibility level to 80 or lower for this statement to be allowed.
19. Cannot use the OUTPUT option when passing a constant to a stored procedure.
20. There are too many parameters in this statement. The maximum number is %d.
21. Cannot use the OUTPUT option in a DECLARE or CREATE FUNCTION statement.
22. Data stream is invalid for WRITETEXT statement in bulk form.
23. Data stream missing from WRITETEXT statement.
24. An invalid date or time was specified in the statement.
25. Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
26. The object or column name starting with '%.*ls' is too long. The maximum length is %d characters.
27. Cannot use an existing function name to specify a stored procedure name.
28. Mixing old and new syntax to specify cursor options is not allowed.
29. Browse mode is invalid for subqueries and derived tables.
30. Cannot set or reset the 'parseonly' option within a procedure or function.
31. Function requires at least %d argument(s).
32. The integer value is out of range.
33. Cannot use If UPDATE within this CREATE TRIGGER statement.
34. The ON clause is not valid for this statement.

Syntax Highlighting in ApexSQL Edit

It can be turned on/off by using context menu command on query editor.



While turned on, this refactor will perform syntax checks on specified check interval. When something wrong is found you can see additional error description by hovering your mouse over underlined words. You will get text tooltip that looks like this:






Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info.

Labels: SQL Development and Coding

posted by Anna Lesniak on Tuesday, January 19, 2010  0 comments