Creating DB from scripts: accessing

Showing posts with label accessing. Show all posts

Thursday, March 8, 2012

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:
> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > I am trying to use linked servers as a means of accessing files in an
> Access
> > database. I have unsuccessfully tried 2 methods:
> > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> server
> > for Access tables, unless the Access file exists on the same server as
> SQL.
> > This seems odd to me. Is this true? Can I create a linked server for an
> > .mdb file that exists on a different server? And can I do this using
> > sp_addlinkedserver?
> > 2) using OPENROWSET. I get an "Authentication failed" error message with
> > the following:
> > SELECT *
> > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp\z.mdb';'';'', Untitled)
> > Do I need to specify some kind of security? What am I missing?
> >
> > My knowledge on security issues is very limited. Any help on getting me
> in
> > the right direction would be appreciated. I am using SQL 8.00.194
> >
> > Thanks
> > Dion
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
> > I would recommend looking at this thread in the
> > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
We
> > just reviewed it there.
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > I am trying to use linked servers as a means of accessing files in an
> > Access
> > > database. I have unsuccessfully tried 2 methods:
> > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > server
> > > for Access tables, unless the Access file exists on the same server as
> > SQL.
> > > This seems odd to me. Is this true? Can I create a linked server
for an
> > > .mdb file that exists on a different server? And can I do this using
> > > sp_addlinkedserver?
> > > 2) using OPENROWSET. I get an "Authentication failed" error message
with
> > > the following:
> > > SELECT *
> > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > 'c:\temp\z.mdb';'';'', Untitled)
> > > Do I need to specify some kind of security? What am I missing?
> > >
> > > My knowledge on security issues is very limited. Any help on getting
me
> > in
> > > the right direction would be appreciated. I am using SQL 8.00.194
> > >
> > > Thanks
> > > Dion
> >
> >
> >|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:
> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won't
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > So the Access file needs to be either on the local drive, or on the same
> > server as sql, right? Thanks much for the thread.
> >
> > Dion
> >
> >
> > "Simon Worth" wrote:
> >
> > > I would recommend looking at this thread in the
> > > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
> We
> > > just reviewed it there.
> > > --
> > > Simon Worth
> > >
> > >
> > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > I am trying to use linked servers as a means of accessing files in an
> > > Access
> > > > database. I have unsuccessfully tried 2 methods:
> > > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > > server
> > > > for Access tables, unless the Access file exists on the same server as
> > > SQL.
> > > > This seems odd to me. Is this true? Can I create a linked server
> for an
> > > > .mdb file that exists on a different server? And can I do this using
> > > > sp_addlinkedserver?
> > > > 2) using OPENROWSET. I get an "Authentication failed" error message
> with
> > > > the following:
> > > > SELECT *
> > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > Do I need to specify some kind of security? What am I missing?
> > > >
> > > > My knowledge on security issues is very limited. Any help on getting
> me
> > > in
> > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > >
> > > > Thanks
> > > > Dion
> > >
> > >
> > >
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
> > The .mdb database file must reside on the server. data_source is
evaluated
> > on the server, not the client, and the path must be valid on the server.
> >
> > I didn't try it with the access db on the local drive. I assume that
won't
> > work, as it's counter to what BOL says. But try it out, and see if it
> > works.
> >
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > > So the Access file needs to be either on the local drive, or on the
same
> > > server as sql, right? Thanks much for the thread.
> > >
> > > Dion
> > >
> > >
> > > "Simon Worth" wrote:
> > >
> > > > I would recommend looking at this thread in the
> > > > microsoft.public.sqlserver.dts newsgroup with subject "Access and
SQL".
> > We
> > > > just reviewed it there.
> > > > --
> > > > Simon Worth
> > > >
> > > >
> > > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > > I am trying to use linked servers as a means of accessing files in
an
> > > > Access
> > > > > database. I have unsuccessfully tried 2 methods:
> > > > > 1) using sp_addlinkedserver. I am told that I cannot create a
linked
> > > > server
> > > > > for Access tables, unless the Access file exists on the same
server as
> > > > SQL.
> > > > > This seems odd to me. Is this true? Can I create a linked
server
> > for an
> > > > > .mdb file that exists on a different server? And can I do this
using
> > > > > sp_addlinkedserver?
> > > > > 2) using OPENROWSET. I get an "Authentication failed" error
message
> > with
> > > > > the following:
> > > > > SELECT *
> > > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > > Do I need to specify some kind of security? What am I missing?
> > > > >
> > > > > My knowledge on security issues is very limited. Any help on
getting
> > me
> > > > in
> > > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > > >
> > > > > Thanks
> > > > > Dion
> > > >
> > > >
> > > >
> >
> >
> >

Tuesday, February 14, 2012

Create view of inline function

Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.

I'm having trouble accessing functions through access. I've built the following function:

CREATE FUNCTION fnSTR_LEASESTATUS(@.TRS nvarchar(12))

RETURNS TABLE

RETURN

(

SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]

FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)

WHERE (((dbo.tblTRACT.STR)=@.TRS))

)

I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?

Thanks for your time.

You function has param @.TRS.

You could use it:

Code Snippet

SELECT * FROM fnSTR_LEASESTATUS('Your param value')

|||

Hi,

Let me try to address your concerns

There's no need to create a view of a table function, unless you want the view results somehow filtered or different than the function results.

To use the your table function simply SELECT * FROM fnSTR_LEASESTATUS(<put value here>)

If you want to create a view you need to have rights on the SQL Server 2005 database.

You are probably using an Access data project? Using these projects you are not able to just save queries as in regular Access database. With these projects you are connected directly to the server and therefore anything you save you are saving on the server.

You may want one project to create server objects like functions. Then have another regualr access database to save your queries (pass- through preferred)

|||

That allowed me to run it, but when I try to save it I get the message "Must declare the scalar variable @.TRS".

|||

Ok. I can run it in SQL Server, but how do I get to it in Access? I was hoping I could save it as a view and then link to it the way I have my other views.

Thanks.

|||

Oh, sorry. Forgot to tell you. I decided against ADP after reading on the Access user's group. So I build my views in SQL Server and then link to them.

Thanks.

|||To save your view in SQL Server you need to replace the variable reference, @.TRS, with an actual value. Once you have it saved in SQL Server, you can link like other view links|||And if you want to be prompted from Access, forget saving it as a view in SQL Server and try saving it as an Access query or some other way in Access

Thursday, March 8, 2012

creating a linked server

creating a linked server

creating a linked server

Tuesday, February 14, 2012

Create view of inline function

Creating DB from scripts

Blog Archive

About Me