SQL query with PS variable

Experts,

I am trying to create a sql query with sqlcmd.  But I am not able to succeed.

I want all the rows with the date from today, and I want to put this in a csv file, I have this already:


$AttachmentPath = "c:\sql\sql.csv"
$today= get-date -format yyyy-MM-dd
$QueryFmt= "SELECT [Incoming/Outgoing],[Dialed_Number],[Date_Time] FROM [dbo].[TCallLog_CO] WHERE [Incoming/Outgoing] = 0 AND [Date_Time] > $today"


Invoke-Sqlcmd -ServerInstance callmon -Database CCsuiteDB -Query $QueryFmt | Export-CSV $AttachmentPath


I have a working sql query:

USE [CCsuiteDB]
GO
DECLARE @Today DATETIME
SET @Today = (SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD])

SELECT [Incoming/Outgoing]
      ,[Dialed_Number]
      ,[Date_Time]
  FROM [dbo].[TCallLog_CO]
WHERE
      [Incoming/Outgoing] = 0 AND
      [Date_Time] > @Today


Can anyone help me out?
trivinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you surely have a couple of options.

I would put the "sql" into either a view OR a stored procedure, and call that one from the PS.
this will simplify the PS a lot, and avoid to have the full SQL in the PS.

apart from that:
USE [CCsuiteDB]
GO

Open in new window

is not needed to be used, as -Database CCsuiteDB is already doing it.

otherwise, you just do it:
$AttachmentPath = "c:\sql\sql.csv"
$QueryFmt= "DECLARE @Today DATETIME
SET @Today = (SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD])

SELECT [Incoming/Outgoing]
      ,[Dialed_Number]
      ,[Date_Time]
  FROM [dbo].[TCallLog_CO]
WHERE
      [Incoming/Outgoing] = 0 AND
      [Date_Time] > @Today"


Invoke-Sqlcmd -ServerInstance callmon -Database CCsuiteDB -Query $QueryFmt | Export-CSV $AttachmentPath

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trivinAuthor Commented:
Great!  I did not know that I could put whole the query in one variable.
This simplifies it a lot!
Thanks angelIII
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.