Hi
i want write a syntax in my storeprosedure or Query Analyzer that execute my sysntaxec in .sql file
forexample i have Mine.sql that contain "select * from my table" and i have a Store procedure
i want write a sysntax in my store procedure to execute my select in Mine.sql
how can i do that?
thanks
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
Thanks
Amit K Patel
Ok, I know this is a stupid newbie question, but I'm a stupid newbie,so it fits.I've got a query that writes a basic report, and I need to be able togive it to a user who doesn't even know how to spell SQL to run.Basically, I want her to be able to double click on a bat file on hercomputer, and it'll produce an output file based on what the queryfinds in the database.I've got the query in a file with a .sql extension, and it runs fine inQuery Analyzer. I saw an old post when searching the google archivesfor this newsgroup, which says to create a .bat file with a command inthe format:isql -Usa -Ppassword -ic: est.sql -oc: est.logThat doesn't work, though. My output file ends up having the error:DB-Library: Unable to connect: SQL Server is unavailable or does notexist. Unable to connect: SQL Server does not exist or network accessdenied.Net-Library error 52: ConnectionOpen (Connect()).I think I understand what the problem is, but I'm not sure what to doabout it. I'm not telling it where the SQL server is. I think theprevious conversation where I got this advice was assuming that thiswould all be done on the same machine as the database, when I'm tryingto run this from just any generic PC on the same network as thedatabase server. How/where do I tell the bat file what server that Iwant it to go to? Or am I completely off in thinking that's theproblem?--Richard
View 5 Replies View RelatedI have a query file, test.sql, in the root of my C drive. It is400,000 lines worth of insert into statements and is so big i cannotload in into management studio. How can I run this?
View 3 Replies View RelatedHi All.
Have a nice day.
I have one file and all tables and fields created in this file, this file name is "db1.sql", i downloaded this file from a internet demo. and programmer give this file to use in the asp.net code. but i cannot open this file.
I want to use this file in my sqlepress, how can i import or bring to my sqlexpress server and use it ?
Please can you help me how can i use it?
Thanks.
Zahyea.
I have a query something like this:
select "bcp EISAT_08_18.."+name +" OUT C:"+ name+".TXT -c -t -SCJACOBI"
from sysobjects
where type = 'U'
ORDER BY NAME
When I run the above query I want to output the result of the query to a file.
Can someone help me on that?
Hi all,
When I run a query in the sql query analyzer I need to write the output of that query in to another file. In Oracle its spool. Can someone help me on this please. Thank you!!!
Hi everybody,
I need a syntax in SQL Server 7.0. By using which I need to excecute a query from file. By using xp_cmdshell I am getting it. But whole results comes in a single column. I need the result set as it appears while executing the query. Can you help me in this. Any help will be appreciated.
Thanks in advance,
Gopakumar N.Kurup
HI
Im running a query using isql from the command line and outputting the results
to a file. Ideally I'd like to have the file in csv format so that it can be used by a client to import the results into their system.
I can run a query and output the results to a file but get i dont get the csv format I require. I can separate the columns using /s parameter but I also additional space within the column fields.
I know it is possible to create the csv format using the SQL query tool and ISQL_w but I was wondering if there was some parameter that I can call that will format the output like the format options in ISQL_w allows me to do.
Thanks in advance
Is there a way in SQL Server 2000 to output your query results to a file (example comma-delimited) in the SQL CODE ITSELF? I know you can check the option under Tools->Options->Results but I want to have it do it in the SQL code. Thanks Eric
View 1 Replies View RelatedDear All,
i need to put a query in a batch file to be easy to use by the user.
jusy only double click on it and it will work.
can any one help?
Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram G
I have the below query which gives me statement to apply the login name, SID and PWD and that I statement I can apply to Dr and secondary server. Incase of failover there will be no connection issue.My query is
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
[code]....
This query output I wanted in a sqlfile so that I can schedule a batch file or sqlcmd and apply the login detail from the new one inserted in the primary.
I need an example of a WQL query to use in the WMI Event Watcher task to watch for the presence of a file. The task has to succeed for either:
1) The file already exists when the task is run
2) The file shows up while the task is running (waiting)
Also the query has to reference a UNC path and file name. The file I'm looking for is just a flag file telling me a table is refreshed in our Data Warehouse.
I also need pointers on how to set the WMI connection manager. For the server, do I use the \servername of the fileserver? Do I need to include the folder the files are in (eg. \servernameflags)?
Thanks!
Kory
I have an sp that runs a query in xp_cmdshell/BCP combo that puts the output into an XML file (using for xml auto hint on the end of the query).
I personally feel that I should now (under SQL 2K5) be able to do this within a query, not going any where near xp_cmdshell or bcp, and then use SSIS in the final step to ftp it up to the clients site.
It builds a command string in the sp, then runs that command string via xp_cmdshell.
I guess what I'm asking is, is it possible to run a query within an SP with the output going to an XML file on the server automatically? AND that file to be a well formed XML file?
Any suggestions?
Hi all,
im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?
dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).
Hi,
I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ?
Thanking you...
Ok, so im pretty much finished writing my forum web page. However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file. Im guessing its simple enough but i cant get the syntax for actually performing any query. I already know the sql syntax like select * from all that stuff but how do i get do something like:
Dim x as integer = sqlQuery("Select count(*) FROM ...")
Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.
I have a report that is based on a query that is read from a .sql file and is executed in the stored procedure. I found this code on the internet. When I run the SP in Query Analyser it tells me that "2 rows have been affected". I don't know why I don't get the data. Any help??
Stored procedure:
CREATE PROCEDURE TimeTracker_Report_ClientNumbers2
AS
DECLARE @SQLQuery as NVARCHAR(4000)
BEGIN
CREATE TABLE #tmpQuery (Query NVARCHAR(4000))
BULK INSERT #tmpQuery FROM '\servernameQueriesReport_ClientNumbers.sql'
SELECT @SQLQuery = Query FROM #TMPQUERY
EXECUTE sp_executesql @SQLQuery
END
GO
and Report_ClientNumbers.sql contains the following query:
SELECT Name, ClientNumber FROM Companies WHERE Not ClientNumber IS NULL ORDER BY ClientNumber
I have come across manual queries being run daily and the results saved to a txt file on the network.
I basically want to set run these up a stored procedure and set up as a scheduled task to run daily.
Does anyone know if you can automatically save the results of a query/stored procedure to a text file on a network??
Many Thanks..
HI,
I have a small problem with SQL server 7.0 where I have to write the output of a query directly to a file without the user going to the menu and clicking on save. Scenario is:
A file is having a query the results of which has to be outputted to a file like in oracle where we have spooling function. Is there any functionality that mimic the oracle functionality where by I can get the output directly to a file?
thanks for your help in advance.
thanks,
Sravan.
Hi All
I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great.
Thanks in advance
Regards
Ram
Hi,
I'm using SQL Server 7.0.
I have a query (select * from table1) and I'd like to have the results of this query sent to a text file instead of the results windows when I run it from Query Analyzer.
Any suggestions?
Thanks in advance,
Darrin Wilkinson
Hi,
In 6.5 you could run a query and then make a flat file with whatever format
you wanted.
I cannot find that option in 7.0. Can someone please tell me where it is????
Thanks,
Dianne Watson
I want to write a small program (maybe just a stored procedure) that will save the results and column names of a simple query to CSV file. This will then be copied onto disk and distributed to user who'll use Excel or a similar package to open the CSV file.
Any suggestions as to the best way to achieve this? I was considering using a view and then BCP but I have problems then when trying to open the CSV file in excel (or other spreadsheet package). Doesn't like the datatypes and so on.
Is there a simple way to do this? And is it possible to save the results in such a way that Excel will choose the right datatypes for the columns. (not convert varchar's like '000122' to numbers.)
Hi everyone,
I'm trying to query an excel file and I get a mistake. The query is as follows:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')
and I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
I'm thanking any help that you can give.
Thanks,
Oscar.
Hi,
SQL2K+SP4
When I load up Query Analyzer and click File -> Open to open up a query
file, the open file dialog is always sorted by - i don't know
alphabetically.
How can I set the default to open up with files ordered alphabetically?
Thanks
Helena
I'm writing a script for a pending migration (2k enterprise to 2k5x64 enterprise). I've got about 325 or so databases, spanned across 3 instances that I need to migrate. My plan is to do a mass scripted detach of all DBs of the old boxes, copy the files (on a new domain) and then reattach all of the mdf and ldf files.
I've been writing a sql script that will scan a "staging" directory for each of the MDF and LDF files, then programmatically run sp_attachdb. The problem is that the mdf and ldf files weren't always a 1 to 1 naming convention. My predecessors stuck all sorts of things in the file names that make doing a simple scripted search pretty difficult. The disk paths are going to be different from server to server (simpler), so I need to move the files
Is there a way (using sp_attachdb or otherwise) that I could somehow scan each of the MDF files to get the path of the LDF? I can already do that with a .bak file using the RESTORE FILELISTONLY command, but I'm not sure how to do it like this. I'm figuring there has to be a way though, as SSMS seems to know where the LDF is if you try to attach an MDF.
Usually, Google does well, but I haven't been able to find anything as of yet. Any ideas?
I am trying to write a query to a file with a unique name and pipe "|" delimited. Problem is in 2012 the sp_cmd does not exist. I am currently using the code
set @filepat = 'C:Temp'
set @filename = 'test.txt'
set @sqlCommand = 'SQL_CMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" - "' +@filepath + @filename + '" -h-1'
EXEC master..xp_cmdshell @sqlCommand
hi
how can read file size in sql query
How can I export my query results to PDF file?
View 3 Replies View Relatedhow to output txt file in query by simple way ? This query have error.
Select * from invoice
into 'c:abc.txt'