|
It seems like the the converted values will be in the format "yyyy/mm/dd"
and those which have to be converted will be in the format "dd/mm/yyyy".
So check the records which has 3rd character '/' and then covert those values alone.
In the update clause , include a where condition as follows:
WHERE SUBSTRING([Date],3,1)='/'
Hope that helps you.
Regards,
Arun Kumar.A
|
|
|
|
|
For SQl-Server 2000, the following should work:
if exists(select sc.*
from sysusers su
inner join sysobjects so
on so.uid = su.uid
inner join syscolumns sc
on sc.id = so.id
where su.name = 'dbo'
and so.name = 'OtherCall'
and so.xtype = 'U'
and sc.name 'Date'
and sc.usertype = 2)
begin
--your update
--your alter-table
endIt should also work with SQL-Server 2005, but you ought to use the new system-catalog views.
Regards
Andy
|
|
|
|
|
Hello all!
I've got some problem. When i'm executing about 10 stored procedures it's tooks 5 second to complete.
Here is procedures code:
ALTER PROCEDURE [dbo].[update_source]<br />
-- Add the parameters for the stored procedure here<br />
@internal_id int,<br />
@parent_id int,<br />
@sstate tinyint,<br />
@title nvarchar(50),<br />
@rng decimal(4,2),<br />
@lat dbo.latitude,<br />
@lon dbo.longtitude,<br />
@course dbo.angle,<br />
@speed dbo.speed,<br />
@stype xml<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
DECLARE @source_id int;<br />
DECLARE @state int;<br />
<br />
SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id);<br />
IF @source_id IS NULL<br />
BEGIN<br />
INSERT INTO sources<br />
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)<br />
VALUES<br />
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);<br />
<br />
SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id);<br />
END<br />
ELSE<br />
SET @state = (SELECT sstate FROM sources WHERE source_id = @source_id);<br />
UPDATE sources SET<br />
parent_id = @parent_id,<br />
updated = getutcdate(),<br />
sstate = @sstate,<br />
title = @title,<br />
rng = @rng,<br />
course = @course,<br />
speed = @speed,<br />
stype = @stype,<br />
lat = @lat*100000000,<br />
lon = @lon*10000000<br />
WHERE source_id = @source_id;<br />
<br />
<br />
<br />
INSERT INTO source_states<br />
(source_id, sstate, acquired)<br />
VALUES<br />
(@source_id, @sstate, getutcdate());<br />
<br />
END
and i'm execute it like this:
<br />
EXEC update_source 2190074, 5, 1, N'2190074', 0,57.7386783333333,10.57468,0,0,N'';<br />
EXEC update_source 2573335, 5, 1, N'2573335', 0,63.8,9.73333333333333,0,0,N'';<br />
.....<br />
.....<br />
.....<br />
EXEC update_source 2573105, 5, 1, N'2573105', 0,59.6166666666667,10.5166666666667,0,0,N'';<br />
EXEC update_source 2573235, 5, 1, N'2573235', 0,60.4,5.35,0,0,N'';
I think that mistake not in my code but somewhere in server configuration. But i can't find it out.
Thanks a lot!
|
|
|
|
|
Which columns have indexes or primary keys specified? I would expect a clustered primary-key on the "internal_id" column and a unique index (or unique constraint) on the "source_id" column.
Regards
Andy
|
|
|
|
|
Yes it is. And it doesn't depends on table size or data size.
When it's executed in Managment Studio with Client statistics everything is zero.
Thank you for quick reply
|
|
|
|
|
If you run your 10 stored procedure calls from a Management Studio query then how long does it take?
If it takes a long time then I would be looking at a configuration issue (you should be able to comfortably hit between 30-50 calls per second).
o What other indexes do you have on that table?
o Do you have any triggers?
o What else is happening on the database server?
o Are you data files held in a compressed folder?
o Is the transaction log being written to a separate disk?
If it is really quick through the SQL-Server tools then I would be looking at your front-end code.
o Are you using the native SqlClient connection?
o Are you reusing your dbcommand object (using parameters).
o Are you opening a separate connection for each call?
o Is the database on the same server? Do you have network problems?
Hope that helps.
Andy
|
|
|
|
|
Thanks for help but...
I try to run this on clean installation on empty tables and got this in Profiler: Print Screen.
There is no triggers, compressed folders, and so on.
Theres is selected a bit more complicated procedure, but as i think it must not take 3 seconds to run got it indexes or not.
The same routines on MySQL server takes zero time and CPU perfomance.
Thanks for advises.
|
|
|
|
|
First of all, is source_id an autoincrement column? If it is, don't reselect it - use the Scope_Identity() function to retrieve it in your insert, e.g.
INSERT INTO sources
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)
VALUES
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);
SET @source_id = SCOPE_IDENTITY() If it's a uniqueidentifier column, select the GUID yourself and then insert it as part of the insert statement.
Secondly, why are you performing the SELECT @state = select in the update portion? I can't see anywhere that you are using this in your procedure.
Third, you are performing a calculation in your query. I would consider either moving it outside the query, or using a computed column in the table.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I move this database to MS SQL Express and it's start working 30 times faster as i assumed it must.
So problem somewhere in server configuration not database design.
Thanks
|
|
|
|
|
can VC++ work with microsoft access 2000?
|
|
|
|
|
|
hai friends how to add the pictures and movie in sql.
ganesh
|
|
|
|
|
Store it in an image column (SQL Server 2000) or a varbinary(max) column (SQL Server 2005)
|
|
|
|
|
|
hi friends
what is the cursor? in sqlserver2000.plz explain
regards
saravanan
|
|
|
|
|
|
cursor means to select the set of record, and mainpulate that record and stored in againg to the database.
for example
declare cursor s1 as select * from emp where salary>1000;
if s1 is the cursor name. The cursor having the records in salary is greater than 1000. we can trace each record and to manipulate the record and to again store to database.
|
|
|
|
|
Based on your last several posts it looks like you are wanting people to answer your homework questions. Use Google for your broad questions.
__________________
Bob is my homeboy.
|
|
|
|
|
This is why most of my answers involve simply a google search lol, unless a question is specific and indicates the author has atleast looked for information and a soltuon of course 
|
|
|
|
|
I think that's fine for maybe the first or second post, but I think this user has been warned before.
__________________
Bob is my homeboy.
|
|
|
|
|
I am using a matrix control in a report containing 2 subreports. The matrix control is present in one of the subreports. When I export the full report into the PDF,if the matrix are rendered in a single page, the report is fine and there are no blank pages. But when the number of columns exceeds the first page and moves into the second page then there are 3 blank pages in the report for every page with content. The number of blank pages after each page with content is equal to the number of times the matrix would scroll into a new page.
This problem occurs only when I try to generate the whole report in PDF. No blank pages appear when I render subreports into PDFs individually.
I am using SQL Reporting Services 2000 SP1.
Thanks in advance.
-- Ravi
|
|
|
|
|
Hi Guys..
Can anyone tell me why this DTS Package is used for, and how to implement it..???
The name is Sandeep
|
|
|
|
|
I have some code (C#) that runs an SQL update query that sets the value of a column to what the user passes. So, this causes an error when anything the user passes in has a ' character in it. I'm sure there's other characters that'll break it too. So, I was wondering, how do I get around this? Is there some commonly accepted regex pattern that will make the value safe to run in an SQL query? How can I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();
/\ |_ E X E GG
|
|
|
|
|
You have to use either Parameters in the update command.
But the best practice is to pass the users input
to a "STORED PROCEDURE" and let the SP do the job.
Regards,
Arun Kumar.A
|
|
|
|
|
The problem is that ' ends your quotes for your string value. In fact, if you typed this "value' GO drop database mydatabase GO", you'd find that the SQL after the ' GO is going to execute. You need to read the articles on SQL injection attacks that exist here on CP.
The short answer is to replace ' with ''. But, please read the article, your code is not safe.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
