Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL Express - Automate query and save as text

Hi,

I'm trying to automate a query on an SQL Express db, Not too sure where to start.

I've written the query and am using Oracle SQL Developer (with the MS SQL Express JDBC driver), to run the query manually.

I want to automate that query to run as a daily task, and save the output as a text file.  I don't know if there is way to automate this from within SQL Developer, so I started to try SQLCMD.

I can't seem to get the connection string correct though.

I'm connecting to another PC, using windows authentication.

I tried >sqlcmd -E -S host\sqlexpress

But get this error msg: HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively re
fused it.

Google offered that perhaps It's refused due not specifying the Port, so I tried:
>sqlcmd -E -S host\sqlexpress,1433

Same error.

It does not make sense to me that I can connect with SQL Developer, but not sqlcmd.  Is there a problem with my connection string?  Do I need to install something else on my PC?   Or should I use a different tool?

I do need to connect to a specific db called tempdata within SQLExpress, should I be including the specific db within the connection string as well?

Thanks!

0
TelMaco
Asked:
TelMaco
  • 7
  • 4
  • 2
  • +2
3 Solutions
 
jbvernejCommented:

SQLCMD is the good tool but it is SQL Express that is restricted to local access only.
By default, you CAN'T connect to SQL express from network, only local connection are allowed.

try your script from the same machine that runs SQL Express

Automated tasks are possible in the other SQLServer editions ( ie with buying true license - (SQL Server Standard, Workgroup, Entreprise) with their SQL Agent component  )
0
 
TelMacoAuthor Commented:
I don't have physical access to the other machine to test, it's in a different city.

I already access the SQL Express database remotely using SQL Developer.  I run the queries manually everyday,  so there is a way to connect remotely.

If SQL Developer or SQLCMD are not the way to go for automation though, could I set-up a connection through something else?  Like, ugg...Excel/Access?  Then I could write the VBA to automate, I just need to connect somehow.

Thanks



0
 
jbvernejCommented:
IMO, you can perfectly use SQLCMD remotely. But it seems you don't use the right syntax in your sqlcmd command, suited syntax to your remote SQl Express

SQLCMD doesn't use JDBC to connect to SQL but use  OLEDB layer, which, in turn, use either TCP NamedPipes, or TCP Socket (1433 port) usually
http://msdn.microsoft.com/en-us/library/ms188247.aspx 

I don't know how your SQL Developer / JDBC accesses to remote SQL, but it is quitely different from SQLCMD.

Try SQLCMD without -E option (windows authencation) but with -U user and -P password (SQL authentication), SQL authentication is a less restricted than integrated (no need to be in the same windows domain, firewall friendly, etc...)

which connection parameters do you use to connect JDBC to SQL (which connect string - server name, instance name, user/login, etc...) ?




0
Technology Partners: 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!

 
TelMacoAuthor Commented:
I use Oracle SQL Developer with a third party JDBC Driver, which enables it to somehow connect to SQLExpress.

Is this what you mean for connection parameters?  I copied it out of SQL Developer
jdbc:jtds:sqlserver://MyHOST:1433;instance=SQLEXPRESS
(I've replaced the actual server name with MyHOST)

I don't have a username / pw, it just uses windows authentication.

I get the same error when I tried >sqlcmd -S host\sqlexpress without the -E



0
 
jbvernejCommented:
ok, thanks for these info. here what I understand from what you wrote:
Summary :
- SQLServer Hotname: MyHost
- Connection type: TCP
- Instance Name = SQLEXPRESS, listening on 1433 TCP Port
- Authentication : windows integrated

try the following syntax:
sqlcmd -S tcp:MyHost,1433 -E

it should work now
0
 
TelMacoAuthor Commented:
Thank you thank you thank you!!!

I played with that a bit and finally came up with:

sqlcmd -S tcp:MyHost\SQLEXPRESS -E

Which actually connects, I honestly jumped out of my seat when I saw the "1>" on the next line.

I've now tested:
USE sampledb
GO
SELECT TOP 10 * from mytable

and that works perfectly!

Now that I have the connection, would you mind if I take a couple days to create the appropriate Batch, and SQL files to automate this?  Then I can bug you if anything minor pops up.  ( ;

Seriously...THANK YOU!




0
 
TelMacoAuthor Commented:
I'm having some difficulty with this part.  

Should I even use an .sql file?  or have it all part of the batch file?  There are different challenges depending on the approach.

If I use both BAT and SQL files like this:

sqlcmd -S tcp:MyHost\SQLEXPRESS -E -d mydb -o "Mydata.txt" -i test.sql

It runs at least, but I get a huge blank header, followed by a bunch of "--------------"
and then finally my results, but there are larges blank gaps between the results.

The SQL itself is this:

SELECT    '=Vlookup("' + user + '",vlook!temp_vlook,6,FALSE)' + '|' +
                 '=Vlookup("' + user + '",vlook!temp_vlook,5,FALSE)' + '|' +
                 username + '|' +
               user + '|' +
               order + '|' +
               product
FROM       tracker
WHERE     orderstart BETWEEN DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))-1 AND
                 DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

Now I've also tried doing this directly from the BAT file, resulting in different problems.

I used this in the BAT file:

sqlcmd -S tcp:MyHost\SQLEXPRESS -E -d Mydb -Q "SELECT username, user, order, product FROM tracker WHERE orderstart BETWEEN DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))-1 AND DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))" -o "Mydata2.txt" -s"|" -W -w 999

There is no CSV file required for this approach, and it displays the results pretty close to what I need

The issues here are that there is a row beneath the headers: -----|---------|---------|-------
I don't need that.

The real problem here is that I can't include my created columns such as:
 '=Vlookup("' + user + '",vlook!temp_vlook,6,FALSE)'
I think the "quotation" marks are messing it up, I'm not sure how I can fix that?

Thoughts?

Thanks!
0
 
jbvernejCommented:

ok, i see.
Is it the headers labels that you want to remove in the query result (= 1 line of labels + 1 line of'' -------')

have a look on:
"sqlcmd Utility"
http://msdn.microsoft.com/en-us/library/ms162773%28v=SQL.90%29.aspx

and try to use  the -h option with  "-h -1"  to remove header

0
 
TelMacoAuthor Commented:
still testing, this q got flagged as abandoned.  

I'm still working on testing the above solution, can it stay open for a few more days?
0
 
TelMacoAuthor Commented:
Hi,

Sorry for the delay - my hard drive got wiped I had to re-install a ton of stuff.

The -h -1 removed the header completely.  Not exactly what I needed, but close.

This is what it looks like with the headers:

Header_A  
--------------
data1
data2
...etc

now without:

data1
data2
...etc

I just wanted the "-------" line removed

With SQLplus I was able to add a PROMPT command and set my own header row, is there something similar I could do in SQLCMD?

I'm happy the keep things as they are, I just need to add a row at the top with my defined headers now
something like:
Header_A | Header_B | Header_C
data_1 | data_1| data_1
data_2 | data_2 | data_2
etc...

here's the command line I am using now:

sqlcmd -S tcp:MyHost\SQLEXPRESS -E -d mydatabase -o "outputfile.txt" -i sqlfile.sql -s"|" -h -1 -W

the s"|" separates the columns ( I chose "|" since couldn't use a comma b/c some feilds contain commas), the h-1 kills the headers, and the -W removes trailing spaces from the columns

0
 
QlemoC++ DeveloperCommented:
The only way I know of is to use a filter outside of sqlcmd:
sqlcmd -S tcp:MyHost\SQLEXPRESS -E -d mydatabase -i sqlfile.sql -s"|" -h -1 -W | find /v "---" > outputfile.txt

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for jbvernej's comment http:/Q_26963321.html#35428157
Assisted answer: 250 points for jbvernej's comment http:/Q_26963321.html#35438762

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
QlemoC++ DeveloperCommented:
The last post (http:#a35998847) is part of the solution, and should be accepted in addition.
0
 
ModernMattCommented:
Starting the automated closure procedure to implement the recommendations from the participating Expert(s).

ModernMatt
Experts Exchange Moderator
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Just for the record, I think I forgot Qlemo's comment in the split.

I must though also indicate that using -o<outputfile> won't generate that "---" line, if I am not mistaken.

CHeers
0
 
TelMacoAuthor Commented:
Thanks everyone, sorry I was out of town for a fre weeks, just catching back up.  Appreciate the help!  I learned a lot from jbvernej's comments, this was totally new for me.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now