Running a proc. on a certain date help?

DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 3

SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber

I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current dateUse SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) to schedule it ?

-PatP|||DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = day(GetDate())
If @.returnDay = 3
begin
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
endsql

Running a delete, insert, update SQL statement from a text field

Let's say you have a text field on some application that's used to be part of a SQL select statement like "SELECT " + txtField.Text() + " FROM [Some_Table];"

What if the user entered "(DELETE *)" or some other insert, update, etc. in the text field? Is there any way it could embed the statement and really mess things up in your database?Yes. Google for "SQL injection". And vow never again to build your SQL like that; use bind variables to pass user input to the SQL engine. This also makes the database perform better AND makes your SQL easier to write:

"SELECT ? FROM [Some_Table]"

Run jobs in SELECT CASE?

Hello!
Is that possible to call a job using SELECT CASE statement? I am trying
to run some jobs based on some cases in my stored procedure. See below
the code:
select case RptName
when 'A' then [msdb].[dbo].[sp_start_job] @.job_name = 'A'
when 'B' then [msdb].[dbo].[sp_start_job] @.job_name = 'B'
when 'C' then [msdb].[dbo].[sp_start_job] @.job_name = 'C'
when 'D' then [msdb].[dbo].[sp_start_job] @.job_name = 'D'
end
from Table
Trying not to use IF-ELSE statement (which I have tried and it is
working fine).
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***>> Trying not to use IF-ELSE statement
SELECT statement returns a resultset, it is not not meant to execute
procedures. CASE is not supposed to be used as you have suggested. It
returns a scalar value.
Each sp_start_job invocation is a separate statement. Use IF.. ELSE
construct to execute them conditionally.
Anith|||In T-SQL, there is no [select case ..] statement. Instead, [case.. when..
then.. end] is an expression, so you can't execute a procedure from it, but
you can call functions.
However, this would seem to do what you want:
select @.RptName = RptName from Table
if @.RptName = 'A' exec sp_start_job @.job_name = 'A'
if @.RptName = 'B' exec sp_start_job @.job_name = 'B'
Or, looking at your example, it seems that perhaps just this would work:
exec sp_start_job @.job_name = @.RptName
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:eWaWmjaBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> Is that possible to call a job using SELECT CASE statement? I am trying
> to run some jobs based on some cases in my stored procedure. See below
> the code:
> select case RptName
> when 'A' then [msdb].[dbo].[sp_start_job] @.job_name = 'A'
> when 'B' then [msdb].[dbo].[sp_start_job] @.job_name = 'B'
> when 'C' then [msdb].[dbo].[sp_start_job] @.job_name = 'C'
> when 'D' then [msdb].[dbo].[sp_start_job] @.job_name = 'D'
> end
> from Table
> Trying not to use IF-ELSE statement (which I have tried and it is
> working fine).
> Thanks for your help!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks, Anith.
*** Sent via Developersdex http://www.examnotes.net ***|||But then so would...
select @.RptName = RptName from Table
exec sp_start_job @.job_name = @.RptName
Just remember to keep the RptName matching the Job name.
Colin Dawson
www.cjdawson.com
"JT" <someone@.microsoft.com> wrote in message
news:OwGor8aBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> In T-SQL, there is no [select case ..] statement. Instead, [case.. when..
> then.. end] is an expression, so you can't execute a procedure from it,
> but you can call functions.
> However, this would seem to do what you want:
> select @.RptName = RptName from Table
> if @.RptName = 'A' exec sp_start_job @.job_name = 'A'
> if @.RptName = 'B' exec sp_start_job @.job_name = 'B'
> Or, looking at your example, it seems that perhaps just this would work:
> exec sp_start_job @.job_name = @.RptName
>
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:eWaWmjaBGHA.1864@.TK2MSFTNGP12.phx.gbl...
>

Run Dynamic Query Using Stored Procedure

Hi,

I need to create a stored procedure, which needs to accept the column name and table name as input parameter,

and form the select query at the run time with the given column name and table name..

my procedure is,

CREATE PROC spTest

@.myColumn varchar(100) ,

@.myTable varchar(100)

AS

SELECT @.myColumn FROM @.myTable

GO

This one showing me the error,

stating that myTable is not declared..

..........as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters..

Plese help me?? Is it possible in stored procedure..

DECLARE @.sql as Char(500) -- or whatever length is needed
SELECT @.sql = 'SELECT ' + @.myColumn + ' FROM ' + @.myTable
EXEC(@.sql)

Run DTS Package based on a Variable

I'm trying to run a DTS package based on the result of a partcular
query.
SELECT SUM(Bal_Orig_Curr) AS bal
INTO tempbal
FROM Gw078p
I want to run the rest of the package only if the value of "bal" is
equal to zero otherwise generate an email. How can create this split?
ThanksYou could store the results of the query in a global variable. After that, a
vbscript task can create the branching logic by updating the precedence
constraints.
Please see http://www.sqldts.com/214.aspx for more info. Much easier in SSIS
as the precedence constraints themselves can be based on an expression in
the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Run DTS Package based on a Variable

I'm trying to run a DTS package based on the result of a partcular
query.
SELECT SUM(Bal_Orig_Curr) AS bal
INTO tempbal
FROM Gw078p
I want to run the rest of the package only if the value of "bal" is
equal to zero otherwise generate an email. How can create this split?
Thanks
You could store the results of the query in a global variable. After that, a
vbscript task can create the branching logic by updating the precedence
constraints.
Please see http://www.sqldts.com/214.aspx for more info. Much easier in SSIS
as the precedence constraints themselves can be based on an expression in
the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Run DTS Package based on a Variable

I'm trying to run a DTS package based on the result of a partcular
query.
SELECT SUM(Bal_Orig_Curr) AS bal
INTO tempbal
FROM Gw078p
I want to run the rest of the package only if the value of "bal" is
equal to zero otherwise generate an email. How can create this split?
ThanksYou could store the results of the query in a global variable. After that, a
vbscript task can create the branching logic by updating the precedence
constraints.
Please see http://www.sqldts.com/214.aspx for more info. Much easier in SSIS
as the precedence constraints themselves can be based on an expression in
the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Run a SQL Query From VBS?

It Seems I've solved my previous doubt about the ADODB
case. I've used this code to launch the query:
SQLString = "SELECT COD_NOTARIO FROM dbo.tblNOTARIO"
Dim oCn, oFSO
Set oCn = CreateObject("ADODB.Connection")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oCn.Open
"PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=FinalMultiple;"
oCn.Execute SQLString
Set oCn = Nothing
However I've run into a much more amusing issue given that
I can't figure out how to access to the query results from
the ActiveX script (a column and many fields).
Could anybody be so kind to tell me how can I see from VBS
the query results?
TIA
Greetings,
David Grant> Could anybody be so kind to tell me how can I see from VBS
> the query results?
You need a recordset object. Code snippet:
Set oRs = oCn.Execute(SQLString)
Do While oRs.EOF = False
'process each row here
cod = oRs.Fields("COD").Value
oRs.MoveNext
Loop
Hope this helps.
Dan Guzman
SQL Server MVP
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:80c501c528bc$b4592170$a601280a@.phx.gbl...
> It Seems I've solved my previous doubt about the ADODB
> case. I've used this code to launch the query:
>
> SQLString = "SELECT COD_NOTARIO FROM dbo.tblNOTARIO"
>
> Dim oCn, oFSO
> Set oCn = CreateObject("ADODB.Connection")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oCn.Open
> "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=FinalMultiple;"
> oCn.Execute SQLString
> Set oCn = Nothing
> However I've run into a much more amusing issue given that
> I can't figure out how to access to the query results from
> the ActiveX script (a column and many fields).
> Could anybody be so kind to tell me how can I see from VBS
> the query results?
> TIA
> Greetings,
> David Grant|||Thank you very much Dan, your code has really helped me a
lot!! :)
Greetings,
David Grant
>--Original Message--
>You need a recordset object. Code snippet:
>Set oRs = oCn.Execute(SQLString)
>Do While oRs.EOF = False
> 'process each row here
> cod = oRs.Fields("COD").Value
> oRs.MoveNext
>Loop
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:03e901c52934$6045c2f0$a401280a@.phx.gbl...
> Thank you very much Dan, your code has really helped me a
> lot!! :)
>
> Greetings,
> David Grant
>

Run a select from against two dbs.

Hi,
is there any possiblity to run a select statement against the tables of two different databases which are both installed on the same database server ? Does anybody know how to join those tables reasonable way ?
Thnxselect *
from db1.dbo.table1 t1 inner join db2.dbo.table2 t2
on t1.pk = t2.pk|||Check BOL (http://msdn2.microsoft.com/en-us/library/ms187879.aspx).

-PatP|||Use Linked Servers concept and BOL is your friend.|||Linked servers unnecessary since both dbs on same server.

rtrim/ltrim works good - but need some additional feedback

Using

select rtrim(ltrim(total)) as Total

from testtable

WHERE Total is NOT NULL

The results come back allright except for a small handful:

594
1242
17458
214
6971
29023
808
1
0
37
9
65
39
9
0
0
0
2
632
9
0
641
0
2438
148
2
1882
153
556
11839
616
1250
17709
187
6630
29548
803
7880
606
1600
1479
690
765
1630
3953
2418
9
75
1563
2
635
3
175
36
783
54
28
665
60
261
11995
679
1438
17548
118
6211
29543
797
7649
572
878
488
948
273
1756
2111
1205
2
54
1068
2
188
0
58

Showing me that there are still some spaces not getting trimmed off.. I would love to present this as trimmed as possible. Is there something to this that I am missing?

this this:

select rtrim(ltrim(cast(total as varchar(100)))) as Total

from testtable

WHERE Total is NOT NULL

|||

IF all of your values are in fact numbers, then use a cast instead of all the lrtim/rtrim.

Code Snippet

DECLARE @.MyTable table
( Col1 varchar(25)
)

INSERT INTO @.MyTable VALUES (' 29023')
INSERT INTO @.MyTable VALUES ('808')
INSERT INTO @.MyTable VALUES (' 1')

SELECT cast(Col1 as int) FROM @.MyTable

--
29023
808
1

|||

They are numbers within a string that looks to be compiled of binary specs of hell.

I am not seeing hex characters, but some binary. When I use your suggestion I get:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '

1' to data type int.

The field value is varchar(200) and there is no way I can change without some other functions blowing up.

In the end - I have some vbscript which I will be applying to a page so that this numeric data can be summed up.

|||

Perhaps this function will 'solve' your issue -it should eliminate all non-numerical characters.

Code Snippet

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'fnNumbersOnly'
)
DROP FUNCTION dbo.fnNumbersOnly
GO

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(50) )
RETURNS varchar(50)
AS

BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

Then use it like this:

Code Snippet

SELECT dbo.fnNumbersOnly( Total ) FROM TestTable

|||

OMG! Yes! Too cool!

Thank you!

|||

The problem was almost certainly due to something like embedded tab characters or other non-space whitespace characters.

If you run:

SELECT ' ' + char(9) + ' 123'

you will see that the second 5 spaces after the TAB are left behind. LTrim (and probably RTrim) only seem to remove spaces.

rtrim in sql server 2000

Hi,
What is wrong with the following query?
select a.name, rtrim(a.name) from (
select top 100 name from dbo.table1 )a
whre table1 has the following in name column
A.B.xyz<space>
AB.xyz<space>
and so on...
When i cut and paste the result set from query analyzer into Excel, i
still see the trailing space.
BTW, i am using sql server 2000
Thanks in advance,
TamasWhat data type is the name column? The char/nchar/binary datatypes are of
fixed length, while varchar/nvarchar/varbinary are variable.
"Trimming spaces from fixed length columns is futile. You will be overpadded
."
ML
http://milambda.blogspot.com/|||what is the a.name datatype ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<tamashee@.yahoo.com> wrote in message
news:1145399053.665196.72630@.e56g2000cwe.googlegroups.com...
> Hi,
> What is wrong with the following query?
> select a.name, rtrim(a.name) from (
> select top 100 name from dbo.table1 )a
> whre table1 has the following in name column
> A.B.xyz<space>
> AB.xyz<space>
> and so on...
> When i cut and paste the result set from query analyzer into Excel, i
> still see the trailing space.
> BTW, i am using sql server 2000
> Thanks in advance,
> Tamas
>

rtrim function

hi,

for example; select field1 from table

field1 value=codesample

I want to change field1 value with rtrim function than field1 value must be codesam. How can I do:?

Hi,

sorry but I didn't understand your request, what do you want to achieve, could you please describe this again ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de