Advice needed - Regarding data transfer between databases on seperate servers

Hello everyone,

Here's my situation...

I'm running a web service which involves 51 seperate servers and databases.

There are fifty licensee servers (One for each US state) and one corporate server.

Each night I need to upload sales and membership data from the licensee's databases to the corporate database to compile reports.

The application platform I'm using is ASP.NET 2.0 and the the database is SQL2005 express.

I want this process to be run automatically, so I believe it's a scheduled windows service I need to setup up in .NET to make the data transfers.

If anyone has already set something up like this, or knows the steps to take? I would love to have your input.

Thanks in advance,

Robert

Did you mean 50 Databases on same server or 50 different servers?

You could set up a DTS package and schedule it to run at a particular time every day. You dont need to do anything with .NET. This can be done completely at the backend.

|||

ndinakar:

Did you mean 50 Databases on same server or 50 different servers?

You could set up a DTS package and schedule it to run at a particular time every day. You dont need to do anything with .NET. This can be done completely at the backend.

This is correct, note that SQLAgent service has to be up & running inorder to have your DTS (in SQL Server 2000) or SSIS (in SQL Server 2005) packages executed on time.

If this service was not started (stopped), any scheduled tasks in SQL Server will not work.

Conclusion: Make sure this SQLAgent service is always up & running in all your database servers (check them after batching your OS or restarting it).

One of the bad thing about this way is, you can not generate reports at anytime .. you have to wait (say till next day) in order to see the reports for all states.

How to overcome this is by using replication (for example), by replicating the data you need from source tables (in diffrent servers) into ONE server (e.g. main branch) so you can generate the reports from all 50 data sources at any time and updated;

Just a suggestion:
If the amount of data is not that much, you can export the data (say in Excel format) then send them by email to your main branch.
This might save some traffic (bandwidth) for you, but on the another hand you need to imported to SQL Server before generating the reports.
One more good thing about this way is, you will always have a copy in your email for all 50 databases.

There are many ways to do this, actullay depends on the case and how important to get the report on anytime and how large the data is.

Good luck.

|||

Hi Guys,

Thanks for the responses!

Nethi - Yes thats right - There will be 50 seperate licensee databases, which run on 50 seperate licensee servers. Each night I need to retrieve data from each of 50 seperate databases and upload it to a database on the corporate server to run reports the next day. (51 seperate servers/databases involved)

The sales data I'll be uploading nightly will be minimal, because i'll only be capturing sales that were made that day (12 am till 11:59:59 pm the licensee's server time) The membership data will be a larger file, because I need to upload all records every night, which will overwrite the prior membership data records. So in this case there will be many rows, but the actual data being uploaded from each row is minimal.

Tell me - Using DTS...

Can I fully specify what data needs to be uploaded? (Conditions such as record creation time, etc)
The servers may be in different time zones - Would this cause problems for DTS regarding capture periods?
Using DTS, will the uploads be secure?
If a scheduled data transfer fails for some reason or another, does DTS have a feature to report the failure, and what would be involved in capturing the data that wasn't transfered as scheduled? Is it complicated, difficult and time consuming?

Thanks again for your input and advice

Robert

|||

>>>Can I fully specify what data needs to be uploaded? (Conditions such as record creation time, etc)

You can use a mix of script in DTS - VB Script/T-SQL. You can call stored procedures. Google for DTS and read up some articles to get some info.

>>>The servers may be in different time zones - Would this cause problems for DTS regarding capture periods?

The DTS will run from the server its created on, on the schedule its scheduled to run. You can schedule it according to souce server timezone or target server timezone.

>>>Using DTS, will the uploads be secure?

DTS will use either Windows Authentication or SQL authentication. You can set up either way.

>>>If a scheduled data transfer fails for some reason or another, does DTS have a feature to report the failure, and what would be involved in capturing the data that wasn't transfered as scheduled? Is it complicated, difficult and time consuming?

You call the DTS package from a scheduled job. The job has additional options of reporting - email/pager and different options for success/failure.

|||

As much as I know Sql Server 2005 Express does not support DTS.

sql

Adventureworks 2005

I am running sqlserver2005(ctp) and trying to get the sample database adventureworks loaded so that I have some examples of proc, views, etc.

I finally found the database and downloaded it but when I try to attach I get the following message:

"The database 'AdventureWorks' cannot be opened because it is version 611. This server supports version 603 and earlier. A downgrade path is not supported. Sould not open new database 'AdventureWorks'. CREATE DATABASE is aborted. "

I have MS SQL Server management Studio version 9.00.116.00
MS Analysis Services Client Tools 2000.090.1116.00
MS Data Access Components 2000.086.1830.00(srv03_sp1_rtm.050324-1447)
MS MSXML 2.6.3.0.4.0.6.0
MS IE 6.0.3790.1830
MS .NET Framework 2.0.50215.44
Operating System 5.2.3790

I believe all of this is up to date. Can someone tell me if I need an upgrade (and where that would be) or if there is a version of the AdventureWorks I can use with my current configuration(and where that would be)?

dfw

I think when you install the CTP, there should me a menu item that prompts you to install the sample databases and other tutorials. Did you check that out? If you are using the September CTP, its there in the Readme notes on how to do this.|||Many, many thanks.
I should have realized the files would be on the install disk. I check and there they are!

dfw

Advatnages and DisAdvantages of

Running SQL Server as a DOMAIN user account.It is a single, two-edged sword. If SQL Server authenticates to the domain, it can have access to the resources of the domain. That is both a blessing and a curse.

It means that (given the proper permissions), the SQL Server can "see" other resources such as disk, printers, etc. The server can then send mail and other forms of messages (that rely on domain authentication).

In general, I usually have one "intereface" server that uses a domain account, but has no end user connections. It does all of the "cross server" work for the whole farm. The other servers use Local System unless some particular reason forces another choice.

-PatP|||Search for MS Best Practices on SQL Server and SQL Agent service accounts.|||with a domain user account the sqlserver and sql server agent accounts can access the local machine and can be audited through the os.

the mssqlserver service can communicate more efficiently with other servers that are domain members.

you can use sqlmail directly(no workarounds) because you will have an exchange mailbox created for your mssqlserver user account.

you can make the accounts [domain users] but give them admin rights on the local sql server machine to control access and use.

you can avoid having to create a cmdexec proxy account for running activex scripts ..

the accounts allow you the ability to perform active directory delegeation and impersonation.

the domain accounts provide for mutal authentication services through kerberos in active directory.sql

Advantages of SQL Server

hi ! my boss is thinking of redoing our accounting system which is currently running on FOXPRO - we are planning on a VB/SQL SERVER platform.but he isn't convinced that the benefits of SQL server outweigh those offered by FOXPRO especially since everyone in the office is very comfortable with Foxpro.Can anyone give me some solid advantages of SQL SErver or any other RDBMS over Foxpro ?http://dbforums.com/arch/125/2003/2/689282|||FoxPro?

Accounting?

Security?

I got it...how 'bout an abacus, quill and parchment...

Ok...enough with the sillyness...guess they keep advancing it...why? I have no idea..

http://gotdotnet.com/team/vfp/

What version of foxpro?|||A couple of observations...

VFP and MS-SQL are not mutually exclusive. You can have the benefits of both if you choose. SQL Server can provide the "back end" at the server, and VFP can provide the "front end" at the client.

SQL Server allows much better security, eliminates damaged files due to workstation disconnects, better performance, practically infinite ability to scale.

-PatP|||I still have a Programmer's Manual for FoxPro 2.0, right before it was bought up by M$. It presented a serious competition to Borland's dBase IV, and gave me an opportunity to really dive into SQL...Anybody wants to buy 19 Installation disks (1.44MB) for FoxPro 2.6 with a Connectivity Kit?|||Still got your Access 1.2 disks?

;)

First consultant gig was on 2.0

:D

Now where did all the side business go?

:(|||I've still got a set of 5 1/4 Paradox discs (pre-Windows). 8:)|||Hey, Paradox 4.0/4.5 was my next step (actually a step backwards in regards to SQL) I believe I still have them too, not sure about the version...Anyone?|||...And I also have the first decent VB for DOS, 2.0. Fits on 2 1.44MB floppies, very compact and suitable for travel. Very low minimum system requirements, produces beautiful forms and popup dialog boxes ;)|||Sorry, my old Kaypro doesn't have 3 1/2 inch drives. Just the old 360KB floppies. No HD either.|||I still have a pair of 8" floppies with Vulcan on them.

-PatP|||I knew I should have kept my first program! But carting all those punch cards around got to be a real pain (literally!) :)|||I tossed my paper tape after the program went into production!|||okay..

i used to get up each morning 3 hrs before i went to sleep and clean the road with my tongue, for 3 cents a week. considering the shoebox at the bottom of the lake that we lived in had a rent of 32 cents a week, we had to do a lot of odd jobs.

my first for pay consulting job was at mr slate's quarry.
i used to maintain(feed) the first database ever created called "joe" after it's creator "joe". it used a series of stones tied to sticks attached to a pelican. and through a series of levers we could calculate pi to 1 place in 7.3 hours.
being a very exclusive database, there was only one copy and since it was a bit on the gamey side, it couldnt be kept indoors.

i have a copy in a storage container in my back yard, it's not totally oledb compatible but with a bit of work it could be dblibrary.|||Wow! You still have a copy of the "Joe" database? What a coincidence! At the back of one of my file cabinets I still have the original RFP I submitted for the project.

Did you know it was actually a conversion from an older legacy system?|||nope your thinking of "joe's" weaker sister "mary" it wasnt even a database per se.
the storage mechanism was elephant turds and while being easy to find, were not very portable and therfore would harden after a few days making the db unusable.
we considered mary to be effetively a slowly created statue not a database.

i have a "mary" coffee table if you need some living room furniture.|||Ruprect, the elephant dung can be softened by the application of moisture. This should have been part of your regularly scheduled maintenance plan.

Really, you should have paid more attention to Books On Rocks. It was full of usefull examples. I guess back then you must have been a rookie DBA (Dung Ball Administrator).|||what is it with you
even on older databases your books online is still out of date

[Books On Rocks] Stone Package 3

in the last release of "Mary" database there is no need to apply moisture to the mammoth dung as it is considered a "wasting asset"

you will have to rebuild the dung clusters with fresh dung every one to two weeks for proper retireval.

everyone knows that you will change the "flies" sort order if you moisten the dung

what an ultramaroon..

update your books on rocks|||I stand corrected. I guess my only excuse is that my thinking has been fuzzy and muddled ever since I stumbled upon a strange black rectangular monolith in the woods...

I concede that your knowledge of Dung Ball Administration is much deeper than mine. Knee-deep, even.

Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pro.../qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:

> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pro.../qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>
|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:

Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any samplin
g
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UP
DATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pr...5/qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampli
ng
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBC
C
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hen
ce
> lessening the usefulness of the statistics. The values for EQ_ROWS are al
so
> much less accurate, which I suppose you'd expect with a smaller sample siz
e.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:

> DBREINDEX will build the statistics based on all rows, i.e., no sampling.
UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pr...5/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE
option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>

Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
> >I have noticed that running UPDATE STATISTICS without specifying any sampling
> > options, thus accepting the default selected by SQL Server, can reduce the
> > level of detail contained within the index distribution statistics.
> >
> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
> > much less accurate, which I suppose you'd expect with a smaller sample size.
> > However, I do not understand what the optimizer gains from reducing the
> > number of steps during an UPDATE STATISTICS execution.
> >
> > Kind Regards
> >
> > Andrew Pike
> > --
> > SQL Server DBA
> > UBS IB
> >
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS
>> without
>> specifying sampling options *will* sample:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
>> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>> >I have noticed that running UPDATE STATISTICS without specifying any sampling
>> > options, thus accepting the default selected by SQL Server, can reduce the
>> > level of detail contained within the index distribution statistics.
>> >
>> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
>> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
>> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
>> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
>> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
>> > much less accurate, which I suppose you'd expect with a smaller sample size.
>> > However, I do not understand what the optimizer gains from reducing the
>> > number of steps during an UPDATE STATISTICS execution.
>> >
>> > Kind Regards
>> >
>> > Andrew Pike
>> > --
>> > SQL Server DBA
>> > UBS IB
>> >
>>

ADS: Sample application cannot access desktop database

The sample application provided with Access Database Synchronizer fires a "A request to send data to the computer running IIS has failed. For more information, see HRESULT." exception on my WinCE 4.2 device.

I debugged it with VS2005 to see what was going on and found that the problem resides in the line:

m_connDesktop.Pull("MSysObjects","SELECT Name FROM MSysObjects WHERE Type=1 and flags=0",accessSysConnectString,RDATrackOption.TrackingOff,"Err_MSysObjects");

It seems that connection m_connDesktop is ok but calling Pull method fails.

I've verified that SSEvAccessSync service is started when ActiveSync detects device connection and that firewall rules are correctly updated.

Also, i verified my System.mdw and i discovered that there is no "MSysObjects" table (only MSysGroupList, MSysGroupMembers, MSysUserList and MSysUserMemberships queries are there).

Do you think the problem is there?

How can i solve it without damaging System.mdw?

I cannot figure out what's the purpose of this line of code, can you help me?

Just a summary of what i've tried so far (unsuccessfully):

1. Once discovered MSysObjects hidden table, i changed "Tools->Security->Users and groups auth" to grant read privileges to the user used for connection

2. I've tried to change connection string to include "User Id=...". Anyway, connection object is created successfully: the error appears on Pull method invocation

3. I've configured SQL Server (SQLEXPRESS) to accept network connections, enabled SQL Server Browser and changed firewall settings accordingly

Feedback from someone that put it to work needed, please...

|||

Hi Fabio,

MSysObjects is not a hidden table inside System.mdw. It is a system table available with every access database.

The error message you are getting indicates desktop is not accepting packets.You can try the following scenario:

1) Before doing the Sync from Given Sample App. Just check if you have written the configuration information like Desktop URL, User Name etc. correctly. Default port Number through which we communicate is 1024. If you have changed the port number by editing registry , then you need to change your firewall setting and Desktop URL accordingly.

2) System.mdw file is located at "C:\Document and setting\<LOGIN ID>\System.mdw". If your Windows installation drive is not C:\ drive. you can change the driver letter by editing config.xml file located in device at "\Program File\AccessSync\Config.xml" under WIN_DIR node.

3) If above information is not helpful, you can open the internet explorer browser at device side and put the desktop URL in the address bar. If you are getting information "Page cannot be found. check the name and try again". It means desktop is not accepting packet at given port. you can try the same URL at desktop side internet explorer. If it is working at desktop side and not working at device side, it means connection from desktop is not proper through active sync. If desktop side also URL is not working, it means service is not started or you are having incorrect port number in URL.

Let me know if it doesn't help.

|||

Thank you pakumar,

it seems a connection through activesync problem (see 3.):

1. Here's my Config.xml:

<?xml version="1.0" encoding="UTF-8"?>

<SystemNode>

<AccessDB>C:\TestDB.mdb</AccessDB>

<DeviceDB>\My Documents\TestDB.sdf</DeviceDB>

<Login>Admin</Login>

<URL>http://PCNAME:1024/</URL>

<WinDir>C:</WinDir>

</SystemNode>

"Admin" is the Windows user logged in during synchronization.

I have opened port 1024 (not changed any registry key and checked it anyway) and granted access to "svcssevas31.exe" application. Anyway from now on i decided to shutdown windows firewall until problem solved.

2. My Windows installation drive is C:\. Debugging the app, i've verified that the connection string points to the correct location of System.mdw. I've found MSysObjects table and granted r/w data and schema access to Admin user.

3. From desktop it DOES work. From device it DOESN'T. I'm confused about this because:

From device's shell, i can successfully ping desktop IP address (seen as 127.0.0.1) and machine name|||

Further tests:

URL http://PCNAME:1024/ is accessible from desktop machines on the same network (also with firewall enabled).|||

Hi Fabio,

1) I can see you have installed both 2.0 and 3.0 device cabs. You can uninstall all 2.0 cabs sql.ppc.wce4.armv4.CAB and sql.dev.ENU.ppc.wce4.armv4.CAB . you should keep only 3.0 cab bits. That should solve the problem.

2) You have installed Wince 5.0 cab of sample application to WinCE 4.2 device. You can build the given sample application for WinCE 4.2 or PPC 2003 SE and copy the exe generated to \Program Files\AccessSync\ folder in device.

3) You can try the sample application on device emulator also if you have it, just to gain confidence in the product.

Let me know if you require more help.

ads wizard: Failure to open SQL Server with given connect string

Hi, sorry but I am just getting my feet wet with the ADS wizard. I have managed to get the PPC emulator running the ADS wizard and have the emulator ActiveSync connected with desktop. Port 1024 opens from the emulator to the desktop. On the ADS wiz I can connect to the device .sdf. WHen I try to connect to the desktop I am getting a connection failure in the ADS wiz log and after stepping through the code VS watch window gives the following message for the SQL exception:

Message "Failure to open SQL Server with given connect string. [ connect string =
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.accdb; Jet OLEDBystem
Database=C:\\Documents and Settings\\vhaporellinr.POR-EE778886\\Application Data\\Microsoft\\Access\\system.mdw ]" string

I do not know enough to know where to go next to move past this no-doubt simple problem. Can you direct me to the appropriate docs or refs to start digging into looking for solutions?

Is there a particular Northwind.mdb file that is supposed to be used?

Are we talking some kind of permissions issues on the database?

I was assuming I did not have to install SQL Server 2005 and the SQL Server management console(?) to do development with the SSCE. Is that correct?

THank you for any helping pointers you can provide.

Try this connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\NorthWind.accdb;Persist Security Info=False

|||Hi Erik. Thanks for the string. Still having problems though. I am going to step back and run through the early tutorials which work with the Northwind.mdb and test.sdf files. This Northwind I am using might be the problem. TIme to pay some dues I guess. Thanks again for the help.|||

Ran into snags on Tutorial 1. Posted problem in this forum. Tutorial 2 worked pretty good. Dropped back to fussing around with access synchcronizer app. Messed with mdw file and other configuration settings. Changed to Access 2003 mdb file. Now application will not run on emulator at all. Same IIS problem when running on device.

I am going to temporarily abandon syncronizing to access database for now. One of these days I will try it on another computer, on a private network.

|||

I tried the ADS wizard installation on another computer on a private network. Tried Access 2003. At first received similar problems. Went back to readme.doc and examined it more precisely. Checked the registry, things looked ok. A Desktop IE would not open to the ads desktop server port. Went back to the XP Firewall settings. Added a 1024 port in the exceptions tab. Ran the ADS wizard exe on the PPC emulator ( had not tried that beforealways had used VS2005 debugger). The ADS wizard connected up to both device and desktop databases and pulled a 6MB database down to the device!

Things are looking pretty good right now on this system. Will check out the other machine/network Firewall settings in a few days. So far, it looks like it probably was operator error on my part causing all my problems.

Thank you Microsoft for investing in this SQL Server Compact Edition and providing this ADS Wizard application and source. The technology looks like the perfect fit for two of my current projects.

|||

Today I went back to previous problem system. I got the ADS Wizard to run on the PocketPC emulator after tweaking the firewall network card port settings and downloading and installing the .CAB file that ships with the syncapp package.

The ADS Wizard.exe that the .CAB file installs works great now on the emulator and will suffice until I need a custom app.

ADS "System timed out waiting for lock" error

I am attempting to modify the ADS sample to work with my own application and am running into a snag. I have a simple sqlce database that has two tables in it. I have everything working up until the point where I try to "Pull" the data. At this point in time this is what happens.

1.) I successfully Drop Table1 from the SQLCE database and then Pull "Table1" from the desktop database

2.) When attempting to Pull Table2. It runs the DoesTableExist Function, identifies that the table does exist and therefore tries to run the "DropTable" routine before Pulling the desktop Table2 data. As soon as the sqlCmd.ExecuteNonQuery of the DropTable routine runs I get the following error....

"The system timed out waiting for a lock. [ Session id = 1,Thread id = 1318094114,Process id = 1318068802,Table name = Table2,Conflict type = x lock (s blocks),Resource = DDL ][Table2]"

Of Note...I converted the sample to VB.NET so it's possible something didn't convert right? My guess is that it probably has something to with my SQLCE Database but I haven't the slightest clue where to go from there. Anyone have any ideas what might be going on?

Thanks,

Mike
I figured out my problem. Just as the error suggests, there was a lock on my database. But what was causing the lock is confusing me. When my main form opens I do a quick query on the database to retrieve some information. I open my connection to the SQLCE database, I execute a ResultSet, retrieve my data, close my ResultSet and close my connection. The datatable that I perform the ResultSet on is now "locked". If I comment out this code, the datatable is no longer locked.

Why is my datatablle still locked? Is there a way that I can let SQLCe know that I'm done with it?

Thanks!|||

Unless you explicitly Dispose the objects you will have the DB locked as Finalize/Dispose may get scheduled any time depending on GC's love on the object.

Thanks,

Laxmi

|||

Laxmi, why if he has explicitly closed the connection is there still a lock on the db?

ADS "System timed out waiting for lock" error

I am attempting to modify the ADS sample to work with my own application and am running into a snag. I have a simple sqlce database that has two tables in it. I have everything working up until the point where I try to "Pull" the data. At this point in time this is what happens.

1.) I successfully Drop Table1 from the SQLCE database and then Pull "Table1" from the desktop database

2.) When attempting to Pull Table2. It runs the DoesTableExist Function, identifies that the table does exist and therefore tries to run the "DropTable" routine before Pulling the desktop Table2 data. As soon as the sqlCmd.ExecuteNonQuery of the DropTable routine runs I get the following error....

"The system timed out waiting for a lock. [ Session id = 1,Thread id = 1318094114,Process id = 1318068802,Table name = Table2,Conflict type = x lock (s blocks),Resource = DDL ][Table2]"

Of Note...I converted the sample to VB.NET so it's possible something didn't convert right? My guess is that it probably has something to with my SQLCE Database but I haven't the slightest clue where to go from there. Anyone have any ideas what might be going on?

Thanks,

Mike
I figured out my problem. Just as the error suggests, there was a lock on my database. But what was causing the lock is confusing me. When my main form opens I do a quick query on the database to retrieve some information. I open my connection to the SQLCE database, I execute a ResultSet, retrieve my data, close my ResultSet and close my connection. The datatable that I perform the ResultSet on is now "locked". If I comment out this code, the datatable is no longer locked.

Why is my datatablle still locked? Is there a way that I can let SQLCe know that I'm done with it?

Thanks!|||

Unless you explicitly Dispose the objects you will have the DB locked as Finalize/Dispose may get scheduled any time depending on GC's love on the object.

Thanks,

Laxmi

|||

Laxmi, why if he has explicitly closed the connection is there still a lock on the db?

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports -
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
Thanks
Maybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks
|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:

> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted for
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>
>
|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports -
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
ThanksMaybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:
> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted for
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> > We currently have a adp project linked with a Sql server 7 running NT4.
> > We need to replace old server with a win server 2003 with SQL 2000 sp4.
> > When viewing tables, stored procedures from ADP project all objects have
> > (dbo) after them. I then run into problems when trying to run saved
> > reports -
> > the report is looking for the table name itself instead of tablename
> > (dbo).
> > It would be a lot of work to fix all of the reports.
> >
> > Thanks
>
>|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:
>> Maybe you should create a view to meet client tool demands:
>> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
>> so reports will access table1 through the view with the name they wanted for
>> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
>> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>> > We currently have a adp project linked with a Sql server 7 running NT4.
>> > We need to replace old server with a win server 2003 with SQL 2000 sp4.
>> > When viewing tables, stored procedures from ADP project all objects have
>> > (dbo) after them. I then run into problems when trying to run saved
>> > reports -
>> > the report is looking for the table name itself instead of tablename
>> > (dbo).
>> > It would be a lot of work to fix all of the reports.
>> >
>> > Thanks
>>
>>

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports
-
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
ThanksMaybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine
-
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:

> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted f
or
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote
in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>
>|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine
-
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:
>

ADODB Vesion 7.0.3300.0 in vb.net

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174626&SiteID=1

HTH

|||I was able to solve this without resorting to the bootstrap loader by adding a reference to the adodb resource in my Studio project, then selecting this reference, and changing it's 'Copy Local' property to true.

ADODB Vesion 7.0.3300.0 in vb.net

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174626&SiteID=1

HTH

|||I was able to solve this without resorting to the bootstrap loader by adding a reference to the adodb resource in my Studio project, then selecting this reference, and changing it's 'Copy Local' property to true.

ADODB Vesion 7.0.3300.0 in vb.net

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174626&SiteID=1

HTH

|||I was able to solve this without resorting to the bootstrap loader by adding a reference to the adodb resource in my Studio project, then selecting this reference, and changing it's 'Copy Local' property to true.

Adobe iFilter 5 & SQL Server 2000 full text indexing of PDFs?

hi all! i'm new and hoping you might help me - have searched E V E R Y W H E R E for this answer!!

--my ASP program is running on IIS box (call it "Lucy")

--my ASP program connects to SQL Server 2000 db on another box (call it "Linus") consisting of .doc, .xls, .PDF and the like

--all's well EXCEPT when PDFs are returned! sometimes browser gives blank screen instead of PDF <?> i figure iFilter will "cure" this.

--question:
WHICH SERVER should iFilter be installed on? Lucy (where IIS is running & ASP program is) or Linus (where SQL Server does its full text indexing)?

thanks in advance for any & all help!!
geekgirlYou will have to install IFilter on the box that runs index server. I am not sure why you should be worried about SQL server when you searching documents from a folder.

Are ur documents stored in a database?|||Originally posted by vibhu
You will have to install IFilter on the box that runs index server. I am not sure why you should be worried about SQL server when you searching documents from a folder.

Are ur documents stored in a database?

sorry, YES docs are stored directly in the SQL db. iFilter says to install it "where the index server is" -- but i don't know if that means the *** IIS Index Server*** or the ***SQL Server**** which is doing the full text indexing. <??>

Adobe Acrobat 8 vs. RS 2005 PDF Rendering

For the last two years I've had an application running in production which would generate and display its reports as PDF via RS 2005. It would take user parameters, render the report, and my application would take the PDF bytestream from RS and save it to a file on disk, then request Windows to open the file. This has worked very smoothly.

Recently there has been a push in my company to get Adobe Acrobat 8 deployed, and apparently the same PDFs which have been working all this time will cause Acrobat 8 to choke. By choke, I mean that Acrobat will load into memory but have some sort of problem with the PDFs, and never show up. The users, not realizing what is going on, will then ask for the same report again, causing two stalled instances of Acrobat to be opened now, etc.

Removing the new acrobat and replacing it with Acrobat 7 solves the problem. Long term, this is not a good solution however... Has anyone else had a similar experience? I've googled around and have come up empty.

I've seen Acrobat 8 act that when it cannot communicate properly with the Adobe updater. On a client that is experiencing that behavior, can you launch Acrobat 8 and manually ensure it is up-to-date?

Thanks.

|||Thanks Donovan,
I've passed this on to our tech support guys and will be doing a test later today myself on one of our spare workstations. I should be able to replicate the issue our users have seen - at which point I'll play with the Acrobat update options and determine whether they are the cause. I'll post a follow-up comment to this thread with the results.

ADO.NET SP throws ArgumentException after migrating to SQL 2005 Standard - UPDATE 2 - FIXED

I just migrated a database from SQL 2000 Developer Edition on W2k Server to a production machine running SQL 2005 Standard on W2k3 Server Standard. A Web Service was also migrated from .NET 1.1 on the development machine to .NET 2.0 on the production machine.

Since the migration, a stored procedure that worked on SQL 2000/.NET 1.1 now throws a System.ArgumentException: "No mapping exists from object type System.RuntimeType to a known managed proider native type."

This occurs with a date value - the stored procedure is filled from XML data the Web Service receives from the client program by a generic subroutine that recognizes the applicable table names and primary key column names, selecting the appropriate stored procedure to use, but may or may not have data to update for each field. I have tried explicitly setting the SQLDBType for the parameter as the parametercollection is filled, but still get the ArgumentException. I also tried changing the parameter type to varchar(20), and having the stored procedure convert it to datetime, but still got the exception.

I know the ultimate solution will probably be to build the data Web Service within SQL 2005, but what can I do to get this working until I have time for that conversion?

Thanks for any help.

Darrell Escola

UPDATE - I was able to use the stored procedures to manually update the database using data that should have passed through the web service, so the problem is within the web service on .NET 2.0 - I think 2.0 is much stricter on data types than was 1.1. The SPs create dynamic SQL that is then executed - I know this is not the best security, hence my eventual interest in creating an end-point within SQL 2005 to handle the data in a more secure manner.

Darrell

|||

I finally got the web service to update through the stored procedure. I was able to access the stored procedure by specifically casting the data types for each parameter using a subroutine similar to that shown below, but the web service was still throwing an exception on the output value type, which I use to get the actual primary key value for the new record, so the off-line client can properly map its dependent records to the proper key value before inserting those dependent records:

Dim newID As SQLTypes.SQLInt32

I added a subroutine to determine the data type for each column used in the update:

Private Sub AddSPParameter(ByRef cmd as SQLClient.SQLCommand, ByVal paramName as String, ByVal fieldValue as Object)

If paramName = "<stringColName1>" Or paramName = "<stringColName...>" Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQLString).DbType = DbType.String

ElseIf paramName = "<otherTypeColName1>" Or paramName.IndexOf("...") > 0 Or paramName.EndsWith("...") Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQL<otherType>).DbType = DbType.<otherType>

ElseIf ...

End If

This subroutine determines the column data type by matches on the column name, and then explicitly casting the parameter value to the applicable database data type.

I also had to explicitly cast the Return Value:

cmd.Parameters.AddWithValue("@.ReturnValue", Ctype(0, SQLTypes.SQLInt32)).DbType = SqlDbType.Int

cmd.Parameters("@.ReturnValue").Direction = ParameterDirection.ReturnValue

Lesson learned: Explicitly type all data values used.

Darrell