|
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 ifs? 
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? 
|
|
|
|
|
I'd rather use a left join instead of a function - this causes a sub select on every line, lousy design!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
True, but we don't know how it'll be (mis-)used.
|
|
|
|
|
hi iam using sql server 2000
i want to execute stored procedure in a function
and the same function i want to call in view.
can you give example which helps me
|
|
|
|
|
developerit wrote: i want to execute stored procedure in a function
This is not possible by design, but you can convert the stored procedure in a function and then call it from another function or stored procedure.
developerit wrote: and the same function i want to call in view.
Not at all possible, View is a mare logical representation of a select statement over tables/ other views. An in line table-valued function can perform as a parametrized view and you can call that in other functions/ stored procedures.
WJFK (Write Just for Kicks)
|
|
|
|
|
Hello, I have a sql (t-sql) question.
I have two tables Orders and Agents.
Each agent does a transaction (bying or selling) and transactions are recorded in Orders table.
Columns in table "Orders":
ItemId, bla, bla, bla, Seller, Buyer
Columns buyer and seller are ID of responsible agent.
Columns in table "Agents":
AgentName, AgentSecondName, ID, bla, bla, bla
NOW i want to display list of all agents alongside with numbers of sold and bought items, in a new table like this:
AGentName, AgentSecondName, AgentId, Bought, Sold
My query is
SELECT AgentName, AgentSecondName, ID
(SELECT COUNT(*)
FROM Orders
WHERE (Seller = Agents.ID)) AS SOLD,
(SELECT COUNT(*)
FROM Orders
WHERE (Buyer = Agents.ID)) AS BOUGHT
FROM Agents
but itąs damn slow with a few millions of records that i have (although its still milliseconds its too slow), any idea how to speed it up?
|
|
|
|
|
There are a number of ways to get this data, I would probably use two left joins and a sum on a case statement based on the id in the seller/buyer fields. You would need to create the alternative queries and run them through the profiler to evaluate the performance.
Performance is more likely to be improved by adding an index to the buyer and seller id fields on the orders table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How's about this?
SELECT ID=MAX(Agent.ID)
,FirstName=MAX(Agent.FirstName)
,LastName=MAX(Agent.SecondName)
,Sold=SUM(CASE Seller WHEN Agent.ID THEN 1 ELSE 0 END)
,Bought=SUM(CASE Buyer WHEN Agent.ID THEN 1 ELSE 0 END)
FROM Agent
LEFT OUTER JOIN [Order]
ON Agent.ID=[Order].Seller
OR Agent.ID=[Order].Buyer
GROUP BY Agent.ID
(And, yeah, be sure to index Buyer and Seller)
|
|
|
|
|
Have a data truncation problem at NULL byte, while trying to store binary array to BLOB
Table 'major' structure is:
(smallint) Frame_number|(longblob) ch1|(longblob) ch2|(longblob) ch3|(longblob) ch4|(smallint) frame_size
Connection to base is done, memory for all necessary handles is allocated.
1. Prepare statemt
SQLPrepare(sql_hStmt,_T("INSERT INTO major(Frame_number,CH1,CH2,CH3,CH4,frame_size) VALUES(?,?,?,?,?,?)"),SQL_NTS);
2. Binding params
SQLBindParameter(sql_hStmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_INTEGER, 0,0, &frames_parsed, 0, 0);
SQLBindParameter(sql_hStmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 4, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 5, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 6, SQL_PARAM_INPUT, SQL_INTEGER, SQL_INTEGER, 0,0, &datasize, 0, 0);
where v - array holding data to be stored to DB. bufsz - its size
3. Cycle execution
SQLExecute(sql_hStmt);
Ant suggestion would be great, thanks.
ver MySQL - 5.5, ver OBDC - Mysql ODBC 5.1
|
|
|
|
|
First check parameter value for null or empty content, if is null then send DBNull value (in .NET is DBNull.Value) , if is not null then send value of array.
Hope this will help you 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi! Thanks, that was helpful, but only partly, since passing SQL_NULL_DATA (-1) instead of 0x00, I get 0xFF value in the base field. if (v[i] == 0x00) v[i] = SQL_NULL_DATA; How do I later distingush real FF bytes from wraped null data?  Still many thanks for help
|
|
|
|
|
I have the fun job of increasing the field lengths of some of our SQL Table columns, Stored Proc parameters, Data Access Layer, GUI, etc. I am looking for a better way than the current way of having hard coded, or constants scattered throughout our code and SQL to define a field length of say 30.
Thinking big, wouldn't it be nice to define a field length or schema in one location that both SQL and code could use for validation? Anyone have any best practices for this problem they would like to share?
Thanks,
Bill
|
|
|
|
|
SQL Server already has all this meta data in the system views, these can be used for validation based on the existing tables.
Adding another layer of abstraction to manage all the data field lengths/formats sounds like a disaster looking for somewhere to happen. We use code generators so we can reproduce the relevant parts of the DAL/Model/ViewModel code, based on the database tables, this leaves us with the table and the UI/View to be refactored when a change is required.
A utility to validate the property based on the table would be trivial but you now need a property with a length attribute in your DAL/Model layer.
If you define a field standard lenght (eg name will always be 150 characters in length) there will always be exceptions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
