I am a VB/.NET/SQL developer that has been tasked with restructuring how our
field offices return data to the central office so some of this "advanced"
SQL Server functionality - like replication - is relatively new to me... and
I need your help, please.
The scenario:
We have installations of our MSDE-powered application installed at over 300
geographically-dispersed remote locations. The server at these remote
locations is on its own internal mini-LAN and not connected to the Internet
full-time but (in most cases, anyway) has the ability to "phone home" nightly
via dial-up. In some cases where dial-up is not an option, our technicians
will occassionally (approx. every 2 weeks) visit the site and retrieve data
using a custom applet to extract a subset of the full database that can then
be sent to the central office from a different location (i.e. the tech's home
or branch office). Now, due to increased reporting requirements, we want to
have a copy of all of the data in the database instead of just a subset.
Hence, the new project...
The variables (distilled):
* MSDE (2000) at the 300+ remote sites
* SQL Server 2000 at the central office
* Central office should, as much as feasible, have a full mirror of the
remote site
* After 8 months of running our application, the MSDE databases are 10 to 20
MB each and will only get larger.
* Remote sites are offline during business hours.
* Data may be uploaded automatically via an on-demand dial-up Internet
connection or manually via copy to external media and upload from a third
location
* As much as possible, the data should be secured as it travels across the
wire. A VPN solution is being reviewed but, as of right now, is not
guaranteed.
The gist of it:
After a solid 10 or so days of research, experimentation and prototyping, I
think that the best solution would be merge replication using the MSDE
servers as the publisher/distributer with a push subscription to the central
office, which will maintain an online copy of each remote database on a
single installation of SQL Server. If dial-up is available, the remote
location would connect nightly and merge changes to the central office.
* In general, will this work (300 publishers connecting nightly to a push
subscription)?
* What is the best way to get the remote server to initiate the dial-up
connection? What about hanging up?
* What happens if the connection gets dropped in the middle of replication?
* What about those schools that can't phone home? How will the "tech grabs a
copy and uploads from elsewhere" scenario work?
* Due to size of the data, I don't think snapshot replication will work and
MSDE can't be a publisher of transactional replication. What about log
shipping? (At the moment, it's a purely one-way transaction.)
* Is there something else that I'm not thinking of? I am open to any
suggestions and happy to answer any question and not afraid to read any
links/documents that might help.
Thanks,
Erik
Do you need data to flow from the remote locations to the head office and
vice versa? Its not clear to me from your description you do. If data only
goes one way you should use transactional replication, if it goes both ways
you will need merge.
The rest of the answers are inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Erik with a K" <Erik with a K@.discussions.microsoft.com> wrote in message
news:16416D59-C495-4002-9ECB-94903199298E@.microsoft.com...
>I am a VB/.NET/SQL developer that has been tasked with restructuring how
>our
> field offices return data to the central office so some of this "advanced"
> SQL Server functionality - like replication - is relatively new to me...
> and
> I need your help, please.
> The scenario:
> We have installations of our MSDE-powered application installed at over
> 300
> geographically-dispersed remote locations. The server at these remote
> locations is on its own internal mini-LAN and not connected to the
> Internet
> full-time but (in most cases, anyway) has the ability to "phone home"
> nightly
> via dial-up. In some cases where dial-up is not an option, our technicians
> will occassionally (approx. every 2 weeks) visit the site and retrieve
> data
> using a custom applet to extract a subset of the full database that can
> then
> be sent to the central office from a different location (i.e. the tech's
> home
> or branch office). Now, due to increased reporting requirements, we want
> to
> have a copy of all of the data in the database instead of just a subset.
> Hence, the new project...
> The variables (distilled):
> * MSDE (2000) at the 300+ remote sites
> * SQL Server 2000 at the central office
> * Central office should, as much as feasible, have a full mirror of the
> remote site
> * After 8 months of running our application, the MSDE databases are 10 to
> 20
> MB each and will only get larger.
> * Remote sites are offline during business hours.
> * Data may be uploaded automatically via an on-demand dial-up Internet
> connection or manually via copy to external media and upload from a third
> location
> * As much as possible, the data should be secured as it travels across the
> wire. A VPN solution is being reviewed but, as of right now, is not
> guaranteed.
> The gist of it:
> After a solid 10 or so days of research, experimentation and prototyping,
> I
> think that the best solution would be merge replication using the MSDE
> servers as the publisher/distributer with a push subscription to the
> central
> office, which will maintain an online copy of each remote database on a
> single installation of SQL Server. If dial-up is available, the remote
> location would connect nightly and merge changes to the central office.
> * In general, will this work (300 publishers connecting nightly to a push
> subscription)?
Yes, but it really depends on the volume of data and the amount of filtering
you are doing.
> * What is the best way to get the remote server to initiate the dial-up
> connection? What about hanging up?
http://support.microsoft.com/kb/241149/en-us
It hangs up/disconnects when it is complete.
> * What happens if the connection gets dropped in the middle of
> replication?
Merge replication will pick up where it left off when it connects again.
> * What about those schools that can't phone home? How will the "tech grabs
> a
> copy and uploads from elsewhere" scenario work?
This is a hard one. I think the best thing to do is to stop your sql server
copy the files for the db to a dvd, move them to a server which is connected
to the internet, stop the server, copy them to the location there, start it
up and hope for the best.
> * Due to size of the data, I don't think snapshot replication will work
> and
> MSDE can't be a publisher of transactional replication. What about log
> shipping? (At the moment, it's a purely one-way transaction.)
Log shipping will work, but only in one direction. MSDE can be subscribers
to transactional publication, but not publishers - so which way is the data
moving to the remotes (which will work) or from the remotes (which won't
work).
> * Is there something else that I'm not thinking of? I am open to any
> suggestions and happy to answer any question and not afraid to read any
> links/documents that might help.
> Thanks,
> Erik
|||I would agree that merge replication seems the most suitable.
I'd probably set it up differently to the way you explained. I'd have the HO
as the publisher and each remote site as a pull subscriber. This is easier
to maintain, although you'll need dynamic filters if you want to partition
the data. You can use alternative synchronization partners for those
occasions where the remote site can't see HO. I believe Hilary has posted
the link for the dialup question.
On the other hand, if you can get Standard Edition on each remote site, then
I'd use Transactional Publishers with a central subscriber which performs
much better.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label return. Show all posts
Showing posts with label return. Show all posts
Tuesday, March 27, 2012
Monday, March 19, 2012
Advanced Query Question
What I am trying to accomplish is to return a resultset that would look
like this:
Company.Name Table1.Column Table2.Column Table3.Column
Company A y y y
Company B n y n
Company C n n y
Company D n n n
I am using 4 tables to do this (Company, Table1, Table2, Table3). What
makes this somewhat complex is that I need to filter on a specific date
range (ie. 5/1/2006 - 5/31/2006) and display a company even if nothing
is available for the date range entered (Company D). Each Table has
its own date column. So I would need to apply my date criteria to
Table1.Date, Table2.Date, and Table3.Date.
In my first attempt I was using an Outer Join to display all company
names even if nothing was available in the three columns. That didn't
seem to work because then Company D wouldn't be included in the output.
The primary/foreign key relationship on the tables is the CompanyId.
The primary CompanyId being in the Company table and the foreign
CompanyId being in Tables 1,2, & 3.
How can I write this SQL statement in order to get the above results?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***"JP" <jp@.nospam.com> wrote in message
news:OJoiMuahGHA.4368@.TK2MSFTNGP03.phx.gbl...
> What I am trying to accomplish is to return a resultset that would look
> like this:
> Company.Name Table1.Column Table2.Column Table3.Column
> Company A y y y
> Company B n y n
> Company C n n y
> Company D n n n
<snip>
CREATE TABLE #Company (CompanyID INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL)
CREATE TABLE #Table1 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table1 ADD
CONSTRAINT FK_Table1_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table2 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table2 ADD
CONSTRAINT FK_Table2_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table3 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table3 ADD
CONSTRAINT FK_Table3_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
INSERT INTO #Company (CompanyID, [Name])
SELECT 1, 'Microsoft'
UNION SELECT 2, 'Hooters'
UNION SELECT 3, 'Ca$h Munny Records'
UNION SELECT 4, 'Widgets LLC.'
UNION SELECT 5, 'Five Inc'
INSERT INTO #Table1 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-01-01'
UNION SELECT 1, 2, '2006-05-01'
UNION SELECT 3, 1, '2005-01-01'
UNION SELECT 5, 1, '2006-05-15'
INSERT INTO #Table2 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-31'
UNION SELECT 1, 2, '2006-01-01'
UNION SELECT 2, 1, '2005-04-01'
UNION SELECT 4, 1, '2006-05-15'
INSERT INTO #Table3 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-02'
UNION SELECT 2, 1, '2006-05-04'
UNION SELECT 3, 1, '2006-05-09'
UNION SELECT 4, 1, '2006-05-15'
UNION SELECT 5, 1, '2006-05-14'
UNION SELECT 2, 2, '2001-01-01'
SELECT DISTINCT c.CompanyID, c.[Name],
CASE WHEN t1.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table1_Column],
CASE WHEN t2.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table2_Column],
CASE WHEN t3.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table3_Column]
FROM #Company c
LEFT OUTER JOIN #Table1 t1
ON c.CompanyID = t1.CompanyID
AND (t1.[Date] >= '2006-05-01' AND t1.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table2 t2
ON c.CompanyID = t2.CompanyID
AND (t2.[Date] >= '2006-05-01' AND t2.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table3 t3
ON c.CompanyID = t3.CompanyID
AND (t3.[Date] >= '2006-05-01' AND t3.[Date] < '2006-06-01')
DROP TABLE #Table3
DROP TABLE #Table2
DROP TABLE #Table1
DROP TABLE #Company|||Thank you very much Mike!
*** Sent via Developersdex http://www.examnotes.net ***
like this:
Company.Name Table1.Column Table2.Column Table3.Column
Company A y y y
Company B n y n
Company C n n y
Company D n n n
I am using 4 tables to do this (Company, Table1, Table2, Table3). What
makes this somewhat complex is that I need to filter on a specific date
range (ie. 5/1/2006 - 5/31/2006) and display a company even if nothing
is available for the date range entered (Company D). Each Table has
its own date column. So I would need to apply my date criteria to
Table1.Date, Table2.Date, and Table3.Date.
In my first attempt I was using an Outer Join to display all company
names even if nothing was available in the three columns. That didn't
seem to work because then Company D wouldn't be included in the output.
The primary/foreign key relationship on the tables is the CompanyId.
The primary CompanyId being in the Company table and the foreign
CompanyId being in Tables 1,2, & 3.
How can I write this SQL statement in order to get the above results?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***"JP" <jp@.nospam.com> wrote in message
news:OJoiMuahGHA.4368@.TK2MSFTNGP03.phx.gbl...
> What I am trying to accomplish is to return a resultset that would look
> like this:
> Company.Name Table1.Column Table2.Column Table3.Column
> Company A y y y
> Company B n y n
> Company C n n y
> Company D n n n
<snip>
CREATE TABLE #Company (CompanyID INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL)
CREATE TABLE #Table1 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table1 ADD
CONSTRAINT FK_Table1_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table2 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table2 ADD
CONSTRAINT FK_Table2_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table3 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table3 ADD
CONSTRAINT FK_Table3_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
INSERT INTO #Company (CompanyID, [Name])
SELECT 1, 'Microsoft'
UNION SELECT 2, 'Hooters'
UNION SELECT 3, 'Ca$h Munny Records'
UNION SELECT 4, 'Widgets LLC.'
UNION SELECT 5, 'Five Inc'
INSERT INTO #Table1 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-01-01'
UNION SELECT 1, 2, '2006-05-01'
UNION SELECT 3, 1, '2005-01-01'
UNION SELECT 5, 1, '2006-05-15'
INSERT INTO #Table2 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-31'
UNION SELECT 1, 2, '2006-01-01'
UNION SELECT 2, 1, '2005-04-01'
UNION SELECT 4, 1, '2006-05-15'
INSERT INTO #Table3 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-02'
UNION SELECT 2, 1, '2006-05-04'
UNION SELECT 3, 1, '2006-05-09'
UNION SELECT 4, 1, '2006-05-15'
UNION SELECT 5, 1, '2006-05-14'
UNION SELECT 2, 2, '2001-01-01'
SELECT DISTINCT c.CompanyID, c.[Name],
CASE WHEN t1.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table1_Column],
CASE WHEN t2.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table2_Column],
CASE WHEN t3.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table3_Column]
FROM #Company c
LEFT OUTER JOIN #Table1 t1
ON c.CompanyID = t1.CompanyID
AND (t1.[Date] >= '2006-05-01' AND t1.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table2 t2
ON c.CompanyID = t2.CompanyID
AND (t2.[Date] >= '2006-05-01' AND t2.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table3 t3
ON c.CompanyID = t3.CompanyID
AND (t3.[Date] >= '2006-05-01' AND t3.[Date] < '2006-06-01')
DROP TABLE #Table3
DROP TABLE #Table2
DROP TABLE #Table1
DROP TABLE #Company|||Thank you very much Mike!
*** Sent via Developersdex http://www.examnotes.net ***
Tuesday, March 6, 2012
ADOMD.net 9.0 (client) and CubeDef.LastProcessed
What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?
Does AMO provide this info more reliably ?
TIA
Dave
Hi Dave,
It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry
It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo
HTH,
Chris
|||
Thanks Chris,
Its not resolved in SP1 either... Im going to check out AMO.
Cheers
Dave
ADOMD.net 9.0 (client) and CubeDef.LastProcessed
What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?
Does AMO provide this info more reliably ?
TIA
Dave
Hi Dave,
It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry
It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo
HTH,
Chris
|||
Thanks Chris,
Its not resolved in SP1 either... Im going to check out AMO.
Cheers
Dave
Friday, February 24, 2012
ADO, SQL Server 2000 and VB5
I am use ADO, SQL S.erver 2000 and aplication in VB 5
When I execute recordset.open VB return error 6 overflow.
Set rsSql = New ADODB.Recordset
rsSql.ActiveConnection = oConn
rsSql.CursorLocation = adUseClient
rsSql.CursorType = adOpenDynamic
rsSql.LockType = adLockPessimistic
rsSql.Index = "ChaveAto"
Sqlquery = "select id_ato from ato where chave_ato = " & "MP00002183200108240120010827PR"
' MsgBox (Sqlquery)
rsSql.Open Sqlqueryif chave_ato is a char/varchar data type you might need to enclose MP00002183200108240120010827PR in quotes.
When I execute recordset.open VB return error 6 overflow.
Set rsSql = New ADODB.Recordset
rsSql.ActiveConnection = oConn
rsSql.CursorLocation = adUseClient
rsSql.CursorType = adOpenDynamic
rsSql.LockType = adLockPessimistic
rsSql.Index = "ChaveAto"
Sqlquery = "select id_ato from ato where chave_ato = " & "MP00002183200108240120010827PR"
' MsgBox (Sqlquery)
rsSql.Open Sqlqueryif chave_ato is a char/varchar data type you might need to enclose MP00002183200108240120010827PR in quotes.
Sunday, February 19, 2012
ADO recordcount returns -1
Hi All
Upgraded Vb6 program from Access 2K to MSDE 2K RelA
Now all my checks for rs.recordcount return -1, except if I use Client side
cursor OR
if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
Why is this?
I don't want to use client side cursors and I need to save data so
(adLockReadOnly) is no good
I have found that that checking for rs.eof seems reliable as a substitute
for recordcount but would like to understand the reasoning
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> Upgraded Vb6 program from Access 2K to MSDE 2K RelA
> Now all my checks for rs.recordcount return -1, except if I use
> Client side cursor OR
> if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> Why is this?
> I don't want to use client side cursors and I need to save data so
> (adLockReadOnly) is no good
> I have found that that checking for rs.eof seems reliable as a
> substitute for recordcount but would like to understand the reasoning
this is quiet normal using ADO recorset as the recordcount will be available
only when the entire rowset has been populated, ant this is true only when a
..movelast operation ins performed and all the data has been transferred..
please be warned that performing a rs.movelast + rs.movefirst can be a
timeconsuming operation for big results...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> please be warned that performing a rs.movelast + rs.movefirst can be a
> timeconsuming operation for big results...
In addition to that, if you DO need to know how many rows you have
(which OP did not in this case), I would first run a select
count(afield) " and the same from/where clause that you use in the
other query. Actually, in those cases you can sometimes eliminate some
of the INNER JOINS that you might have to use when getting the full
field list, and some of the WHERE clause, too -- which will give you
your rowcount faster.
I do this a lot when I want to display a progress bar.
In addition, when looping through a recordset, I ALWAYS do until
rs.eof...I never use the .rowcount property for anything but advising
the user. Not sure WHY I do this, but I've been doing it for years...
Matt
Matt
Upgraded Vb6 program from Access 2K to MSDE 2K RelA
Now all my checks for rs.recordcount return -1, except if I use Client side
cursor OR
if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
Why is this?
I don't want to use client side cursors and I need to save data so
(adLockReadOnly) is no good
I have found that that checking for rs.eof seems reliable as a substitute
for recordcount but would like to understand the reasoning
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> Upgraded Vb6 program from Access 2K to MSDE 2K RelA
> Now all my checks for rs.recordcount return -1, except if I use
> Client side cursor OR
> if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> Why is this?
> I don't want to use client side cursors and I need to save data so
> (adLockReadOnly) is no good
> I have found that that checking for rs.eof seems reliable as a
> substitute for recordcount but would like to understand the reasoning
this is quiet normal using ADO recorset as the recordcount will be available
only when the entire rowset has been populated, ant this is true only when a
..movelast operation ins performed and all the data has been transferred..
please be warned that performing a rs.movelast + rs.movefirst can be a
timeconsuming operation for big results...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> please be warned that performing a rs.movelast + rs.movefirst can be a
> timeconsuming operation for big results...
In addition to that, if you DO need to know how many rows you have
(which OP did not in this case), I would first run a select
count(afield) " and the same from/where clause that you use in the
other query. Actually, in those cases you can sometimes eliminate some
of the INNER JOINS that you might have to use when getting the full
field list, and some of the WHERE clause, too -- which will give you
your rowcount faster.
I do this a lot when I want to display a progress bar.
In addition, when looping through a recordset, I ALWAYS do until
rs.eof...I never use the .rowcount property for anything but advising
the user. Not sure WHY I do this, but I've been doing it for years...
Matt
Matt
ADO InfoMessage doesn't return all messages
I would like to return messages from a 3 'action' stored procedure to
the user of an Access front end via ADO. I've tried looping through
MyConnection.Errors within the InfoMessage event and this returns the
message I see in QA for each action if the user has only changed 1
record. However if the user first updates more than 1 record in Access
and then executes this stored procedure (via a command object) I still
get back only 1 message per action as opposed to 1 message per action
per record as I do in QA. MyConnection.Errors.Count always stays at 3
no matter how many records are updated, though in QA I get 3 * the
number of records messages. I can also reproduce this with print. QA
will display say 9 print messages for 3 records but ADO will only
display 3 messages, those for the first record in the batch.
I can load all the info into an output parameter but would like to use
InfoMessages to build 1 complete string for Msgbox.You might try adding SET NOCOUNT ON to the beginning of your procs. This
will suppress DONE_IN_PROC (including rowcounts) that can cause problems
with ADO application processing of resultsets and messages.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <morriszone@.hotmail.com> wrote in message
news:1110927775.827057.213060@.o13g2000cwo.googlegroups.com...
>I would like to return messages from a 3 'action' stored procedure to
> the user of an Access front end via ADO. I've tried looping through
> MyConnection.Errors within the InfoMessage event and this returns the
> message I see in QA for each action if the user has only changed 1
> record. However if the user first updates more than 1 record in Access
> and then executes this stored procedure (via a command object) I still
> get back only 1 message per action as opposed to 1 message per action
> per record as I do in QA. MyConnection.Errors.Count always stays at 3
> no matter how many records are updated, though in QA I get 3 * the
> number of records messages. I can also reproduce this with print. QA
> will display say 9 print messages for 3 records but ADO will only
> display 3 messages, those for the first record in the batch.
> I can load all the info into an output parameter but would like to use
> InfoMessages to build 1 complete string for Msgbox.
>
the user of an Access front end via ADO. I've tried looping through
MyConnection.Errors within the InfoMessage event and this returns the
message I see in QA for each action if the user has only changed 1
record. However if the user first updates more than 1 record in Access
and then executes this stored procedure (via a command object) I still
get back only 1 message per action as opposed to 1 message per action
per record as I do in QA. MyConnection.Errors.Count always stays at 3
no matter how many records are updated, though in QA I get 3 * the
number of records messages. I can also reproduce this with print. QA
will display say 9 print messages for 3 records but ADO will only
display 3 messages, those for the first record in the batch.
I can load all the info into an output parameter but would like to use
InfoMessages to build 1 complete string for Msgbox.You might try adding SET NOCOUNT ON to the beginning of your procs. This
will suppress DONE_IN_PROC (including rowcounts) that can cause problems
with ADO application processing of resultsets and messages.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <morriszone@.hotmail.com> wrote in message
news:1110927775.827057.213060@.o13g2000cwo.googlegroups.com...
>I would like to return messages from a 3 'action' stored procedure to
> the user of an Access front end via ADO. I've tried looping through
> MyConnection.Errors within the InfoMessage event and this returns the
> message I see in QA for each action if the user has only changed 1
> record. However if the user first updates more than 1 record in Access
> and then executes this stored procedure (via a command object) I still
> get back only 1 message per action as opposed to 1 message per action
> per record as I do in QA. MyConnection.Errors.Count always stays at 3
> no matter how many records are updated, though in QA I get 3 * the
> number of records messages. I can also reproduce this with print. QA
> will display say 9 print messages for 3 records but ADO will only
> display 3 messages, those for the first record in the batch.
> I can load all the info into an output parameter but would like to use
> InfoMessages to build 1 complete string for Msgbox.
>
Thursday, February 16, 2012
ADO Connection to Backend SQL Server
I am using MS Access forms as a front end to a backend SQL Server
Database. I am trying to use an ADO connection to return a Select
Statement from the SQL Server to an Access form to be viewable by the
user. I have been able to establish the connection to the SQL Server
and have verified that the SQL statement is correct. I am completely
new to ADO and I can't figure out how to display the data returned in
the ADO recordset. Could someone please help me out with this? Is
there a way to display the returned recordset in an Access
form/datasheet to be viewable by the user? Below is my code for your
reference:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim Conn As Connection
Dim RS As Recordset
Dim LOC
Dim SQL
LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
SQL = _
"SELECT * From tblClaim"
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open LOC
RS.Open SQL, Conn, adOpenKeyset
Me.RecordSource = RS
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Much simpler to make use of the linked tables. Make the form's source the
linked table or use an access query based on the linked table.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
>I am using MS Access forms as a front end to a backend SQL Server
> Database. I am trying to use an ADO connection to return a Select
> Statement from the SQL Server to an Access form to be viewable by the
> user. I have been able to establish the connection to the SQL Server
> and have verified that the SQL statement is correct. I am completely
> new to ADO and I can't figure out how to display the data returned in
> the ADO recordset. Could someone please help me out with this? Is
> there a way to display the returned recordset in an Access
> form/datasheet to be viewable by the user? Below is my code for your
> reference:
> Option Compare Database
> Option Explicit
> Private Sub Form_Open(Cancel As Integer)
> Dim Conn As Connection
> Dim RS As Recordset
> Dim LOC
> Dim SQL
> LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
> Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
> SQL = _
> "SELECT * From tblClaim"
> Set Conn = CreateObject("ADODB.Connection")
> Set RS = CreateObject("ADODB.Recordset")
> Conn.Open LOC
> RS.Open SQL, Conn, adOpenKeyset
> Me.RecordSource = RS
> RS.Close
> Set RS = Nothing
> Conn.Close
> Set Conn = Nothing
> End Sub
>
|||Thanks Dave,
I am currently utilizing the linked tables to pass information to and
from the SQL server, but wanted to move to ADO connections so I could
get rid of the tables in the Access application. One of the tables
contains password info, so I didn't want there to be any way for a user
to access this table. Maybe ADO isn't the way around this; any links
or tips on securing linked tables in Access would be much appreciated.
-Ashley
Dave Patrick wrote:[vbcol=seagreen]
> Much simpler to make use of the linked tables. Make the form's source the
> linked table or use an access query based on the linked table.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> <ashleycvernon@.gmail.com> wrote:
|||Personally I'd do the security on SQL server rather than trying to do in
Access. I'd use windows authentication rather than sql logins. You could
also link to a view rather than the table and setup security on the view
based on windows user and or windows group association.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
> Thanks Dave,
> I am currently utilizing the linked tables to pass information to and
> from the SQL server, but wanted to move to ADO connections so I could
> get rid of the tables in the Access application. One of the tables
> contains password info, so I didn't want there to be any way for a user
> to access this table. Maybe ADO isn't the way around this; any links
> or tips on securing linked tables in Access would be much appreciated.
> -Ashley
Database. I am trying to use an ADO connection to return a Select
Statement from the SQL Server to an Access form to be viewable by the
user. I have been able to establish the connection to the SQL Server
and have verified that the SQL statement is correct. I am completely
new to ADO and I can't figure out how to display the data returned in
the ADO recordset. Could someone please help me out with this? Is
there a way to display the returned recordset in an Access
form/datasheet to be viewable by the user? Below is my code for your
reference:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim Conn As Connection
Dim RS As Recordset
Dim LOC
Dim SQL
LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
SQL = _
"SELECT * From tblClaim"
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open LOC
RS.Open SQL, Conn, adOpenKeyset
Me.RecordSource = RS
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Much simpler to make use of the linked tables. Make the form's source the
linked table or use an access query based on the linked table.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
>I am using MS Access forms as a front end to a backend SQL Server
> Database. I am trying to use an ADO connection to return a Select
> Statement from the SQL Server to an Access form to be viewable by the
> user. I have been able to establish the connection to the SQL Server
> and have verified that the SQL statement is correct. I am completely
> new to ADO and I can't figure out how to display the data returned in
> the ADO recordset. Could someone please help me out with this? Is
> there a way to display the returned recordset in an Access
> form/datasheet to be viewable by the user? Below is my code for your
> reference:
> Option Compare Database
> Option Explicit
> Private Sub Form_Open(Cancel As Integer)
> Dim Conn As Connection
> Dim RS As Recordset
> Dim LOC
> Dim SQL
> LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
> Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
> SQL = _
> "SELECT * From tblClaim"
> Set Conn = CreateObject("ADODB.Connection")
> Set RS = CreateObject("ADODB.Recordset")
> Conn.Open LOC
> RS.Open SQL, Conn, adOpenKeyset
> Me.RecordSource = RS
> RS.Close
> Set RS = Nothing
> Conn.Close
> Set Conn = Nothing
> End Sub
>
|||Thanks Dave,
I am currently utilizing the linked tables to pass information to and
from the SQL server, but wanted to move to ADO connections so I could
get rid of the tables in the Access application. One of the tables
contains password info, so I didn't want there to be any way for a user
to access this table. Maybe ADO isn't the way around this; any links
or tips on securing linked tables in Access would be much appreciated.
-Ashley
Dave Patrick wrote:[vbcol=seagreen]
> Much simpler to make use of the linked tables. Make the form's source the
> linked table or use an access query based on the linked table.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> <ashleycvernon@.gmail.com> wrote:
|||Personally I'd do the security on SQL server rather than trying to do in
Access. I'd use windows authentication rather than sql logins. You could
also link to a view rather than the table and setup security on the view
based on windows user and or windows group association.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
> Thanks Dave,
> I am currently utilizing the linked tables to pass information to and
> from the SQL server, but wanted to move to ADO connections so I could
> get rid of the tables in the Access application. One of the tables
> contains password info, so I didn't want there to be any way for a user
> to access this table. Maybe ADO isn't the way around this; any links
> or tips on securing linked tables in Access would be much appreciated.
> -Ashley
Monday, February 13, 2012
ADO & C++ return values after error
Hi everybody,
i've a problem calling a sql sp from my c++ component, if i
voluntarily crash the sp (for example try to insert a null value where
is not allow) return value are all blank and isn't possible for me to
understand correctly the problem
here you are sp:
CREATE PROCEDURE SP_TEST_RETURN_VALUE
(
@.ErrNum INT OUTPUT,
@.ErrMsg varchar(256) OUTPUT
)
AS
BEGIN
DECLARE @.PatientCode int
SET @.ErrNum = 999
SET @.ErrMsg = 'Empty'
BEGIN TRANSACTION
INSERT INTO TabellaTest( [INDEX], CampoTest)
VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
value!!
SET @.ErrNum = @.@.ERROR
IF @.ErrNum > 0 BEGIN
SET @.ErrMsg = 'ERROR (pos 2)'
goto abort_transaction
END
COMMIT TRANSACTION
RETURN 0
abort_transaction:
ROLLBACK TRANSACTION
RETURN -1
END
GO
here you are the call from c++:
.............
try
{
olehr = accessor.Open(dataSource.GetSession(),
_T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
// if fail value is 0 not -1!!
result = accessor.m_RETURNVALUE;
// IF FAIL the 2 memeber below contain wrong value
//accessor.m_ErrNum;
//accessor.m_ErrMsg;
if (FAILED(olehr())) _com_issue_error(olehr());
} // end try
catch (_com_error &e)
{
char * descr = (char*) e.Description();
char * msg = (char*) e.ErrorMessage();
accessor.Close();
}
it seems that the sp fail make the rollback but not se any values into
in/out parameters.
Please help me......Put SET NOCOUNT ON as the first statement in the sp.
CREATE PROCEDURE SP_TEST_RETURN_VALUE
(
@.ErrNum INT OUTPUT,
@.ErrMsg varchar(256) OUTPUT
)
AS
set nocount on
DECLARE @.PatientCode int
SET @.ErrNum = 999
SET @.ErrMsg = 'Empty'
BEGIN TRANSACTION
INSERT INTO TabellaTest( [INDEX], CampoTest)
VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
value!!
SET @.ErrNum = @.@.ERROR
IF @.ErrNum > 0 BEGIN
SET @.ErrMsg = 'ERROR (pos 2)'
goto abort_transaction
END
COMMIT TRANSACTION
RETURN 0
abort_transaction:
ROLLBACK TRANSACTION
RETURN -1
GO
AMB
"Matteo" wrote:
> Hi everybody,
> i've a problem calling a sql sp from my c++ component, if i
> voluntarily crash the sp (for example try to insert a null value where
> is not allow) return value are all blank and isn't possible for me to
> understand correctly the problem
> here you are sp:
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> BEGIN
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> END
> GO
> here you are the call from c++:
> ..............
> try
> {
> olehr = accessor.Open(dataSource.GetSession(),
> _T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
> // if fail value is 0 not -1!!
> result = accessor.m_RETURNVALUE;
> // IF FAIL the 2 memeber below contain wrong value
> //accessor.m_ErrNum;
> //accessor.m_ErrMsg;
> if (FAILED(olehr())) _com_issue_error(olehr());
> } // end try
> catch (_com_error &e)
> {
> char * descr = (char*) e.Description();
> char * msg = (char*) e.ErrorMessage();
> accessor.Close();
> }
>
> it seems that the sp fail make the rollback but not se any values into
> in/out parameters.
> Please help me......
>|||Wow, thanks a lot it seems to be working fine...... I've another
question (if you have another bit time for me!)
As you can see there is a try-catch block, every time the sp crash the
e.Description return "IDispatch Error #3092", i expect that in this
member is store the error description related to e.Error member (which
contain the error code), but indeed there is always the message above,
do you know something about it? Thanks.
Matteo
"examnotes" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge news:<B4E1BBA4-BB85-4D27-8BB0-16A1D4618CA3@.microsoft.com>...
> Put SET NOCOUNT ON as the first statement in the sp.
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> set nocount on
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> GO
>
> AMB
>
> "Matteo" wrote:
>|||sorry, in my previous mail i described a new problem, but the member
that contain "IDispatch Error #3092" is nor e.Desciption, but
e.ErrorMessage!!! I'm sorry...
mbellardi@.ferraniait.com (Matteo) wrote in message news:<3c42a11c.0504220511.4152e939@.posti
ng.google.com>...
> Hi everybody,
> i've a problem calling a sql sp from my c++ component, if i
> voluntarily crash the sp (for example try to insert a null value where
> is not allow) return value are all blank and isn't possible for me to
> understand correctly the problem
> here you are sp:
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> BEGIN
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> END
> GO
> here you are the call from c++:
> .............
> try
> {
> olehr = accessor.Open(dataSource.GetSession(),
> _T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
> // if fail value is 0 not -1!!
> result = accessor.m_RETURNVALUE;
> // IF FAIL the 2 memeber below contain wrong value
> //accessor.m_ErrNum;
> //accessor.m_ErrMsg;
> if (FAILED(olehr())) _com_issue_error(olehr());
> } // end try
> catch (_com_error &e)
> {
> char * descr = (char*) e.Description();
> char * msg = (char*) e.ErrorMessage();
> accessor.Close();
> }
>
> it seems that the sp fail make the rollback but not se any values into
> in/out parameters.
> Please help me......
i've a problem calling a sql sp from my c++ component, if i
voluntarily crash the sp (for example try to insert a null value where
is not allow) return value are all blank and isn't possible for me to
understand correctly the problem
here you are sp:
CREATE PROCEDURE SP_TEST_RETURN_VALUE
(
@.ErrNum INT OUTPUT,
@.ErrMsg varchar(256) OUTPUT
)
AS
BEGIN
DECLARE @.PatientCode int
SET @.ErrNum = 999
SET @.ErrMsg = 'Empty'
BEGIN TRANSACTION
INSERT INTO TabellaTest( [INDEX], CampoTest)
VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
value!!
SET @.ErrNum = @.@.ERROR
IF @.ErrNum > 0 BEGIN
SET @.ErrMsg = 'ERROR (pos 2)'
goto abort_transaction
END
COMMIT TRANSACTION
RETURN 0
abort_transaction:
ROLLBACK TRANSACTION
RETURN -1
END
GO
here you are the call from c++:
.............
try
{
olehr = accessor.Open(dataSource.GetSession(),
_T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
// if fail value is 0 not -1!!
result = accessor.m_RETURNVALUE;
// IF FAIL the 2 memeber below contain wrong value
//accessor.m_ErrNum;
//accessor.m_ErrMsg;
if (FAILED(olehr())) _com_issue_error(olehr());
} // end try
catch (_com_error &e)
{
char * descr = (char*) e.Description();
char * msg = (char*) e.ErrorMessage();
accessor.Close();
}
it seems that the sp fail make the rollback but not se any values into
in/out parameters.
Please help me......Put SET NOCOUNT ON as the first statement in the sp.
CREATE PROCEDURE SP_TEST_RETURN_VALUE
(
@.ErrNum INT OUTPUT,
@.ErrMsg varchar(256) OUTPUT
)
AS
set nocount on
DECLARE @.PatientCode int
SET @.ErrNum = 999
SET @.ErrMsg = 'Empty'
BEGIN TRANSACTION
INSERT INTO TabellaTest( [INDEX], CampoTest)
VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
value!!
SET @.ErrNum = @.@.ERROR
IF @.ErrNum > 0 BEGIN
SET @.ErrMsg = 'ERROR (pos 2)'
goto abort_transaction
END
COMMIT TRANSACTION
RETURN 0
abort_transaction:
ROLLBACK TRANSACTION
RETURN -1
GO
AMB
"Matteo" wrote:
> Hi everybody,
> i've a problem calling a sql sp from my c++ component, if i
> voluntarily crash the sp (for example try to insert a null value where
> is not allow) return value are all blank and isn't possible for me to
> understand correctly the problem
> here you are sp:
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> BEGIN
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> END
> GO
> here you are the call from c++:
> ..............
> try
> {
> olehr = accessor.Open(dataSource.GetSession(),
> _T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
> // if fail value is 0 not -1!!
> result = accessor.m_RETURNVALUE;
> // IF FAIL the 2 memeber below contain wrong value
> //accessor.m_ErrNum;
> //accessor.m_ErrMsg;
> if (FAILED(olehr())) _com_issue_error(olehr());
> } // end try
> catch (_com_error &e)
> {
> char * descr = (char*) e.Description();
> char * msg = (char*) e.ErrorMessage();
> accessor.Close();
> }
>
> it seems that the sp fail make the rollback but not se any values into
> in/out parameters.
> Please help me......
>|||Wow, thanks a lot it seems to be working fine...... I've another
question (if you have another bit time for me!)
As you can see there is a try-catch block, every time the sp crash the
e.Description return "IDispatch Error #3092", i expect that in this
member is store the error description related to e.Error member (which
contain the error code), but indeed there is always the message above,
do you know something about it? Thanks.
Matteo
"examnotes" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge news:<B4E1BBA4-BB85-4D27-8BB0-16A1D4618CA3@.microsoft.com>...
> Put SET NOCOUNT ON as the first statement in the sp.
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> set nocount on
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> GO
>
> AMB
>
> "Matteo" wrote:
>|||sorry, in my previous mail i described a new problem, but the member
that contain "IDispatch Error #3092" is nor e.Desciption, but
e.ErrorMessage!!! I'm sorry...
mbellardi@.ferraniait.com (Matteo) wrote in message news:<3c42a11c.0504220511.4152e939@.posti
ng.google.com>...
> Hi everybody,
> i've a problem calling a sql sp from my c++ component, if i
> voluntarily crash the sp (for example try to insert a null value where
> is not allow) return value are all blank and isn't possible for me to
> understand correctly the problem
> here you are sp:
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> BEGIN
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> END
> GO
> here you are the call from c++:
> .............
> try
> {
> olehr = accessor.Open(dataSource.GetSession(),
> _T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
> // if fail value is 0 not -1!!
> result = accessor.m_RETURNVALUE;
> // IF FAIL the 2 memeber below contain wrong value
> //accessor.m_ErrNum;
> //accessor.m_ErrMsg;
> if (FAILED(olehr())) _com_issue_error(olehr());
> } // end try
> catch (_com_error &e)
> {
> char * descr = (char*) e.Description();
> char * msg = (char*) e.ErrorMessage();
> accessor.Close();
> }
>
> it seems that the sp fail make the rollback but not se any values into
> in/out parameters.
> Please help me......
Sunday, February 12, 2012
ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005
Hello,
I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.
To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.
try{
pCmd.CreateInstance((__uuidof(Command)));
pCmd->ActiveConnection = m_pConnection;
pCmd->CommandType = adCmdStoredProc;
pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");
............................ code here ........................................
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));
VARIANT returnVal;
returnVal.vt = VT_I2;
returnVal.intVal = NULL;
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));
//Get Return value by executing the command
//The return value should be the DB unique ID.
pCmd->Execute(NULL, NULL, adCmdStoredProc);
int uniqueId = returnVal.intVal;
//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);
//GetFieldValue(0,pRst,uniqueId);
printf("The DB unique ID is: %i",uniqueId);
return uniqueId;
}
Cheers,
Seth
You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.
Here is what you should do:
_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));
pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();
ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005
Hello,
I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.
To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.
try{
pCmd.CreateInstance((__uuidof(Command)));
pCmd->ActiveConnection = m_pConnection;
pCmd->CommandType = adCmdStoredProc;
pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");
............................ code here ........................................
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));
VARIANT returnVal;
returnVal.vt = VT_I2;
returnVal.intVal = NULL;
pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));
//Get Return value by executing the command
//The return value should be the DB unique ID.
pCmd->Execute(NULL, NULL, adCmdStoredProc);
int uniqueId = returnVal.intVal;
//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);
//GetFieldValue(0,pRst,uniqueId);
printf("The DB unique ID is: %i",uniqueId);
return uniqueId;
}
Cheers,
Seth
You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.
Here is what you should do:
_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));
pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();
Subscribe to:
Posts (Atom)