|
Hi all
I'm trying to write a DTS that as it's first step checks a log table as to whether the process has already run for that date, and if it has, exit.
I thought I'd be able to have an 'Execute SQL Task' with something like the following:
DECLARE @ContributionDate DATETIME
DECLARE @NoOfRecords INT
SET @ContributionDate = GetDate()
CREATE TABLE #Results(NoOfRecords INT)
INSERT INTO #Results EXEC db.dbo.vsDissemination_HasRun @ContributionDate, 'C'
SET @NoOfRecords = (SELECT TOP 1 NoOfRecords FROM #Results)
DROP TABLE #Results
IF @NoOfRecords > 0
BEGIN
RAISERROR('Dissemination has occurred',1,1)
END
ELSE
SELECT 1
Which when the error was raised would go down the On Failure branch, but it always goes to the On Success branch.
Running the script in Query Analyzer does either return a numeric value or an error.
Firstly, is this the best way to do this check?
If it is, why doesn't it work - if not, how should I be doing this?
Thanks
Ben
|
|
|
|
|
Try RAISERROR('Dissemination has occurred',16,1).
|
|
|
|
|
Thanks Andy -
The error is now being thrown (if I run the package in the designer) but it doesn't go to the On Failure step - is that expected?
Ben
|
|
|
|
|
dbo.Members got some column mID, mfName, mlName, mJoinDate, mExpiredDate
dbo.MemberStatus got some column mID, mStatus
Now I want to create a function which will be automatically check Mebers.mExpiredDate and update the value into MemberStatus.mStatus
IF mExpiredDate > today Then
memberStatus.status = true
Else
memberStatus.mStatus = False
End IF
Could please tell me how will I create this function into my database.
Sarfarj Ahmed
|
|
|
|
|
Assuming that you use MS SQL Server:
<code>Update dbo.MemberStatus
Set status = Case when dbo.Members.mExpiredDate > getdate() then 1 Else 0 End
From dbo.Members join dbo.MemberStatus On dbo.Members.mID=dbo.MemberStatus.mID</code>
|
|
|
|
|
Thanks
Im using sql server 2005.
could you please tell me where should i keep this function? inside TableValued, Scalar .....
Sarfarj Ahmed
|
|
|
|
|
Thanks
Im learning ASP.NET and SQL SERVER 2005
If i put the code (you given me) inside my ASP.NET QueryString then I can execute this command. But I want this code inside my SQL SERVER 2005, So when someone Logon then it will cecck the the Status from MemberStatus.
Please tell me what should I do? Thanks again for your Help.
Sarfarj Ahmed
|
|
|
|
|
Hello, I was hoping someone could help me with an error I recieve during program execution. Code below with explanation below that. Codes in C# with Access backend.
public partial class Form1 : Form
{
public string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\testdatabase.mdb";
public OleDbConnection con;
public OleDbDataAdapter dAdapter;
public DataSet dSet;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
con = new OleDbConnection(conString);
dAdapter = new OleDbDataAdapter("SELECT * FROM agent WHERE firstname = '" + textBox1.Text + "'", con);
dSet = new DataSet();
dAdapter.Fill(dSet);
DataTable datatable = dSet.Tables[0];
if (datatable.Rows.Count != 0)
{
textBox1.Text = (string)datatable.Rows[0][1];
textBox2.Text = (string)datatable.Rows[0][2];
}
}
I was running a test and noticed that , for instance, if my sql inquiry where modified to search the ID field in my database
SELECT * FROM agent WHERE agentid = '" + textBox1.Text + "'",
and when I enter a number into textbox1 say the number 1 , I get an error thrown in .net at the line below
dAdapter.Fill(dSet);
Which in short says "datatype mismatch" - I am successfully able to do SQL select inquiries with strings typed into the textbox but not numbers.
Also, the test database has 1 table with around 5 fields.
As spelled
( agentid, firstname,lastname,username,password)
1 bob jenkins bjenkins 1234
2 john willows jwillows 4321
I test the program by entering 1 into textbox1.
For the life of me I can't figure it out and do not have much experience.
Thanks in advance.
|
|
|
|
|
I think you don't need to enclose agentid in ' so your query should look like this:
SELECT * FROM agent WHERE agentid = textBox1.Text
Also, your query is prone to sql injection attacks. For more information about it and preventing them have a look at this article:
SQL Injection Attacks and Some Tips on How to Prevent Them[^]
|
|
|
|
|
Awesome!
Thanks for the information. I will read up on it later.
|
|
|
|
|
You are welcome 
|
|
|
|
|
If you are attempting to filter a resultset by applying a WHERE condition to a numeric field, you shouldn't surround the filter value with quotes.
Paul Marfleet
|
|
|
|
|
hi ,
thank you all for your time .
currently im working on a project which involve oracle database there for my application insist of oracle client 8.7 or higher to be installed on the end user machine in order to work.
i need an advice for a light oracle client , untill now i was testing the application with oracle client 9i ,it works great , but the problem is that this client is about 600mb while compressed and arround 1gb after installation , my customers are concerned about it , since its a small application , why does it need such a huge oracle client in order to work?
so my question is , if you know of some "smaller" oracle client that applications like that can run with?
thnks again.
Net
|
|
|
|
|
On the Oracle website you should be able to find the 'Instant Client' which is around 25Mb.
|
|
|
|
|
I'm modifying a program (oQuery) that I found on this site so that it will allow updates too.
My problem is how do I do an update using some generic query execution. My problem comes up when I try and get the number of rows returned. So how can I tell that the user just did an update and then find out how many rows were affect by that update?
<br />
mssqlComm.CommandType = CommandType.Text<br />
mssqlComm.CommandText = sqlQuery 'this is the query from the user<br />
mssqlComm.Connection = mssqlConn<br />
mssqlcomm.ExecuteNonQuery()<br />
<br />
mssqladpt = New SqlDataAdapter(mssqlcomm)<br />
mssqlds = New DataSet<br />
mssqladpt.fill(mssqlds)<br />
Thanks
Tom
Tom Wright
tawright915@gmail.com
|
|
|
|
|
ExecuteNonQuery returns an integer value that indicates how many records were affected by the query being executed.
Paul Marfleet
|
|
|
|
|
Colin must be asleep...
Tom Wright wrote: sqlQuery 'this is the query from the user
I assume you're parsing this string to check for any kind of SQL injection attack ? Accepting raw SQL and running it blindly is a real recipe for someone to wipe your DB on you.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I need add some hour(or second to be more exact) with a date field(using dateadd),
but I can only count business hour between 08:00 am and 06:00 pm.
for example:
we suppose the following paramters below.
date_field: 10/16/2007 05:00:00 pm
hour: 2:00:00
if I use dateadd(hh,hour,date_field), I will have 10/16/2007 07:00:00 pm. right?
but I can only count business hour between 08:00:00 am and 06:00:00 pm.
the date/time would be "10/17/2007 09:00:00"
I need to do a function in sql that return the result of this add.
sorry, my english is poor yet, I'm from brazil. Do you understand my doubts?
can someone help me?
|
|
|
|
|
As far as I know, there is no direct way to do this in SQL Server.
You should do by making a user defined function or in your application logic.
|
|
|
|
|
how can i store my select statment result in one variable
"Select Into"
i don't want to use cursor.
my select statment returns one value only
thx
|
|
|
|
|
SELECT @variable = column FROM table
or
SET @variable = (SELECT column FROM table)
The first enables you to set multiple variables in a single statement, and has no problems with multiple rows being returned - the variables are set to the values in the last row returned. SET will raise an error if more than one row is returned.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
DECLARE @variable datatype
SELECT @variable = column FROM tablename
Regards,
Sandeep Kumar.V
|
|
|
|
|
Hy
I have 2 databases and one table for each of database. The tables have the same structure. One of them is populate and I want to copy the contain of this to the second table. I want to ask if this is possible with DTS.
thx
|
|
|
|
|
Yes, And You can check the (DTS Export/Import Wizard) section of this article[^].
|
|
|
|
|
But I want to do that with C# 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
