SQL Express - Automate query and save as text

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  )

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

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...) ?

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

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!

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!

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?

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

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.

The last post (http:#a35998847) is part of the solution, and should be accepted in addition.

Starting the automated closure procedure to implement the recommendations from the participating Expert(s).

ModernMatt
Experts Exchange Moderator

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

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.