Hi everyone,
I just used the upsize wizard in Access to create a Access front end and SQL server 2000 backend with linked tables, but I am receiving an error that I can't figure out.
When I am trying to add a record into my 'Cases' linked table in the SQL server, I get a error saying: "Invalid object name" with -2147217865 as the error message.
Here is my code:
Dim testrecord As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb.1;" & _
"DSN=intelitrak;" & _
"UID=geir;PWD=test"
conn.Mode = adModeReadWrite
conn.Open
Set testrecord = New ADODB.Recordset
testrecord.Open "Cases", conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect -- the ERROR occurs here!!!
With testrecord
.AddNew
If Forms(stDocName)!NewReopen = "New" Then
!MasterID = NewID
IsReOpen = False
Else
....etc.
I looked all around for an answer, and I partially found a answer at Microsoft's website: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1
However, I do not know how to make this work. I tried to create a stored procedure, and test if that made any differance, but it still said it the it was a invalid object.
Can anyone help me?
GeirJust a shot in the dark...
What if you add dbo_ to the front of Cases|||That was not it.
I tried to put 'dbo_Cases' in my VBA code, but the same error happens.
Geir|||Sorry...mayb a post in the VB forum...
Do you have other code that connects?|||none of my code that involves adding new records to the database is working. The code that views the records, however, works perfectly.
Geir
Showing posts with label adding. Show all posts
Showing posts with label adding. Show all posts
Tuesday, March 20, 2012
Problems adding MS access datasource in VB studios 2005 enterprise
I am running Visual Studios 2005 enterprise edition and I am creating a mobile application to keep track of some receipts. The problem I'm currently having is that even though I can connect to the MS Access Database with Server Explorer that I'll be aquiring data from; Everytime I attempt to use the Data Configuration Wizard to add a new data source that namely the database I get this error message:
The new connection uses a data provider that is not supported. (end of message)
There is no username or password necessary to access this database and it is an Access 2003 database.
Any help would be greatly appreciated.
Just to make sure...
You did change the Data Source to:
Microsoft Access Database File (OLE DB)
Which will use the following .NET Data Provider:
.NET Framework Data Provider for OLE DB
I assume you did in order to select the database, but I just want to make sure.
And when you click the button "Test Connection", it gives you that error?
Regards,
Dave
|||
> mobile application to keep track of some receipts. The problem I'm
> currently having is that even though I can connect to the MS Access
> Database with Server Explorer that I'll be aquiring data from; Everytime
> I attempt to use the Data Configuration Wizard to add a new data source
> that namely the database I get this error message: >
> The new connection uses a data provider that is not supported. (end of
> message) >
> There is no username or password necessary to access this database and
> it is an Access 2003 database. >
> Any help would be greatly appreciated. >
>|||
I have no idea sir, I started the project as a smart device app, and in all the walkthroughs I have viewed nothing has been said about the Jet database engine being incompatible with mobile devices. If this is the case I'd like to know because this project is currently dependent on VS being able to connect with our Access database.
I'm not sure of the limitations, you're best asking at the SQL Mobile Forum.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1
Am moving this thread to there.
Here's some info on SQL Server 2005 Mobile Edition
http://msdn.microsoft.com/sql/mobile/default.aspx
Alex.
|||The JET database engine runs only on desktop and server versions of Windows. It's certainly possible to develop a mobile web app that talks to a JET database on the server, but if you need a local data store on the mobile device that will remain available when the device is not connected to the network, then you will not be able to use JET. Your alternatives - as I understand it - include SQL Server CE and XML. There is a database engine on the Pocket PC called 'Pocket Access' but it has very little to do with the desktop product, and there is no built-in support for 'Pocket Access' in the .NET framework. There is, however, a third-party component you could look at. There's an article on this at the URL below, and there's a link from the article to the vendor of the third-party component. Note that I haven't personally used this, and I don't know what plans, if any, Microsoft has to continue to support 'Pocket Access'. So this isn't a recommendation, just something that may be worth further investigation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road10222003.asp There's an interesting article on 'Data Access Strategies for the Microsoft ..NET Compact Framework' at the following URL. Although the article dates back to the .NET 1.0 timeframe, I believe it is still relevant. http://msdn.microsoft.com/mobility/understanding/articles/default.aspx?pull=/library/en-us/dnnetcomp/html/dataaccessstrategy.asp -- Brendan Reynolds Access MVP> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
> To: Mr. Reynolds
> I have no idea sir, I started the project as a smart device app, and in
> all the walkthroughs I have viewed nothing has been said about the Jet
> database engine being incompatible with mobile devices. If this is the
> case I'd like to know because this project is currently dependent on VS
> being able to connect with our Access database. > > > >
>|||Thank you for the information. Unfortunately I have a follow up question. If I were to make an intermediate SQL database to function as the go between for a SQL Mobile database and a Microsoft access database, is such a solution even possible? Our organization recently purchased VS 2005 on the assumption that it would be easy to use the .NET framework to develop a mobile app that would easily transfer data to an access database. Now with this new information its quite possible the data will go SQL Mobile -> SQL Database -> MS Access database.
Footnote:
The SQL Database would just function as a temporary store to check data generated from receipts made in the mobile database. Once the checks are done the corrected data would be passed to the MS Access database. Then the SQL database would wipe itself clean.
|||
Yes, but it makes no sense. To accomplish that you would have to purchase SQL 2000/2005 and pay CALs per device (or per CPU license). If that’s acceptable, simply get rid of Access and use much more powerful SQL you've paid for.
Alternative solution is to use Web Service to talk to Access database. Create couple WEB methods to talk to access and call them from device. Something like this should do:
DataSet GetAccessData(String command, …)
{
// Connect to Access DB here, execute command and populate data set with returned data.
return dataSet;
}
Bool UpdateAccessData(DataSet dataSet, … )
{
// Connect to Access DB here and update DB with data from Data Set.
return success;
}
|||To: IlyaThe enterprise edition that we purchased of VBS 2005 came with SQL server 2005 and I just installed it upon learning that I can't communicate directly with the Access database. I'm not sure if we still have to pay the CALS per device though.
If I used these web service methods where would I write them? In the upload and download portion of the code for the mobile app?
|||
I believe it comes with 5 CALs (to be verified).
These WS methods would run on desktop via IIS which would host WS. To create them, please click ‘Create/Web Site’ and choose "ASP.Net Web Service". You'll get project skeleton, simply add code you need and run it. Decorate added methods with WebMethod attribute. This is desktop project, so you could use anything desktop has to offer.
Calling WS from device pretty much looks like a normal function call. All necessary code would be created for you automatically as soon as you add Web Reference to your project.
|||i want to connecting Access database on PDA ?i am setting Access database on PDA..
can i do ?
and how i doing?|||How would one connect to an access database inside the web method?
|||
while the .NET Compact Framework has no APIs to leverage Access databases, there is an open source set of APIs that support Access at www.opennetcf.org
there are also some commercial libraries available - use Google Advanced Groups search on microsoft.public.dotnet.framework.compactframework and look for "Access Database"
Darren
Problems adding MS access datasource in VB studios 2005 enterprise
I am running Visual Studios 2005 enterprise edition and I am creating a mobile application to keep track of some receipts. The problem I'm currently having is that even though I can connect to the MS Access Database with Server Explorer that I'll be aquiring data from; Everytime I attempt to use the Data Configuration Wizard to add a new data source that namely the database I get this error message:
The new connection uses a data provider that is not supported. (end of message)
There is no username or password necessary to access this database and it is an Access 2003 database.
Any help would be greatly appreciated.
Just to make sure...
You did change the Data Source to:
Microsoft Access Database File (OLE DB)
Which will use the following .NET Data Provider:
.NET Framework Data Provider for OLE DB
I assume you did in order to select the database, but I just want to make sure.
And when you click the button "Test Connection", it gives you that error?
Regards,
Dave
|||
> mobile application to keep track of some receipts. The problem I'm
> currently having is that even though I can connect to the MS Access
> Database with Server Explorer that I'll be aquiring data from; Everytime
> I attempt to use the Data Configuration Wizard to add a new data source
> that namely the database I get this error message: >
> The new connection uses a data provider that is not supported. (end of
> message) >
> There is no username or password necessary to access this database and
> it is an Access 2003 database. >
> Any help would be greatly appreciated. >
>|||
I have no idea sir, I started the project as a smart device app, and in all the walkthroughs I have viewed nothing has been said about the Jet database engine being incompatible with mobile devices. If this is the case I'd like to know because this project is currently dependent on VS being able to connect with our Access database.
I'm not sure of the limitations, you're best asking at the SQL Mobile Forum.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1
Am moving this thread to there.
Here's some info on SQL Server 2005 Mobile Edition
http://msdn.microsoft.com/sql/mobile/default.aspx
Alex.
|||The JET database engine runs only on desktop and server versions of Windows. It's certainly possible to develop a mobile web app that talks to a JET database on the server, but if you need a local data store on the mobile device that will remain available when the device is not connected to the network, then you will not be able to use JET. Your alternatives - as I understand it - include SQL Server CE and XML. There is a database engine on the Pocket PC called 'Pocket Access' but it has very little to do with the desktop product, and there is no built-in support for 'Pocket Access' in the .NET framework. There is, however, a third-party component you could look at. There's an article on this at the URL below, and there's a link from the article to the vendor of the third-party component. Note that I haven't personally used this, and I don't know what plans, if any, Microsoft has to continue to support 'Pocket Access'. So this isn't a recommendation, just something that may be worth further investigation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road10222003.asp There's an interesting article on 'Data Access Strategies for the Microsoft ..NET Compact Framework' at the following URL. Although the article dates back to the .NET 1.0 timeframe, I believe it is still relevant. http://msdn.microsoft.com/mobility/understanding/articles/default.aspx?pull=/library/en-us/dnnetcomp/html/dataaccessstrategy.asp -- Brendan Reynolds Access MVP> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
> To: Mr. Reynolds
> I have no idea sir, I started the project as a smart device app, and in
> all the walkthroughs I have viewed nothing has been said about the Jet
> database engine being incompatible with mobile devices. If this is the
> case I'd like to know because this project is currently dependent on VS
> being able to connect with our Access database. > > > >
>|||Thank you for the information. Unfortunately I have a follow up question. If I were to make an intermediate SQL database to function as the go between for a SQL Mobile database and a Microsoft access database, is such a solution even possible? Our organization recently purchased VS 2005 on the assumption that it would be easy to use the .NET framework to develop a mobile app that would easily transfer data to an access database. Now with this new information its quite possible the data will go SQL Mobile -> SQL Database -> MS Access database.
Footnote:
The SQL Database would just function as a temporary store to check data generated from receipts made in the mobile database. Once the checks are done the corrected data would be passed to the MS Access database. Then the SQL database would wipe itself clean.
|||
Yes, but it makes no sense. To accomplish that you would have to purchase SQL 2000/2005 and pay CALs per device (or per CPU license). If that’s acceptable, simply get rid of Access and use much more powerful SQL you've paid for.
Alternative solution is to use Web Service to talk to Access database. Create couple WEB methods to talk to access and call them from device. Something like this should do:
DataSet GetAccessData(String command, …)
{
// Connect to Access DB here, execute command and populate data set with returned data.
return dataSet;
}
Bool UpdateAccessData(DataSet dataSet, … )
{
// Connect to Access DB here and update DB with data from Data Set.
return success;
}
|||To: IlyaThe enterprise edition that we purchased of VBS 2005 came with SQL server 2005 and I just installed it upon learning that I can't communicate directly with the Access database. I'm not sure if we still have to pay the CALS per device though.
If I used these web service methods where would I write them? In the upload and download portion of the code for the mobile app?
|||
I believe it comes with 5 CALs (to be verified).
These WS methods would run on desktop via IIS which would host WS. To create them, please click ‘Create/Web Site’ and choose "ASP.Net Web Service". You'll get project skeleton, simply add code you need and run it. Decorate added methods with WebMethod attribute. This is desktop project, so you could use anything desktop has to offer.
Calling WS from device pretty much looks like a normal function call. All necessary code would be created for you automatically as soon as you add Web Reference to your project.
|||i want to connecting Access database on PDA ?i am setting Access database on PDA..
can i do ?
and how i doing?|||How would one connect to an access database inside the web method?
|||
while the .NET Compact Framework has no APIs to leverage Access databases, there is an open source set of APIs that support Access at www.opennetcf.org
there are also some commercial libraries available - use Google Advanced Groups search on microsoft.public.dotnet.framework.compactframework and look for "Access Database"
Darren
Problems adding an article to an existing merge replication if owner is not dbo
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
Just some more info. It seems like the problem occurs when the snapshotagent
generates the bulk copy data for system table
ms_merge_contents_<<newtable>>. It seems like this procedure doesn't use the
right owner to generate the bcp file.
Stefan
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> schreef in bericht
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I created a new table on the publication database the owner of the new
> table is not dbo. We then called sp_addmergearticle to add the article to
> the publication, using the @.source_owner and @.destination_owner parameters
> to specify the owner is different from dbo, and the
> @.force_invalidate_snapshot parameter.
> When we start the snapshot agent, it prepares the newly added table for
> replication, and generates the necessary scripts. At one moment the
> snapshot agent stops with an error stating 'invalid object name' and the
> name of the new table. I think this occurs when the snapshot agent wants
> tot generate the bcp files.
> Inspecting the article's properties dialog, showed 'that source table
> owner' and 'destination table owner' are correct. And except for the
> tablename an article name the properties are no different to the
> previously published articles.
> I retried this for a table that has dbo as owner. This worked without
> problems.
> Does anybody experienced the same problems?
> Best regards,
> Stefan
>
>
|||I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs in merge replication system stored procedures. Try looking for my messages (end of last year) - you should find attached archive with those fixes. if you will not find it, I could re-post it in this thread (I have fixes for SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||OK - I've put them on the downloads section. I can't give you the precise link, other that request that you browse to www.replicationanswers.com, as this website is currently done on the cheap (soon to change providers so I can have absolute urls).
Rgds,
Paul Ibison (SQL Server MVP)
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message news:%23AaQjgHBFHA.2552@.TK2MSFTNGP09.phx.gbl...
Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
Just some more info. It seems like the problem occurs when the snapshotagent
generates the bulk copy data for system table
ms_merge_contents_<<newtable>>. It seems like this procedure doesn't use the
right owner to generate the bcp file.
Stefan
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> schreef in bericht
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I created a new table on the publication database the owner of the new
> table is not dbo. We then called sp_addmergearticle to add the article to
> the publication, using the @.source_owner and @.destination_owner parameters
> to specify the owner is different from dbo, and the
> @.force_invalidate_snapshot parameter.
> When we start the snapshot agent, it prepares the newly added table for
> replication, and generates the necessary scripts. At one moment the
> snapshot agent stops with an error stating 'invalid object name' and the
> name of the new table. I think this occurs when the snapshot agent wants
> tot generate the bcp files.
> Inspecting the article's properties dialog, showed 'that source table
> owner' and 'destination table owner' are correct. And except for the
> tablename an article name the properties are no different to the
> previously published articles.
> I retried this for a table that has dbo as owner. This worked without
> problems.
> Does anybody experienced the same problems?
> Best regards,
> Stefan
>
>
|||I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs in merge replication system stored procedures. Try looking for my messages (end of last year) - you should find attached archive with those fixes. if you will not find it, I could re-post it in this thread (I have fixes for SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||OK - I've put them on the downloads section. I can't give you the precise link, other that request that you browse to www.replicationanswers.com, as this website is currently done on the cheap (soon to change providers so I can have absolute urls).
Rgds,
Paul Ibison (SQL Server MVP)
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message news:%23AaQjgHBFHA.2552@.TK2MSFTNGP09.phx.gbl...
Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
Problems adding % sign to a number
Hi,
I have a stored procedure which outputs a number as decimal(5,1). I
would like to add a % sign to the end of this number, however when I do
this using CAST(x as VARCHAR)+'%' I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to numeric.
The following code works:
SELECT
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
END AS [Variance],
but when i change it to add the percentage sign (as follows) i get the
above error:
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
(CAST(
CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
AS VARCHAR) + '%')
END AS [Variance],
Can anyone shed any light onto this annoying problem. I have a feeling
that it may just be syntax related, but i've spent ages trying other
things without any success.
Many thanks,
ChrisChris
DECLARE @.dec AS DECIMAL(5,1)
SET @.dec=18.1
SELECT CAST(@.dec AS VARCHAR(10))+'%'
--OR
SELECT CAST(CAST((100-20)/2*100 AS DECIMAL(5,1))AS VARCHAR(10))+'%'
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||It's usually best to perform data formatting in application code rather than
in Transact-SQL. That approach is much more robust and scalable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||Thanks guys,
nested casts worked a treat
Chris
*** Sent via Developersdex http://www.examnotes.net ***
I have a stored procedure which outputs a number as decimal(5,1). I
would like to add a % sign to the end of this number, however when I do
this using CAST(x as VARCHAR)+'%' I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to numeric.
The following code works:
SELECT
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
END AS [Variance],
but when i change it to add the percentage sign (as follows) i get the
above error:
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
(CAST(
CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
AS VARCHAR) + '%')
END AS [Variance],
Can anyone shed any light onto this annoying problem. I have a feeling
that it may just be syntax related, but i've spent ages trying other
things without any success.
Many thanks,
ChrisChris
DECLARE @.dec AS DECIMAL(5,1)
SET @.dec=18.1
SELECT CAST(@.dec AS VARCHAR(10))+'%'
--OR
SELECT CAST(CAST((100-20)/2*100 AS DECIMAL(5,1))AS VARCHAR(10))+'%'
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||It's usually best to perform data formatting in application code rather than
in Transact-SQL. That approach is much more robust and scalable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||Thanks guys,
nested casts worked a treat
Chris
*** Sent via Developersdex http://www.examnotes.net ***
Monday, February 20, 2012
Problem with using a text data type.
I am using a 'text' data type in a table and am having trouble adding data to it. I'm using an MS Access ADP project front end for my forms. I want to copy and paste from a text editor such as NotePad to a 'text' data type field in a form. There appears to be a limit that the field will accept which is much below the 2GB size that is allowed for the field. I get the following error message: "The text is too long to be edited." Once I get this error I can add characters from the keyboard but eventually the field will not accept any more characters. Typing or pasting does nothing. I can delete. So I've hit a limit but I'm not sure if it is in Access or SQL Server.
Any help would be appreciated.
Dennis GeasanGo to design view on the form. Right click on your text box. Look at the properties. I believe there's a property there to set the max length.|||No such property exists on a 'text' data type field. I did some further testing. The field will take up to 64000 characters and then quit accepting any more. It looks like I will have to use special SQL commands to add additional data to this field. It does not behave the same as an MS Access Memo field and directly allow a large amount of text to be added. Further research indicates the SQL Server 'text' data type is a linked list and I will have to start working with pointers to be able to add more data. Ugly.
Dennis Geasan
Any help would be appreciated.
Dennis GeasanGo to design view on the form. Right click on your text box. Look at the properties. I believe there's a property there to set the max length.|||No such property exists on a 'text' data type field. I did some further testing. The field will take up to 64000 characters and then quit accepting any more. It looks like I will have to use special SQL commands to add additional data to this field. It does not behave the same as an MS Access Memo field and directly allow a large amount of text to be added. Further research indicates the SQL Server 'text' data type is a linked list and I will have to start working with pointers to be able to add more data. Ugly.
Dennis Geasan
Subscribe to:
Posts (Atom)