Connecting to a web-based sql server database

[George1111]

I have a Remote Desktop Session which allows me to run Excel 2010 and through the DATA Options in Excel, select a connection to an SQL Server Database.

The connection allows me to select ANY or ALL Tables in the database and Export them to Excel - I have all permissions while in RDP to do this

The problem is that I can only export the data to Excel, which means that if I want the data from a few tables I am basically manually driving the export, which can be very lengthy

Can anyone suggest another way to get to the data (other than Excel) so that I am not manually doing the operation step by step every time.

Ideally I set up a connection without going through RDP and write some code to achieve the extraction automatically

Many Thanks

[Alsvha]

I'm unsure what you ask?


You can connect to a SQL Server instance using SQL Server Management studio, if there's an open connection to it (needs to be exposed via an IP / Port number and have access via the firewall).

You can make an application or a web application and install it on the server on your RPD and use that to connect to the SQL Server instance. If doing a web application, you can then (if that's exposed via the IIS) access that and use that to extract data.

[George1111]

Unfortunately I am not able to install my software on the server

[dglienna]

Put the files on a LIVE SKY DRIVE, and you can view them online.

[Alsvha]

Unfortunately I am not able to install my software on the server

Management Studio does not need to be installed on the server. It just needs to be able to connect to the server on the SQL Server port.

[George1111]

The main problem is that on one hand I have full access and permission to read all the tables in a 30GB Hosted SQL Database while on the other hand, I am only allowed to read the data via Excel 2010

Herein lies the problem - some of my tables have more than 1,000,000 rows so downloading a Table to excel is out of the question (just rejects the download)

So what I am trying to do is find some other way to get to the data

I can't use my software as the I can't install it on the hosted server

What I really need is the ability to run an SQL Query in the allowed connection so I can filter out what I really want (not 1,000,000 + Rows)

I will try to see if an Excel Forum may be of help

(Really I would rather not be using Excel at all as this is just a pain - already I am having problems reading xlsx files in VB6 as my Microsoft Office Compatibility software just doesn't want to work - even just opening an xlsx file using Excel 2000 won't work)

Here are the Connection Strings which allow me to get to the data through Excel

CONNECTION STRING

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ABC_XYZ;Data Source=SERVER_ABC33;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ABC14;Use Encryption for Data=False;Tag with column collation when possible=False

COMMAND TYPE

Table

COMMAND TEXT

"ABC_XYZ"."dbo"."XYZ$Customer"

The above is how you set these up in Excel 2010 Data Connections

There is an option to Use COMMAND TYPE = SQL (Rather than Table) but I am then not clear as to how one frases the COMMAND TEXT

I tried a simple SQL Select Statement but when running the connection it just said that it could not find the Stored Procedure

[George1111]

I've worked out how to get the SQL Query running now (It was a simple syntax problem)

Still would prefer not to work in Excel though

[Alsvha]

<snip>
So what I am trying to do is find some other way to get to the data

I can't use my software as the I can't install it on the hosted server
<snip>

Have you tried connecting to the SQL server with SQL Management Studio?
It does not need to be installed on the server but can be installed on a client. It just needs to be able to connect to the server IP/Port number.