Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

DatabaseMetadata methods with catalog parameters now error if current database does not match.

In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.

Is this the intended behavior going forward?

In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.

ResultSet rs = conn.getDatabaseMetadata.getTables("B","dbo","%",{"TABLES" });

[junit] The database name component of the object qualifier must be the name of the current database.
[junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of
the current database.
[junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source)
[junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)

~Mike Hale

Hi Michael,

Do you have a standalone application that will reproduce this problem? If not, allow me some time to author one and investigate.

Regards,

Jaaved Mohammed

|||

I see that there is a bug filed for this issue via Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277128

We will investigate and prioritize accordingly.

|||

I am not sure you can do this. This implying that you can access tables from database B when you are connected to database A.

This would imply that your connection can be re-directed to another database. My understanding of how this works is that a connection is to a single (one and only one) SQL database.

If this is possible then I would also expect to be able to joins across databases, which I believe is not possible.

|||Michael, did MS ever tell you when this would be released? I'm having the same problem and would like to get the fix in.
|||

Hi,

Thank you for evaluating the v1.2 CTP and providing feedback.

As Jaaved said earlier, a bug was filed to address this issue. It has since been fixed in internal builds. The next public CTP of the driver, targeted for release later this summer, should contain the fix.

--David Olix [MSFT]

|||

I'm getting the same problem but directly from SQL Server 2005 (SP2)...

In Mangement Studio I'm connected to 'Database_A'

I execute the following SQL:

exec sp_columns
'ViewName'
, 'dbo'
, 'Database_B'

SQL Server returns:

Msg 15250, Level 16, State 1, Procedure sp_columns, Line 24
The database name component of the object qualifier must be the name of the current database.

What the point in providing a database parameter to choose a database if it can only be set to the current database of the connection?

My version is:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

|||

I eneded up doing the following as a work around:

exec [Database_B].dbo.sp_columns

'ViewName'

but this isn't ideal as "Database_B" is provided as a parameter to the stored procedure that's executing this code so I have to build some dynamic sql on the fly to execute the statement.

I have no problems with sp_columns_ex when using linked servers so it seems crazy to me that sp_columns doesn't work in the same manner when not linking servers (e.g. "Database_A" and "Database_B" are on the same server)

Database versioning SQL Server 2000

Where can we find a Database Version Control Tool for all the database objects, not only for stored procedures like Visual SourceSafe.I am not sure exactly what you are after but if you want to be able to capture scripts of all objects in a database into sourcesafe then try:

http://www.sqlservercentral.com/products/bwunder/archiveutility/

for a neat little freeware utility.

P.S. You may need to register on sqlservercentral.com|||There is SQL Source Control 2003 http://www.skilledsoftware.com/sqlsourcecontrol.htm and Embarcadero Change Manager. The first uses integration with MS Visual Source Safe, the second stores scripted versions locally or on the server. I never worked with any of them though.|||check out these links, may be they'll be helpful.

http://www.nigelrivett.net/DMOScriptAllDatabases.html
http://www.nigelrivett.net/SQLServerReleaseControl.htm

Database version/compare

I'm looking for something that will tell me the DDL differences between
instances of a database (dev/qa/prod/sandbox) and something to apply a
version number to it. Perhaps with SourceSafe intagration?Hello JayKon,
> I'm looking for something that will tell me the DDL differences
> between instances of a database (dev/qa/prod/sandbox) and something to
> apply a version number to it. Perhaps with SourceSafe intagration?
>
For SQL Server you should take a look at
-Visual Studio Team Edition for Database professionals (integrates in VS,
so has source control support)
- Red-Gate SQL Compare (great tool, but last version I tried (4.0) had no
source control support.
- EMS DB Compare for * (also a great tool, available for multiple database
types (mysql, mssql, oracle, postgesql, firebird, sybase)
You can also directly integrate your database project in Management Studio
with source control (not compare schema's though).
Jess|||Apex SQL diff also has a nice interface for comaparing and syncronizing. I
was having issues with VS Team Edition, but I didnt really give it much of a
chance yet.
"Jesse Houwing" wrote:
> Hello JayKon,
> > I'm looking for something that will tell me the DDL differences
> > between instances of a database (dev/qa/prod/sandbox) and something to
> > apply a version number to it. Perhaps with SourceSafe intagration?
> >
> For SQL Server you should take a look at
> -Visual Studio Team Edition for Database professionals (integrates in VS,
> so has source control support)
> - Red-Gate SQL Compare (great tool, but last version I tried (4.0) had no
> source control support.
> - EMS DB Compare for * (also a great tool, available for multiple database
> types (mysql, mssql, oracle, postgesql, firebird, sybase)
> You can also directly integrate your database project in Management Studio
> with source control (not compare schema's though).
> Jesse
>
>|||While 3rd party products are OK, I was hoping for some SQL someone had
already written. The task doesn't seem hard, just time consuming.
"JayKon" wrote:
> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?|||> While 3rd party products are OK, I was hoping for some SQL someone had
> already written. The task doesn't seem hard, just time consuming.
Yes, but why not utilize a product that doesn't constitute re-inventing the
wheel? SQL Compare is $400. The amount of time you would spend perfecting
someone else's SQL scripts to meet your requirements (and not getting any
official support while doing so), even if you are very good, would easily
exceed that on the first day. It's easy to say "there is no budget" but
man-hours have to be quantified as money as well, unless you don't value
your time.
Anyway, if you're dead-set on doing it yourself, have a look at:
http://databases.aspfaq.com/general/how-do-i-document/compare-my-sql-server-database-s.html
After a few hours, if you are frustrated, there are several 3rd party tools
that will be up and running in minutes. SQL Compare has my 100%
endorsement, and the tool from Apex has received great reviews as well. You
can always try them for 14 days to see if they do a better job than your own
scripts. However, as SQL Server continues to get more complex, my guess is
that may not be enough time to finish your own version.
--
Aaron Bertrand
SQL Server MVP|||On Aug 10, 1:16 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > While 3rd party products are OK, I was hoping for someSQLsomeone had
> > already written. The task doesn't seem hard, just time consuming.
> Yes, but why not utilize a product that doesn't constitute re-inventing the
> wheel? SQLCompareis $400. The amount of time you would spend perfecting
> someone else'sSQLscripts to meet your requirements (and not getting any
> official support while doing so), even if you are very good, would easily
> exceed that on the first day. It's easy to say "there is no budget" but
> man-hours have to be quantified as money as well, unless you don't value
> your time.
> Anyway, if you're dead-set on doing it yourself, have a look at:http://databases.aspfaq.com/general/how-do-i-document/compare-my-sql-...
> After a few hours, if you are frustrated, there are several 3rd party tools
> that will be up and running in minutes. SQLComparehas my 100%
> endorsement, and the tool from Apex has received great reviews as well. You
> can always try them for 14 days to see if they do a better job than your own
> scripts. However, asSQLServercontinues to get more complex, my guess is
> that may not be enough time to finish your own version.
> --
> Aaron BertrandSQLServerMVP
Even better, xSQL Software (http://www.xsqlsoftware.com) has a
completely free lite edition of its xSQL Object that does what you
need. And, if you want to tweak things yourself you can check out xSQL
SDK it comes with a thorough documentation and easy to follow
samples.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||www.dbconstructor.com
If you would like I can show you how to do it using the above tool.
Regards,
Rober Allen Schambach
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:80C2CA91-7FA4-4035-B9B8-59D8E8D66E65@.microsoft.com...
> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?|||Did the Red Gate SQL Compare work well for you? Do you know if it
syncronizes the changes also?
--
Thanx in advance!
Always OpenTo Suggestions
"JayKon" wrote:
> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?

Database version/compare

I'm looking for something that will tell me the DDL differences between
instances of a database (dev/qa/prod/sandbox) and something to apply a
version number to it. Perhaps with SourceSafe intagration?
Hello JayKon,

> I'm looking for something that will tell me the DDL differences
> between instances of a database (dev/qa/prod/sandbox) and something to
> apply a version number to it. Perhaps with SourceSafe intagration?
>
For SQL Server you should take a look at
-Visual Studio Team Edition for Database professionals (integrates in VS,
so has source control support)
- Red-Gate SQL Compare (great tool, but last version I tried (4.0) had no
source control support.
- EMS DB Compare for * (also a great tool, available for multiple database
types (mysql, mssql, oracle, postgesql, firebird, sybase)
You can also directly integrate your database project in Management Studio
with source control (not compare schema's though).
Jesse
|||Apex SQL diff also has a nice interface for comaparing and syncronizing. I
was having issues with VS Team Edition, but I didnt really give it much of a
chance yet.
"Jesse Houwing" wrote:

> Hello JayKon,
>
> For SQL Server you should take a look at
> -Visual Studio Team Edition for Database professionals (integrates in VS,
> so has source control support)
> - Red-Gate SQL Compare (great tool, but last version I tried (4.0) had no
> source control support.
> - EMS DB Compare for * (also a great tool, available for multiple database
> types (mysql, mssql, oracle, postgesql, firebird, sybase)
> You can also directly integrate your database project in Management Studio
> with source control (not compare schema's though).
> Jesse
>
>
|||While 3rd party products are OK, I was hoping for some SQL someone had
already written. The task doesn't seem hard, just time consuming.
"JayKon" wrote:

> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?
|||> While 3rd party products are OK, I was hoping for some SQL someone had
> already written. The task doesn't seem hard, just time consuming.
Yes, but why not utilize a product that doesn't constitute re-inventing the
wheel? SQL Compare is $400. The amount of time you would spend perfecting
someone else's SQL scripts to meet your requirements (and not getting any
official support while doing so), even if you are very good, would easily
exceed that on the first day. It's easy to say "there is no budget" but
man-hours have to be quantified as money as well, unless you don't value
your time.
Anyway, if you're dead-set on doing it yourself, have a look at:
http://databases.aspfaq.com/general/how-do-i-document/compare-my-sql-server-database-s.html
After a few hours, if you are frustrated, there are several 3rd party tools
that will be up and running in minutes. SQL Compare has my 100%
endorsement, and the tool from Apex has received great reviews as well. You
can always try them for 14 days to see if they do a better job than your own
scripts. However, as SQL Server continues to get more complex, my guess is
that may not be enough time to finish your own version.
Aaron Bertrand
SQL Server MVP
|||On Aug 10, 1:16 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Yes, but why not utilize a product that doesn't constitute re-inventing the
> wheel? SQLCompareis $400. The amount of time you would spend perfecting
> someone else'sSQLscripts to meet your requirements (and not getting any
> official support while doing so), even if you are very good, would easily
> exceed that on the first day. It's easy to say "there is no budget" but
> man-hours have to be quantified as money as well, unless you don't value
> your time.
> Anyway, if you're dead-set on doing it yourself, have a look at:http://databases.aspfaq.com/general/how-do-i-document/compare-my-sql-...
> After a few hours, if you are frustrated, there are several 3rd party tools
> that will be up and running in minutes. SQLComparehas my 100%
> endorsement, and the tool from Apex has received great reviews as well. You
> can always try them for 14 days to see if they do a better job than your own
> scripts. However, asSQLServercontinues to get more complex, my guess is
> that may not be enough time to finish your own version.
> --
> Aaron BertrandSQLServerMVP
Even better, xSQL Software (http://www.xsqlsoftware.com) has a
completely free lite edition of its xSQL Object that does what you
need. And, if you want to tweak things yourself you can check out xSQL
SDK it comes with a thorough documentation and easy to follow
samples.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com
|||www.dbconstructor.com
If you would like I can show you how to do it using the above tool.
Regards,
Rober Allen Schambach
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:80C2CA91-7FA4-4035-B9B8-59D8E8D66E65@.microsoft.com...
> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?

Database version/compare

I'm looking for something that will tell me the DDL differences between
instances of a database (dev/qa/prod/sandbox) and something to apply a
version number to it. Perhaps with SourceSafe intagration?Hello JayKon,

> I'm looking for something that will tell me the DDL differences
> between instances of a database (dev/qa/prod/sandbox) and something to
> apply a version number to it. Perhaps with SourceSafe intagration?
>
For SQL Server you should take a look at
-Visual Studio Team Edition for Database professionals (integrates in VS,
so has source control support)
- Red-Gate SQL Compare (great tool, but last version I tried (4.0) had no
source control support.
- EMS DB Compare for * (also a great tool, available for multiple database
types (mysql, mssql, oracle, postgesql, firebird, sybase)
You can also directly integrate your database project in Management Studio
with source control (not compare schema's though).
Jesse|||Apex SQL diff also has a nice interface for comaparing and syncronizing. I
was having issues with VS Team Edition, but I didnt really give it much of a
chance yet.
"Jesse Houwing" wrote:

> Hello JayKon,
>
> For SQL Server you should take a look at
> -Visual Studio Team Edition for Database professionals (integrates in VS,
> so has source control support)
> - Red-Gate SQL Compare (great tool, but last version I tried (4.0) had no
> source control support.
> - EMS DB Compare for * (also a great tool, available for multiple database
> types (mysql, mssql, oracle, postgesql, firebird, sybase)
> You can also directly integrate your database project in Management Studio
> with source control (not compare schema's though).
> Jesse
>
>|||While 3rd party products are OK, I was hoping for some SQL someone had
already written. The task doesn't seem hard, just time consuming.
"JayKon" wrote:

> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?|||> While 3rd party products are OK, I was hoping for some SQL someone had
> already written. The task doesn't seem hard, just time consuming.
Yes, but why not utilize a product that doesn't constitute re-inventing the
wheel? SQL Compare is $400. The amount of time you would spend perfecting
someone else's SQL scripts to meet your requirements (and not getting any
official support while doing so), even if you are very good, would easily
exceed that on the first day. It's easy to say "there is no budget" but
man-hours have to be quantified as money as well, unless you don't value
your time.
Anyway, if you're dead-set on doing it yourself, have a look at:
http://databases.aspfaq.com/general...r />
se-s.html
After a few hours, if you are frustrated, there are several 3rd party tools
that will be up and running in minutes. SQL Compare has my 100%
endorsement, and the tool from Apex has received great reviews as well. You
can always try them for 14 days to see if they do a better job than your own
scripts. However, as SQL Server continues to get more complex, my guess is
that may not be enough time to finish your own version.
Aaron Bertrand
SQL Server MVP|||On Aug 10, 1:16 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Yes, but why not utilize a product that doesn't constitute re-inventing th
e
> wheel? SQLCompareis $400. The amount of time you would spend perfecting
> someone else'sSQLscripts to meet your requirements (and not getting any
> official support while doing so), even if you are very good, would easily
> exceed that on the first day. It's easy to say "there is no budget" but
> man-hours have to be quantified as money as well, unless you don't value
> your time.
> Anyway, if you're dead-set on doing it yourself, have a look at:http://databases
.aspfaq.com...pare-my-sql-...
> After a few hours, if you are frustrated, there are several 3rd party tool
s
> that will be up and running in minutes. SQLComparehas my 100%
> endorsement, and the tool from Apex has received great reviews as well. Y
ou
> can always try them for 14 days to see if they do a better job than your o
wn
> scripts. However, asSQLServercontinues to get more complex, my guess is
> that may not be enough time to finish your own version.
> --
> Aaron BertrandSQLServerMVP
Even better, xSQL Software (http://www.xsqlsoftware.com) has a
completely free lite edition of its xSQL Object that does what you
need. And, if you want to tweak things yourself you can check out xSQL
SDK it comes with a thorough documentation and easy to follow
samples.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||www.dbconstructor.com
If you would like I can show you how to do it using the above tool.
Regards,
Rober Allen Schambach
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:80C2CA91-7FA4-4035-B9B8-59D8E8D66E65@.microsoft.com...
> I'm looking for something that will tell me the DDL differences between
> instances of a database (dev/qa/prod/sandbox) and something to apply a
> version number to it. Perhaps with SourceSafe intagration?

Database Version Logging

Hi i just planning to creating a database version logging, where the
idea is this application running every night give me detail about table
schema change , new sproc , alter/drop sproc and other database
schema changes.
One of difficulty that i facing right now is how to get the username
that change the database schema ( alter sproc , etc ) , do anyone have
any recommendation how i able to get this information ? ThanksWhat version of SQL Server are you using? (2000/2005)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||If you are using SQL Server 2005 you can easily accomplish this by using DDL
Triggers.
There is not easy solution for SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"kwangsa" wrote:
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||Hi i m using sql server 2000 , hmm for the "not easy solution" , any
recommendation '
I thinking of using DBCC Log but which field that showing the userid.
Thanks.
Ben Nevarez wrote:
> If you are using SQL Server 2005 you can easily accomplish this by using DDL
> Triggers.
> There is not easy solution for SQL Server 2000.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "kwangsa" wrote:
> > Hi i just planning to creating a database version logging, where the
> > idea is this application running every night give me detail about table
> > schema change , new sproc , alter/drop sproc and other database
> > schema changes.
> >
> > One of difficulty that i facing right now is how to get the username
> > that change the database schema ( alter sproc , etc ) , do anyone have
> > any recommendation how i able to get this information ? Thanks
> >
> >|||Before you spend much time trying to code a solution, look at the various
third party tools used for SarBox and HIPPA compliance logging. It may be
less expensive to get something that is made for the job and taking the time
to kludge something together.
Check out Quest, Idera, Lumigent, SQL AuditPro, and many more I'm sure.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152857175.914841.230840@.b28g2000cwb.googlegroups.com...
> Hi i m using sql server 2000 , hmm for the "not easy solution" , any
> recommendation '
> I thinking of using DBCC Log but which field that showing the userid.
> Thanks.
> Ben Nevarez wrote:
>> If you are using SQL Server 2005 you can easily accomplish this by using
>> DDL
>> Triggers.
>> There is not easy solution for SQL Server 2000.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "kwangsa" wrote:
>> > Hi i just planning to creating a database version logging, where the
>> > idea is this application running every night give me detail about table
>> > schema change , new sproc , alter/drop sproc and other database
>> > schema changes.
>> >
>> > One of difficulty that i facing right now is how to get the username
>> > that change the database schema ( alter sproc , etc ) , do anyone have
>> > any recommendation how i able to get this information ? Thanks
>> >
>> >
>|||hi,
checkout this tools for auditing
http://www.lumigent.com/Downloads
Amol Lembhe
MCDBA
"Arnie Rowland" wrote:
> What version of SQL Server are you using? (2000/2005)
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "kwangsa" <SareCrow@.gmail.com> wrote in message
> news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> > Hi i just planning to creating a database version logging, where the
> > idea is this application running every night give me detail about table
> > schema change , new sproc , alter/drop sproc and other database
> > schema changes.
> >
> > One of difficulty that i facing right now is how to get the username
> > that change the database schema ( alter sproc , etc ) , do anyone have
> > any recommendation how i able to get this information ? Thanks
> >
>
>|||Hi thx for the sugestion to using 3rd party tools but what i planning
to do is only a simple database logging , and this is just personal
project so i really interested to make a tools like that.
Do anyone know any DBCC log result mean , i mean any documentation
would be really helfpul. THx
Amol Lembhe wrote:
> hi,
> checkout this tools for auditing
> http://www.lumigent.com/Downloads
> Amol Lembhe
> MCDBA
> "Arnie Rowland" wrote:
> > What version of SQL Server are you using? (2000/2005)
> >
> > --
> > Arnie Rowland*
> > "To be successful, your heart must accompany your knowledge."
> >
> >
> >
> > "kwangsa" <SareCrow@.gmail.com> wrote in message
> > news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> > > Hi i just planning to creating a database version logging, where the
> > > idea is this application running every night give me detail about table
> > > schema change , new sproc , alter/drop sproc and other database
> > > schema changes.
> > >
> > > One of difficulty that i facing right now is how to get the username
> > > that change the database schema ( alter sproc , etc ) , do anyone have
> > > any recommendation how i able to get this information ? Thanks
> > >
> >
> >
> >|||You could have a Profiler trace running (from a second server) , restricted
to DDL activities only, and then have sprocs that harvest the information
you seek from the trace table.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152873932.402525.309830@.h48g2000cwc.googlegroups.com...
> Hi thx for the sugestion to using 3rd party tools but what i planning
> to do is only a simple database logging , and this is just personal
> project so i really interested to make a tools like that.
> Do anyone know any DBCC log result mean , i mean any documentation
> would be really helfpul. THx
>
> Amol Lembhe wrote:
>> hi,
>> checkout this tools for auditing
>> http://www.lumigent.com/Downloads
>> Amol Lembhe
>> MCDBA
>> "Arnie Rowland" wrote:
>> > What version of SQL Server are you using? (2000/2005)
>> >
>> > --
>> > Arnie Rowland*
>> > "To be successful, your heart must accompany your knowledge."
>> >
>> >
>> >
>> > "kwangsa" <SareCrow@.gmail.com> wrote in message
>> > news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
>> > > Hi i just planning to creating a database version logging, where the
>> > > idea is this application running every night give me detail about
>> > > table
>> > > schema change , new sproc , alter/drop sproc and other database
>> > > schema changes.
>> > >
>> > > One of difficulty that i facing right now is how to get the username
>> > > that change the database schema ( alter sproc , etc ) , do anyone
>> > > have
>> > > any recommendation how i able to get this information ? Thanks
>> > >
>> >
>> >
>> >
>

Database Version Logging

Hi i just planning to creating a database version logging, where the
idea is this application running every night give me detail about table
schema change , new sproc , alter/drop sproc and other database
schema changes.
One of difficulty that i facing right now is how to get the username
that change the database schema ( alter sproc , etc ) , do anyone have
any recommendation how i able to get this information ? ThanksWhat version of SQL Server are you using? (2000/2005)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||If you are using SQL Server 2005 you can easily accomplish this by using DDL
Triggers.
There is not easy solution for SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"kwangsa" wrote:

> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||Hi i m using sql server 2000 , hmm for the "not easy solution" , any
recommendation '
I thinking of using DBCC Log but which field that showing the userid.
Thanks.
Ben Nevarez wrote:[vbcol=seagreen]
> If you are using SQL Server 2005 you can easily accomplish this by using D
DL
> Triggers.
> There is not easy solution for SQL Server 2000.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "kwangsa" wrote:
>|||Before you spend much time trying to code a solution, look at the various
third party tools used for SarBox and HIPPA compliance logging. It may be
less expensive to get something that is made for the job and taking the time
to kludge something together.
Check out Quest, Idera, Lumigent, SQL AuditPro, and many more I'm sure.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152857175.914841.230840@.b28g2000cwb.googlegroups.com...
> Hi i m using sql server 2000 , hmm for the "not easy solution" , any
> recommendation '
> I thinking of using DBCC Log but which field that showing the userid.
> Thanks.
> Ben Nevarez wrote:
>|||hi,
checkout this tools for auditing
http://www.lumigent.com/Downloads
Amol Lembhe
MCDBA
"Arnie Rowland" wrote:

> What version of SQL Server are you using? (2000/2005)
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "kwangsa" <SareCrow@.gmail.com> wrote in message
> news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
>
>|||Hi thx for the sugestion to using 3rd party tools but what i planning
to do is only a simple database logging , and this is just personal
project so i really interested to make a tools like that.
Do anyone know any DBCC log result mean , i mean any documentation
would be really helfpul. THx
Amol Lembhe wrote:[vbcol=seagreen]
> hi,
> checkout this tools for auditing
> http://www.lumigent.com/Downloads
> Amol Lembhe
> MCDBA
> "Arnie Rowland" wrote:
>|||You could have a Profiler trace running (from a second server) , restricted
to DDL activities only, and then have sprocs that harvest the information
you seek from the trace table.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152873932.402525.309830@.h48g2000cwc.googlegroups.com...
> Hi thx for the sugestion to using 3rd party tools but what i planning
> to do is only a simple database logging , and this is just personal
> project so i really interested to make a tools like that.
> Do anyone know any DBCC log result mean , i mean any documentation
> would be really helfpul. THx
>
> Amol Lembhe wrote:
>

Database Version 539 vs. 611 (using SMO)

I am using SMO in my .NET application to determine the version of a database
(2000 or 2005). When I get the version of a SQL 2000 database, it returns
539. When I get the version of a SQL 2005 database, it returns 611.
My question is this. Can I safely assume that a value of 539 always means
the database is a SQL 2000 database, and can I safely assume that a value of
611 always means a SQL 2005 database?
Also, does anyone know what '539' and '611' refer to?
Thanks, Amos.Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>|||Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Database Version 539 vs. 611 (using SMO)

Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||I am using SMO in my .NET application to determine the version of a database
(2000 or 2005). When I get the version of a SQL 2000 database, it returns
539. When I get the version of a SQL 2005 database, it returns 611.
My question is this. Can I safely assume that a value of 539 always means
the database is a SQL 2000 database, and can I safely assume that a value of
611 always means a SQL 2005 database?
Also, does anyone know what '539' and '611' refer to?
Thanks, Amos.|||Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>|||Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Database version - can't install SP1

I have a SQL Server 2005 install with the 64-bit Database Server, but
I've had to install the 32-bit version of Reporting Services, since IIS
is running in 32-bit mode. Everything's green in the Configuration
Tool, but when I go to http://server/reportserver, I get the following
error:
The version of the report server database is either in a format that is
not valid, or it cannot be read. The found version is 'C.0.8.43'. The
expected version is 'C.0.8.40'. To continue, update the version of the
report server database and verify access rights.
(rsInvalidReportServerDatabase)
I think the problem is that I have SP1 installed on the 64-bit install,
but it isn't installed on the 32-bit install. However, when I run the
x86 SP1, it doesn't find that I have Reporting Services installed, so
it doesn't update it. I've run the 32-bit and the 64-bit SP1 installer
several times (with reboots!), but to no avail.
Any suggestions? I think I need the SP1 installer to find my
installation of the 32-bit Reporting Services.
Thanks,
CliffeA little more info: If I try to run the 32-bit setup from the original
install CD, it doesn't recognize that anything's installed - it walks
through the wizard from scratch.
If I run ARPWrapper.exe from \Program Files (x86)\Microsoft SQL
Server\90\Setup Bootstrap, it comes up with an error "SQL Server Setup
cannot proceed because the component you have selected has already been
uninstalled. Refer to books online..."
Any suggestions?
Thanks,
Cliffe
cliffeh wrote:
> I have a SQL Server 2005 install with the 64-bit Database Server, but
> I've had to install the 32-bit version of Reporting Services, since IIS
> is running in 32-bit mode. Everything's green in the Configuration
> Tool, but when I go to http://server/reportserver, I get the following
> error:
> The version of the report server database is either in a format that is
> not valid, or it cannot be read. The found version is 'C.0.8.43'. The
> expected version is 'C.0.8.40'. To continue, update the version of the
> report server database and verify access rights.
> (rsInvalidReportServerDatabase)
> I think the problem is that I have SP1 installed on the 64-bit install,
> but it isn't installed on the 32-bit install. However, when I run the
> x86 SP1, it doesn't find that I have Reporting Services installed, so
> it doesn't update it. I've run the 32-bit and the 64-bit SP1 installer
> several times (with reboots!), but to no avail.
> Any suggestions? I think I need the SP1 installer to find my
> installation of the 32-bit Reporting Services.
> Thanks,
> Cliffe

Database Version

Hello

if we generate a database file on a particular version of ms sql express and in time to come if there are a few more versions of ms sql express out there with the users - if we continue to package with our app a database file from an older version of sql express will that be a problem with users running a newer version of sql express ?

Also what about the converse scenario - where we ship a database file from a newer version of sql while the customer is still running on an older version.

Thanks,

Avinash

Hi,

normally you speak out only the support where you are sure that it works. It you are a userfriendly vendorf :-) you would check the database version and give a message box that the software can′t be installed or will be installed at their own risks because this version isn′t supported by you. Othwise, if you have a fire-and-forget attitude (like some software vendors) you only write the prerequisites on your box or readme.txt stating that the users *should* read that carefully or otherwise won′t get any support.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Many thanks for your response.

What I wanted to actually know is; since being able to do an auto attach on a database file and because applications in general and smart clients in particular have the ability to bring with them their own database file; surely in time to come there may be several versions of sql express out there with the users.

From your earlier email, I undertand that you are suggesting we perform some kind of a defensive check before we use the database file

But I was curious to hear of any features - upgrade/migration features that express has within it. For example there should be no problem opening a word 97 document in word 2000 but the otherway round may not be possible. But it may not be fully dysfunctional aswell. For example - may be the limitation is that (hypothetically) x, y and z features may not be rendered/rendered corectly. But the document itself should be rendered minus support for the newer features.

Although it may quite a while before the next version of express comes along, I wanted to get an understanding of what potential situations we will need to prepare for with our customers and also what measures we can take now to hopefully ease support to our customers later.

I'm not sure if I've been able to explain what I was unsure about or require clarifications on. Pl let me know if I need to clarify any of my questions.

Thanks and Regards,

Avinash

PS: It will be great if you can point me to some documentation that talks about the issues that I'm not certain about.

|||

Hi,

don′t get me wrong, in th most cases you are sure right. There is an automatical upgrade path with attaching databases, even if their service pack level differs, but I wouldn′t rely on that. Although this is a nice cool feature, we would have to go the like the one I mentiond above in Germany due to law restrictions / warranty.

As the version of SQL Server Express and 2000 / 7.0 are upgrade compatible , there sure will be an automated way to upgrade the SQL Server Express version among each other. But due to database changes on the main successor version of SQl Server they are not (AFAIK and my experience is) downgrade compatible.

What can you do ? -Testing testing testing prior to sending out your database version. Thats a way to document that you did your best to eliminate errors. What of a customers calls you and tells "Hey, I did upgrade my database from Version 9.0.2043 to your new version and this didn′t work and my database is crashed."

What about you telling then that you don′t know if that particular version is not compatible for upgrading ? So you would have to go the test afterwards, inform all customers to whom you shipped the upgrade product and extinguish the burning house at the customer site.

But on the other side, what about telling the customers that all version were tested for upgrading and had gone though a quality test at your center and this is a customer specific problem ?

I guess the latter one is more relaxing for the vendors, becasue they can proof that they took care to this scenarios.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Many thanks Jens Suessmeyer. I think I understand what you are saying.

Thanks and Regards,

Avinash

database version

Hi,
What is the difference between a personal edition and the msde edition ?
TanksPersonal is a tool for a single end user. MSDE is a component for application development and delivery, not intended to be accessed by the end user at all except through an application.

-PatP