Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we call functions that assign a value to a field

Can we call functions that assign a value to a filed. For example, we use
oracle sequence to assign a value to fields. Since SQLSvr does not have
sequences, can a function be used instead to assign a unique value in BCP
tool.JP
What is the version are you using?
Can you be more specific?
How about using an IDENTITY Properies?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:59C56298-7B6D-4A63-B2F7-EE4063CBA6B4@.microsoft.com...
> Can we call functions that assign a value to a filed. For example, we use
> oracle sequence to assign a value to fields. Since SQLSvr does not have
> sequences, can a function be used instead to assign a unique value in BCP
> tool.|||Hi,
The version used by us is SQL SERVER 2005 (9.0)
Since identity property can assign value to only one field, we are looking
for an alternative as we have to more than one field where the sequnece need
s
be set.
We are using the BCP.exe utiliy for inserting the flat file data into the
table.
So at the same time we need the sequence to be generated for some of the
fields in the Format file for the table columns.
"Uri Dimant" wrote:

> JP
> What is the version are you using?
> Can you be more specific?
> How about using an IDENTITY Properies?
>
>
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:59C56298-7B6D-4A63-B2F7-EE4063CBA6B4@.microsoft.com...
>
>

Can we call a RS report from within another VB or VBA application?

Will I be able to launch a reporting services report from within a VB/VBA
application? How would I do this?
Thanks
Ranjit CharlesThree options:
1. URL integration. Embed a IE control into any app and set the url
appropriately. Works with VB6, VB.Net, etc.
2. Web services integration. Works with VB.Net, VB6 with soap toolkit (but
can be problematic and I suggest staying away from it).
3. VB.Net 2005 use the new report control that ships with VS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> wrote in message
news:0391CF5D-44EE-4710-BCC1-3F8CDC1CD5DD@.microsoft.com...
> Will I be able to launch a reporting services report from within a VB/VBA
> application? How would I do this?
> Thanks
> Ranjit Charlessql

Can SQL Server Make A call to a Windows Service?

I have an application which needs to be able to use our local SQL server
database to get data (go figure, what else would it do right?). When the
data being sought is NOT in the database I need to be able to connect to a
MySQL database which is located... oh maybe 1500km. from here.
I use a Windows Service to do some time based updates from this remote site
already. I would like to be able to have the process work like this:
SQL server does lookup.
Data does not exist.
SQL server calls Windows Service to update database.
Windows Service Updates tables and makes data available.
Can I do this?
Does it matter that the windows service is running on .Net?
The firewall rules dictate that we can get data from them, but they cannot
push data to us. Since the transfer medium is the Internet I don't want the
SQL server to talk directly to the remote server. (I want to keep that
access closed).
Thanks.Am Wed, 16 Nov 2005 11:10:44 -0500 schrieb Roger Twomey:

> I have an application which needs to be able to use our local SQL server
> database to get data (go figure, what else would it do right?). When the
> data being sought is NOT in the database I need to be able to connect to a
> MySQL database which is located... oh maybe 1500km. from here.
> I use a Windows Service to do some time based updates from this remote sit
e
> already. I would like to be able to have the process work like this:
> SQL server does lookup.
> Data does not exist.
> SQL server calls Windows Service to update database.
.. till here no problems, You can do all this

> Windows Service Updates tables and makes data available.
> Can I do this?
> Does it matter that the windows service is running on .Net?
> The firewall rules dictate that we can get data from them, but they cannot
> push data to us. Since the transfer medium is the Internet I don't want th
e
> SQL server to talk directly to the remote server. (I want to keep that
> access closed).
> Thanks.
You are still not complete safe. Because somebody could capture the data
transfered from MySQL to SQL-Server, change it and send it to SQL-Server.
The only safe way would be VPN or SSL.
bye,
Helmut|||If you're using SQL2005 you can create a .net stored proc to call the
windows service.
If you're using SQL2000 you'll need to create a COM wrapper for the .NET
call.
research into: sp_OACreate, sp_OADestroy, sp_OAMethod, sp_OAGetProperty.
etc.
Thanks,
- Becky
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:uuQXkis6FHA.4076@.tk2msftngp13.phx.gbl...
> I have an application which needs to be able to use our local SQL server
> database to get data (go figure, what else would it do right?). When the
> data being sought is NOT in the database I need to be able to connect to a
> MySQL database which is located... oh maybe 1500km. from here.
> I use a Windows Service to do some time based updates from this remote
site
> already. I would like to be able to have the process work like this:
> SQL server does lookup.
> Data does not exist.
> SQL server calls Windows Service to update database.
> Windows Service Updates tables and makes data available.
> Can I do this?
> Does it matter that the windows service is running on .Net?
> The firewall rules dictate that we can get data from them, but they cannot
> push data to us. Since the transfer medium is the Internet I don't want
the
> SQL server to talk directly to the remote server. (I want to keep that
> access closed).
> Thanks.
>|||IIS allows free https setup. Do you think that would make this person
secure?

> Am Wed, 16 Nov 2005 11:10:44 -0500 schrieb Roger Twomey:
>
> .. till here no problems, You can do all this
>
> You are still not complete safe. Because somebody could capture the data
> transfered from MySQL to SQL-Server, change it and send it to SQL-Server.
> The only safe way would be VPN or SSL.
> bye,
> Helmut
new|||I don't know if you are a C++ programmer. If you are then you'll want to
research into extended stored procedures. We use this for WinInet/Winsock
communications as well as http/ftp and many other uses. Very powerful, very
fast, very dangerous if you don't know what you are doing.

> I have an application which needs to be able to use our local SQL server
> database to get data (go figure, what else would it do right?). When the
> data being sought is NOT in the database I need to be able to connect to a
> MySQL database which is located... oh maybe 1500km. from here.
> I use a Windows Service to do some time based updates from this remote
> site already. I would like to be able to have the process work like this:
> SQL server does lookup.
> Data does not exist.
> SQL server calls Windows Service to update database.
> Windows Service Updates tables and makes data available.
> Can I do this?
> Does it matter that the windows service is running on .Net?
> The firewall rules dictate that we can get data from them, but they cannot
> push data to us. Since the transfer medium is the Internet I don't want
> the SQL server to talk directly to the remote server. (I want to keep that
> access closed).
> Thanks.
new|||We don't have IIS on our SQL servers. We try to keep each server to a single
purpose, then secure it as much as we can.
"beginthreadex" <beginthreadex@.hotmail.com> wrote in message
news:OK0wyOt6FHA.2600@.tk2msftngp13.phx.gbl...
> IIS allows free https setup. Do you think that would make this person
> secure?
>
> --
> new|||I most definately am NOT a c++ programmer. I use vb.Net primarily.
Maybe some day I will get into C++, but only if there is a need. My brain is
pretty much full already!
;)
"beginthreadex" <beginthreadex@.hotmail.com> wrote in message
news:%23TKEtPt6FHA.2600@.tk2msftngp13.phx.gbl...
>I don't know if you are a C++ programmer. If you are then you'll want to
> research into extended stored procedures. We use this for WinInet/Winsock
> communications as well as http/ftp and many other uses. Very powerful,
> very
> fast, very dangerous if you don't know what you are doing.
>
> --
> new|||Thanks for the suggestion. I think your answer sounds the most promising,
and the most re-useable. There could be many other uses for the knowlege
once I have it.
Thanks.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437b60ce$0$138$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you're using SQL2005 you can create a .net stored proc to call the
> windows service.
>
> If you're using SQL2000 you'll need to create a COM wrapper for the .NET
> call.
> research into: sp_OACreate, sp_OADestroy, sp_OAMethod,
> sp_OAGetProperty.
> etc.
>
> Thanks,
> - Becky
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:uuQXkis6FHA.4076@.tk2msftngp13.phx.gbl...
> site
> the
>

Can SQL Server 2005 using C# make a SOAP call do set operations?

We have two remote data sources that we will access via a SOAP call.
But then we need to operate on the two sets (remove dups, etc.) and,
ideally, return a SQL result set to the client app.
So questions:
* Can we make a SOAP call using C# from within SQL Server?
* Can SQL Server operate on the resulting data (an XML record)?
* Can SQL Server return the combined results as a standard results set?
Thank youHello shp.jc,

> * Can we make a SOAP call using C# from within SQL Server?
Yes.

> * Can SQL Server operate on the resulting data (an XML record)?
Yes, but you'll probably want to do that with CLR code.

> * Can SQL Server return the combined results as a standard results
> set?
Yes, but recommend loading the data from the SOAP into a temp table and then
doing that work with T-SQL. Note that you can just have the Spoc or Function
turn an XML instance and query over that with Xquery.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

can replication works for a Data Warehouse ?

Hi ,
I am trying to set replication for an exsiting Data Center (let's call it
DC) from the various databases in Server1
my problem :
1) i set up the publication with the snapshot to keep existing data in the
table(there's already data) if same table name is found , however, the
synchronization failed becoz of duplicate index/key
ques : shldn't it just ignore those duplicates ?
and even if there's new data for that table it couldn't replicate over due
to the duplicates
2) i have also tried to use this option "to delete those matching with row
filter" but i realised that those that are the same record it would be
deleted and re-replicated over but those in the destination that not in the
source table have been deleted
3) i finally tried using this option "to delete data and re-create the table
and it works but the issue here is i have many databases to be replicated
over to the data warhouse , i shldn't be forced to purposely removed a table
with data to somewhere else and then after replicate is successful then copy
that data over
could any one kindly advise
tks & rdgs
Yes replication can be used in this scenario. Snapshot replication performs
a period refresh of all of the data. It sounds like transactional
replication might be a better option for you (incremental changes).
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> Hi ,
> I am trying to set replication for an exsiting Data Center (let's call it
> DC) from the various databases in Server1
> my problem :
> 1) i set up the publication with the snapshot to keep existing data in the
> table(there's already data) if same table name is found , however, the
> synchronization failed becoz of duplicate index/key
> ques : shldn't it just ignore those duplicates ?
> and even if there's new data for that table it couldn't replicate over due
> to the duplicates
> 2) i have also tried to use this option "to delete those matching with row
> filter" but i realised that those that are the same record it would be
> deleted and re-replicated over but those in the destination that not in
> the
> source table have been deleted
> 3) i finally tried using this option "to delete data and re-create the
> table
> and it works but the issue here is i have many databases to be replicated
> over to the data warhouse , i shldn't be forced to purposely removed a
> table
> with data to somewhere else and then after replicate is successful then
> copy
> that data over
> could any one kindly advise
> tks & rdgs
|||Hi,
If Replication does work for a Data Warehouse , how shld i set it up as i
have tried the settings below but it somehow did not work for me
appreciate any advise
tks & rdgs
"Jerry Spivey" wrote:

> Yes replication can be used in this scenario. Snapshot replication performs
> a period refresh of all of the data. It sounds like transactional
> replication might be a better option for you (incremental changes).
> HTH
> Jerry
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
>
>
|||Replication can be a difficult. If I were you I would step back from the
whole data warehouse thing and implement both snapshot and transactional
replication on a test box and become familiar with the varioius offerings,
options, agents, icons and settings (BOL is pretty good at explaining
replication types). Once you have a better understand of how replication
works and what are the basic and advanced feature sets of repliction, I
think you'll be able to determine which replication type (if any - maybe DTS
would work better or BULK INSERT) would be best for your scenario.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A7FEFCA8-8244-45D7-B664-6A5656121451@.microsoft.com...[vbcol=seagreen]
> Hi,
> If Replication does work for a Data Warehouse , how shld i set it up as i
> have tried the settings below but it somehow did not work for me
> appreciate any advise
> tks & rdgs
> "Jerry Spivey" wrote:

can replication works for a Data Warehouse ?

Hi ,
I am trying to set replication for an exsiting Data Center (let's call it
DC) from the various databases in Server1
my problem :
1) i set up the publication with the snapshot to keep existing data in the
table(there's already data) if same table name is found , however, the
synchronization failed becoz of duplicate index/key
ques : shldn't it just ignore those duplicates ?
and even if there's new data for that table it couldn't replicate over due
to the duplicates
2) i have also tried to use this option "to delete those matching with row
filter" but i realised that those that are the same record it would be
deleted and re-replicated over but those in the destination that not in the
source table have been deleted
3) i finally tried using this option "to delete data and re-create the table
and it works but the issue here is i have many databases to be replicated
over to the data warhouse , i shldn't be forced to purposely removed a table
with data to somewhere else and then after replicate is successful then copy
that data over
could any one kindly advise
tks & rdgsYes replication can be used in this scenario. Snapshot replication performs
a period refresh of all of the data. It sounds like transactional
replication might be a better option for you (incremental changes).
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> Hi ,
> I am trying to set replication for an exsiting Data Center (let's call it
> DC) from the various databases in Server1
> my problem :
> 1) i set up the publication with the snapshot to keep existing data in the
> table(there's already data) if same table name is found , however, the
> synchronization failed becoz of duplicate index/key
> ques : shldn't it just ignore those duplicates ?
> and even if there's new data for that table it couldn't replicate over due
> to the duplicates
> 2) i have also tried to use this option "to delete those matching with row
> filter" but i realised that those that are the same record it would be
> deleted and re-replicated over but those in the destination that not in
> the
> source table have been deleted
> 3) i finally tried using this option "to delete data and re-create the
> table
> and it works but the issue here is i have many databases to be replicated
> over to the data warhouse , i shldn't be forced to purposely removed a
> table
> with data to somewhere else and then after replicate is successful then
> copy
> that data over
> could any one kindly advise
> tks & rdgs|||Hi,
If Replication does work for a Data Warehouse , how shld i set it up as i
have tried the settings below but it somehow did not work for me
appreciate any advise
tks & rdgs
"Jerry Spivey" wrote:
> Yes replication can be used in this scenario. Snapshot replication performs
> a period refresh of all of the data. It sounds like transactional
> replication might be a better option for you (incremental changes).
> HTH
> Jerry
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> > Hi ,
> >
> > I am trying to set replication for an exsiting Data Center (let's call it
> > DC) from the various databases in Server1
> >
> > my problem :
> >
> > 1) i set up the publication with the snapshot to keep existing data in the
> > table(there's already data) if same table name is found , however, the
> > synchronization failed becoz of duplicate index/key
> >
> > ques : shldn't it just ignore those duplicates ?
> >
> > and even if there's new data for that table it couldn't replicate over due
> > to the duplicates
> >
> > 2) i have also tried to use this option "to delete those matching with row
> > filter" but i realised that those that are the same record it would be
> > deleted and re-replicated over but those in the destination that not in
> > the
> > source table have been deleted
> >
> > 3) i finally tried using this option "to delete data and re-create the
> > table
> > and it works but the issue here is i have many databases to be replicated
> > over to the data warhouse , i shldn't be forced to purposely removed a
> > table
> > with data to somewhere else and then after replicate is successful then
> > copy
> > that data over
> >
> > could any one kindly advise
> >
> > tks & rdgs
>
>|||Replication can be a difficult. If I were you I would step back from the
whole data warehouse thing and implement both snapshot and transactional
replication on a test box and become familiar with the varioius offerings,
options, agents, icons and settings (BOL is pretty good at explaining
replication types). Once you have a better understand of how replication
works and what are the basic and advanced feature sets of repliction, I
think you'll be able to determine which replication type (if any - maybe DTS
would work better or BULK INSERT) would be best for your scenario.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A7FEFCA8-8244-45D7-B664-6A5656121451@.microsoft.com...
> Hi,
> If Replication does work for a Data Warehouse , how shld i set it up as i
> have tried the settings below but it somehow did not work for me
> appreciate any advise
> tks & rdgs
> "Jerry Spivey" wrote:
>> Yes replication can be used in this scenario. Snapshot replication
>> performs
>> a period refresh of all of the data. It sounds like transactional
>> replication might be a better option for you (incremental changes).
>> HTH
>> Jerry
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
>> > Hi ,
>> >
>> > I am trying to set replication for an exsiting Data Center (let's call
>> > it
>> > DC) from the various databases in Server1
>> >
>> > my problem :
>> >
>> > 1) i set up the publication with the snapshot to keep existing data in
>> > the
>> > table(there's already data) if same table name is found , however, the
>> > synchronization failed becoz of duplicate index/key
>> >
>> > ques : shldn't it just ignore those duplicates ?
>> >
>> > and even if there's new data for that table it couldn't replicate over
>> > due
>> > to the duplicates
>> >
>> > 2) i have also tried to use this option "to delete those matching with
>> > row
>> > filter" but i realised that those that are the same record it would be
>> > deleted and re-replicated over but those in the destination that not in
>> > the
>> > source table have been deleted
>> >
>> > 3) i finally tried using this option "to delete data and re-create the
>> > table
>> > and it works but the issue here is i have many databases to be
>> > replicated
>> > over to the data warhouse , i shldn't be forced to purposely removed a
>> > table
>> > with data to somewhere else and then after replicate is successful then
>> > copy
>> > that data over
>> >
>> > could any one kindly advise
>> >
>> > tks & rdgs
>>

can replication works for a Data Warehouse ?

Hi ,
I am trying to set replication for an exsiting Data Center (let's call it
DC) from the various databases in Server1
my problem :
1) i set up the publication with the snapshot to keep existing data in the
table(there's already data) if same table name is found , however, the
synchronization failed becoz of duplicate index/key
ques : shldn't it just ignore those duplicates ?
and even if there's new data for that table it couldn't replicate over due
to the duplicates
2) i have also tried to use this option "to delete those matching with row
filter" but i realised that those that are the same record it would be
deleted and re-replicated over but those in the destination that not in the
source table have been deleted
3) i finally tried using this option "to delete data and re-create the table
and it works but the issue here is i have many databases to be replicated
over to the data warhouse , i shldn't be forced to purposely removed a table
with data to somewhere else and then after replicate is successful then copy
that data over
could any one kindly advise
tks & rdgsYes replication can be used in this scenario. Snapshot replication performs
a period refresh of all of the data. It sounds like transactional
replication might be a better option for you (incremental changes).
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> Hi ,
> I am trying to set replication for an exsiting Data Center (let's call it
> DC) from the various databases in Server1
> my problem :
> 1) i set up the publication with the snapshot to keep existing data in the
> table(there's already data) if same table name is found , however, the
> synchronization failed becoz of duplicate index/key
> ques : shldn't it just ignore those duplicates ?
> and even if there's new data for that table it couldn't replicate over due
> to the duplicates
> 2) i have also tried to use this option "to delete those matching with row
> filter" but i realised that those that are the same record it would be
> deleted and re-replicated over but those in the destination that not in
> the
> source table have been deleted
> 3) i finally tried using this option "to delete data and re-create the
> table
> and it works but the issue here is i have many databases to be replicated
> over to the data warhouse , i shldn't be forced to purposely removed a
> table
> with data to somewhere else and then after replicate is successful then
> copy
> that data over
> could any one kindly advise
> tks & rdgs|||Hi,
If Replication does work for a Data Warehouse , how shld i set it up as i
have tried the settings below but it somehow did not work for me
appreciate any advise
tks & rdgs
"Jerry Spivey" wrote:

> Yes replication can be used in this scenario. Snapshot replication perfor
ms
> a period refresh of all of the data. It sounds like transactional
> replication might be a better option for you (incremental changes).
> HTH
> Jerry
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
>
>|||Replication can be a difficult. If I were you I would step back from the
whole data warehouse thing and implement both snapshot and transactional
replication on a test box and become familiar with the varioius offerings,
options, agents, icons and settings (BOL is pretty good at explaining
replication types). Once you have a better understand of how replication
works and what are the basic and advanced feature sets of repliction, I
think you'll be able to determine which replication type (if any - maybe DTS
would work better or BULK INSERT) would be best for your scenario.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A7FEFCA8-8244-45D7-B664-6A5656121451@.microsoft.com...[vbcol=seagreen]
> Hi,
> If Replication does work for a Data Warehouse , how shld i set it up as i
> have tried the settings below but it somehow did not work for me
> appreciate any advise
> tks & rdgs
> "Jerry Spivey" wrote:
>

Can one report call another?

I would like to create a "generic" version of a report, that is fully
parameterized, and make specialized versions of that report (with "canned"
parameters and default values) that merely calls to that generic report.
Is this possible?
--
Jim Campbell
FujiFilm eSystemsYes it is possible
use the Subreport control from the Toolbox
Regards,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD|||Linked reports might work as well.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Campbell" <JimCampbell@.discussions.microsoft.com> wrote in message
news:4616F221-1747-4439-8692-C1DD93C5A2C4@.microsoft.com...
>I would like to create a "generic" version of a report, that is fully
> parameterized, and make specialized versions of that report (with "canned"
> parameters and default values) that merely calls to that generic report.
> Is this possible?
> --
> Jim Campbell
> FujiFilm eSystems