SQL server accessed as file system?

Question

SQL server accessed as file system?

zach-435178

SSC-Addicted

Points: 463
More actions
August 15, 2007 at 2:16 pm

#176277

Hello all, I was wondering if there was any way to specify a path to SQL server table or DB like a regular path i.e. "C:\folder\path.txt"
I need this to specify where my records will be goin to and some pulling from, thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

What are you trying to accomplish with this?

You can't really address the databases and tables residing inside the database management system as though they were other computing objects, like files, unless you write some sort of mapping application between the two. The objects inside a database management system may be scattered over several different files on several different drives. Only the DBMS knows where they are and how to put them together in a meaningful way.

Some older systems, like DBase, that used the one-table-per-file idea might be used this way, but even then, the internal structure was known only to the application that used the DBase API, so addressing the files in any other way would not have been productive.

And then again, I might be wrong ...
David Webb

zach-435178 SSC-Addicted Points: 463 More actions · Answer 2

Well I am using it in VB6 code to open a recordset. I am doing so using a DAO reference(I know dead technology, but I think I can make it work).

I am thinking something along these lines:

dim connect as PrivDBEngine

dim db as dao.database

newdb = connect.OpenDatabase(***This is where I want to put the SQL address string***)

'Do code here

I am using vb6 code that I have semi-converted to VB.NET

Hope that helps

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 3

You could try this Zach - just replace the variables with literals for testing, some security issues with the code (you need a password in plain text somewhere, I just have a low privilege user for these connections),but it's flexible and it works.

Function Get_Connection() As ADODB.Connection

Dim Cn As New ADODB.Connection

Dim CS As String

'''On Error Resume Next

CS = Get_Connection_String(frmSetup.cboServerType.ListIndex, frmSetup.txtServerName.Text, frmSetup.txtUserID.Text, frmSetup.txtPassword.Text, frmSetup.txtDatabaseName.Text)

Cn.ConnectionTimeout = 5

If CS = "OFFLINE" Then

Cn.Close

Else

Cn.Open CS

''Msgbox CN.State

End If

Set Get_Connection = Cn

End Function

Here's an example where I'm using the ADO connection to retrieve a list of stored procedures :

Sub Get_ProcedureList()

On Error GoTo 1

Dim cmd As New ADODB.Connection

Dim rs As New ADODB.Recordset

If Not frmSetup.cboDataSource.ListIndex >= 0 Then Exit Sub

frmSetup.cmbProcList.Clear

Select Case frmSetup.cboDataSource.List(frmSetup.cboDataSource.ListIndex, 1)

Case "SQL_Server" ' SQL Server

cmd.Open Get_Connection_String(frmSetup.cboServerType.ListIndex, frmSetup.txtServerName.Text, frmSetup.txtUserID.Text, frmSetup.txtPassword.Text, frmSetup.txtDatabaseName.Text)

Set rs = cmd.Execute("SELECT o.name FROM dbo.sysobjects o where (OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1) and o.name not like N'#%%' and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 order by o.name")

Do While Not rs.EOF

frmSetup.cmbProcList.AddItem rs(0)

rs.MoveNext

Loop

rs.Close

cmd.Close

HTH

Rich

zach-435178 SSC-Addicted Points: 463 More actions · Answer 4

I think this maybe what I need to do, but I do not see where the Get_Connection_String() function came from.

Please elaborate on your code a little. I think if I can convert it to VB.Net then it will definitely of some use to me, thanks for your help.

Also, what are your text boxes holding(excuse my ignorance), I am little confused on what to put in my line:

Dim connect As New dao.PrivDBEngine

Dim

newdb As dao.Database

newdb = connect.OpenDatabase( <-----here is my problem----> )

Zach

zach-435178 SSC-Addicted Points: 463 More actions · Answer 5

Ok I had to re-work my structure but I got it, thanks all

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Using VB 6

In Project References include:

Microsoft OLE DB Service Component 1.0 Type Library

Input the following code in the form open sub

Dim Cnn_String as String

Set objMSDAC = New MSDASC.DataLinks

Cnn_String = objMSDAC.PromptNew

Cnn.ConnectionString = Cnn_String

Run it and either set a break point or print out the Cnn_String.

If you are familiar with VB.Net you will immediately recognize what the wizard displays when constructing a connection string.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 7

Sorry Zach - I wasn't entirely clear when I said switch variables for literals, all Get Connection String does is populate the connection string from another form (giving you a dynamic connection based on form input), but I guess you've got that sorted now. Just send me a message if you want more code....

Richard