Pre-execute error in simple import/export

I am trying to copy data from a SQL Server 2000 DB to SQL Server 2005 DB using the import/export wizard in SQL Server Management Studio. The two databases are not identical with different table names and different columns but I thought I had set up all the right mappings and had set the 'Enable identity insert' option. When I ran the wizard it errored at the Pre-execute phase. I simplified the wizard down to one table and only as couple of varchar columns and this also errored in the same way. The error report is detailed below.

For reference the SQL2000(ent. edn) DB is on a windows 2000 server and the SQL2005(dev. edn) DB and the management studio are both on my WinXPSP2 workstation.

Could somebody explain why these errors have occured and more importantly how to rectify the problem?

Many thanks,
Michael.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Warning)

Messages

Warning 0x80047076: Data Flow Task: The output column "DateAdd" (23) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "DateChg" (26) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

... NOTE: I have removed the rest of the warnings as they were the same as above (many of them).

- Pre-execute (Error)

Messages

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - Nrs_NewsArticles" (112) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

- Executing (Success)

- Copying to [NereusV2_1].[dbo].[Nrs_NewsArticles] (Stopped)

- Post-execute (Stopped)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - Nrs_NewsArticles" (112)" wrote 0 rows.
(SQL Server Import and Export Wizard)

Michael,

this is most likely a truncation problem. Could you check the sizes of used source and destination columns?

Thanks.

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
MikeMike,
I think you can change the File Type Association in Windows - sounds
like you have give Windows a gentle nudge in the right direction.
HTH
Barry|||I tried that and it didn't work : - (|||What do you mean by "recognized by the compiler as SQL text"? SQL Server (th
e database engine)
doesn't open any files (containing source code), so it couldn't care less wh
at extensions you have
for the files on the files systems on your machines. So your problem has to
be with the tools you
are using. Perhaps you can elaborate a bit?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike" <mikepmyers@.hotmail.com> wrote in message
news:1136388956.187342.158280@.g14g2000cwa.googlegroups.com...
> Most of our files were scripted out to our VSS as PRC files. In MSSQL
> Management Studio 2005 these file extensions are not recognized by the
> compiler as SQL text. Now I could go into VSS and change all my
> extensions to SQL, but I feel like that would be a very time consuming
> task. Is there a way to have MSSQL Management Studio 2005 recognize
> this as executable SQL text?
> Thanks,
> Mike
>|||I found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.sql

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
Mike
I found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
MikeI found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
MikeI found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.

PowerPoint format possible?

Hello,

we plan to use Reporting Services in the company in the near future. However, management prefers power point reports, a format which reporting services does not support (please correct me if I am wrong here).

Is there a third party extenstion tool for RS available which can produce PPT reports?

Best regards,

Michael Fuchs

Hello,

I'm not aware of any third-party PowerPoint renderer.

Can you explain your scenario that calls for PowerPoint rendering? Any insight you can offer will help to justify the possibility of providing this functionality in the future.

Thanks,
Chris

|||

Hello,

Management presents the reports in periodic meetings, currently pdf slides are used (these pdfs don't come from RS but from a self developed program), but this is not desired by management anymore, as pdf is not optimal for a presentation. What we need is nothing too fancy, just a slide with some formatting options (i.e. different templates) and the relevant charts and data presented on the slide.

I heard that ppt output is planned anyway in the next version of reporting services and that it already is in the testing stage. Is that just a rumor or can you confirm that?

Thanks,

Michael

Possible to restrict which dbs are shown in Management Studio...

Hi,
I'm migrating a load of databases from a SQL 2000 server to SQL 2005 and was
wondering if anyone knew how to restrict the list of databases that users
see when they connect using the Management Studio to only those that they
have access to? This was done in SQL 2000 by using the following:
http://support.microsoft.com/default.aspx/kb/889696
Any help is appreciated.
Thanks - Joe.In 2005, you have meta-data security built-in. By default, a user can't even see the existence of an
object they don't have permissions to use. Database is the one exception though, but you can handle
that through the VIEW DEFINITION permission.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe Mills" <joemills84@.hotmail.com> wrote in message news:u2y1H$0BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm migrating a load of databases from a SQL 2000 server to SQL 2005 and was wondering if anyone
> knew how to restrict the list of databases that users see when they connect using the Management
> Studio to only those that they have access to? This was done in SQL 2000 by using the following:
> http://support.microsoft.com/default.aspx/kb/889696
> Any help is appreciated.
> Thanks - Joe.
>

Possible to restrict which dbs are shown in Management Studio...

Hi,
I'm migrating a load of databases from a SQL 2000 server to SQL 2005 and was
wondering if anyone knew how to restrict the list of databases that users
see when they connect using the Management Studio to only those that they
have access to? This was done in SQL 2000 by using the following:
http://support.microsoft.com/default.aspx/kb/889696
Any help is appreciated.
Thanks - Joe.In 2005, you have meta-data security built-in. By default, a user can't even
see the existence of an
object they don't have permissions to use. Database is the one exception tho
ugh, but you can handle
that through the VIEW DEFINITION permission.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe Mills" <joemills84@.hotmail.com> wrote in message news:u2y1H$0BHHA.2328@.TK2MSFTNGP02.phx
.gbl...
> Hi,
> I'm migrating a load of databases from a SQL 2000 server to SQL 2005 and w
as wondering if anyone
> knew how to restrict the list of databases that users see when they connec
t using the Management
> Studio to only those that they have access to? This was done in SQL 2000 b
y using the following:
> http://support.microsoft.com/default.aspx/kb/889696
> Any help is appreciated.
> Thanks - Joe.
>

possible bug regarding single line comments in SQL Server Management Studio

I don't know if this is a bug or something I've messed up in my settings. I am using SQL Server Management Studio with SQL 2005 service pack 1 installed, and I've been using it successfully ever since SP1 came out. All of a sudden, any time I have a query with a single line comment (i.e. using dash dash -- ), the rest of the query is ignored as if the single line comment is really a multi-line comment without the closing */

An example:

select top 100 *

from myTable

-- this is a comment

where myTableID = 1234

This would normally return exactly one row, but instead returns 100 rows ( as if the where clause did not exist). If I move the where clause above the comment, it works as expected. Any ideas?

In trying to fix this, I've uninstalled and reinstalled and even reinstalled SP1, but it still isn't fixed.

You've got me stumped. I took the code you have posted and it ran fine for me.

Can you post the exact code that you are using? Maybe we'll see that hanging character you missed or somthing.

|||

I used the exact query, merely changing the table name and ID column.

I ended up going through the process yet again (uninstalling the tools, reinstalling the tools, then installing SP1) and rebooted but still had the problem. However, after a second reboot, it starting behaving properly again. I hope my problem does not resurface.

|||We (me a colleague of mine), have withnessed this same behaviour and, atfer some deep analysis, we have concluded that this bug arises when the Transact-Sql line terminates with a CR (Carriage Return, Ascii(13)) only charaters when it should terminate with both CR (Carriage Return, Ascii(13)) followed by an LF (Line Feed, Ascii(10))

It seems that once SqlServer reachs a single-line comment identifier (--) it keeps fetching bytes untill a compounded line termination (CR followed by LF) is reached.

In the following sample, the {CR+LF} and {CR} are representations of their respective invisible ascii codes.

--This is just a normal single-line comment {CR+LF}
SELECT 'This command works fine' {CR+LF}

--This is a buggy single-line comment {CR}
SELECT 'This command does not work'{CR}
SELECT 'This command does not work either' {CR+LF}
SELECT 'This command work fine too' {CR+LF}

Moreover we have reasons to bellieve that this bug resides in Query Editor provided with SQL Server Management Studio Express Edition (at least, this was version my colleague was using when this bug arose). Anyway, although this bug must be corrected, LF should just be ignored when dealing with Sql scripts.

Running software:

SQL Server Management Studio Express Edition

Ms SQLServer 2000 (8.0.2039)

Win XP Portuguese SP2

Getting this sample you can:

- Load and execute it, using QueryAnalyzer to see the refered behaviour;
- View it, using Notepad, to see what actually happened.

possible bug regarding single line comments in SQL Server Management Studio

I don't know if this is a bug or something I've messed up in my settings. I am using SQL Server Management Studio with SQL 2005 service pack 1 installed, and I've been using it successfully ever since SP1 came out. All of a sudden, any time I have a query with a single line comment (i.e. using dash dash -- ), the rest of the query is ignored as if the single line comment is really a multi-line comment without the closing */

An example:

select top 100 *

from myTable

-- this is a comment

where myTableID = 1234

This would normally return exactly one row, but instead returns 100 rows ( as if the where clause did not exist). If I move the where clause above the comment, it works as expected. Any ideas?

In trying to fix this, I've uninstalled and reinstalled and even reinstalled SP1, but it still isn't fixed.

You've got me stumped. I took the code you have posted and it ran fine for me.

Can you post the exact code that you are using? Maybe we'll see that hanging character you missed or somthing.

|||

I used the exact query, merely changing the table name and ID column.

I ended up going through the process yet again (uninstalling the tools, reinstalling the tools, then installing SP1) and rebooted but still had the problem. However, after a second reboot, it starting behaving properly again. I hope my problem does not resurface.

|||We (me a colleague of mine), have withnessed this same behaviour and, atfer some deep analysis, we have concluded that this bug arises when the Transact-Sql line terminates with a CR (Carriage Return, Ascii(13)) only charaters when it should terminate with both CR (Carriage Return, Ascii(13)) followed by an LF (Line Feed, Ascii(10))

It seems that once SqlServer reachs a single-line comment identifier (--) it keeps fetching bytes untill a compounded line termination (CR followed by LF) is reached.

In the following sample, the {CR+LF} and {CR} are representations of their respective invisible ascii codes.

--This is just a normal single-line comment {CR+LF}
SELECT 'This command works fine' {CR+LF}

--This is a buggy single-line comment {CR}
SELECT 'This command does not work'{CR}
SELECT 'This command does not work either' {CR+LF}
SELECT 'This command work fine too' {CR+LF}

Moreover we have reasons to bellieve that this bug resides in Query Editor provided with SQL Server Management Studio Express Edition (at least, this was version my colleague was using when this bug arose). Anyway, although this bug must be corrected, LF should just be ignored when dealing with Sql scripts.

Running software:

SQL Server Management Studio Express Edition

Ms SQLServer 2000 (8.0.2039)

Win XP Portuguese SP2

Getting this sample you can:

- Load and execute it, using QueryAnalyzer to see the refered behaviour;
- View it, using Notepad, to see what actually happened.