Error Saving View using Linked Server
-
I have a linked server to an AS400 database. I need to create a view that is a join between a table on the 400 and one in the local SQL DB. When I set up the view using the following:
SELECT *
FROM dbo.cfgStoreParameters INNER JOIN
OPENQUERY([AS400], 'SELECT Bigint(mcmcu) as CC, mcrp01, mcrp02, mcrp06, mcrp10 FROM CRPDTA.F0006') BU_Mstr ON
dbo.cfgStoreParameters.eBISStoreNumber = BU_Mstr.CC
it runs fine within the View design window but when I try and save it I get the following:
ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server] OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported by the provider.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDASQL'ItransactionJoi JoinTransaction returned 0x80070057: One or more arguments were reported invalid by provider.]
Can anyone tell me why this will execute but I can not save it for use? I'm stumped.
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
a wild guess, do both tables have a primary key or unique index?
-
The AS400 has a primary, the SQL doesn't, but this doesn't seem to bother the query is run in the View design screen (or Query Analyzer). Only when I try to save the view do I get the error.
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
Try using the Create View statement in Query Analyser instead of the View Designer. I have had quite a few similar occurrances where Enterprise manager validates syntax or connections incorrectly, but opens the view successfully when created in Query Analyser.
Must be a Microsoft Mystery
-
Thanks Daniel,
That worked just fine. Too obvious a solution for me to have thought of. Guess it's just a flaw with Ent.Mgr.
Don
PS - "Microsoft Mystery"? Isn't that a redundant phrase??
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
Not a mystery. The view designer simply has limited functionality as do most designers in the system. Witht he exception of DTS Designer, I never use them.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply