Import queries

I am copying data from an Excel workbook/worksheet to a SQL Server 2005
data-mart database table (not previously existing) using the SQL Server 2005
Import/Export Wizard. I would like to do it using an import query that
recasts the data types, as opposed to copying the Excel data and letting the
Wizard set the data types. Below is a small sample of the code I'm trying t
o
use. I always get an error message (also below). What do I do to make this
work?
select
cast(REPORT_NAME as char(10)) as [Report_Name],
cast(SYSTEM as char(10)) as [System],
cast(PRIN as char(10)) as [Prin],
cast(AGENT as char(10)) as [Agent],
cast(BANK_NAME as char(50)) as [Bank_Name],
Report_Date as [Report_Date]
TITLE: SQL Server Import and Export Wizard
--
The statement could not be parsed.
ADDITIONAL INFORMATION:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005). (System.Data)You will get this error if you use a keyword or a reserved word as the table
or column name you are using.
Check it out with the list of keyowrds. You may want to try this link..
http://msdn.microsoft.com/library/d...getkeywords.asp
or try googling to get the latest list.
Hope this helps.|||No reserved words were column headings or table names. It still doesn't
work.
"Omnibuzz" wrote:

> You will get this error if you use a keyword or a reserved word as the tab
le
> or column name you are using.
> Check it out with the list of keyowrds. You may want to try this link..
> http://msdn.microsoft.com/library/d...getkeywords.asp
> or try googling to get the latest list.
> Hope this helps.|||Is all the data within the ranges specified.
if importing to char(10) does all data fit (only have 10 chars)?
"Mitch" wrote:
> No reserved words were column headings or table names. It still doesn't
> work.
> "Omnibuzz" wrote:
>|||Yes... I have been very careful with the cast statements.
"MattB" wrote:
> Is all the data within the ranges specified.
> if importing to char(10) does all data fit (only have 10 chars)?
> "Mitch" wrote:
>|||I discovered an easy solution to this problem. In the Import/Export Wizard
do not use a query to transfer the table. The wizard brings you to a point
where you can click on the EDIT button for the sheet or table selected. Thi
s
bring up a form that allows you to recast each column. It works beautifully
!
"Mitch" wrote:

> I am copying data from an Excel workbook/worksheet to a SQL Server 2005
> data-mart database table (not previously existing) using the SQL Server 20
05
> Import/Export Wizard. I would like to do it using an import query that
> recasts the data types, as opposed to copying the Excel data and letting t
he
> Wizard set the data types. Below is a small sample of the code I'm trying
to
> use. I always get an error message (also below). What do I do to make th
is
> work?
> select
> cast(REPORT_NAME as char(10)) as [Report_Name],
> cast(SYSTEM as char(10)) as [System],
> cast(PRIN as char(10)) as [Prin],
> cast(AGENT as char(10)) as [Agent],
> cast(BANK_NAME as char(50)) as [Bank_Name],
> Report_Date as [Report_Date]
> TITLE: SQL Server Import and Export Wizard
> --
> The statement could not be parsed.
> --
> ADDITIONAL INFORMATION:
> IErrorInfo.GetDescription failed with E_FAIL(0x80004005). (System.Data)
>

Import Access data to SQL

I was using DTS to import some Access data to SQL Server 2000 and came
across some queries in Access that I thought would be converted into views .
There were around 50 queries in Access and just 3 of them showed up in the
DTS Wizard and that too they turned out to be tables on SQL Server. I was
hoping that maybe these select queries may turn into view or built as stored
procedure.
Is that not the case ? If so, do I have to manually create them.
Thanks
Most likely, (IMO, it is preferred), you need to manually recreate those
queries in Access as most suitable SQL Server object, views, SPs or UDFs,
not only due to the differences of the SQL syntex, but also due to the
different functionalities Access and SQL Server provide.
"Hassan" <hassan@.hotmail.com> wrote in message
news:uu2xhi8yHHA.1212@.TK2MSFTNGP05.phx.gbl...
>I was using DTS to import some Access data to SQL Server 2000 and came
>across some queries in Access that I thought would be converted into views
>.
> There were around 50 queries in Access and just 3 of them showed up in the
> DTS Wizard and that too they turned out to be tables on SQL Server. I was
> hoping that maybe these select queries may turn into view or built as
> stored procedure.
> Is that not the case ? If so, do I have to manually create them.
> Thanks
>

Import Access data to SQL

I was using DTS to import some Access data to SQL Server 2000 and came
across some queries in Access that I thought would be converted into views .
There were around 50 queries in Access and just 3 of them showed up in the
DTS Wizard and that too they turned out to be tables on SQL Server. I was
hoping that maybe these select queries may turn into view or built as stored
procedure.
Is that not the case ? If so, do I have to manually create them.
ThanksMost likely, (IMO, it is preferred), you need to manually recreate those
queries in Access as most suitable SQL Server object, views, SPs or UDFs,
not only due to the differences of the SQL syntex, but also due to the
different functionalities Access and SQL Server provide.
"Hassan" <hassan@.hotmail.com> wrote in message
news:uu2xhi8yHHA.1212@.TK2MSFTNGP05.phx.gbl...
>I was using DTS to import some Access data to SQL Server 2000 and came
>across some queries in Access that I thought would be converted into views
>.
> There were around 50 queries in Access and just 3 of them showed up in the
> DTS Wizard and that too they turned out to be tables on SQL Server. I was
> hoping that maybe these select queries may turn into view or built as
> stored procedure.
> Is that not the case ? If so, do I have to manually create them.
> Thanks
>

Import Access data to SQL

I was using DTS to import some Access data to SQL Server 2000 and came
across some queries in Access that I thought would be converted into views .
There were around 50 queries in Access and just 3 of them showed up in the
DTS Wizard and that too they turned out to be tables on SQL Server. I was
hoping that maybe these select queries may turn into view or built as stored
procedure.
Is that not the case ? If so, do I have to manually create them.
ThanksMost likely, (IMO, it is preferred), you need to manually recreate those
queries in Access as most suitable SQL Server object, views, SPs or UDFs,
not only due to the differences of the SQL syntex, but also due to the
different functionalities Access and SQL Server provide.
"Hassan" <hassan@.hotmail.com> wrote in message
news:uu2xhi8yHHA.1212@.TK2MSFTNGP05.phx.gbl...
>I was using DTS to import some Access data to SQL Server 2000 and came
>across some queries in Access that I thought would be converted into views
>.
> There were around 50 queries in Access and just 3 of them showed up in the
> DTS Wizard and that too they turned out to be tables on SQL Server. I was
> hoping that maybe these select queries may turn into view or built as
> stored procedure.
> Is that not the case ? If so, do I have to manually create them.
> Thanks
>

Implicit queries

Implicit queries seem to increase locks. I was wondering
why?
ThanksWhat exactly do you mean by "Implicit queries"?
--
Andrew J. Kelly
SQL Server MVP
"Jay Kay" <jeelanik@.yahoo.com> wrote in message
news:08ff01c38911$5050fc80$a101280a@.phx.gbl...
> Implicit queries seem to increase locks. I was wondering
> why?
> Thanks|||Andrew,
Explicit transacitons are started with "BEGIN TRANSACTION" and end when the
transaction is either committed or rolled back, therefore the lock is held
for only that period.
Implicit Transactions
When a connection is operating in implicit transaction mode, Microsoft=AE SQL
ServerT automatically starts a new transaction after the current transaction
is committed or rolled back. You do nothing to delineate the start of a
transaction; you only commit or roll back each transaction. Implicit
transaction mode generates a continuous chain of transactions.
Thanks.
>--Original Message--
>What exactly do you mean by "Implicit queries"?
>-- >Andrew J. Kelly
>SQL Server MVP
>
>"Jay Kay" <jeelanik@.yahoo.com> wrote in message
>news:08ff01c38911$5050fc80$a101280a@.phx.gbl...
>> Implicit queries seem to increase locks. I was wondering
>> why?
>> Thanks
>
>.
>|||I know what Implicit Transactions are but you said Queries and I wasn't sure
that is what you meant.
Usually when you use Explicit trans your transactions are short and you only
have code in between the Begin and Commit that absolutely needs to be there.
What I see a lot with Implicit trans are that there is a lot of extra
statements that get "caught up " in the transaction that really don't need
to be there. Any extra statements increases the time it takes and also the
possibility of more locks simply because there is more being executed inside
the actual tran. One other thing you can look at is to see what the
isolation level is set to when the Implicit tran begins. Some clients
default to Serializable and that will definitely cause more locks.
--
Andrew J. Kelly
SQL Server MVP
"Jay Kay" <jeelanik@.yahoo.com> wrote in message
news:1f46a01c389cf$22b5c800$a601280a@.phx.gbl...
Andrew,
Explicit transacitons are started with "BEGIN
TRANSACTION" and end when
the
transaction is either committed or rolled back, therefore
the lock is
held
for only that period.
Implicit Transactions
When a connection is operating in implicit transaction
mode, Microsoft®
SQL
ServerT automatically starts a new transaction after the
current
transaction
is committed or rolled back. You do nothing to delineate
the start of a
transaction; you only commit or roll back each
transaction. Implicit
transaction mode generates a continuous chain of
transactions.
Thanks.
>--Original Message--
>What exactly do you mean by "Implicit queries"?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jay Kay" <jeelanik@.yahoo.com> wrote in message
>news:08ff01c38911$5050fc80$a101280a@.phx.gbl...
>> Implicit queries seem to increase locks. I was
wondering
>> why?
>> Thanks
>
>.
>