++++ Automate Query +++++++

MS SQL SERVER

    Sponsored Links

    Next

  • 1. repository engine v3.0 or higher is required to run meta data browser
    repository engine v3.0 or higher is required to run meta data browser
  • 2. classroom setup
    The files needed are typically on the cd's that come with the MOC class. I have not attended any of those mentioned but in the past the student resource cd's contained all you needed. HTH -- Ray Higdon MCSE, MCDBA, CCNA --- "rodney" < XXXX@XXXXX.COM > wrote in message news:028401c36368$2d7448d0$ XXXX@XXXXX.COM ... > Hey all, > > I just took some Sql classes from a Microsoft partner > (class 2071, 2072, and 2073). I'm trying to recreate the > classroom setup but I'm not sure of all the details of a > client/server network. > > Does anyone have any detailed notes on setting up the > Microsoft classroom setting? Domain nwtraders and the > controller being London. > > It would be helpful if I can redo the labs at my own pace. > > Thanks in advance, > Rodney > >
  • 3. installing Enterprise and Standard on the Same Server
    I am building a fail over cluster and need to run SQL 2000 Enterprise and SQL 2000 Standard on the same server. Is this possible?
  • 4. Transfer SQL databases from one machine to another
    I am a newbie and have built a number of databases in SQL server from some SPSS files I have. I want to send the database to a colleague via EMail so he can take a look at it on his machine. I have sent him the ,mdf file but how does he load this into his system. There does not seem to be a load in from mdf option in Enterprise manager. Basically how do you transfer databases from one machine with SQL server to another? Cheers, Mike

++++ Automate Query +++++++

Postby V2lsZFBhY2tldA » Thu, 08 Apr 2010 00:34:01 GMT

Not a SQL guy .....

Want to schedule the query to run automatically -> import data to a file and 
email me.  Using SQL 2005 Express Edition with SQL Management Studio Express.

Advise Please.

Thank you

Re: ++++ Automate Query +++++++

Postby Rick Byham, MSFT » Thu, 08 Apr 2010 05:38:23 GMT

To run that query automatically entirely from within SQL Server you need the 
SQL Server Agent. But SQL Server Agent is not included in SQL Server Express 
Edition.
So you will have to create a Windows job that will connect to SQL Server and 
execute the statement.
There are a variety of tools you can use.
See How To Schedule Tasks in Windows XP 
 http://www.**--****.com/ 
I think PowerShell also has a scheduling system as well.
And the task will probably use sqlcmd or osql to connect to SQL Server and 
execute some set of statements. I hope this gets you started.
-- 
Rick Byham, MSFT
(Implies no warranty or rights)









Re: ++++ Automate Query +++++++

Postby Um9iZXJ0Sy4 » Sun, 11 Apr 2010 11:56:04 GMT

I second Rick's suggestion of PowerShell. I use it to perform tasks like this 
a lot. I have several reports/alerts that I have automated using PowerShell 
to issue a SQL query and then send an HTML formatted message.

Look at  http://www.**--****.com/ !








Similar Threads:

1.Automate Queries

What is the procedure to automate quiries? I have about a dozen that need to 
be run at end of each month. 


2.Automating Query

I am I am looking for the best method of automating an INSERT query in SQL
Server 2005

Code:
------------------------------------------------------------------------------

 'INSERT INTO SaltInvWhOpen                      (StockCode, Warehouse,
TrnMonth, TrnYear, OpenBalQty, OpenBalCost)SELECT     StockCode, Warehouse,
Month(GETDATE()) AS TrnMonth, YEAR(GETDATE()) AS TrnYear, OpenBalQty12,
OpenBalQty12 * UnitCost AS Expr1FROM         InvWarehouse  
------------------------------------------------------------------------------


In older versions I would have used a DTS package (It's been a long time and
I'd have had to use a book to guide me through it), but as that is gone now,
I am unsure where to even begin looking.

If someone could assist by giving me the current method, and dare I ask, walk
me through it, the help would be much appreciated.

Thank you!

Julia

3.Automate query to dump to CSV / Email

Hello,

I'm looking at trying to schedule jobs in SQL that will run a query on a 
database, dump the results to a file (preferably CSV) and email that file to 
specific people in our organization.

I have set up a recurring job that runs the query and outputs the 
information to a txt file, but that also provides the status of the job and 
other information.  Basically all I need is to grab the field names and 
information in those fields, in a flat file/csv format so it can be imported 
for another organization.  I know I can do the process manually by running my 
query and then selecting the resulting fields and saving the information as a 
CSV.  I'd like to automate this process as it needs to be run for 6 different 
queries daily.  Is there a better way to set this up?

Also, I am trying to setup SQL mail so the results can be emailed (if it's 
possible to set it up to attach a file as well, emailing results as text will 
work for now though) and although I have configured a SQL Server service 
account in Active Directory (2003) and set up a mailbox on our exchange 
server and also setup an Outlook profile on the SQL Server, I receive the 
following error when setting up the SQL Mail MAPI profile:

SQL Mail - Error 18025: xp_test_mapi_profile : failed w/ mail error 0x80040111

I get similar errors setting up the SQL Agent to email me alerts.

We're running SQL 2000 Enterprise, sp4 on a dedicated 2000 Server, sp4 in an 
ADS domain run on 2k3.  Our Exchange Server is 2k3, sp2.

-- 
Thanks, Jeff

4.Automated Query Output to CSV

Hi Mike,

Look at bcp utility in BOL. It should do the trick for the 
first part i.e. result set from SQL goes into a csv file. 

E.g. 

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY 
au_lname" queryout Authors.txt -c -Sservername -Usa -
Ppassword

Ricky

>-----Original Message-----
>Hello,
>
>I'm looking for a way to programmatically, in a stored 
>procedure, create a CSV file from a query result set.
>
>Here are the processing steps:
>
>1. Users submit an unknown combination of search criteria 
>and custom filters from a custom client application (not 
a 
>web browser).
>
>2. Stored procedures build the SQL on the fly based on 
the 
>info received in step 1. A series of SQL statements, 
which 
>cannot be known beforehand, are executed at the server.
>
>3. The final result set is transferred over the internet 
>from the server to the client workstation.
>
>Since result sets are sometimes huge, we don't want to 
>transfer them over the internet. We would prefer to 
>convert them to CSV first and transfer the smaller CSV 
>file instead. (We'd love it if we could also compress the 
>CSV with WinZip.)
>
>The client application can easily process the CSV file 
and 
>display the results to the user - if only we could create 
>a CSV file at the server.
>
>Any suggestions are much appreciated.
>
>Thanks,
>
>Mike
>
>.
>

5.Automated Query Problem

I need a query that will have the logic to increment 
settlement_batch_number by one.  I need the  settlement_batch_number 
field to be incremented by one daily not literally only in the query so 
I can use the SELECT statement for the incremented value. I have left 
some values out for security reasons (Below).

Any help would be appreciated.

Use database
GO

SELECT

	convert(char(11), value) RR,
         value,
	value,
         convert(char(8), value) SN,
         convert(char(25), value) value,
         isnull(convert(char(2), value), '  ') ID,
         convert (char (20), value)CardNumber,
         convert(char(20), value) Time,
         convert(char(2), value) RC,
         isnull(convert(char(2), value), '') HRC,
         convert(char(20), value)Message,
         convert(char(7), value) STAN,
         convert(char(12), value) Amount,
	value


FROM
         value (NOLOCK)

WHERE
         settlement_batch_number = '388'
	and
         value = '0220'
	and
         value > '00'

ORDER BY value



-- 
Ben M

6. List of automated tool to convert Access database, query, form and interface to SQL Server

7. automated emails based on SQL Queries

8. Automating a time-sensitive query



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 63 guest