EXEC keyword

I am in the process of importing an Oracle database into SQL Server.
Once of the tables has a field called "EXEC".
SQL Server seems to reject any queries that include that particular
field because EXEC is a keyword. For eg.
SELECT ID, EXEC from USERS
results in a syntax error near keyword EXEC.
I can't change the fieldname becuase it will require reworking of a
whole bunch of scripts.
What can I do to adjust the query?
Bijoy
You can use square brackets as delimiters:
select [EXEC] from ...
See "Delimited Identifiers" and "Using Identifiers" in Books Online for
more information.
Simon
|||Try
SELECT [ID], [EXEC] FROM Users
Regards
Steen
b_naick@.yahoo.ca wrote:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
> Bijoy
|||(b_naick@.yahoo.ca) writes:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
Rather than using [] as suggested in other posts, "" may be better.
SELECT ID, "EXEC" FROM USERS
[] is usually preferred on SQL Server. However, that is proprietary syntax,
whereas quoting with "" is ANSI-compliant, and may thus work on Oracle as
well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
sql

EXEC keyword

I am in the process of importing an Oracle database into SQL Server.
Once of the tables has a field called "EXEC".
SQL Server seems to reject any queries that include that particular
field because EXEC is a keyword. For eg.
SELECT ID, EXEC from USERS
results in a syntax error near keyword EXEC.
I can't change the fieldname becuase it will require reworking of a
whole bunch of scripts.
What can I do to adjust the query?
BijoyYou can use square brackets as delimiters:
select [EXEC] from ...
See "Delimited Identifiers" and "Using Identifiers" in Books Online for
more information.
Simon|||Try
SELECT [ID], [EXEC] FROM Users
Regards
Steen
b_naick@.yahoo.ca wrote:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
> Bijoy|||(b_naick@.yahoo.ca) writes:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
Rather than using [] as suggested in other posts, "" may be better.
SELECT ID, "EXEC" FROM USERS
[] is usually preferred on SQL Server. However, that is proprietary synt
ax,
whereas quoting with "" is ANSI-compliant, and may thus work on Oracle as
well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

EXEC keyword

I am in the process of importing an Oracle database into SQL Server.
Once of the tables has a field called "EXEC".
SQL Server seems to reject any queries that include that particular
field because EXEC is a keyword. For eg.
SELECT ID, EXEC from USERS
results in a syntax error near keyword EXEC.
I can't change the fieldname becuase it will require reworking of a
whole bunch of scripts.
What can I do to adjust the query?
BijoyYou can use square brackets as delimiters:
select [EXEC] from ...
See "Delimited Identifiers" and "Using Identifiers" in Books Online for
more information.
Simon|||Try
SELECT [ID], [EXEC] FROM Users
Regards
Steen
b_naick@.yahoo.ca wrote:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
> Bijoy|||(b_naick@.yahoo.ca) writes:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
Rather than using [] as suggested in other posts, "" may be better.
SELECT ID, "EXEC" FROM USERS
[] is usually preferred on SQL Server. However, that is proprietary syntax,
whereas quoting with "" is ANSI-compliant, and may thus work on Oracle as
well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

EXEC keyword

I am in the process of importing an Oracle database into SQL Server.
Once of the tables has a field called "EXEC".

SQL Server seems to reject any queries that include that particular
field because EXEC is a keyword. For eg.

SELECT ID, EXEC from USERS

results in a syntax error near keyword EXEC.

I can't change the fieldname becuase it will require reworking of a
whole bunch of scripts.

What can I do to adjust the query?

BijoyYou can use square brackets as delimiters:

select [EXEC] from ...

See "Delimited Identifiers" and "Using Identifiers" in Books Online for
more information.

Simon|||Try

SELECT [ID], [EXEC] FROM Users

Regards
Steen

b_naick@.yahoo.ca wrote:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?
> Bijoy|||(b_naick@.yahoo.ca) writes:
> I am in the process of importing an Oracle database into SQL Server.
> Once of the tables has a field called "EXEC".
> SQL Server seems to reject any queries that include that particular
> field because EXEC is a keyword. For eg.
> SELECT ID, EXEC from USERS
> results in a syntax error near keyword EXEC.
> I can't change the fieldname becuase it will require reworking of a
> whole bunch of scripts.
> What can I do to adjust the query?

Rather than using [] as suggested in other posts, "" may be better.

SELECT ID, "EXEC" FROM USERS

[] is usually preferred on SQL Server. However, that is proprietary syntax,
whereas quoting with "" is ANSI-compliant, and may thus work on Oracle as
well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Excluding rows on a table while importing

I'm using DTSWizard to import a table from my main database to Temp.

This is the SQL statement...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)

What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?

This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.

Thanks,

Bill

Do you have to use the DTSWizard?

If not, BULK INSERT has a FIRSTROW parameter.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley,

I'm really new at this so I'm not sure how to use BULK INSERT, is that a statement that would be used in a Query? Maybe I'm expecting to much from an import/export tool like DTSWizard to get so particular.

Thanks,

Bill

|||

It is indeed a TSQL statement. The Books Online has excellent documentation and examples.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||YOu can also use the DTS wizard with the query option of "skip rows" Normally this is ment for skipping rows that are part of the metadata like column headers etc. But this can be also used in your situation for skipping (non-)relevant data rows.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

In the DTS wizard I don't see anything relating to "skip rows", is this something that would run in the

Create Table Query?

Thanks,

Bill

|||OK, I assumed that you are using a text provider for the insert, which format is the data of or which data source are you querying ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

You mentioned "skip rows" using DTS wizard, and now I'm a bit confused... is there a feature in the DTS wizard where I can skip rows in a table when importing to another table?

Thanks,

Bill

|||

Hi Jens,

What I'm doing in this particular step is... I'm using DTS Wizard to copy a table from my Main Database to Temp. I went thru DTS Wizard and I see that if I was importing a FlatFile into SQL Server, on top of the Preview it allows you to Skip Rows, and this is what you were probably talking about.

Being that there isn't a Skip Row feature when copying a regular table from a database to Temp, but it does have the Create Table statement under Edit Mapping/Edit SQL, is there something that can be added to that statement to Skip Rows in the source table?.

Thanks,

Bill

|||Yes, that depends on the version you are using. In Sql Server 005 you can use the ROW_NUMBER() function to filter out appropiate rows. In SQL Server 2000 you would have to use another approach.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

I'm using SQL Server 2005 Express. This is what I have currently under Edit Mapping/Edit SQL...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[custom1] varchar(255),
[custom2] varchar(255),
[custom3] varchar(255),
[custom4] varchar(255),
[custom5] varchar(255),
[bounces] int
)

I'm really new at this which you can probably tell... do you think you can let me know the code I need to add to the above to exclude certain rows?

Thanks,

Bill

|||

You can use this as the source of the table (instead of using the table direct)

SELECT

*

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM SomeTable

) SubQuery

WHERE ROWCOUNTER>5000

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, thanks for responding to this. I ran your code in DTS Wizard/Edit Mapping/Edit SQL and I received the error below.

Also, I'm not sure what you meant when you said... "You can use this as the source of the table (instead of using the table direct)." Was I correct in running this in DTS Wizard?

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Incorrect syntax near 'subscriberid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

|||Well just a comma missing

SELECT

*

FROM

(

Code Snippet

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM xlaANLsubscribers

) SubQuery

WHERE ROWCOUNTER>90091

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for getting back with me Jens.

I ran your code in DTS Wizard.

This is the error I received...

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)

Messages

Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(

SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Invalid object name 'xlaANLsubscribers'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Exchanging a "?" with NULL value

Hi SQL Champs!

I have a text file source where some data are an question mark (?). Importing this to SQL serever, I want to exchange these (?) with an NULL -value.

How do I do this most easy? Do I need a another tmptable first?

Many thaks

kurlan

While importing this you can use the replace function to replace these values on the fly.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Rule!

Thanks

Exception from HRESULT: 0xC0048004

Hi, there;
I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table.

Does anybody how to resolve it?

Thanks

Qiuwei wrote:

Hi, there;
I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table.

Does anybody how to resolve it?

Thanks

its nothing to do with indexes in your tables. It is most likely referring to an attempt to reference an item in a collection within your package. It suggests that your package is corrupt.

Do you get the same error when you execute the package using dtexec.exe?

-Jamie