[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Windows Server SQL via IIS - dump db contents

I'm a LAMP/MySQL person.  I've barely dealt with IIS in the past, but never .asp and SQL
I have a new client that i'm converting from SQL to MySQL -- that part is okay.
I DO have access to the FTP account and have downloaded all the .asp code files, etc.

I'm hoping there's a way that i can upload a canned .asp file and use the connection string that i amazingly found in the format  dsn=xxx;uid=yyy;pwd=zzz;
in order to dump the db structure and contents in SQL format, so that i can upload to create the structure and populate the db.

Does such a canned dump file exist (and is it safe?)  (i cannot risk altering anything in the db)
I would delete the file thereafter - the client is not friendly with the old host/maintainer-- i'm sure you know the situation :)

I use DW CS5 which opens the .asp files VERY nicely, and the code is quite readable even tho i've never programmed in it.

Also, the host did not know if there was a dashboard or control panel for the account (the host is not good, thus the reason for the move). I thought if there was a dashboard i could perhaps find the equivalent of phpmyadmin that might have a dump-everything command.  but it appears that the account is barebones.

Once i get a dump, i should be home free to begin my work in PHP and MySQL.

Thus i'm seeking an .asp dump file that's all set to go (the more automatic it is, the better!)

thanks
0
willsherwood
Asked:
willsherwood
  • 15
  • 12
  • 3
2 Solutions
 
sammySeltzerCommented:
Can you please explain better what exactly you are trying to do, no pun intended at all.

Are you trying to export all SQL Server data to MySQL and use php to manipulate them?

OR

Are you looking to move all asp files into some server and individually rewrite in php?

I am asking so I can see if I can be of help.

thx
0
 
willsherwoodAuthor Commented:
i want to export the SQL database structure and contents and upload into MySQL on a LAMP server
and i will  write from scratch the PHP and queries (it's a simple search and advanced search)

my roadblock is that i don't have time to learn/research SQL and .asp in order to dump/export the structure and contents.

the alternative is to deduce what the fields and table structure is, and do a series of wildcard searches in order to display the contents (several thousand records, with  probably 30 fields each), and then write  a script to parse and re format into CSV or MySQL  INSERTs
0
 
sammySeltzerCommented:
Do you have access to the SQL box?

If yes, then this might work for you.

First, you download Oracle SQL Developer tool from here:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Once installation is complete, then go here:

http://djiang.wordpress.com/2007/02/27/using-oracle-sqldeveloper-to-access-sqlserver/

and follow the instruction to download and install jdbc driver and you will be able to export sql database into .csv files and go from there.

This is easy and straight forward and that's what I use.

But most important thing is whether you have access to the SQL Server box.

Let me know and I will work with you to make it happen.

If not, then even a script won't  help us here.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
willsherwoodAuthor Commented:
I have FTP access and the db connection string.
I'm not sure if that means i have access or not - sorry, i'm unfamiliar with SQL  (just know MySQL).
The hosting company is difficult to work with and i'm needing to walk on thin ice so they don't sniff that the client is moving away and shut them down (even tho they're paid up for 2 more months). They are answering only surface questions, and taking 2-3 days to do that.

I was hoping there'd be a "simple" script, similar to one of many "backup" scripts in PHP for MySQL that gets a list of tables, and then FOREACH dumps the content and structure.
For Oracle, i can sign up with them and download/install if there's no simpler way.
0
 
sammySeltzerCommented:
In that case, this script from this link.

Everything you need, including invoking the script is there.

http://www.codeproject.com/KB/database/SQLBackupLib.aspx

Let me know if it is any help.

0
 
Scott Fell, EE MVEDeveloperCommented:
Step 1 - find all the tables.  List ALL the tables you need and we get that by using where xtype=u

Step 2 - find all fields for each table

Step 3 - list the field names and data in a comma separated format.  This will make the screen look pretty but I would replace the <br> for vbCrLf which is a line feed and of course remove the <hr> and table names. But this gives you an idea of what you can do.

just replace <!--#include file=/Connections/YourConnection.asp" --> with the connection string you found on the other pages.    If the data is very large, you may not want to do all the tables at once.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file=/Connections/YourConnection.asp" -->
<%

' Step 1

Dim rsTables
Dim rsTables_cmd
Dim rsTables_numRows

Set rsTables_cmd = Server.CreateObject ("ADODB.Command")
rsTables_cmd.ActiveConnection = MM_conMSC_STRING
rsTables_cmd.CommandText = "SELECT * FROM sysobjects Where xtype ='u'" 
rsTables_cmd.Prepared = true

Set rsTables = rsTables_cmd.Execute
rsTables_numRows = 0
%>



<%
'Step 2 get all the fields for each table
Dim rsExport
if not rsTables.bof or not rsTables.eof then
do until rsTables.eof

	response.write("<hr>"&rsTables.Fields.Item("name").Value&"<br>")

%>

<%
step 3 list the field names and data for each table
Set rsFields_cmd = Server.CreateObject ("ADODB.Command")
rsFields_cmd.ActiveConnection = MM_conMSC_STRING
rsFields_cmd.CommandText = "SELECT * FROM ["&(rsTables.Fields.Item("name").Value)& "]" 
rsFields_cmd.Prepared = true

Set rsFields = rsFields_cmd.Execute
rsFields_numRows = 0

xcount=0
for each f in rsFields.Fields
   xcount=xcount+1
 next
ycount=0
for each f in rsFields.Fields
   ycount=ycount+1
   Response.Write(f.Name)
 	if ycount<xcount then 
		response.write(",")
	end if
next


do until rsFields.eof
	ycount=0
	for each f in rsFields.Fields
		ycount=ycount+1
		response.write(rsFields.Fields.Item(""&f.Name&"").Value)
		if ycount<xcount then
			response.write(",")
		end if
	next

rsFields.movenext
loop

%>

<hr>

<%
rsTables.movenext
Loop
end if
%>
</body>
</html>
<%
rsTables.Close()
Set rsTables = Nothing
%>

Open in new window

0
 
Scott Fell, EE MVEDeveloperCommented:
that line 33 above should have a single quote

'step 3 list the field names and data for each table
0
 
Scott Fell, EE MVEDeveloperCommented:
Another option I would try is to download server manager studio from microsoft and using the info in your connection string you should be able to see the entire database.  It will look similar to phpmyadmin.

If the host will let you get a back up of the database you can install it locally and preform an export through server manager studio.  

The code I did above I tried to use what you may see in dreamweaver and that will be the stealthiest way to grab what you need.
0
 
willsherwoodAuthor Commented:
thanks all i'll explore and give a try.
i don't have any interactive interface.  there is no dashboard and no equiv of phpmyadmin
thus i would need to comb thru about 20 files to make sure i had collected all the names of tables :(
unfortunately this is all inherited -- if it were MySQL, i would know my way around to do all this.

thanks
0
 
Scott Fell, EE MVEDeveloperCommented:
You can connect to the db via php if that is what you are used to.  http://www.webcheatsheet.com/PHP/connect_mssql_database.php.

also, if that db is very large, instead of printing to the screen you can use file system object to write a text file to the sever as long as you can get write permission to a folder.  

http://www.w3schools.com/asp/asp_ref_filesystem.asp
<%
dim fs,fname
set fs=Server.CreateObject("Scripting.FileSystemObject")
set fname=fs.CreateTextFile("c:\test.txt",true)
fname.WriteLine("Hello World!")
fname.Close
set fname=nothing
set fs=nothing
%>
0
 
willsherwoodAuthor Commented:
I am trying the free studio express, that seemed simplest.
Questions:
1. for the connection, what format does it want the SQL SERVER  name in?  i tried the account's domain name, and  "Cannot connect to  xxx.com"

2. is it possible, as in MySQL, that my connectivity IP address needs to be "signed in" to allow connections from this IP address?   Since there's no dashboard or control panel, what do i ask for from the host (assuming they'll respond) -  in MySQL it was called  "Remote Database Access Hosts"


thus without this permission, i may have to go back to the .asp script and web-based dumping.
0
 
Scott Fell, EE MVEDeveloperCommented:
You can put in the IP address of the sql server.   However, there is a good chance they only allow local connections.  If the host is one of the larger outfits with shared hosting then chances are  you just need the IP our full address of the sql server.  However, if the host is a private person sharing a dedicated server and is a little bit smart, then they probably have remote access turned off although they can permit specific IP's to get access.   I forgot about that when I made that suggestion.

Did you try running that code I published?  

If you have dreamweaver and everything is working, you can simply create a new asp vb page.  Then using the Application  tab, create a new recordset.  Dreamweaver will do this for  you. Click the plus sign, choose simple mode and choose the first table.  Call the recordset rs.

You should end up with this


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="file:/Connections/myconnection.asp" -->
<%
Dim rs
Dim rs_cmd
Dim rs_numRows

Set rs_cmd = Server.CreateObject ("ADODB.Command")
rs_cmd.ActiveConnection = MM_conMSC_STRING
rs_cmd.CommandText = "SELECT * FROM dbo.msc_tAgeGroup"
rs_cmd.Prepared = true

Set rs = rs_cmd.Execute
rs_numRows = 0
%>
<%
rs.Close()
Set rs = Nothing
%>

Next in the applications panel expand the recordset so you see each field.   Then just drag the first field to the page (use code view) so you end up with this:

<%
Dim rs
Dim rs_cmd
Dim rs_numRows

Set rs_cmd = Server.CreateObject ("ADODB.Command")
rs_cmd.ActiveConnection = MM_conMSC_STRING
rs_cmd.CommandText = "SELECT * FROM dbo.msc_tAgeGroup"
rs_cmd.Prepared = true

Set rs = rs_cmd.Execute
rs_numRows = 0
%>
<%=(rs.Fields.Item("ID").Value)%>
<%
rs.Close()
Set rs = Nothing
%>

Then where you see <%=(rs.Fields.Item("ID").Value)%> add a comma after the %> and do it for each field so you end up with something like this:

<%=(rs.Fields.Item("ID").Value)%>, <%=(rs.Fields.Item("field2").Value)%>, <%=(rs.Fields.Item("field3").Value)%>,<%=(rs.Fields.Item("field4").Value)%>

Then add a <%=vbCrL%> this adds a line feed.  Or you could just use a <br> tag.


<%=(rs.Fields.Item("ID").Value)%>, <%=(rs.Fields.Item("field2").Value)%>, <%=(rs.Fields.Item("field3").Value)%>,<%=(rs.Fields.Item("field4").Value)%><%=vbCrL%>


Lastly, add scripting to run through all rows

<%
if not rs.eof or not rs.bof then
do until rs.eof
%>
<%=(rs.Fields.Item("ID").Value)%>, <%=(rs.Fields.Item("field2").Value)%>, <%=(rs.Fields.Item("field3").Value)%>,<%=(rs.Fields.Item("field4").Value)%><%=vbCrL%>
<%
rs.movenext
Loop
end if
%>

This will give you a csv file.  And if you want to use field headings in the first row then



ID,Field1, Field2,Field3,Field4<br>
<%
if not rs.eof or not rs.bof then
do until rs.eof
%>
<%=(rs.Fields.Item("ID").Value)%>, <%=(rs.Fields.Item("field2").Value)%>, <%=(rs.Fields.Item("field3").Value)%>,<%=(rs.Fields.Item("field4").Value)%><%=vbCrL%>
<%
rs.movenext
Loop
end if
%>


Since there are only 20 tables, this could be manageable.  Then surf to that page and copy/paste the data.   If you have more then 1000 rows of data, it may be easier to write to a file as I explained before.  The code is not the best but it is how dreamweaver creates it..  


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="file:/Connections/myconnection.asp" -->
<%
Dim rs
Dim rs_cmd
Dim rs_numRows

Set rs_cmd = Server.CreateObject ("ADODB.Command")
rs_cmd.ActiveConnection = MM_conMSC_STRING
rs_cmd.CommandText = "SELECT * FROM dbo.msc_tAgeGroup"
rs_cmd.Prepared = true

Set rs = rs_cmd.Execute
rs_numRows = 0
%>

ID,Field1, Field2,Field3,Field4<br>
<%
if not rs.eof or not rs.bof then
do until rs.eof
%>
<%=(rs.Fields.Item("ID").Value)%>, <%=(rs.Fields.Item("field2").Value)%>, <%=(rs.Fields.Item("field3").Value)%>,<%=(rs.Fields.Item("field4").Value)%><%=vbCrL%>
<%
rs.movenext
Loop
end if
%>

<%
rs.Close()
Set rs = Nothing
%>
0
 
willsherwoodAuthor Commented:
thanks for your continued patience, i'm uploading the previous script now.
0
 
Scott Fell, EE MVEDeveloperCommented:
Make sure on line 33 you remove or comment out

step 3 list the field names and data for each table

should be deleted or commented with a single quote
0
 
willsherwoodAuthor Commented:
yes thanks, i commented it out.
i'm negotiating the Windows path for the connections.
the server keeps having random network errors.  it'll be good this client is moving away!


File attribute '/httpdocs/Connections/pmd.asp"' cannot start with forward slash or back slash.
0
 
willsherwoodAuthor Commented:
it's a path issue now.
i've tried all combinations of
slash and no slash at the beginning
httpdocs/Connections/pmd.asp
Connections/pmd.asp

the script dump file is in a parallel directory to  Connections/
in Linux, i would do  ../Connections
but that didn't work either
0
 
Scott Fell, EE MVEDeveloperCommented:
Where you see

<!--#include file

Change to

<!--#include virtual
0
 
willsherwoodAuthor Commented:
that fixed that, now:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Index.asp, line 12
0
 
Scott Fell, EE MVEDeveloperCommented:
Try the include virtual instead of include file as I show above.  Also are you sure the site starts with /httpdocs/Connections/pmd.asp?  

httpdocs is typically not a public folder and everything inside of httpdocs is public.  So the include file should be  /Connections/pmd.asp  You may need to update your dreamweaver site set up and in the remote section where it asks for the Host Directory instead of leaving it blank use /httpdocs/

Take a quick look at the current pages that are there now and see how it is set up.
0
 
Scott Fell, EE MVEDeveloperCommented:
Can you post the rest of the code so I can see what it is.
0
 
Scott Fell, EE MVEDeveloperCommented:
Dumb question but where I have, "MM_conMSC_STRING" that should be your connection info.  You can verify what is is by looking in the file Connections/pmd.asp  More then likely it will be MM_pmd_STRING
0
 
willsherwoodAuthor Commented:
the connection was fine:
and your MM_..     fix got me to the next step.  (sorry for step by step debugging;  i am in foreign territory)

i stumbled on it again in line 35 and substituted the same.

IT WORKED and spewed a ton of data, and then has this:
mls_PropMain_burl

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mls_PropMain_burl'.

Index.asp, line 39 

Open in new window

0
 
willsherwoodAuthor Commented:
Even what i got out will help tremendously, altho is there a way to QUOTE the field values?
the CSV syntax is polluted by commas within field contents.

0
 
Scott Fell, EE MVEDeveloperCommented:
My line 49 above change

  Response.Write(f.Name)

to

  Response.Write("""&f.Name&""")

then on my line 60

change response.write(rsFields.Fields.Item(""&f.Name&"").Value)
to response.write(""&rsFields.Fields.Item(""&f.Name&"").Value&""")


I am leaving for a few hours...you are almost there.  PHP and classic asp are somewhat similar in logic.
0
 
willsherwoodAuthor Commented:
super trying it now, when you have a chance,  it'd be great to resolve the above Line 35 error.
It'd be great to have this for monday mornng :)

you are very kind to help!!
0
 
willsherwoodAuthor Commented:
i used           Response.Write("""&f.Name&""")

and got

"&f.Name&","&f.Name&""&rsFields.Fields.Item("&f.Name&").Value&","&rsFields.Fields.Item("&f.Name&").Value&""&rsFields.Fields.Item("&f.Name&").Value&","&rsFields.Fields.Item("&f.Name&").Value&""&rsFields.Fields.Item("&f.Name&").Value&","&rsFields.Fields.Item("&f.Name&").Value&""&rsFields.Fields.Item("&f.Name&").Value&","&rsFields.Fields.Item("&f.Name&").Value&""&rsFields.Fields.Item("&f.Name&").Value&",     ETC.

and still the Line 39 error (i mistyped "35" above, the err mssg several back is accurate)
0
 
Scott Fell, EE MVEDeveloperCommented:
I'm sorry, I think I messed you up on the 2nd one.  I didn't really check it out.  I did double check this and it worked for me.



response.write("&quot;")
Response.Write(f.Name)
response.write("&quot;")

and

response.write("&quot;")
response.write(rsFields.Fields.Item(""&f.Name&"").Value)
response.write("&quot;")


When you get that other error, will you please paste all of your code so I can see what it is.
0
 
willsherwoodAuthor Commented:
good progress.
please see code i've accumulated at sherwoodphoto.com/clients/p along with error message
thanks again
0
 
willsherwoodAuthor Commented:
sorry, i thought i had finalized this
0
 
willsherwoodAuthor Commented:
thanks all!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 15
  • 12
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now