|
dear,
Yes T' you are right, but I want to do it using Job Scheduler [SQL Server 2000] I hope you help me with this idea
1] I create new job in that I execute my Stored procedure which returns a emailaddress
2] In that I create 2 job which execute on suceess of Job-1
In job-2 I execute xp_sendmail Stored procedure [which send's mail from sql server 2000]
Now my problem is the how can I provide the email address from job-1 to sendmail's @receiver parameter of Job-2
Develop2Program & Program2Develop
|
|
|
|
|
hi
I have following problem.
i'd like to use the MAX() function to get the highest ID from a table.
i've used is lik this:
<br />
INSERT INTO Table<br />
(ID, Employee, Project, Date, ...)<br />
VALUES (MAX(ID) + 1,@Employee,@Project,@Date, ...)<br />
but this is not working. i've searched a bit in the internet that it's not possible to put functions into VALUES.
so i'd like to know another way to solve this problem.
thanks in advance
greetz pdluke
PS: sorry for my bad english
|
|
|
|
|
You could either make the id an identity column, or you can get the value using a sub select:
insert into A
(id, ....)
values
((SELECT MAX(ID) FROM A), ...)
|
|
|
|
|
yes an sub select is one possibility, but I'm
using MSSQL and an sub select is not supported like in MySQL.
|
|
|
|
|
Why don't you make the ID a primary key and set its Identity to true, then it will auto increment by 1.
|
|
|
|
|
the problem is that I'm using this database via a WCF-Service.
so multiuser accessibility is the main problem.
If 2 users are saving their date at the same, who would this be solved with assigning the IDs?
so I've tryed this with MAX().
I really don't know how it would act with the IDs.
I would be grateful if someone could tell me if this could work like you say.
greetz
pdluke
|
|
|
|
|
Accoring to this [^] mySQL support sequence numbers.
Regards
Andy
|
|
|
|
|
I use the following construct in my system:
INSERT INTO myTable(ID, Employee, Project, Date, ...)
SELECT MAX(ID)+1, @Employee, @Project, @Date, ...
FROM myTable;
and it works fine.
Hope it helps.
|
|
|
|
|
Hi,
Iam creating a stored procedure, in this stored procedure i want a view to be created whenever that store procedure is being executed,
Can i have a view created inside a stored procedure?
Thanks!
Gautham
|
|
|
|
|
Given it was running with the right permissions, you could. I don't see why you'd want to however. Given that you could accomplish the same through the use of temporary tables/in-memory tables, what are you trying to do here?
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi all,
how to validate the range of values in a table.No duplicate entries are accept.
ex : In a table, having cards serial number from 10 to 20, but again i try to insert 11 to 19 serial number cards, it won't be accept.how to validate this type of range validation.
table fields are :
vndrCode :v001
serialFrom:10
SerialTo:20
IssueDate:23-mar-2007
pls help me.
|
|
|
|
|
to validate data before inserting to a column can be done using CHECK constraint.
but, you are looking for comparing data from multiple columns and then validate the same. I've not sure whether is possible using constraint. however you can write a TRIGGER for validating.
Regards
KP
|
|
|
|
|
I am posting the code.....please check if it works or not......here the logic is........
check whether duplicate record exist or not before inserting records in table.....
i am only posting the code to check the duplicate record in table.....
this SQL will find record if the number ,you are going to insert, exist in table between fromSerial and toSerial
Dim fS as Integer = 10
Dim tS as Integer = 20<br />
Dim myConnection As System.Data.OleDb.OleDbConnection<br />
Dim ds As DataSet<br />
Dim da As OleDbDataAdapter <br />
<br />
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" <br />
& myDatabaseath & ";Persist Security Info=False" <br />
<br />
Dim sqlDuplicate As String = "SELECT vndrCode FROM myTable WHERE <br />
"serialFrom > " & fS & " " & _<br />
"And serialFrom < " & tS & " " & _<br />
"Or serialTo > " & fS & " " & _<br />
"And serialTo < " & tS & " " & _<br />
"Or serialFrom < " & fS & " " & _<br />
"And serialTo > " & tS & " " & _<br />
"Or serialFrom < " & fS & " " & _<br />
"And serialTo > " & tS & " " & _<br />
"Or serialFrom < " & fS & " " & _<br />
"And serialTo > " & fS & " " & _<br />
"Or serialFrom < " & tS & " " & _<br />
"And serialTo > " & tS & " )"<br />
<br />
<br />
// Set up connection<br />
<br />
If Not myConnection Is Nothing Then<br />
If myConnection.State = ConnectionState.Open Then <br />
myConnection.Close()<br />
myConnection = New OleDbConnection(strConn)<br />
myConnection.Open()<br />
Else<br />
myConnection = New OleDbConnection(strConn)<br />
myConnection.Open()<br />
End If<br />
<br />
// Bring records<br />
<br />
da = New OleDbDataAdapter(sqlDuplicate , myConnection)<br />
ds = New DataSet<br />
da.Fill(ds, "Tablename")<br />
<br />
// Check if duplicate record exist <br />
<br />
If ds.Tables(0).Rows.Count = 0 Then : Return 0 //Duplicate Record doesn't exist<br />
Else : Return 1 //Duplicate Record exists<br />
End If
Last of all I dont think this code is very smart .....looking for better answer
Tirtha
"A man can ride on your back only when it is bent....."
|
|
|
|
|
Can some one let me know what would be the best way to authenticate a client that runs on a different AD domain from the domain where the SQL server runs..
Is there some way to use Trusted connection in this scenario
NaReN
|
|
|
|
|
If you are talking about a web app, yes there is. You set the identity of the app pool your web app is running in as a domain user account that has the correct rights in sql server. Then your connection string for your web app can use trusted connection.
If you are talking about a windows app, I am not sure if it is possible or not.
Hope that helps.
Ben
|
|
|
|
|
how to run a program in sqlserver automatically without human intervention
|
|
|
|
|
When u would to run and which kind of program.
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
My actual requirement is to send automatically mails regarding birthday wishes to employees by checking dob from database without human intervention
|
|
|
|
|
You can make a service
and Write there business login for sending mail
Start one time service on server.
Or Write and CLR stored procedure and Schedule in sql server job scheduler.
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
for Scheduled Jobs ...
in SQL Server Enterprise Manager
Managenemt -> SQL Server Agent - > Jobs.
To my knowledge it is possible to schedule and run SQL commands only.
for executing other applications on scheduled interavals ...
1. write an application and schedule it in windows scheduler so that it
executes on regular intervals
or
2. write a service and configure for automatic startup.
Regards
KP
|
|
|
|
|
Hi
In my database I have two tables with the same structure (columns, data types, etc).
How would I copy or move specific records from the one table to the other one using Transact-SQL in a stored procedure?
Thanks.
Kobus
|
|
|
|
|
do u mean to copy the data from one table to another table ?or u want to move only specific records?
|
|
|
|
|
I would just like to be able to copy/move specific records, for example those records where UserIndex = 123.
Kobus
|
|
|
|
|
INSERT INTO tblA SELECT * FROM tblB WHERE userIndex = 123
Regards
KP
|
|
|
|
|
Thanks KP
Exactly what I was looking for.
Kobus
|
|
|
|