|
I have a table MyTable with columns col1,col2,col3,col4,col5 in which col1,col2,col3 comprises of composite primary key.
I want to find the number of rows that have different col3 values for same col1 and col2 values.
I want a stored procedure which will provide @c1, @c2 as parameters
Need help
|
|
|
|
|
What have you tried so far? While I could do the query for you, it's helpful to see that you have attempted to do this yourself.
As a hint though, consider using the DISTINCT and COUNT() functions with the parameters to get the count of each. Have a go, and then let me know if you need more help (posting the SQL that you've attempted).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi
i run a query in sql server 2005 , that show me follow error message ! how can solve this problem?
<br />
Msg 468, Level 16, State 9, Line 1<br />
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the equal to operation.<br />
<br />
tanks
|
|
|
|
|
|
yes
you say right
tanks so much
|
|
|
|
|
Cross posting is not a good idea. I believe that Christian has already answered this one for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
yes
but in SQL part usually response not fast .
|
|
|
|
|
That doesn't matter. There are rules for using the forums, and you have to respect them. The forums are frequented by developers who give their time freely, so you operate on their timescales. This is one of the reasons why you don't use the word Urgent in your post anywhere.
Plus, you do need to think of more meaningful titles than help.... You'll find that a better post title will generally get you an answer quicker than random wording.
Please see this[^] article for more information.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
it's very interest for me ...
ok , i will write better subject for questions
tanks![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
I have developed a small database software in .Net 2.0 which has backend as Sql Server Express2005.Both of the server and client are running Windows XP. My client successfully detects the server but when
connecting to the server, it displays the error message "Cannot generate SSPI context".
I have also searched the internet but couldnot find any good solutions.
Could you people have any solution for this terrible error?
Help appreciated in both VB.Net and C#.
X
|
|
|
|
|
I have done this hundreds of times; however I am getting stumped on why the date will not convert. I have tried using both CAST and CONVERT.
I am using DTS to export a table to a CSV flat file. There are four fields that need to be converted, all have some null values. I am not including the nulls in the export, NO NULL.
The field Data Types are varchar (255).
The date in the fields are store in the following format 2003-12-11 (no time stamp) and I need to export them as 12-11-2003 (no time stamp). - vs / makes no difference.
I have tried the following in a query.
CONVERT (varchar,DATE1,101) NO NULL,
export does not convert, results in 2003-12-11.
CAST (DATE1 AS DATETIME) AS DATE NO NULL,
export does not convert, results in 2003-12-11.
Thanks for your help.
-- modified at 15:06 Monday 6th August, 2007
Jack Fleet
|
|
|
|
|
Use the VBScript features of DTS to perform a transform. You then get all the power of VBScript to format things however you want.
|
|
|
|
|
Thanks,
Ouch thou, I am not a vbscript writer.
Any help on it would be appreciated.
Jack Fleet
|
|
|
|
|
I have a table tblSummarySells which have columns column1, column2, column3,
column4, column5, column6,column7.
I want a stored procedure which will take parameters @c1,@c2,@c3,@c4,@c5,@c6,@c7 for each column respectively....
it will check for the @c1,@c2,@c3,@c4 already present in the tblSummarySells or not
1. If present then it will update the table values of column5, column 6, column7...
2. If not present it will insert the values into the table....
Need help urgent
|
|
|
|
|
For SQL-Server, your code should be something like:
create procedure MyProcedure
@c1 int,
@c2 int,
@c3 int,
@c4 int,
@c5 int,
@c6 int,
@c7 int
as begin
set nocount off
update MyTable set c5 = @c5, c6 = @c6, c7 = @c7
where c1 = @c1
and c2 = @c2
and c3 = @c3
and c4 = @c4
if (@@ROWCOUNT = 0) begin
insert into MyTable (c1, c2, c3, c4, c5, c6, c7)
values (@c1, @c2, @c3, @c4, @c5, @c6, @c7)
end
return (0)
endI have assumed that all of your variables are integers, and that none of your primary key columns can be null.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Thanks
But I have data already in table so is @@rowcount=0 going to work
|
|
|
|
|
The @@ROWCOUNT variable holds the number of records hit by the previous update statement.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
In Sql Server from version 2000 onwards there is a feature called an INSTEAD OF trigger that you can use. To use it, you create a copy of your table as a view and use this as the target of your inserts. Behind this view, create a trigger with the signature INSTEAD OF INSERT where you would normally put the FOR INSERT.
The trigger would look something like this:
CREATE TRIGGER InsertIntoSummarySells
INSTEAD OF INSERT
AS
BEGIN
DECLARE @C1 INT -- or whatever type it is
DECLARE @C2 INT -- ...
... -- And so on, with the parameters
SET NOCOUNT ON
UPDATE tblSummarySells
SET C5 = @C5,
C6 = @C6,
C7 = @C7
WHERE
C1 = @C1 AND C2 = @C2 AND C3 = @C3 AND C4 = @C4
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO tblSummarySells ....
END
ENDNow, the advantage of this approach is that anytime you do an insert onto the view, this code will run so it should always behave consistently - rather than having to remember to call the stored procedure to perform the update.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am getting following exception in the code below
Exception occurs when closing connection,
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The SqlCommandTimeout is already set 0 as you can see in the code below,
When i search records for first time it works fine, but the exceptions start coming second time or third time onwards,..
Protected Overrides Sub DataPortal_Fetch(ByVal criteria As Object)
' Load object data from database.
Dim crit As criteria = DirectCast(criteria, criteria)
Dim con As New SqlConnection(DB())
// db string is something like
//"data source=hurst\SQL2000_SP4;initial catalog=MYDB_OOP_11;Connection Timeout=60;user=asd_user;password=asd_user;"
Dim cmd As New SqlCommand
con.Open()
Try
With cmd
.Connection = con
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
' Set up and call appropriate stored procedure.
If crit.IsSimpleList Then
.CommandText = DBObject & "_info_simple_get"
With .Parameters
// some code for adding params for SP
End With
Else
.CommandText = DBObject & "_info_get"
With .Parameters
// adding param for Stored Procedure here
End With
End If
Dim dr As New SafeDataReader(.ExecuteReader)
Try
Me.Fetch(dr, crit.IsSimpleList)
// The fectch function loops for each read of datareader and creates a new object, passing it dr object, each object then reads its column from dr
Finally
dr.Close()
End Try
End With
Finally
con.Close() // I get exception here..on closing..
End Try
End Sub
Is it something because of improper use of DataReader ? or what is it, I really couldnt figure out, I have Tried using GC.collect() etc , but no use,
please help
Ahmed
Ahmed
|
|
|
|
|
U can Set ur own time for the connection and try.....
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
I would suspect that your problem is more to do with the connection timeout you are using in your connection string.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi
I use the following code to add a row to my database using ADO.NET in VB.NET:
Dim strConnstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DB.mdb"
Dim Sql As String = String.Format("INSERT INTO Employee(FName,LName,Salary) VALUES('{0}','{1}',{2})", txtFirstName.Text, txtLastName.Text, txtSalary2.Text)
Dim Conn As New OleDbConnection(strConnstring)
Dim Comm As New OleDbCommand(Sql, Conn)
Dim DA As New OleDbDataAdapter(Comm)
Dim DS As New DataSet
Conn.Open()
Comm.ExecuteNonQuery()
Conn.Close()
Everything looks fine, the datagrid which shows Employee table, shows that the item is added.
But DataAdapter does not update the dataset! I noticed that when I add a row by this method, the State of the row is not set to Added, like when I use Rows.Add (which works just fine!)
I need to do this using this SQL command, but how to update the database also?
(I again notice that myDataSet.GetChanged is null!)
|
|
|
|
|
Hi,
I have configured MAPI profile using POP3 and the same profile has been configured in SQL server 2000 in SQL server Agent(Management) and in SQL Mail(Support Services).Mail is sent successfully when we compose it manually through Microsoft office outlook.The problem is that when i execute XP_sendmail stored procedure is executing fine and the output is Mail Sent.But the mail is remaining in outlook itself.When i open the outlook Send/Receive is happening and the mail is sent successfully.Unless i open outlook Iam unable to send the mail.Please suggest me what would be the possible solution for this.Any help from you side is appreciated.Thanks in Advance.
regards,
Shaik.
|
|
|
|
|
My only advice is not to use SQL Server 2000 built-in mail capabilities - consider using blat.exe[^].
This[^] may be helpful.
|
|
|
|
|
Hi,
I need to bring back records in a certain date range.
I need it to be from May 2007 to August 2007. I can pass through (2007 and 5) and (2007 and 8) but not sure how my WHERE will look like.
Please can some one help??
Regards
ma se
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
