|
The value of TEXT_ID will be as like 1779562, 1779563, 1779564, and so on.
|
|
|
|
|
There are three records already in the database you said. What are *those* values? (List them all three please) And what is the value of the PK-field of the record that you're inserting?
I are Troll
|
|
|
|
|
I have a stored procedure with two params in sql2005. If I excute the stored procedure it takes 2 min but i copied the same query in new window, declare variables and run the query it takes 1sec. What is the problem.
|
|
|
|
|
Can you post the query?
I are Troll
|
|
|
|
|
Is it having dynamic queries? Without watching the code, it is quite difficult to find the problem. Can you post relevant code? (Please do not dump everything. Just provide relevant code and use code block link.)
|
|
|
|
|
|
Good info!
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you, I've been looking for that article for ages, it (or a similar article) was posted some time ago as a similar response and I didn't grab it then.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A friend of mine just informed me about that this week.
|
|
|
|
|
When you encounter such a performance difference, does it then matter, which one was executed first? Or in other words: is the stored procedure faster after you run the normal query?
SQL Server can cache execution plans and results, maybe that's the issue here.
|
|
|
|
|
how to write a query change the datatype for primary key coulm in atable using sqlserver 2008.
Thnaks In advance
Ch.Gayatri
|
|
|
|
|
use ALTER TABLE , you might also consider this[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i tired but its did not worked ou becoz my primary key is used as Fkey in so many tables.
|
|
|
|
|
Then you have a serious design problem, what are the data type changes you want to affect?
You could do this right by:
Identify and drop all the FK referencing the table
use alter table to change the data type on the redacted table
change the data types on all the fk fields
recreate the fk you identified and dropped.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You are in big problem, just drop refference and recreate them. Thanks
|
|
|
|
|
We have a maintenance plan set up to run on a set period taht includes indexing the databases. For some reason, the index tasks is faling with the following:
Failed:(-1073548784) Executing the query "ALTER INDEX [PK_redactedname] ON [dbo].[tblredactedname] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'PK_redactedname' because the index contains column 'redactedfield' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm not an expert so I could be reading this wrong but the field that it's referring to...is not an identity field nor is it indexable so why is it trying? The message code is unfortunately too generic judging from google fu. I've tried looking around to see if there's a setting I need to override so that it doesn't try to index this table but to no effect.
Can anyone offer some pointers?
|
|
|
|
|
Can you delete the PK_redactedname key and/or remove the index.
If the key/index does not exist then look into the sysobjects meta data or the system views, you may have a left over entry that needs removing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply, in the end the index itself was marked as being clustered to include fields that were text ones.
|
|
|
|
|
hello
I'm using Trasnactionscope from my asp.net running on app server (SQL separate physical machine) - to enable MSDTC should I following this procedure[^] on app server? Or the db server?
Thanks
dev
|
|
|
|
|
Hey, I have the following code but I dunno why I get and error!!??
<pre><pre> CREATE FUNCTION [dbo].[GetSubTeacher] (@ID Int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @TeacherName NVARCHAR(100)
IF (@ID = 0)
SET @TeacherName = 'NO SUB'
IF (@ID <> 0)
SET @TeacherName = SELECT FName + ' ' + LName AS [Name] from Teachers WHERE ID = @ID
RETURN @TeacherName
END
Incorrect syntax near the keyword 'SELECT'.
Regards,
K
|
|
|
|
|
Try this
CREATE FUNCTION [dbo].[GetSubTeacher] (@ID Int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @TeacherName NVARCHAR(100)
IF (@ID = 0) BEGIN
SET @TeacherName = 'NO SUB'
END
IF (@ID <> 0) BEGIN
SET @TeacherName = (SELECT FName + ' ' + LName AS [Name] from Teachers WHERE ID = @ID)
END
RETURN @TeacherName
END
modified on Monday, August 9, 2010 8:10 AM
|
|
|
|
|
You were very close:
IF (@ID <> 0)
SELECT @TeacherName = FName + ' ' + LName AS [Name] from Teachers WHERE ID = @ID
|
|
|
|
|
What's with the if s?
alter FUNCTION GetSubTeacher
(
@ID integer
)
RETURNS Varchar(100)
AS
BEGIN
RETURN ( SELECT CASE @ID WHEN 0 THEN 'NO SUB' ELSE ( SELECT FName+' '+LName FROM Teachers WHERE ID=@ID ) END )
END
|
|
|
|
|
Add in ISNULL() as well (you may need to move the WHEN)
alter FUNCTION GetSubTeacher
(
@ID integer
)
RETURNS Varchar(100)
AS
BEGIN
RETURN ( SELECT CASE ISNULL(@ID,0) WHEN 0 THEN 'NO SUB' ELSE ( SELECT FName+' '+LName FROM Teachers WHERE ID=@ID ) END )
END
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well I don't like the "magic number" anyway; I'd rather simply search and return "NO SUB" if the ID doesn't exist.
Besides, where'd they get the ID in the first place?
|
|
|
|