|
You're welcome 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I just got some SQL Query to get updated cell like below:
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR ('Dont change the key column!!', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3
And, I want to change it into dynamic SQL since it is too much to type the column one by one:
DECLARE @COLUMN_NUMBER AS integer
DECLARE @COLUMN_TOTAL AS integer
DECLARE @COLUMN_NAME AS nvarchar(100)
DECLARE @SQL AS nvarchar(1024)
SET @COLUMN_NUMBER = 1
SET @COLUMN_TOTAL = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request')
WHILE @COLUMN_NUMBER <= @COLUMN_TOTAL
BEGIN
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request' AND ORDINAL_POSITION = @COLUMN_NUMBER)
SET @SQL =
'INSERT transaksi_requestlog(log_date, log_user, log_id, log_column)
SELECT ''' + CAST(GETDATE() AS nvarchar(20)) + ''', Inserted.request_modifiedby, Inserted.request_id, ''' + @COLUMN_NAME + '''
FROM Inserted
INNER JOIN Deleted ON Deleted.request_id' + ' = Inserted.request_id' + '
WHERE Inserted.' + @COLUMN_NAME + ' <> Deleted.' + @COLUMN_NAME
EXEC(@SQL)
SET @COLUMN_NUMBER = @COLUMN_NUMBER + 1
END
And it got an error message saying Invalid object name 'Inserted'
Can anyone help me?
Thanks
- No Signature Available -
|
|
|
|
|
AFAIK you cannot use inserted or deleted tables via dynamic sql in a trigger. Perhaps you could use COLUMNS_UPDATED or UPDATE functions instead.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Error Message:-
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
I have dotnet application and using sql server 2000 for database. This application installed at client server is working fine since last release, but from last 2 days all users, logged in to appliction. Application start sucessfully and after some time (not fixed) as application fetch the data this error is generate and its continue till user close the application.
I am not able to find out the actual root of the error.
I am feeling that code dont have any problem, since same application running fine before this error message and we don' t
do any change there.
If you required any more information please let me know about that.
Thanks in advance for the your feedback and help..
I am wating for reply...
Thanks and regard,
Nitin
|
|
|
|
|
You may be using a closed connection object. That is the most common cause.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
Hi to all
select ModelName from SRO_ModelMaster
select RegionCode from cmp_RegionMaster
SELECT Count(*) as 'TOT' FROM SRO_SurveySelectedModel,cmp_RegionMaster WHERE
modelno in (Select ModelNo From SRO_ModelMaster where ModelName='FNRO')
AND approved='Y' AND RosapCode in (
SELECT ROsapCode FROM SRO_ROMaster where region='ER')
Please tell me how i can combine these three statments into Single SQL
Statment
I want to develop Stored procedure From this without using temp table
Thanks & regard
Sonia
|
|
|
|
|
Basically you need to join those tables and then group by ModelName and RegionCode but to get this statement further: what is the relationship between those four tables?
Your query should look something like:
SELECT SRO_ModelMaster.ModelName,
cmp_RegionMaster.RegionCode,
Count(*) as 'TOT'
FROM SRO_SurveySelectedModel,
cmp_RegionMaster,
SRO_ModelMaster,
SRO_ROMaster
WHERE ???.modelno = SRO_ModelMaster.ModelNo
AND SRO_ModelMaster.ModelName='FNRO'
AND ???.approved='Y'
AND ???.RosapCode = SRO_ROMaster.ROsapCode
AND SRO_ROMaster.region='ER'
AND SRO_ROMaster.??? = ???.???
GROUP BY SRO_ModelMaster.ModelName,
cmp_RegionMaster.RegionCode
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hello All,
I am getting this error "Conversion failed to when converting datetime string to smalldatetime data type" in Sql Server 2008 and my string is 20081110072938 .....I also tried with datetime data type in Sql server but same error.
Exception comes at query
sql.Format("Insert into trade values('%s','%s','%s','%s','%s','%ld')",
m_sTimeStamp,m_sTradeStatus, m_sTradeType, m_sTradeXML, m_sTradeKcc, m_nTrdNumber);
here m_sTimeStamp has the value 20081110072938 .
query is like
insert into trade values('20081110072938','yes','Regular','xml','kcc','1');
first column is of datetime type.. it gives the conversion error in SQL Server 2008 .
.. whereas same string is getting converted to datetime format in MySQL ... and working fine with MySQL...
I am doing all this in VC++.
Can anybody help me out of this...
Thanks
|
|
|
|
|
Yes I could able to insert now by converting string 20081110110223 to 2008/11/10 11:02:23 and then using into insert query.. now both sqlserver as well as MySql could abto insert this into datetime format..
insert into trade values('20081110072938','yes','Regular','xml','kcc','1');
To
insert into trade values('2008/11/10 11:02:23','yes','Regular','xml','kcc','1');
Regards,
|
|
|
|
|
So are you still having problems with this?
Each database server typically has a different 'native' format for dates and times. The easiest (and a correct) way to overcome this is to use parameters for operations instead of literal values. For example if you're using OdbcConnection, you should use OdbcParameter class to define values for the operations.
If you still need to use literal, perhaps you could use native conversion functions such as CONVERT or CAST in SQL Server.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
So whats the problem? Where's the code thats failing?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi, how can i create a database script? so that i dont have to copy paste my declarations of creating the database and tables from notepad.
Im using MySQL Server 5.0
Thank you!
|
|
|
|
|
Can you simply save it as a text file and then use source command in mysql command line client?
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
notee (\t) Don't write into outfile.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
really? so i can just save it as db.txt and inside it it will contain:
create database myDatabase;
use myDatabase;
etc.
etc....
then i will just type:
source db.txt
?
Thank you! 
|
|
|
|
|
IT WORKED! thank you!
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
in joining the data in a certain in the database.. but how can i join this data using combo box?? can someone help me..
|
|
|
|
|
If anybody could undertsand what you are a talking about, probably we could. SQL doesn't have combo boxes, so I guess you are looking to filter the data in your code somewhere.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
I've developed an application that's using SQL Server for DB, now I want to create a setup and give it to users.
For the DB part, I have a simple '.mdf' file that is my DB and working with that like this:
sql_conn = new SqlConnection(@"AttachDBFilename=c:\test.mdf;Integrated Security=true");
then it's a simple connection, but I don't know how to use my DB in the user's computer, I should only copy the .mdf file in the user's computer? what's the exact steps to deploy a SQL DB related application?
I've searched the Internet and some eBooks + MSDN but didn't find anything about that.
Please answer soon, since I want that in a week.
Thanks in advance.
|
|
|
|
|
|
Thanks that was good,
but I'm using SQL Server 2005, how can I deploy the DB? What the user that wants to use my app should have so that my app can run properly? is the SQL Express should be installed? Please guide me on this! 
|
|
|
|
|
Are you using SQL Server 2005 Express Edition or Compact Edition?
Express Edition needs to be installed before it can be used, but Compact Edition is basically working on .Net framework so if .Net framework is installed on the client you already have all the dll's.
If you're using Express Edition, this should help you forward: Embedding SQL Server Express into Custom Applications[^].
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
ADO.NET offers a great functionality which makes it very easy to implement databinding (two directional - read and write). Let`s assume that we have a DataSet with some simple DataTable tied with TableAdapter.
Here is what we have to do:
1) Create DataTable with the standard GetData method from the Adapter
2) Assign this datatable as binding source for our datagridview on UI
3) When user makes some changes in the datagridview, it is enough to call 'Update' method on the Adapter to transfer all changes into database.
This is very easy and fast to implement. However it does not work when our DataTable contains data from more than 1 physical tables from database (any joins in select clause).
Why is it impossible? The update method on tableAdapter does not generate at all. How to solve this?
Thank you very much for any help!
|
|
|
|
|
Haven't really used TableAdapter so I'm not able to say why it's not genereting all the methods (my guess is that the generator thinks that the view isn't updatable).
On the server side there are strict rules when the view can be updated:
- Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
- The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
- An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
- A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
- The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
- TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
So you may have to use VIEW_METADATA option on the view or INSTEAD OF triggers.
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
