|
how to use the @@identity function in stored procedure pls
|
|
|
|
|
yazan_zahi wrote: how to use the @@identity function in stored procedure pls
Same way as you'd use it anywhere else.
You might, however, find that SCOPE_IDENTITY() is a better choice if you have a lot of other database interaction going on at the same time.
|
|
|
|
|
Hi,
what do you want to exactly with this, or better how do you want to use it?
I think for storing it into another variable you can something like:
<br />
SELECT @@identity into @myVariable<br />
or using it within a query
<br />
SELECT * FROM my_table where my_PK = @@identity<br />
Hope this helps,
regards
Sebastian
|
|
|
|
|
when i submit my settings from my application it gives an error.
GORSHPPNWS0100K:Unable to process a customer number.
-2147217887 [Microsoft][ODBC driver for Oracle][Oracle]ORA-01460: unimplemented or unreasonable conversion requested
Here "GORSHPPNWS0100K" is a production server through which application interact.
Pls suggest the cause of the problem.
Thanks
|
|
|
|
|
What were you trying to do at this point in time (relevant code snippets would be good also)
|
|
|
|
|
hi
actually there is an application which save user preferences to database.
since number of user is using the same application with their ligin id but only one user is facing the mentioned problem.
Preferences are like changing name,email,address etc.
|
|
|
|
|
I meant lower level than that. What were you doing (in code)?
|
|
|
|
|
Frndz,
How do I get time part from DateTime value in SQL 2005?
Regards,
Vipul Mehta
Regards,
Vipul Mehta
Sr. Software Engineer
NIIT Technologies Ltd
|
|
|
|
|
Here are a few ways:
select right(convert(varchar,DateTimeField,0),7) as JustTime from MyTestTable
or
select convert(varchar,DateTimeField,14) as JustTime from MyTestTable
or
select cast(DATEPART(hh, DateTimeField) as varchar) + ':'
+ cast(DATEPART(mi, DateTimeField) as varchar) as JustTime from MyTestTable
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
AS IS – Scenario
================
BIZTALK Rule Engine recognizes USERS only from an Active Directory. These users connect to the BizTalk SQL Server 2005 database through Windows Authentication.
Our windows application accesses the BIZTALK rule Engine using the user context of the logged-in user. In short, the Authentication for the application happens when a user logs in to the windows OS. To connect to the BIZTALK database a connection string is used, among its parameters is the INTEGRATED SECURITY=TRUE value, which simple means it uses Windows Authentication.
Also a LOGIN for the windows user needs to be created on the SQL SERVER 2005 Instance, Which then is associated with a User of the BIZTALK database.
PROBLEM
=======
The problem we face right now begins with the increase in the number of users accessing the application. For every user added to the Active directory, a corresponding login has to be added in the SQL Server 2005 lists of LOGINS. This has created a problem in terms of administration.
I’ve tried using a TRUSTED CONNECTION by creating and using SQL AUTHENTICATED users in the connection string, but this approach has failed.
I’ve tried group the users in the active directory and using this group as login in the SQL SERVER 2005 database, but that has failed too.
TO BE Scenario
==============
The Ideal solution for this problem would be to create a Trusted Sub-System, Which would mean creating only one Login at the SQL Server end and using it to connect it to the BIZTALK database. And all the users in the Active Directory would use the user context of this new Login to get connected to the BIZTALK database.
I haven’t been able to find a solution for this problem. Would really appreciate you help.
|
|
|
|
|
i have tow stored procedure i put them in one procedure the 1fst have the primary key idntity
value the seconed have the forign key how ican solve the problem by passing the value to the 2nd procedure
=================================
i will pass th procedure to u :
create procedure ks_media_item_full_data
@Item_No int,
@Desc nvarchar(200),
@added_date datetime,
@Added_by nvarchar(100),
@content ntext,
@textlength int
as
execute dbo.Ks_Ins_Media_Item @Item_No, @Desc, @added_date, @Added_by
execute dbo.Ks_Ins_Media_Item_content @Item_no,@content, @textlength
========================================
can you help me pls fast
yazan
|
|
|
|
|
In the first stored procedure insert the record and then get the new value if identity using @@IDENTITY and pass this value to the second stored procedure. BTW why you are using two stored procedure for this do the work of the second stored procedure in the first one itself.
Best Regards,
Apurva Kaushal
|
|
|
|
|
my DBA wants that sorry i do not undrstand can you send the sentax to me pls
and thank you alot
|
|
|
|
|
what you have to do is to first insert the record in the table. Then put @@IDENTITY in a local variable like this:
@TempVar = @@IDENTITY this line will be there just after the insert statement. And then you can pass this local variable(@TempVar) to the second stored procedure.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Hi,
I'm working with MS Access 2000 database...
I would like to use SQL to create a new table and set a field to "Allow Zero Length" = Yes. Is this possible? I can't seem to get it to work.
CREATE TABLE NewTable (FieldName TEXT(30) ALLOWZEROLENGTH)
thanks,
Ron
|
|
|
|
|
Dear Sir
Please tell me how can I write XML file using SQL?
(I need the syntax for the query which perform this task)
Thank You
MNG
|
|
|
|
|
You can use FOR XML clause to retrieve result in XML format. For example:
SELECT TOP 10 *<br />
FROM sysobjects<br />
FOR XML AUTO, ELEMENTS
But I don't know if it is possible to write this result straight to a file.
|
|
|
|
|
hello every one!
well I got this query here, I'm really stuck in this error. I'm working oin a master detail form in .net 2.0 C# in Windows Forms. its a System.ArgumentException that I'm getting here. I've these dataGridview on the form. I've made a typed dataset for it. the tables are these
PurchBill(PurchBillID, VendID, Bookno, BillDate)
Purch(purchID,purchbillID,Serial, ModelID, PresentAt,PurchWarranty,PurchPrice)
ProdLine
ProdModel
and vu_manNames thats a view
I've set up seperate dataGridViews for Purch, ProdLine and ProdModel and for selecting the manufacturer name here. Look at this code
private void saverecord()
{
try
{
int n = 0;
if (flagUpdate == false)
{
for (int row = 0; row < dataGridViewPurch.Rows.Count; row++)
{
DataRow dr = dsPurch1.Purch.NewRow();
dr["PurchBillID"] = sqlInsertCommandPurch.Parameters["@purchBillID"].Value = dsPurch1.PurchBill[this.BindingContext[dsPurch1, "PurchBill"].Position].PurchBillID;
MessageBox.Show("Value of Serial \n" + dataGridViewPurch.Rows[row].Cells["Serial"].Value.ToString());
dr["Serial"] = sqlInsertCommandPurch.Parameters["@Serial"].Value = dataGridViewPurch.Rows[row].Cells["Serial"].Value.ToString();
dr["PurchPrice"] = sqlInsertCommandPurch.Parameters["@PurchPrice"].Value = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchPrice"].Value.ToString());
MessageBox.Show("Value of ModelID: " + dsPurch1.ProdModel[this.BindingContext[dsPurch1, "ProdModel"].Position].ModelID.ToString());
dr["PurchWarranty"] = sqlInsertCommandPurch.Parameters["@Purchwarranty"].Value = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchWarranty"].Value.ToString());
dr["modelID"] = sqlInsertCommandPurch.Parameters["@ModelID"].Value = dsPurch1.ProdModel[this.BindingContext[dsPurch1,"ProdModel"].Position].ModelID ;
dsPurch1.Purch.Rows.Add(dr);
//MessageBox.Show("Hello");
n += daPurch.Update(dsPurch1, dsPurch1.Purch.ToString());
//refreshDataSet();
}
MessageBox.Show("Number of records inserted: " + n.ToString());
dsPurch1.AcceptChanges();
}
else
{
//this code is for the updating a record.
for (int row = 0; row < dataGridViewPurch.Rows.Count; row++)
{
if (dataGridViewPurch.Rows[row].Cells["PurchPrice"].Value.ToString().Length != 0)
{
dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].BeginEdit();
sqlUpdateCommandPurch.Parameters["@PurchBillID"].Value = dsPurch1.PurchBill[this.BindingContext[dsPurch1, "PurchBill"].Position].PurchBillID = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchBillID"].Value.ToString());
//sqlUpdateCommandPurch.Parameters["@PurchBillID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchBillID = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchBillID"].Value.ToString());
sqlUpdateCommandPurch.Parameters["@PresentAt"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PresentAt = 1;
sqlUpdateCommandPurch.Parameters["@ModelID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].ModelID = Convert.ToDecimal(dataGridViewProdModel.SelectedRows[0].Cells["ModelID"].Value.ToString());
for (int col = 0; col < 3; col++)
{
if (col == 0)
sqlUpdateCommandPurch.Parameters["@PurchPrice"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchPrice = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchPrice"].Value.ToString());
else if (col == 1)
sqlUpdateCommandPurch.Parameters["@Serial"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].Serial = dataGridViewPurch.Rows[row].Cells["serial"].Value.ToString();
else
sqlUpdateCommandPurch.Parameters["@Purchwarranty"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchWarranty = Convert.ToDecimal(dataGridViewPurch.Rows[row].Cells["PurchWarranty"].Value.ToString());
sqlUpdateCommandPurch.Parameters["@Original_PurchID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchID;
sqlUpdateCommandPurch.Parameters["@Original_purchBillID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchBillID;
sqlUpdateCommandPurch.Parameters["@Original_purchPrice"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchPrice;
sqlUpdateCommandPurch.Parameters["@Original_Serial"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].Serial;
sqlUpdateCommandPurch.Parameters["@Original_PurchWarranty"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PurchWarranty;
sqlUpdateCommandPurch.Parameters["@Original_PresentAt"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].PresentAt;
sqlUpdateCommandPurch.Parameters["@Original_ModelID"].Value = dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].ModelID;
MessageBox.Show("Position\n" + this.BindingContext[dsPurch1, "Purch"].Position.ToString());
dsPurch1.Purch[this.BindingContext[dsPurch1, "Purch"].Position].EndEdit();
n += daPurch.Update(dsPurch1, "Purch");
dsPurch1.AcceptChanges();
MessageBox.Show("Number of records updated" + n.ToString());
}
}
else
{
MessageBox.Show("Invalid Serial no");
}
}
}
//btnNavFirst.Enabled = btnNavNext.Enabled = btnNavPrev.Enabled = btnLast.Enabled = true;
}
catch (DBConcurrencyException ex)
{
MessageBox.Show("Zero records affected.\n\n" + ex.ToString());
}
catch (SystemException ex)
{
MessageBox.Show("The source table could not be found.\n\n" + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
=======================================================================
this is meant to save and update as necessary and I’m getting the error on the line I’ve made bold and it says that
System.ArgumentException: Column names Serial can not be found
Parameter name: columnName
At system.Windows.Forms.DataGridViewCellCollection.get_Item(String columnName)
I’ve set up the datamember property to u know the child of PurchBill that is the purch table. I think there are a few errors in the datagridView class (maybe).
Plz help me out of this, I think I’m getting crazy here
Thanks in advance
What I don’t understand is that I’ve done the same work for anoter form in the same fashion and its working fine. It seems to me that there is no property named with these columns here but why is this happenin? And what should I do abt it?
Rocky
|
|
|
|
|
Hello,
I am trying to do a query from 2 tables, which I have done million of times before, but I haven't been able to find a solution to this error - where I resolved in other cases but in this case ran out of ideas:
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Here is sp:
SELECT
tc.TXT_CTR_NUMERO_SGF,
REPLACE (tc.DEC_CTR_VLR_PI , ',' , '.') + REPLACE (tc.DEC_CTR_VLR_ADITIVO , ',' , '.') AS Total_PI_ADIT,
convert(varchar(30), Sum(convert(decimal(20,2), REPLACE(mr.DEC_MDR_VLR_PI, ',' , '.')))) VALOR_MEDIDO,
tc.COD_ID_CTR,
tc.COD_ID_TOB,
tc.DEC_CTR_VLR_SALDO_MEDIDO
FROM
tbContrato tc
LEFT JOIN tbMedicaoResumo mr
ON tc.COD_ID_CTR = mr.COD_ID_CTR
GROUP BY
tc.TXT_CTR_NUMERO_SGF, tc.DEC_CTR_VLR_PI, tc.DEC_CTR_VLR_ADITIVO,
tc.COD_ID_CTR, tc.COD_ID_TOB, tc.DEC_CTR_VLR_SALDO_MEDIDO
I have tried to convert all the text, nvarchar, etc to varchar and used like instead of =, but the problem seems to be with the grouping part!
A solution would be greatly appreciated.
Reza
|
|
|
|
|
Have you tried:
...<br />
GROUP BY<br />
CAST(tc.TXT_CTR_NUMERO_SGF AS varchar(8000),... ?
|
|
|
|
|
Actually did, well the convert, but didn't seem to recognize it in group clause.
I did find a solution though by breaking it into parts. Seems like when you have an agragate function inside a sp, the grouping part gets very picky, so I left for more experts 
|
|
|
|
|
Interesting, I never thought of doing that before. I've always known you can't group by ntext and image fields but never considered casting it like that, just coded around it.
|
|
|
|
|
Hello everyone !!!
I want to migrate SQL Server 2000 database onto SQL Server 2005 database.
The original SQL Server 2000 database tables contain some Japanese characters and I need to
transfer them as well to SQL Server 2005 database.
For this purpose I have done following things.
1. Exported all tables data to a excel sheet. (by choosing Microsoft Excel 97/2000)
2. But when I try to import this data to SQL server 2005 I am getting following error
It verifies it. (error)
Message
Error 0xc02020f6: data flow task: The character-string data type of the Unicode form and the character-string data types of the form other than Unicode cannot be converted by the row "Description".
(SQL Server import and export wizard)
Error 0xc004706b: "Component" conversion of the data flow task ahead- tblCMSTypeMaster "(26)" failed in the verification, and the verification state "VS_ISBROKEN" was returned.
(SQL Server import and export wizard)
Error 0xc004700c: data flow task: It failed in the verification because of one or more
components.
(SQL Server import and export wizard)
Error 0xc0024107: data flow task: The error occurred while verifying the task.
(SQL Server import and export wizard)
I guess this problem is related to importing of Japanese characters.
I am stuck with this problem. Any solution/suggestion on this issue is greatly appreciated.
Thanks in advance.
Thanks & regards,
Vaibhav
|
|
|
|
|
Hi all
i have a table Student
id FName LName
-----------------------------
1 Rahit singh
2 Mohit null
3 Sanjay malhotra
4 Pankaj null
i want a sql query which will return the following layout
id Name
-----------------------------
1 Rahit singh
2 Mohit NA
3 Sanjay Malhotra
4 Pankaj NA
please any one help me
thanks in advance...
thanks n regards,
Srinandan
|
|
|
|
|
Hi srinandan,
which dialect of sql are you using exactly, because you have to write a case-statement I think to get this done with NA...
e.g. for Sybase:
<br />
SELECT id, (FName + ' ' + (CASE WHEN (LNAME is null) THEN 'NA' ELSE LNAME END)) AS NAME <br />
FROM Student order by name<br />
(For Sybase there is also a function named IsNull to perform a null check with an assignment of a default value if the tested one is null)
Hope this helps you a bit,
regards
Sebastian
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
