How to treat Database Snapshots as if they were one database?

Hi,
I am considering various methods of creating a reporting database from
our production ERP system, in SQL Server 2005, and one of the solutions
suggested by the technical documentation is to create a database mirror
and a sequence of database snapshots. These snapshots, of course, have
different names.
This would be fine for client applications that we manage, in that we
could intercept client requests and connect them to the most recent
snapshots, and clear out the old snapshots as older connections finish.
However, we use applications such as Business Objects which point to a
named database. Does anyone have any suggestions about how we can get
such an application to always connect to the most up-to-date snapshot?
Thanks for any help in advance,
Rich"Rich B" <rjback@.hotmail.com> wrote in message
news:1138093573.261952.197210@.o13g2000cwo.googlegroups.com...
> Hi,
> I am considering various methods of creating a reporting database from
> our production ERP system, in SQL Server 2005, and one of the solutions
> suggested by the technical documentation is to create a database mirror
> and a sequence of database snapshots. These snapshots, of course, have
> different names.
> This would be fine for client applications that we manage, in that we
> could intercept client requests and connect them to the most recent
> snapshots, and clear out the old snapshots as older connections finish.
> However, we use applications such as Business Objects which point to a
> named database. Does anyone have any suggestions about how we can get
> such an application to always connect to the most up-to-date snapshot?
>
You can have a single database full of synonyms or views which point to the
most current snapshot. When you have a new snapshot you need to drop and
recreate all the synonyms or views. So you would need to ensure that client
applications don't hold schema locks (Sch-S) on the target objects for long
periods of time, preventing the switch. And you should recreate the objects
in a transaction so the client always gets a consitent view of the data.
David|||Thankyou, that seems like a reasonable approach.sql

How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

how to transfer to multiple tables based on environment

How is it possible to set data to flow through development,test,and production environment?

I have created one package.Right now i am transferring data to tables in the development environment.

I want to use the same package for several diffrent distinations two based on environment.

Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..

I think Package configurations will do that for you. You can make your package 'portable' through environments by using them. Search this forum; there is a lot of info on that|||Configurations are designed to help you change the environment without alter the code in your package. You can use them to set connect strings, etc dynamically at runtime. If you right-click in an empty area of your package, the menu will contain an option for package configurations. There's good information in Books Online and this forum on how to use them.|||

Is it possible to change the connection for the lookups which i have used in the package..

|||Yes, by setting the connection string for the connection manager used in the lookup.|||sorry for not been clear ...is it possible to change connection in lookup using package configuration..|||I don't think I am understanding your question. Do you want to change the connection the lookup is using? If so, the answer is yes. If you are asking if you can use the lookup to change another connection string, the answer is no (at least not easily.) Could you describe your scenario in a little more detail?|||

sureshv wrote:

sorry for not been clear ...is it possible to change connection in lookup using package configuration..

The lookup component like the OLE DB components and other source destinations, use connection managers. The connection managers have properties. Then you can use package configurations to change those properties at run time (e.g connection strings) without having to change the package.

So, the short answer is YES.

Look in this forum and BOL for package configurations and you will get a better understanding.

|||will it cause any problem if i set the package configuration after creating the entire package..|||No, you should be able to set up configurations at any time.

how to transfer to multiple tables based on environment

How is it possible to set data to flow through development,test,and production environment?

I have created one package.Right now i am transferring data to tables in the development environment.

I want to use the same package for several diffrent distinations two based on environment.

Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..

I think Package configurations will do that for you. You can make your package 'portable' through environments by using them. Search this forum; there is a lot of info on that|||Configurations are designed to help you change the environment without alter the code in your package. You can use them to set connect strings, etc dynamically at runtime. If you right-click in an empty area of your package, the menu will contain an option for package configurations. There's good information in Books Online and this forum on how to use them.|||

Is it possible to change the connection for the lookups which i have used in the package..

|||Yes, by setting the connection string for the connection manager used in the lookup.|||sorry for not been clear ...is it possible to change connection in lookup using package configuration..|||I don't think I am understanding your question. Do you want to change the connection the lookup is using? If so, the answer is yes. If you are asking if you can use the lookup to change another connection string, the answer is no (at least not easily.) Could you describe your scenario in a little more detail?|||

sureshv wrote:

sorry for not been clear ...is it possible to change connection in lookup using package configuration..

The lookup component like the OLE DB components and other source destinations, use connection managers. The connection managers have properties. Then you can use package configurations to change those properties at run time (e.g connection strings) without having to change the package.

So, the short answer is YES.

Look in this forum and BOL for package configurations and you will get a better understanding.

|||will it cause any problem if i set the package configuration after creating the entire package..|||No, you should be able to set up configurations at any time.

how to test before putting in production

Hi all,
I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000.
The application is in production and everytime I want to do some changes, i do the changes on my
development machine then I copy the application dll on the server.
The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements.
These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.

How can I do modifications without affecting the production server and the users ?
thanks.

The simplest solution is to have a local copy of your database and test against that. You can download the MSDE from Microsoft for free.

A more complicated solution would be to create the test SPs in a different schema from dbo, and reference them that way. (I've had good luck doing that with Oracle using synonyms. Everything was referenced via synonym, and if a developer wanted to override the object, he'd create a new one with the same name as the synonym in his own schema. I'm not sure how well this would work in SQL Server.)

|||Thanks for your reply. As you said the first solution seems easier.

I still have a few questions if you don't mind:
When I download MSDE, do they have utilities to copy my whole database from SQL server to MSDE ?|||

I use enterprise manager to back up the production DB, and then restore it on MSDE.

Yes. Just change the connection string to point to the appropriate db. It you have it in your web.config, it should be easy to do.

You can do a simple copy/paste. I use SqlDiff to copy schema/procedure changes from one database to another.

|||thank you , seems good i'll try that.

How to tell what version of SQL 2005 is intalled (Enterprise or standard)

I need to make sure that the proper version of SQL Server 2005 was installed on the production server.

What do I do to find out?

Thanks in advance!

Connect to the instance using SQLCMD and run:

1> select @.@.version;
2> go

You should get output that looks like:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

The 9.00.1399.06 is the version for SQL Server 2005 RTM.

Or from SQLCMD you can run:

1> select CONVERT(char(20), SERVERPROPERTY('productversion'));
2> go

Which will output:

9.00.1399.06

Also, if you connect to the server using SQL Server Management Studio, the version is displayed in Object Explorer on the root server node. Here it's displayed as 9.00.1399.

Cheers,
Dan

|||

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

Name: Edition

Type: REG_SZ

Data: Enterprise Edition

This result supports you install SQL Server 2005 on a clean machine. Most often, you should check the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.n\Setup if you install multiple instances.

|||

In my previous posting,

"This result supports you install SQL Server 2005 on a clean machine."

should be

"This result supposes you install SQL Server 2005 ENT on a clean machine."

In addition, you can also get the version information from the registry key by checking the value of "Version".

sql