Get a list of files a SQL Server instance can access

I'd like to be able to present my user with a tree view of the devices and
directories a remote SQL Server machine can access.
What I need to do is what Enterprise Manager can do: if I connect to a SQL
Server database on a remote machine to do a backup, when I come to select a
filename the browse button shows me a view of devices and directories on the
remote machine.
Is this a private trick reserved to EM or is there a way to do it with
SQL-DMO?
Thanks in advance for any help
GrahamAt the TSQL level, EM uses some extended stored procedures. Run Profiler and
see what they are. Not
documented, use at own risk etc.
I'm not sure whether these procedures are available through DMO, though. It
would be great to have a
DMO trace tool, btw. A quick check in BOL didn't show anything obvious. The
closest I could come is
the EnumDirectories method, perhaps worth using as a starting point?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:eVG$wvUCFHA.3976@.tk2msftngp13.phx.gbl...
> I'd like to be able to present my user with a tree view of the devices and
directories a remote
> SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to a SQ
L Server database on a
> remote machine to do a backup, when I come to select a filename the browse
button shows me a view
> of devices and directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with SQL
-DMO?
> Thanks in advance for any help
> --
> Graham
>|||Graham Morris wrote:
> I'd like to be able to present my user with a tree view of the
> devices and directories a remote SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to
> a SQL Server database on a remote machine to do a backup, when I come
> to select a filename the browse button shows me a view of devices and
> directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with
> SQL-DMO?
> Thanks in advance for any help
> --
> Graham
-- To query available drives
Execute master..xp_availablemedia 2
-- To query directories
Execute master..xp_subdirs N'C:'
David Gugick
Imceda Software
www.imceda.com|||Thanks - just what I need.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uIERzKWCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Graham Morris wrote:
> -- To query available drives
> Execute master..xp_availablemedia 2
> -- To query directories
> Execute master..xp_subdirs N'C:'
> --
> David Gugick
> Imceda Software
> www.imceda.com

Get a list of files a SQL Server instance can access

I'd like to be able to present my user with a tree view of the devices and
directories a remote SQL Server machine can access.
What I need to do is what Enterprise Manager can do: if I connect to a SQL
Server database on a remote machine to do a backup, when I come to select a
filename the browse button shows me a view of devices and directories on the
remote machine.
Is this a private trick reserved to EM or is there a way to do it with
SQL-DMO?
Thanks in advance for any help
Graham
At the TSQL level, EM uses some extended stored procedures. Run Profiler and see what they are. Not
documented, use at own risk etc.
I'm not sure whether these procedures are available through DMO, though. It would be great to have a
DMO trace tool, btw. A quick check in BOL didn't show anything obvious. The closest I could come is
the EnumDirectories method, perhaps worth using as a starting point?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:eVG$wvUCFHA.3976@.tk2msftngp13.phx.gbl...
> I'd like to be able to present my user with a tree view of the devices and directories a remote
> SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to a SQL Server database on a
> remote machine to do a backup, when I come to select a filename the browse button shows me a view
> of devices and directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with SQL-DMO?
> Thanks in advance for any help
> --
> Graham
>
|||Graham Morris wrote:
> I'd like to be able to present my user with a tree view of the
> devices and directories a remote SQL Server machine can access.
> What I need to do is what Enterprise Manager can do: if I connect to
> a SQL Server database on a remote machine to do a backup, when I come
> to select a filename the browse button shows me a view of devices and
> directories on the remote machine.
> Is this a private trick reserved to EM or is there a way to do it with
> SQL-DMO?
> Thanks in advance for any help
> --
> Graham
-- To query available drives
Execute master..xp_availablemedia 2
-- To query directories
Execute master..xp_subdirs N'C:\'
David Gugick
Imceda Software
www.imceda.com
|||Thanks - just what I need.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uIERzKWCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Graham Morris wrote:
> -- To query available drives
> Execute master..xp_availablemedia 2
> -- To query directories
> Execute master..xp_subdirs N'C:\'
> --
> David Gugick
> Imceda Software
> www.imceda.com
sql

Get 'DB Error 10083' with SQL Server 2000

Setup: SQL Server 2000 sp3 on a Windows 2000 machine, transactions running through an ODBC connection running SQLSRV32.dll version 2000.86.1830.00

Transactions: Being generated via a Citrix server (MetaFrame Presentation Server, Enterprise Edition 4.0(Build 2198), Service Pack 2005.04.... This sits on top of an install of Windows 2003 terminal server installaion.

Error: Appears to only be happening on a ADD(Insert) ....

Any thoughts on this would be a big help.

Dave

Is there any more to the error message? I can't find reference to error 10083|||

Here is the exact message I received...

Db Error: 10083

"Cursor statement generated no results.

No changes made to database.

Insert Into Test (testid, testdesc,sequence,createdt,creatorid,lastmodifieddt,modifierid,testinstances,allowinstances,activityid,worklisttable,qutoqc) Values ('999','Test Test',0,'10-31-2006 9:22:30.253','SysAdmin','10-31-2006 9:22:30.253','SysAdmin',1,'N',0,'N','N')

The above highlighted text was the exact message...

Thanks-David

|||

This is not a SQL Server error message. You need to check your data access layer. Here are few guesses on what the problem is:

1. You are issueing insert statement but expecting a result set to be returned. SQL Server will return some result if SET NOCOUNT OFF (in which it will be number of rows affected) or if the insert trigger returns results. In other cases, if SET NOCOUNT ON is set you will not get any results for the insert statement

2. You are doing the wrong thing from the data access layer and should use some direct execute mechanism that doesn't look for results from the command.

|||

Thanks for giving me some direction on this issue! I will do some testing to how I can resolve this issue in a good way.

David

|||

I have same this error message in some computer

I use MSDE with 2 clients. found this problem 1 client.

The error message show with this query

Db Error: 10083

"Cursor statement generated no results.

No changes made to database.

INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 1000, '001', 'test', '' )

This is query from another computer:

Begin Transaction

go

declare @.P1 int

set @.P1=180150003

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT sidecode ', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150003, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150003

go

Select prefix From SMId where name ='aum/AUM/emside'

go

Select max ( prefix ) From SMID Where Type =1 And charindex ( 'emside' , name ) > 0

go

Begin Transaction

go

INSERT INTO SMID ( Name , Prefix , Type ) VALUES ( 'aum/AUM/emside' , 1000 , 1 )

go

Commit Transaction

go

declare @.P1 int

set @.P1=180150004

declare @.P2 int

set @.P2=8

declare @.P3 int

set @.P3=1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT max(sidei', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150004, @.fetchtype = 1, @.rownumber = 0, @.nrows = 1

go

exec sp_cursorclose @.cursor = 180150004

go

declare @.P1 int

set @.P1=180150005

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT sideid FR', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150005, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150005

go

INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 1000, '001', 'test', '' )

go

Select InsertFlag From SMaudit_option Where brchid =1

go

SELECT GETDATE( ) As cf_server_time

go

Select prefix From SMId where name ='aum/AUM/smAudit'

go

declare @.P1 int

set @.P1=180150006

declare @.P2 int

set @.P2=8

declare @.P3 int

set @.P3=1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT max(audit', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150006, @.fetchtype = 1, @.rownumber = 0, @.nrows = 1

go

exec sp_cursorclose @.cursor = 180150006

go

declare @.P1 int

set @.P1=180150007

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT audit_id ', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150007, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150007

go

INSERT INTO SMAudit ( audit_id, audit_system, audit_screen, audit_datetime, audit_username, audit_action, audit_docuno, audit_columnid, brchid, audit_computername, audit_refid ) VALUES ( 139002, 1, 6, '5-3-2007 18:36:24.453', 'prosoft', 'I', '001', 1000, 1, 'AUM', 139002 )

go

Commit Transaction

go

|||

This's profiler from another computer with same form.

Begin Transaction
go
sp_tables @.table_name = 'emside'
go
sp_special_columns @.table_name = 'emside'
go
select emside.SideID FROM emside where 0 = 1
go
select timestamp from emside where 0 = 1
go
select * from emside where 0 = 1
go
SELECT sidecode FROM emside where 0 = 1
go
set rowcount 2
go
SELECT sidecode , emside.SideID , emside.SideID, emside.SideCode, emside.SideName, emside.SideNameEng, emside.Remark FROM emside WHERE (sidecode like '%AAA%' AND (LTRIM(sidecode) = 'AAA' OR RTRIM(sidecode) = 'AAA')) order by emside.SideID
go
set rowcount 0
go
Select prefix From SMId where name ='accdomain\sql2000/MORAGOTD/emside'
go
Select max ( prefix ) From SMID Where Type =1 And charindex ( 'emside' , name ) > 0
go
Begin Transaction
go
INSERT INTO SMID ( Name , Prefix , Type ) VALUES ( 'accdomain\sql2000/MORAGOTD/emside' , 2000 , 1 )
go
Commit Transaction
go
sp_tables @.table_name = 'emside'
go
sp_special_columns @.table_name = 'emside'
go
select emside.SideID FROM emside where 0 = 1
go
select timestamp from emside where 0 = 1
go
select * from emside where 0 = 1
go
SELECT sideid FROM emside where 0 = 1
go
set rowcount 2
go
SELECT sideid , emside.SideID , emside.SideID, emside.SideCode, emside.SideName, emside.SideNameEng, emside.Remark FROM emside WHERE sideid = 2000 order by emside.SideID
go
set rowcount 0
go
INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 2000, 'AAA', 'AAA', '' )
go
Rollback Transaction
go

|||

Did you ever find a resolution to this? We are getting this error in an application we are upgrading. The upgraded version uses SQL Server 2000 and the old version uses SQL Server 7. We are only seeing the error on some of our machines but not all. On two different machines where we have SQL Server 2000 client utilities installed we do not get the error. On another machine where I have SQL Server 7 client utilities or no client utilities exist we do get the error. I think there is a driver found in the 2000 client utilities that resolves the issue, but can't quite put my finger on which one it is. The application vendor seems to think it is our machine configuration.

Thanks,

I found a solution: Check your version of the ntwdblib.dll driver. Version 2000.80.194.0 fixed the issue.

Generate XML File in SQL Server 2005

I want to generate XML file which will be stored in C: of SQL Server 2005 machine. How to generate XML file in SQL Server 2005It is not recommended to access local discs of the SQL Server. It will require admin permissions and also it is a potential security vulnerability.
You can create XML using different techniques, starting from select... for XML to constructing XML using .NET integration, depending on data source for XML and required XML structure. Then you can store constructed XML inside database, or send it to client that will store it wherever needed.

If you want to get more help on constructing XML on SQL Server, please provide description on how do you get the data for XML, expected structure of XML, and samples of both.|||Create a assembly of ur own and use that assembly in SQL Server.

Don't forgot to save that assembly inside the \binn folder of ur sql server

http://www.sqldbatips.com/showarticle.asp?ID=23

:)