Hello,
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
SylviaTry specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegroups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:\')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegroups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>|||That did it! Thanks much - it's just the kludge I needed to get past
this.
Tuesday, March 27, 2012
Accessing lower level members
Hi All,
returns all members of State Provinces which have USA as its parent.
I have used .Children function to retrieve members of dimension at one
level below the current level of dimension. For example (using FoodMart
2000 and AS 2000)
Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( [Customers].[Country].[USA].Children ) on rows
>From sales
This query returns me Children of member Country which is USA. Itreturns all members of State Provinces which have USA as its parent.
Can I access or retrieve all members of City with Country member being
USA?
I am building a web application where user applies filters. This is
necessary because if user wants to view sales data about City which
belongs to country USA.
Many thanks in advance.
Raghu
Hi Raghu. Yes, you can retrieve members of City where the Country is USA. Use the MDX DESCENDANTS() function. Change your query to the following:
Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( DESCENDANTS([Customers].[Country].[USA], [Customers].[City]) ) on rows
From sales
Here's a link to the BOL description of the DESCENDANTS() function:
http://msdn2.microsoft.com/en-us/library/ms146075.aspx
Good luck - Paul Goldy
|||Thanks Paul.
This solved it.
Raghu
Accessing local temporary tables
I've been able to get the local temporary table name from sysobjects, but I still can't select from it.
for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.
I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.
I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.
Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.
They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.
for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.
I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.
I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.
Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.
They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.
Accessing linked servers dynamically
We have a view in one database that consists of the union of all the
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!
Accessing linked server
Is there any way to access a linked server without using four-part naming?
My problem is that I am trying to find a way to connect to an access database on a 64 bit system (there is no 64 Jet OleDb provider) and I have to run in a 64 bit process so no WoW solutions will work for me 
I am looking into trying to use a Sql Linked server but am trying to find a workaround without having to re-write all our queries to use four-part naming.
Is there someway to configure a connectionstring to default to executing against a linked server?
Any help or suggestions would be welcomed.
You can create a synonym (if your server is SQL 2005) for a four-part name.
See CREATE SYNONYM.|||Brilliant thanks for that Anton,
Had a bit of a play and it seems to work nicely. I guess I will run into trouble if any of the queries contain VBA expressions but that gets me a long way.
I am going to make a dumby Sql Database that contains no objects only the synonyms for the tables and views that I need in the Access database, then I can create a connection to that dumby database for any of the Jet work I need to do.
As a thought: I guess given that the synonyms are entities of the Sql database then preprossing will occur at the Sql Server, therefore it is unlikely that you could configure seemless passthrough style execution (in terms of the Sql dialect to use) to the Jet Linked server because Sql will always want its own dialect rather than the Jet dialect.
For example if I wanted a PIVOT/Cross Tab query, I would need to decide to send a request to a precompiled Jet query that contained a 'TRANSFORM' statement or send a Sql String with the Sql Server Style 'PIVOT....FOR...' Statement. Would this be right?
Thanks
Simon
Accessing Linked Excel Server
I created a linked Excel server that is stored in a SQL2000 database.
I can run the following from the SQL server with no problem.
Select * From CSCNEDI...EDI$
When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Thanks
David Davis
Schuette Inc.
Hi, David,
The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:
== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again
== If you are using NT authentication, try using SQL authentication to see if this changes the effect
== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Accessing jobs in EM
Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorThere is no supported way to do this with Enterprise
Manager. The proxy account doesn't really come in to play
here. The system stored procedures involved in displaying
the job info in Enterprise Manager have checks for job owner
or sysadmin server role membership.
-Sue
On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Hello!
>
> I would like to allow non-admin users seeing all jobs (including ones
>they do now own) in Enterpise Manager.SQL Profiler displays execution of
>exec msdb..sp_help_job when querying job list. According to BOL: ' A user
>who is not a member of the sysadmin fixed role can use sp_help_job to view
>only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't b
e
>of any help in this case. Is this possible for non-admin users to see all
>jobs?
>
>Thanks,
>Igor
>|||Thanks,Sue. I have found another way:
1.. Grant access to MSDB
2.. Add users to the member of TargetServerRole.
Regards,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
> There is no supported way to do this with Enterprise
> Manager. The proxy account doesn't really come in to play
> here. The system stored procedures involved in displaying
> the job info in Enterprise Manager have checks for job owner
> or sysadmin server role membership.
> -Sue
> On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>|||Okay but just remember it's not supported though and how
this works with this role depends on what service pack you
are on.
-Sue
On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Thanks,Sue. I have found another way:
> 1.. Grant access to MSDB
> 2.. Add users to the member of TargetServerRole.
>Regards,
>Igor
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
>|||Thanks a lot,Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5f5dv0d14fq7ajas3r6qq577is20d0gn57@.
4ax.com...
> Okay but just remember it's not supported though and how
> this works with this role depends on what service pack you
> are on.
> -Sue
> On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorThere is no supported way to do this with Enterprise
Manager. The proxy account doesn't really come in to play
here. The system stored procedures involved in displaying
the job info in Enterprise Manager have checks for job owner
or sysadmin server role membership.
-Sue
On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Hello!
>
> I would like to allow non-admin users seeing all jobs (including ones
>they do now own) in Enterpise Manager.SQL Profiler displays execution of
>exec msdb..sp_help_job when querying job list. According to BOL: ' A user
>who is not a member of the sysadmin fixed role can use sp_help_job to view
>only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't b
e
>of any help in this case. Is this possible for non-admin users to see all
>jobs?
>
>Thanks,
>Igor
>|||Thanks,Sue. I have found another way:
1.. Grant access to MSDB
2.. Add users to the member of TargetServerRole.
Regards,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
> There is no supported way to do this with Enterprise
> Manager. The proxy account doesn't really come in to play
> here. The system stored procedures involved in displaying
> the job info in Enterprise Manager have checks for job owner
> or sysadmin server role membership.
> -Sue
> On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>|||Okay but just remember it's not supported though and how
this works with this role depends on what service pack you
are on.
-Sue
On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Thanks,Sue. I have found another way:
> 1.. Grant access to MSDB
> 2.. Add users to the member of TargetServerRole.
>Regards,
>Igor
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
>|||Thanks a lot,Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5f5dv0d14fq7ajas3r6qq577is20d0gn57@.
4ax.com...
> Okay but just remember it's not supported though and how
> this works with this role depends on what service pack you
> are on.
> -Sue
> On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>
Subscribe to:
Posts (Atom)