|
rezarafiee wrote: how can i transfering data of table with script?
Write script to read data, and write a script to write data. Connect those 2 and wolla.
|
|
|
|
|
You can do this without a script.
INSERT INTO table2 (field1, field2, ..., fieldn) SELECT ..... FROM table1
|
|
|
|
|
Dear All,
I need to send an email every 30 minutes if Insert happenned...Any one plz can help???
I need this to be inside the SQL server 2005, this means when an Insert happens on 'myTable' and depending on some time, an Email is being sent to a specified person.
I just do not know where to go to implement such idea... 
I searched over the Internet, but nothing is useful for this purpose.
Kind Regards
OBarahmeh
|
|
|
|
|
SQL Server has email functionality.[^] You need to setup a job that runs in every 30 minutes, checks for inserts and sends mail.
|
|
|
|
|
Dear Navaneeth,
I know there is email functionality in SQL server, and I tried it...but I could not make the scenario that check If Insert has happenned...
So I need a sample of how to check for Inserts on myTable, then I can do the part of sending email..
Also I need to send the last inserted rows on myTable as attachement or HTML inside the email sent.
Kind Regards
Kind Regards
OBarahmeh
|
|
|
|
|
obarahmeh wrote: .but I could not make the scenario that check If Insert has happenned...
Ok - Say your table has 2 columns, "id" and "name". Now to get latest items inserted and not emailed, we need to add one more flag say "emailed" which will have a value "y" if email is sent. When new rows are inserting, set this filed value as "n".
In your SQL job, you can select records which has "emailed" flag value as "n". These will be the records that are newly inserted and pending to be emailed.
Hope this helps
|
|
|
|
|
I've got a program that needs to insert lots of rows into a table, typically, anywhere from a couple hundred, to tens of thousands. As such, I'd like them to go as fast as possible, so I'm using a .Prepare() on my OleDbCommand. If I run the following code with the .Prepare() commented out, it works fine. If I un-comment the .Prepare(), when it gets to the .ExecuteNonQuery(), it ALWAYS fails with the same complaint:
System.Data.OleDb.OleDbException: Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
Even better, it works fine on Oracle. But SqlServer, not so much. 
private bool SaveOccurance(ref OleDbCommand cmd, ref OleDbTransaction CurTrans)
{
if (cmd == null)
{
string stmt = @"
insert into work_occurance(
crew_size, due_date, due_fl, est_do,
job_qty, labor_time, location, occurance,
priority, reported_by, source_fl, job_reason,
work_req_no, first_date, last_date, span_fl,
avg_meter, avg_meter2, avg_fuel, labor_rate,
company, maint_lock, change_dt, change_login_user)
values(
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?)";
cmd = new OleDbCommand(stmt, Conn, CurTrans);
cmd.Parameters.Add("@crew_size", OleDbType.Integer);
cmd.Parameters.Add("@due_date", OleDbType.Date);
cmd.Parameters.Add("@due_fl", OleDbType.VarChar, 1);
cmd.Parameters.Add("@est_do", OleDbType.Double);
cmd.Parameters.Add("@job_qty", OleDbType.Integer);
cmd.Parameters.Add("@labor_time", OleDbType.Integer);
cmd.Parameters.Add("@location", OleDbType.VarChar, 6);
cmd.Parameters.Add("@occurance", OleDbType.Integer);
cmd.Parameters.Add("@priority", OleDbType.Integer);
cmd.Parameters.Add("@reported_by", OleDbType.VarChar, 11);
cmd.Parameters.Add("@source_fl", OleDbType.VarChar, 1);
cmd.Parameters.Add("@job_reason", OleDbType.VarChar, 1);
cmd.Parameters.Add("@work_req_no", OleDbType.Integer);
cmd.Parameters.Add("@first_date", OleDbType.Date);
cmd.Parameters.Add("@last_date", OleDbType.Date);
cmd.Parameters.Add("@span_fl", OleDbType.VarChar, 1);
cmd.Parameters.Add("@avg_meter", OleDbType.Double);
cmd.Parameters.Add("@avg_meter2", OleDbType.Double);
cmd.Parameters.Add("@avg_fuel", OleDbType.Double);
cmd.Parameters.Add("@labor_rate", OleDbType.Double);
cmd.Parameters.Add("@company", OleDbType.VarChar, 10);
cmd.Parameters.Add("@maint_lock", OleDbType.Integer);
cmd.Parameters.Add("@change_dt", OleDbType.Date);
cmd.Parameters.Add("@change_login_user", OleDbType.VarChar, 8);
foreach (OleDbParameter p in cmd.Parameters)
{
p.IsNullable = true;
}
}
cmd.Parameters["@crew_size"].Value = 1;
cmd.Parameters["@due_date"].Value = DateTime.Now;
cmd.Parameters["@due_fl"].Value = "F";
cmd.Parameters["@est_do"].Value = 314.15;
cmd.Parameters["@job_qty"].Value = 1;
cmd.Parameters["@labor_time"].Value = TimeSpan.FromHours(1.5).TotalMilliseconds;
cmd.Parameters["@location"].Value = "NORMM";
cmd.Parameters["@occurance"].Value = 3;
cmd.Parameters["@priority"].Value = 5;
cmd.Parameters["@reported_by"].Value = "FORECAST";
cmd.Parameters["@source_fl"].Value = "A";
cmd.Parameters["@job_reason"].Value = "S";
cmd.Parameters["@work_req_no"].Value = 1641781;
cmd.Parameters["@first_date"].Value = DateTime.Now - TimeSpan.FromDays(7);
cmd.Parameters["@last_date"].Value = DateTime.Now + TimeSpan.FromDays(4);
cmd.Parameters["@span_fl"].Value = "Y";
cmd.Parameters["@avg_meter"].Value = 0;
cmd.Parameters["@avg_meter2"].Value = null;
cmd.Parameters["@avg_fuel"].Value = 10;
cmd.Parameters["@labor_rate"].Value = 45;
cmd.Parameters["@company"].Value = "1";
cmd.Parameters["@maint_lock"].Value = 0;
cmd.Parameters["@change_dt"].Value = DateTime.Now;
cmd.Parameters["@change_login_user"].Value = "FORECAST";
ListParms(cmd);
try
{
int rows = cmd.ExecuteNonQuery();
return true;
}
catch (Exception E)
{
Console.WriteLine(E.ToString());
return false;
}
}
If anyone has any advice, I would be terribly grateful!
Thanks!
|
|
|
|
|
Hi.
Try change
cmd.Parameters["@first_date"].Value = DateTime.Now - TimeSpan.FromDays(7);
cmd.Parameters["@last_date"].Value = DateTime.Now + TimeSpan.FromDays(4);
to
cmd.Parameters["@first_date"].Value = DateTime.Now.AddDays(-7);
cmd.Parameters["@last_date"].Value = DateTime.Now.AddDays(4);
It should not work, but it have helped me before.
Kjetil
|
|
|
|
|
Thanks for the reply. I tried your suggestion, but I get the same error (and it still works fine if I do not Prepare() that command). My real program just supplies an already calculated date anyways (based upon things like an acceptable deviation range, avoiding weekends and holidays, etc), like:
cmd.Parameters["@first_date"].Value = this.earliest_date;
cmd.Parameters["@last_date"].Value = this.latest_date;
For now, I'm going to go with not preparing the statement, even if it will get called many thousands of times. It doesn't seem to make much difference in my testing so far (unlike with Oracle, where not Preparing that statement made it run significantly slower).
|
|
|
|
|
Hi.
Have you tried OleDbType.DBDate instead of OleDbType.Date ?
Worth a try.
Kjetil
|
|
|
|
|
Here is the code:
cn = null;
cn = new SqlConnection(cnStr);
SqlCommand cmdSet = new SqlCommand("SELECT * FROM MonthSet", cn);
SqlDataAdapter daSet = new SqlDataAdapter(cmdSet);
SqlCommand cmdHeader = new SqlCommand("SELECT * FROM MonthSetHeader", cn);
SqlDataAdapter daHeader = new SqlDataAdapter(cmdHeader);
SqlTransaction trnMonthData = null;
cn.Open();
trnMonthData = cn.BeginTransaction();
cmdSet.Transaction = trnMonthData;
daSet.UpdateCommand = cmdSet;
cmdHeader.Transaction = trnMonthData;
daHeader.UpdateCommand = cmdHeader;
try
{
daSet.Update(monthSet, "MonthSet");
daHeader.Update(header, "MonthSetHeader");
trnMonthData.Commit();
}
catch(Exception ex)
{
trnMonthData.Rollback();
return ex.Message;
}
finally
{
cn.Close();
}
return "";
I have played with the code to check the following"
1) Both datasets have changes
2) No exception returned
3) If I do only 1 update in the transaction, I can return the number of rows affected - correct.
4) This applies to both datasets
5) Rollback is not called
However, no changes are made to the database.
If I do the updates individually, using the same datasets, without using a transaction, the changes are made to the database.
Nigel
|
|
|
|
|
Any Oracle expert in here can guide us through the InstantClient steps in details?? I tried everything possible and I just gave up
ps. Please, do not link me to a website, I need your own experiences in this matter!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
hi
i'm using vb.net 2008 and database sql server 2005 ,, my connection string is as :
Data Source=ServerName; Initial Catalog=DataBaseName ;User ID=sa;Password=1111"
it's ok and works as good ,,,
but when i test exe project on client pc ,, an error with the connectoin string ,
this problem has happend with me before with sql server 2000 and i solved by add
Network=DBMSSOCN;
to the connection string ,
but with sql server 2005 it still doesn't work ,,
what's is the correct connection string to allow the client coneects the server
thanks
jooooo
|
|
|
|
|
kindman_nb wrote: an error with the connectoin string ,
What was the error ? Take a look at Connection strings[^]
|
|
|
|
|
I am using MSSQL and I have a value named NUG(4'') in a field named Sz in the table tbl_Sz.
But when i use the query -> Select count(*) from tbl_Sz where Sz='NUG(4'')', it gives the count zero, which should be one in this case.
Is the count is zero due to NUG(4'') has two Apostrophes ? Because I think sql ignores the string after the Apostrophe or something like that?
If it is so then how to solve this problem ?
|
|
|
|
|
Hi.
Single quotes must be escaped (with single quotes) in queries:
Select count(*) from tbl_Sz where Sz='NUG(4'''')'
Kjetil
|
|
|
|
|
Hi can any body help me or give me some tips to designing database for multilingual websites.
thanks
|
|
|
|
|
Hi guys,
I am trying to figure out how to solve this simple select statement, but somehow I am unable to get it all together. I simplified my problem which is like this. I have a unlimited number of persons and teams. Each person can be a member of one or more teams. I would like to figure out which person is a member of all teams. To make it easier for you to follow here is a SQL snippet to create sample tables and data
create table #person
(
PersonId varchar(2),
Firstname nvarchar(50)
)
create table #member
(
PersonId varchar(2),
TeamId varchar(2)
)
create table #team
(
TeamId varchar(2),
TeamName nvarchar(50)
)
insert into #person values ('P1', 'Wayne');
insert into #person values ('P2', 'Rick');
insert into #person values ('P3', 'Bob');
insert into #team values ('T1', 'Team 1');
insert into #team values ('T2', 'Team 2');
insert into #team values ('T3', 'Team 3');
insert into #member values ('P1', 'T1');
insert into #member values ('P2', 'T1');
insert into #member values ('P2', 'T2');
insert into #member values ('P2', 'T3');
insert into #member values ('P3', 'T3');
drop table #member
drop table #person
drop table #team
The only person in all teams is Rick.
Thanks for any kind of help on this matter.
Cheers!
Byteloser
P.S: By the way I am using SQL Server 2005
|
|
|
|
|
SELECT PersonId, Firstname
FROM #person
WHERE
PersonId IN (
SELECT PersonId
FROM #member
GROUP BY PersonId
HAVING COUNT(*) = (SELECT COUNT(*) FROM #team)
)
|
|
|
|
|
Hi,
Thanks for the reply. I came up with the same idea last night. Hope this works in my real work szenario, because I have a high data volume.
Cheers!
Stephan
|
|
|
|
|
Hi,
In my table, my date is look like '2007-10-01 08:01:14.543'. But I need '2007-10-01' only. How to use convert function to get this format? Pls help. tnx in adv.
Balasubramanian K.
|
|
|
|
|
You have here some examples:
PRINT GETDATE()
--|OUTPUT:
--|Jul 4 2008 9:45AM
PRINT CONVERT(NVARCHAR, GETDATE(), 121)
--|OUTPUT:
--|2008-07-04 09:45:45.477
PRINT CONVERT(NVARCHAR(10), GETDATE(), 121)
--|OUTPUT:
--|2008-07-04
PRINT CONVERT(NVARCHAR, GETDATE(), 112)
--|OUTPUT:
--|20080704
You can find mode details here: http://msdn.microsoft.com/en-us/library/aa226054.aspx[^]
|
|
|
|
|
Hi,
I have 3 tables : Products, Orders and OrderDetails.
The Products table has a ProductCode field
The Orders table has an AccountNumber, DeliveryDate field and an ID field
The OrderDetails has a foreign key that points to the ID field in Orders
The OrderDetails has a foreign key that points to the ProductCode in Products
I need to get a list of all ProductCodes
For each ProductCode I need to get the most recent Orders.DeliveryDate and OrderDetails.Quantity where Order.AccountNumber equals a given AccountNumber
For example assume each table contains the following records:
Products Table
ProductCode
A1
B2
C3
D4
E5
OrderDetails Table
ID ProductCode Quantity
1 A1 1
1 B2 2
1 D4 4
2 C3 1
2 E5 2
Orders Table
ID AccountNumber DeliveryDate
1 1234 03/07/2008
2 6789 26/06/2008
Assume I want information for AccountNumber 1234. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 03/07/2008 1
B2 03/07/2008 2
C3 NULL NULL
D4 03/07/2008 4
E5 NULL NULL
Assume I want information for AccountNumber 6789. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 NULL NULL
B2 NULL NULL
C3 26/06/2008 1
D4 NULL NULL
E5 26/06/2008 2
I know that I could get a datatable with a list of products and then write some code to loop through it and lookup Orders and OrderDetails to get the other two fields but I'd rather get all the information that I need in one call.
Any ideas how to go about writing the SQL Select statement?
Thanks,
dlarkin77
|
|
|
|
|
dlarkin77 wrote: go about writing the SQL Select statement?
Have you tried one? Try a query and if it doesn't work, ask for help.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
First result for accountnumber 1234
SELECT OD.ProductCode, O.DeliveryDate, CASE WHEN O.DeliveryDate IS NULL THEN NULL ELSE OD.Quantity END AS quantity<br />
FROM dbo.Products P INNER JOIN<br />
dbo.OrderDetails OD ON P.ProductCode = OD.ProductCode LEFT OUTER JOIN<br />
dbo.Orders O ON OD.ID = O.ID AND O.AccountNumber = 1234
Second result for accountnumber 6789
<br />
SELECT OD.ProductCode, O.DeliveryDate, CASE WHEN O.DeliveryDate IS NULL THEN NULL ELSE OD.Quantity END AS quantity<br />
FROM dbo.Products P INNER JOIN<br />
dbo.OrderDetails OD ON P.ProductCode = OD.ProductCode LEFT OUTER JOIN<br />
dbo.Orders O ON OD.ID = O.ID AND O.AccountNumber = 6789
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
