Gerar JSON em qualquer cláusula SQL

CREATE PROCEDURE [dbo].[GetJSON] (
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString = CAST(@XML AS VARCHAR(MAX))

DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)

DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'
DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'
DECLARE @StartField VARCHAR(100); SET @StartField = '<'
DECLARE @EndField VARCHAR(100); SET @EndField = '>'

SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
SET @JSON = ''
WHILE @RowStart > 0
BEGIN
	SET @RowStart = @RowStart+Len(@StartRoot)
	SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
	SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)
	SET @JSON = @JSON+'{'

	-- for each row
	SET @FieldStart = CharIndex(@StartField, @Row, 0)
	WHILE @FieldStart > 0
	BEGIN
		-- parse node key
		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@KEY+'":'

		-- parse node value
		SET @FieldStart = @FieldEnd+1
		SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
		SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@Value+'",'

		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
	END	
	IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
	SET @JSON = @JSON+'},'
	--/ for each row

	SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
END
IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
SET @JSON = '[' + @JSON + ']'
SELECT @JSON

END

exemplo de uso:
EXEC GetJSON 'SELECT * FROM dbo.Employee_TBL'
retorno do select:
[{"UserId":"7C92EB27-DD81-498E-82CE-18192C940328","FirstName":"Bill","LastName":"Gates","Age":"45","LastLogin":"2008-11-02T00:00:00"},{"UserId":"58E75687-8D0F-423A-8A76-4B1D750F62FD","FirstName":"Steve","LastName":"Job","Age":"52","LastLogin":"2008-10-17T00:00:00"}]

LISTAR TODAS Stored Procedures,Tables e Views

Hoje me aconteceu uma situação nova, preciso listar todas tabelas e sp’s na impressora… a solução mais rápida foi essa:

1.  Stored Procedures
Select name From sys.objects Where type = ‘P’

2.  Tables
Select name From sys.objects Where type = ‘U’

3.Views
Select name From sys.objects Where type = ‘V’

Mostrar o número de dias de um determinado mês

CREATE FUNCTION [dbo].[GetDaysInMonth] ( @day DATETIME )
RETURNS INT
AS
BEGIN

RETURN CASE WHEN MONTH(@day) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
 WHEN MONTH(@day) IN (4, 6, 9, 11) THEN 30
 ELSE CASE WHEN (YEAR(@day) % 4 = 0 AND --Leap Year
 YEAR(@day) % 100 != 0) OR
 (YEAR(@day) % 400 = 0)
 THEN 29
 ELSE 28
 END
 END

END
GO

Remover = tab – line feed – carriage return

REPLACE(REPLACE(REPLACE([TextField], CHAR(9), ”), CHAR(10), ”), CHAR(13), ”)

Reiniciando o valor da chave primária

No SQL Server podemos reiniciar o valor da chave primária de uma tabela com o simples comando abaixo.

DBCC CHECKIDENT (‘Produtos’, RESEED, 0)

‘Produtos’ é o nome da Tabela e o número 0 é o valor que deseja iniciar sua chave primária.

SQL Server: Busca de uma palavra com ou sem acentos

select * from tabela where campo_tabela collate Latin1_General_CI_AI = ‘DISNEYLANDIA’

A instrução “collate Latin1_General_CI_AI” realiza a busca pela palavra chave independentemente de acentos (AI) e da formatação da caixa (Case) (CI).

CI: Case Insensitive

CS: Case Sensitive

AI: Accents Insensitive

AS: Accents Sensitive

What are the main differences between Access and SQL Server?

This article will try to explain some of the differences between Access and SQL Server. It is not an exhaustive list, and in no means should be considered an ultimate authority (you can also seethis MSDN article for a more through treatment of the differences). If you have anything to add or correct, please let us know…

---

DATA TYPES

---

Here is a list of data types in each environment, and how they are different. Some datatypes from SQL Server were left out (e.g. SQL_VARIANT, TABLE).

w

Access	SQL Server	SQL Server Definition
Yes/No	BIT	(Integer: 0 or 1)
Number (Byte)	TINYINT	(Positive Integer 0 -> 255)
Number (Integer)	SMALLINT	(Signed Integer -32,768 -> 32,767)
Number (Long Integer)	INT	(Signed Integer -(2^31) -> (2^31)-1)
(no equivalent)	BIGINT	(Signed Integer -(2^63) -> (2^63)-1)
Number (Single)	REAL	(Floating precision -1.79E + 308 -> 1.79E + 308)
Number (Double)	FLOAT	(Floating precision -3.40E + 38 -> 3.40E + 38)
Currency	MONEY	(4 decimal places, -(2^63)/10000 -> ((2^63)-1)/10000)
Currency	SMALLMONEY	(4 decimal places, -214,748.3648 -> 214,748.3647)
Hyperlink	(no equivalent – use VARCHAR())
Decimal	DECIMAL	(Fixed precision -10^38 + 1 -> 10^38 – 1)
Numeric	NUMERIC	(Fixed precision -10^38 + 1 -> 10^38 – 1)
Date/Time	DATETIME	(Date+Time 1753-01-01 -> 9999-12-31, accuracy of 3.33 ms)
Date/Time	SMALLDATETIME	(Date+Time 1900-01-01 -> 2079-06-06, accuracy of one minute)
Text(n)	CHAR(n)	(Fixed-length non-Unicode string to 8,000 characters)
Text(n)	NCHAR(n)	(Fixed-length Unicode string to 4,000 characters)
Text(n)	VARCHAR(n)	(Variable-length non-Unicode string to 8,000 characters)
Text(n)	NVARCHAR(n)	(Variable-length Unicode string to 4,000 characters)
Memo	TEXT	(Variable-length non-Unicode string to 2,147,483,647 characters)
Memo	NTEXT	(Variable-length Unicode string to 1,073,741,823 characters)
OLE Object	BINARY	(Fixed-length binary data up to 8,000 characters)
OLE Object	VARBINARY	(Variable-length binary data up to 8,000 characters)
OLE Object	IMAGE	(Variable-length binary data up to 2,147,483,647 characters)
Autonumber Autoincrement	IDENTITY	(any numeric data type, with IDENTITY property)

Some notes on usage of data types:

Switching from Yes/No to BIT

In Access, you could use integers or TRUE/FALSE keywords to determine the value of the column. In SQL Server, and especially during migration, you should use integer values only. So here are some sample queries; note that the SQL Server queries will work Access as well.

– DETERMINING TRUE – Access: [...] WHERE ynColumn = TRUE [...] WHERE ynColumn = -1 – SQL Server: [...] WHERE ynColumn <> 0 —————————— – DETERMINING FALSE – Access: [...] WHERE ynColumn = FALSE [...] WHERE ynColumn = 0 – SQL Server: [...] WHERE ynColumn = 0

Switching from Currency to MONEY

You will no longer be able to use cute VBA functions like FORMAT to add dollar signs, thousand separators, and decimal places to your numbers. In fact, in Access, some of this data is actually stored along with the value. With SQL Server, this extraneous data is not stored, reducing disk space and making calculations more efficient. While you can apply this formatting in SQL Server, as explained in Article #2188, it’s messy — and better handled, IMHO, by the client application. In ASP, you can use built-in functions like FormatCurrency to apply proper formatting to your money values.

Switching from Hyperlink to VARCHAR()

Like Currency, Access uses internal formatting to make the values stored in the application clickable. This is partly because Access is a client application, and this feature makes it easier to use. However, when you’re not physically in the application, you may not want the URL to be clickable (it may just be a display value, or you may want to wrap alternate text — or an image — inside the <a href> tag). In SQL Server, use a VARCHAR column (likely 1024 or greater, depending on the need) and apply <a href> tags to it in the client application. Don’t expect the database to maintain HTML for you… this only increases storage size, and hurts performance of searches against that column.

Switching from Date/Time to DATETIME

When passing dates into Access from ASP or an application, you use pound signs (#) for surrounding dates. SQL Server, on the other hand, uses apostrophes (‘). So the following query conversion would be required:

– Access: [...] WHERE dtColumn >= #2001-11-05# – SQL Server: [...] WHERE dtColumn >= ’20011105′

MM/DD/YYYY format is strongly discouraged, because of the inevitable confusion (many such dates can look like DD/MM/YYYY to Canadians or Brits). The only safe format to use is YYYY-MM-DD in Access, and YYYYMMDD in SQL Server. See Article #2260 for more information. There is also further information on this later in this article.

In addition, Access allows you to store date and time independently. SQL Server, including SQL Server 2005 (“Yukon”), does not allow this (see Article #2206 for more info). To see if a date equals 2001-11-05 in SQL Server, you would have to convert the stored value (which includes time) to a date only. Here is how a typical query would have to change:

– Access: [...] WHERE dtColumn = #11/05/2001# – SQL Server: [...] WHERE CONVERT(CHAR(8), dtColumn, 112) = ’20011105′ – if dtColumn has an index, this will be more efficient: [...] WHERE dtColumn >= ’20011105′ AND dtColumn < ’20011106′

If you want to retrieve the current date and time, the syntax is slightly different:

– Access: SELECT Now() SELECT Date() & ” ” & Time() – SQL Server: SELECT GETDATE() SELECT CURRENT_TIMESTAMP

If you want just the time:

– Access: SELECT Time() – SQL Server: SELECT CONVERT(CHAR(8), GETDATE(), 108) SELECT LTRIM(STUFF(RIGHT(CONVERT(CHAR(19), GETDATE(), 0), 7), 6, 0, ‘ ‘))

If you want just today’s date, there are other options:

– Access: SELECT Date() – SQL Server: SELECT GETDATE() – {fn CURRENT_TIME} SELECT {fn CURDATE()} — not friendly with certain languages SELECT CONVERT(CHAR(8), GETDATE(), 112)

To get tomorrow’s date, here is how your queries would look:

– Access: SELECT DateAdd(“d”,1,date()) – SQL Server: SELECT CONVERT(CHAR(8), GETDATE()+1, 112) –or SELECT DATEADD(DAY, 1, CONVERT(CHAR(8), GETDATE(), 112))

To get the date and time 24 hours from now:

– Access: SELECT cstr(DateAdd(“d”,1,date())) & ” ” & cstr(time()) – SQL Server: SELECT DATEADD(day, 1, GETDATE())

To get the first day of the current month:

– Access: SELECT DateAdd(“d”,1-day(date()),date()) – SQL Server: SELECT CONVERT(CHAR(10),GETDATE()+1-DAY(GETDATE()),101)

To get the number of days in the current month:

– Access: SELECT DAY(DATEADD(“m”, 1, 1-DAY(date()) & date())-1) – SQL Server: SELECT DAY(DATEADD(MONTH, 1, 1-DAY(GETDATE())+GETDATE())-1)

To get the current millisecond:

– This is impossible in Access, but just for fun: SELECT “Pick a number between 1 and 1000″ – SQL Server: SELECT DATEPART(millisecond, GETDATE())

To get the current weekday:

– Access: SELECT weekdayname(weekday(date())) – SQL Server: SELECT DATENAME(WEEKDAY, GETDATE())

It would be nice if you could use the same syntax against both data sources, but alas that is not the case. Try using the following in Access:

SELECT columns FROM table WHERE dateColumn = #20030709# SELECT columns FROM table WHERE dateColumn = #2003-07-09#

The first should generate an error, the second should work fine. Now check out the problems when you use the following syntax in SQL Server:

SET DATEFORMAT MDY SELECT ISDATE(’20030713′) — 1 SELECT ISDATE(’2003-07-13′) — 1 SET DATEFORMAT DMY SELECT ISDATE(’20030713′) — 1 SELECT ISDATE(’2003-07-13′) — 0

Now, there is a way to make YYYY-MM-DD safe in SQL Server, and it involves using the canonical format for dates:

SET DATEFORMAT MDY SELECT ISDATE(‘{d 2003-07-13}’) — 1 SET DATEFORMAT DMY SELECT ISDATE(‘{d 2003-07-13}’) — 1

However, I don’t believe Access will accept this format, so we’re back to square one: writing different code for each database (I guess we were there already, since Access requires # delimiters — but it would be nice if the formats were consistent).

Like switching from Currency to Money, when you present dates in SQL Server, you lose the convenience of the FORMAT() function, which accepts multiple ways of formatting a date (e.g. ). For more information on how to overcome this change, see Article #2464 for a cheat sheet of available formatting options with CONVERT(), and Article #2460 for a roll-your-own function that mimics the FORMAT() functionality, and then some.

Switching from Autonumber to IDENTITY

Not much difference here, except for how you define the column in DDL (CREATE TABLE) statements:

– Access: CREATE TABLE tablename (id AUTOINCREMENT) – SQL Server: CREATE TABLE tablename (id INT IDENTITY)

Handling Strings

There are many changes with string handling you will have to take into account when moving from Access to SQL Server. For one, you can no longer use double-quotes (“) as string delimiters and ampersands (&) for string concatenation. So, a query to build a string would have to change as follows:

– Access: SELECT “Foo-” & barColumn FROM TABLE – SQL Server: SELECT ‘Foo-’ + barColumn FROM TABLE

(Yes, you can enable double-quote characters as string delimiters, but this requires enabling QUOTED_IDENTIFIERS at each batch, which impacts many other things and is not guaranteed to be forward compatible.)

Another change is the ability to concatenate NULL values to a string. If you do this in Access:

SELECT FirstName & ‘ ‘ & LastName FROM table

If either FirstName or LastName is NULL, you will still get the portion of the string that was not NULL. In SQL Server:

SELECT FirstName + ‘ ‘ + LastName FROM table

If any of the values is NULL, the whole expression will yield NULL (unless you change the default setting for CONCAT_NULL_YIELDS_NULL). A common workaround is to use COALESCE around each potentially NULL value:

SELECT COALESCE(FirstName, ”) + ‘ ‘ + COALESCE(LastName, ‘ ‘) FROM table

(Or, avoiding NULLs in the first place… see Article #2073.)

Built-in CHR() constants in Access change slightly in SQL Server. The CHR() function is now spelled slightly differently. So, to return a carriage return + linefeed pair:

– Access: SELECT CHR(13) & CHR(10) – SQL Server: SELECT CHAR(13) + CHAR(10)

This one is confusing for many people because the CHAR keyword doubles as a function and a datatype definition.

Another thing to note is that Access can use & or + for string concatenation. SQL Server uses & for Boolean AND, so you need to use + for all string concatenation in SQL Server. Also, keep in mind that ‘string’ + NULL = NULL, so you should always use COALESCE() on column names / values that might be NULL, in order to avoid setting the whole result to NULL.

String Functions

• Access	SQL Server	TEXT Equivalent
  CINT(), CLNG()	CAST()	CAST(SUBSTRING())
  FORMAT()	CONVERT()	CONVERT(SUBSTRING())
  INSTR()	CHARINDEX()	CHARINDEX(), PATINDEX()
  ISDATE()	ISDATE()	ISDATE(SUBSTRING())
  ISNULL()	ISNULL()	ISNULL()
  ISNUMERIC()	ISNUMERIC()	ISNUMERIC(SUBSTRING())
  LEFT()	LEFT()	SUBSTRING()
  LEN()	LEN()	DATALENGTH()
  LCASE()	LOWER()	LOWER(SUBSTRING())
  LTRIM()	LTRIM()	LTRIM(SUBSTRING())
  REPLACE()	REPLACE()	STUFF() (also see Article #2445)
  RIGHT()	RIGHT()	SUBSTRING()
  RTRIM()	RTRIM()	RTRIM(SUBSTRING())
  CSTR()	STR()	STR(SUBSTRING())
  MID()	SUBSTRING()	SUBSTRING()
  UCASE()	UPPER()	UPPER(SUBSTRING())
  StrConv()	n/a	n/a
  TRIM()	n/a	n/a

There are many VBA-based functions in Access which are used to manipulate strings. Some of these functions are still supported in SQL Server, and aside from quotes and concatenation, code will port without difficulty. Others will take a bit more work. Here is a table of the functions, and they will be followed by examples. Some functions are not supported on TEXT columns; these differences are described in Article #2061.

CINT(data) -> CAST(data AS INT)
This function converts NUMERIC data that may be stored in string format to INTEGER format for comparison and computation. Remember that SQL Server is much more strongly typed than VBA in Access, so you may find yourself using CAST a lot more than you expected.

– Access: SELECT CINT(column) – SQL Server: SELECT CAST(column AS INT)

INSTR(data, expression) -> CHARINDEX(expression, data)
This function returns an integer representing the character where the search expression is found within the data parameter. Note that the order of these parameters is reversed!

– Access: SELECT INSTR(“franky goes to hollywood”,”goes”) – SQL Server: SELECT CHARINDEX(‘goes’,'franky goes to hollywood’)

ISDATE(data)
This function returns 1 if the supplied parameter is a valid date, and 0 if it is not. Aside from delimiters, the syntax is identical.

– Access: SELECT ISDATE(#12/01/2001#) – SQL Server: SELECT ISDATE(’12/01/2001′)

ISNULL(data)
This function works a bit differently in the two products. In Access, it returns 1 if the supplied parameter is NULL, and 0 if it is not. In SQL Server, there are two parameters, and the function works more like a CASE statement. The first parameter is the data you are checking; the second is what you want returned IF the first parameter is NULL (many applications outside the database haven’t been designed to deal with NULL values very gracefully). The following example will return a 1 or 0 to Access, depending on whether ‘column’ is NULL or not; the code in SQL Server will return the column’s value if it is not NULL, and will return 1 if it is NULL. The second parameter usually matches the datatype of the column you are checking.

– Access: SELECT ISNULL(column) FROM tbl – SQL Server: SELECT ISNULL(column,1) FROM tbl

A more intuitive function to use in SQL Server is the ANSI standard COALESCE() function. Not only does it allow you to substitute a value when a NULL is found, it will allow you to step through a series of possible values, and stop at the first non-NULL.

SELECT COALESCE(NULL, DateUpdated, DateAdded, GETDATE()) FROM tbl

ISNUMERIC(data)
This function returns 1 if the supplied parameter is numeric, and 0 if it is not. The syntax is identical.

SELECT ISNUMERIC(column)

LEFT(data, n)
This function returns the leftmost n characters of data. The syntax is identical.

SELECT LEFT(column,5)

LEN(data)
This function returns the number of characters in data. The syntax is identical.

SELECT LEN(column)

LCASE(data) -> LOWER(data)
This function converts data to lower case.

– Access: SELECT LCASE(column) – SQL Server: SELECT LOWER(column)

LTRIM(data)
This function removes white space from the left of data. The syntax is identical.

SELECT LTRIM(column)

REPLACE(data, expression1, expression2)
This function scans through data, replacing all instances of expression1 with expression2.

SELECT REPLACE(column, ‘bob’, ‘frank’)

RIGHT(data, n)
This function returns the rightmost n characters of data. The syntax is identical.

SELECT RIGHT(column,8)

RTRIM(data)
This function removes white space from the right of data. The syntax is identical.

SELECT RTRIM(column)

CSTR(data) -> STR(data)
This function converts data to string format.

– Access: SELECT CSTR(column) – SQL Server: – if column is NUMERIC: SELECT STR(column) – if column is not NUMERIC: SELECT CAST(column AS VARCHAR(n))

MID(data, start, length) -> SUBSTRING(data, start, length)
This function returns ‘length’ characters, starting at ‘start’.

– Access: SELECT MID(“franky goes to hollywood”,1,6) – SQL Server: SELECT SUBSTRING(‘franky goes to hollywood’,1,6)

UCASE(data) -> UPPER(data)
This function converts data to upper case.

– Access: SELECT UCASE(column) – SQL Server: SELECT UPPER(column)

StrConv
This function converts a string into ‘proper’ case (but does not deal with names like O’Hallaran or vanDerNeuts). There is no direct equivalent for StrConv in SQL Server, but you can do it per word manually:

– Access: SELECT StrConv(“aaron bertrand”,3) – SQL Server: SELECT LEFT(UPPER(‘aaron’),1) + LOWER(RIGHT(‘aaron’,LEN(‘aaron’)-1)) + ‘ ‘ + LEFT(UPPER(‘bertrand’),1) + LOWER(RIGHT(‘bertrand’,LEN(‘bertrand’)-1))

There is a thread stored at Google dealing with proper casing an entire block of text; you could likely implement something like that in both Access and SQL Server.

TRIM(data)
This function combines both LTRIM() and LTRIM(); there is no equivalent in SQL Server. To mimic the functionality, you would combine the two functions:

– Access: SELECT TRIM(column) SELECT LTRIM(RTRIM(column)) – SQL Server: SELECT LTRIM(RTRIM(column))

String Sorting

Access and SQL Server have different priorities on string sorting. These differences revolve mostly around special characters like underscores and apostrophes. These might not change the way your application works, but you should be aware of the differences. Let’s take this fictional example (SQL Server):

CREATE TABLE names ( fname VARCHAR(10) ) INSERT names VALUES(‘bob’) INSERT names VALUES(‘_bob’) INSERT names VALUES(‘ bob’) INSERT names VALUES(‘=bob’) INSERT names VALUES(‘andy’) INSERT names VALUES(‘_andy’) INSERT names VALUES(‘ andy’) INSERT names VALUES(‘=andy’) INSERT names VALUES(”’andy’) INSERT names VALUES(”’bob’) INSERT names VALUES(‘-andy’) INSERT names VALUES(‘-bob’) INSERT names VALUES(‘andy-bob’) INSERT names VALUES(‘bob-andy’) SELECT fname FROM names ORDER BY fname DROP TABLE names

Now, insert identical data into a similar table in Access 2000, and compare the SELECT results:

SQL Server  Access 2K ———-  ——— andy        andy bob         bob ‘andy       _andy ‘bob        _bob -andy       =andy -bob        =bob =andy       andy =bob        ’andy _andy       -andy _bob        andy-bob andy        bob andy-bob    ’bob bob         -bob bob-andy    bob-andy

Notice the inconsistencies – Access (like Windows) treats underscore (_) as the highest non-alphanumeric character. Also, it ignores apostrophe (‘) and hyphen (-) in sorting. You can see the other slight differences in sorting this otherwise identical list. At least they agree on which names are first and last… if only all of our queries used TOP 1! Add on top of this that both database engines’ concepts of sort order are sensitive to changes in the underlying operating system’s regional settings. SQL Server is also variable in its server-level (and in SQL Server 2000, table- and column-level) collation options. So, depending on all of these variables, your basic queries that sort on a text/char/varchar column will potentially start working differently upon migration.

NULL Comparisons

SQL Server handles NULL comparisons differently. If you are trying to determine whether a column contains a NULL value, the following query change should be made:

– Access: [...] WHERE column <> NULL – SQL Server: [...] WHERE column IS NULL [...] WHERE column IS NOT NULL

If you set ANSI_NULLS OFF and are trying to compare two columns, they won’t equate. A column that contains a NULL will equate with an expression that yields NULL, as will two expressions that yield NULL. But two columns that contain NULL will never be considered equal, regardless of ANSI_NULLS settings or the ANSI standards. As a workaround, use the following comparison to determine that two columns are “equal” AND both contain NULL (without the extra AND condition, these two would also evaluate as equal if they both contained an empty string):

[...] WHERE ISNULL(col1,”) = ISNULL(col2,”) AND col1 IS NULL

Yes, it’s not pretty. For more information on how SQL Server handles NULLs (and why you might want to avoid them), see Article #2073.

---

OTHER SYNTAX CHANGES

---

There are possibly dozens of other slight syntax changes that may have to be made when moving from Access to SQL Server. Here are a few of the more significant ones:

WITH TIES

WITH TIES is a common usage with SELECT TOP in SQL Server. This syntax is not valid in Access (the default behavior for TOP in Access is to use WITH TIES, so in truth, there is no direct way to use “without TIES” iN Access).

FIRST(), LAST()

FIRST() and LAST() don’t have relevance in a relational database that is not based on physical ordering of a table – a table is, by definition, an unordered set of rows. If you want the “first” or “last” row in a dataset, you probably have some idea of which row should be first or last, so use an ORDER BY clause to force it. The order of a SELECT statement is not guaranteed, and could change from one execution to the next (it’s up to the optimizer, not the physical storage or what you’d like the order to be). The only way you can ensure a desired ordering of a result set is by using an ORDER BY clause; absolutely no exceptions. So, instead of using FIRST() or LAST(), when you move your query to SQL Server, you can use TOP 1 with an ORDER BY (or ORDER BY DESC) clause, or MIN()/MAX() (with or without a subquery).

IIF(expression, resultIftrue, resultIfFalse)

IIF() is a handy inline switch comparison, which returns one result if the expression is true, and another result if the expression is false. IIF() is a VBA function, and as such, is not available in SQL Server. Thankfully, there is a more powerful function in SQL Server, called CASE. It operates much like SELECT CASE in Visual Basic. Here is an example query:

– Access: SELECT alias = IIF(Column<>0, “Yes”, “No”) FROM table – SQL Server: SELECT alias = CASE WHEN Column<>0 THEN ‘Yes’ Else ‘No’ END FROM table

SQL Server’s CASE also supports multiple outcomes, for example:

SELECT alias = CASE WHEN Column=’a’ THEN ‘US’ WHEN Column=’b’ THEN ‘Canada’ ELSE ‘Foreign’ END FROM table

Switch()

Similar to IIF, Switch() can be handled in SQL Server using CASE, e.g.

– Access: SELECT Switch( On=1,’On’, On=0,’Off’ ) FROM table – SQL Server SELECT CASE WHEN On=1 THEN ‘On’ WHEN On=0 THEN ‘Off’ END FROM table – or SELECT CASE On WHEN 1 THEN ‘On’ WHEN 0 THEN ‘Off’ END FROM table

VAL()

The Val() function in Access returns the numeric portion of a string if it appears at the beginning of the string, otherwise 0, e.g.

Val(’5561T5′) = 5561 Val(‘T55615′) = 0 Val(’556165′) = 556165

To mimic this functionality in SQL Server, you will need to do a little more:

DECLARE @val VARCHAR(12) SET @val = ’5561T5′ SELECT CONVERT(INT, LEFT(@val,PATINDEX(‘%[^0-9]%’,@val+’ ‘)-1))

DISTINCTROW

SQL Server supports DISTINCT but does not support DISTINCTROW.

---

OBJECTS

---

When creating tables and other objects, keep the following limitations in mind:

• Access uses MAKE TABLE, while both platforms support CREATE TABLE;
• Access object names are limited to 64 characters;
• SQL Server 7.0+ object names are limited to 128 characters;
• SQL Server 6.5 object names were limited to 30 characters and no spaces; and,
• Stored queries in Access become Stored Procedures in SQL Server.

  For information about the number and size of objects allowed in Access and SQL Server, see Article #2345.

---

STORED QUERIES

---

Stored queries in Access are a way to store query information so that you don’t have to type out ad hoc SQL all the time (and update it throughout your interface everywhere you make a similar query). Being a non-GUI guy, the easiest way I’ve found to create a stored query in Access is to go to Queries, open “Create query in Design View”, switch to SQL View, and type in a query, such as:

PARAMETERS ProductID INTEGER; SELECT ProductName, Price FROM Products WHERE ProductID = [productID]

Be careful not to use any reserved words, like [name], as parameter names, or to give your parameters the SAME name as the column — this can easily change the meaning of the query.

Once you have the same schema within SQL Server, when moving to stored procedures, the basic difference you’ll need to know is syntax. The above stored query becomes:

CREATE PROCEDURE MyQuery @ProductID INT AS BEGIN SELECT ProductName, Price FROM Products WHERE ProductID = @productID END

You can create this stored procedure using this code through QUery Analyzer, or you can go into the Enterprise Manager GUI, open the database, open the Stored Procedures viewpane, right-click within that pane and choose New > Stored Procedure. Paste the above code (or a query that might make a bit more sense given *your* schema), click Check Syntax, and if it all works, click Apply/OK. Don’t forget to set permissions!

Now in both cases, you can call this code from ASP as follows:

<% productID = 5 set conn = CreateObject(“ADODB.Connection”) conn.open “<connection string>” set rs = conn.execute(“EXEC MyQuery ” & productID) do while not rs.eof ‘ process recordset here ‘ … rs.movenext loop %>

See Article #2201 for a quasi-tutorial on writing stored procedures.

---

FORMS

---

Yes, Access has pretty little forms that you can create easily with VBA. There is no such thing in SQL Server; you will either need to develop an application (the most rapid to put together would probably be an ASP front end), or you could use Access as a front end (employing an Access Data Project, or ADP).

---

SECURITY

---

Access is limited to security in terms of username / password on the database. It also is subject to Windows security on the file itself (as well as the folder it resides in). Typically, ASP applications must allow the anonymous Internet guest account (IUSR_<machine_Name>) to have read / write permissions on file and folder. Username / password access to the database cannot be controlled with any more granularity.

SQL Server has two authentication modes, and neither are much like Access security at all. You can use Windows Authentication, which allows you direct access to domain Users and Groups from within the interface. You can also use Mixed Mode, which allows SQL Server to maintain usernames and passwords (thereby negating the need for a domain or other Windows user/group maintenance).

Once you have determined an authentication mode, users have three different levels of access into the database: login (at the server level), user (at the database level), and object permissions within each database (for tables, views, stored procedures, etc). Just to add a layer of complexity, SQL Server makes it easy to “clone” users by defining server-wide roles, and adding users to that role. This is much like a Group in a Windows domain; in SQL Server, you can use the built-in definitions (and customize them), or create your own. Alterations to a role’s permissions affect all users that are members of that role.

Microsoft has a thorough whitepaper you should skim through before jumping into SQL Server. If you’re going to deploy your own SQL Server box (as opposed to leasing a dedicated SQL Server, or a portion of one), by all means read the SQL Server Security FAQ.

---

MORE INFORMATION

---

Article #2182 has a list of tools and tutorials that will aid in the migration process. Also be sure to read Microsoft’s migration whitepaper for some helpful info from the vendors themselves. Finally, if you’re into books, APress has a great title called From Access to SQL Server.

SQL to Select a random row from a database table

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Thanks Mark Murphy

Feel free to post other example, variations, and SQL statements for other database servers in the comments.

Zerar Campo ID

select * from [tabela]

delete  [tabela]

DBCC CHECKIDENT ([ [tabela]],RESEED, 0)

Funções matemáticas do Oracle 10g, do SQL Server 2005, do DB2 9 e do PostgreSQL 8.0.0

Função	Oracle 10g [a] [b]	SQL Server 2005 [c]	DB2 9.1 [d]	PostgreSQL 8.0.0
valor absoluto	abs(n)	abs(n)	abs(n) ou absval(n)	abs(x)
arco cosseno	acos(n)	acos(f)	acos(d)	acos(x)
arco seno	asin(n)	asin(f)	asin(d)	asin(x)
arco tangente de n	atan(n)	atan(f)	atan(d)	atan(x)
arco tangente de x/y	atan2(n, m)	atn2(f, f)	atan2(d, d)	atan2(x, y)
raiz cúbica	-	-	-	cbrt(dp)
menor inteiro não menor que o argumento	ceil(n)	ceiling(n)	ceil(n) ou ceiling(n)	ceil(dp ou numeric)
cosseno	cos(n)	cos(f)	cos(d)	cos(x)
cosseno hiperbólico	cosh(n)	-	cosh(d)	-
cotangente	-	cot(f)	cot(d)	cot(x)
radianos para graus	-	degrees(n)	degrees(d)	degrees(dp)
exponenciação	exp(n)	exp(f)	exp(d)	exp(dp ou numeric)
maior inteiro não maior que o argumento	floor(n)	floor(n)	floor(n)	floor(dp ou numeric)
logaritmo natural	ln(n)	log(f)	ln(d) ou log(d)	ln(dp ou numeric)
logaritmo, qualquer base	log(m, n)	-	-	log(b numeric, x numeric)
logaritmo, base 10	log(10, n)	log10(f)	log10(d)	log(dp ou numeric)
módulo (resto)	mod(m, n)	dividendo % divisor	mod(n, n)	mod(y, x)
constante π	-	pi()	radians(180)	pi()
potenciação	power(m, n)	power(f, y)	power(n, n)	pow(a dp, b dp) e pow(a numeric, b numeric)
graus para radianos	-	radians(n)	radians(d)	radians(dp)
número randômico	-	rand()	rand()	random()
arredondar para o inteiro mais próximo	round(n)	round(n,0)	round(n,0)	round(dp ou numeric)
arredondar para s casas decimais	round(n [,s integer])	round(n, s integer [,função])	round(n, s integer)	round(v numeric, s integer)
define a semente para as próximas chamadas a random()	-	rand(semente)	rand(semente)	setseed(dp)
sinal do argumento (-1, 0, +1)	sign(n)	sign(n)	sign(n)	sign(dp ou numeric)
seno	sin(n)	sin(f)	sin(d)	sin(x)
seno hiperbólico	sinh(n)	-	sinh(d)	-
raiz quadrada	sqrt(n)	sqrt(f)	sqrt(d)	sqrt(dp ou numeric)
tangente	tan(n)	tan(f)	tan(d)	tan(x)
tangente hiperbólica	tanh(n)	-	tanh(d)	-
trunca em direção ao zero	trunc(n)	-	trunc(n, 0)	trunc(dp ou numeric)
trunca com s casas decimais	trunc(n [,s integer])	-	trunc(n ,s integer)	trunc(v numeric, s integer)
Notas: a. Oracle 10g — As funções numéricas recebem entradas numéricas e retornam valores numéricos. A maior parte destas funções retornam valores com precisão de 38 dígitos decimais. As funções transcendentais cos, cosh, exp, ln, log, sin, sinh, sqrt, tan e tanh têm precisão de 36 dígitos decimais. As funções transcendentais acos, asin, atan e atan2 têm precisão de 30 dígitos decimais.Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 (N. do T.) b. Função transcendental — Em matemática, uma função que não pode ser expressa como uma combinação finita de operações algébricas de adição, subtração, multiplicação, divisão, elevar a uma potência ou extrair uma raiz. Os exemplos incluem as funções log x, sin x, cos x, ex e qualquer função contendo uma destas. Estas funções são expressas em termos algébricos apenas como séries infinitas. Em geral, o termo transcendental significa não-algébrico. transcendental function — Britannica Concise Encyclopedia c. SQL Server 2005 — As funções abs, ceiling, degrees, floor, power e radians retornam um valor que possui o mesmo tipo de dado da expressão numérica da entrada. As funções acos, asin, atan,cos, cot, exp, log, log10, sin, sqrt, square e tan recebem como argumento uma expressão de ponto flutuante, e retornam um valor de ponto flutuante. Todas as funções matemáticas, exceto rand, são funções determinísticas; retornam o mesmo resultado toda vez que são chamadas com o mesmo conjunto de valores de entrada. rand só é determinística quando é especificado o parâmetro semente. SQL Server 2005 Books Online — Mathematical Functions (Transact-SQL) d. DB2 9.1 — DB2 Version 9 for Linux, UNIX, and Windows — Supported functions and administrative SQL routines and views