|
If you are worried about the holes then just use correlated subqueries to find the ranges. A WHERE clause would be a pain since you would end up with a tremendous set of loopped queries if the data was of any real size. See if you can grasp this nasty beast:
SELECT
ID,
Name,
num AS StartNumber,
(SELECT
min(num)
FROM
(SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = b.num+1)) tmp
WHERE
num >= a.num) AS EndNumber
FROM
TestTable a
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = a.num-1)
I tested it with your data set so it works with SQL 2000. Be sure to change the table name (TestTable) and the column names if your database is set up as case sensitive.
The concept comes from finding the start numbers:
SELECT
ID,
name,
num
FROM
TestTable a
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = a.ID AND Name = a.Name AND
num = a.num-1)
and the last numbers:
SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = b.ID AND Name = b.Name AND
num = b.num+1)
You just have to find a way to put the two queries together in a neat correlated package.
|
|
|
|
|
thank you so much for your response. i tried to use this concept in my query at the end i do a group by id,name and num but it takes really long time to do an insert to a table. could you please tell me why its taking such a long time than normal group by query.
thanks
|
|
|
|
|
If you are running this on a large table, you will force a table scan for each portion of the query. Highly inefficient! If this query is run each time you insert a record, you will need to support it with an index.
CREATE INDEX MyIndexName
ON MyTableName
(
id,
name,
num
)
This will slightly slow down a direct INSERT/UPDATE/DELETE but, it should greatly speed up the query to find the holes. This will net you a significant gain if you are running the query on every INSERT.
|
|
|
|
|
Hi, thanks for looking
Im developing a website
On a page i have a listing of products
Products are returned by a really anidated query:
1. I have a view that returns products (Sorted by name)
2. A function then takes this results and makes some calculations and returns a table again (This function is called by many stored procedures and this is the cause of anidated query, not that i like to anidate)
3. The table is then filtered with where clauses by a stored procedure wich is the one that return results to a sqldatasource
My db has a collation of Traditional_Spanish_CI_AS
I added asp.net membership wich has a collation of Modern_Spanish_CI_AS
When i generate the scripts and try to execute them on the remote server, i get an error that says collation conflict, So i tried removing all collations and i executed the scripts succesfully
But now, i have that sorting problem
What could be the cause of this?
Thanks in advance
Alexei Rodriguez
modified on Thursday, April 17, 2008 8:37 PM
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
alter FUNCTION [dbo].[GetPhoto](@Photo nvarchar(max),@UploadedDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @GetDate DATETIME
DECLARE @strDate varchar(24)
SET @GetDate = @UploadedDate
if NOT (@Photo IS NULL)
BEGIN
--RETURN @GetDate
DECLARE @Startposition int
SET @Startposition= patindex('%Date:%', IsNull(@Photo, '') )+5
SET @strDate = SUBSTRING(@Photo,@Startposition,len(@Photo)-@Startposition)
if (len(@strDate ) < 24)
BEGIN
RETURN @GetDate
END
if not (@strDate IS NULL)
BEGIN
SET @strDate = LEFT(@strDate ,patindex('%|%',@strPhotoDate )-1)
SET @strDate = replace(@strDate ,'th','')
SET @strDate = replace(@strDate ,'nd','')
SET @strDate = replace(@strDate ,'rd','')
SET @strDate = replace(@strDate ,'st','')
SET @strDate = replace(@strDate ,',','')
set @GetDate = cast(@strDate as DATETIME)
-- set @GetPhotoDate = convert(char,Convert(DateTime,@strPhotoDate,101))
-- set @GetPhotoDate = cast(CONVERT(datetime,@strPhotoDate)-convert(datetime,substring(@strPhotoDate,3,3)) as datetime)
--cast(@GetPhotoDate as DATETIME)
END
END
RETURN @GetPhotoDate
END
like that...
it will convert the 4/12/2008,4-july-2007....but not converting the 4th july 2008
22nd june 2008.
thanks in advance
Thanks
Subbu.
|
|
|
|
|
select convert(varchar(20),convert(datetime,'3 august 1997'),103) will give you this result 03/08/1997
hope it will help...
I Love T-SQL
modified on Thursday, April 17, 2008 10:12 PM
|
|
|
|
|
Hi ,
Thanks for ur response....
select convert(varchar(20),convert(datetime,'3 august 1997'),103)querry works fine .
but i need to convert the date...'3th august 1997' .
if u find any solution then pls....... send me
Thanks in advance
Thanks
Subbu.
|
|
|
|
|
I have an insert trigger on a table that checks a certain set of conditions, and in certain cases, i want it to raise an error and stop the insert from happening.
I've used RAISEERROR to, not surprisingly, raise my error, but if i don't specify a severity of 20 or greater, then the insert just goes ahead anyway and the error gets reported back. I can't specify 20 or greater, as the user that creates the trigger then needs to be a system admin and that's not an option for me.
Has anyone any better idea of how to do this?
|
|
|
|
|
Did you add a 'Return' after the error has been raised?
Example:
IF x<br />
BEGIN<br />
RAISERROR x <br />
RETURN<br />
END
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
If it's SQL Server, use the instead of trigger.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi Everyone,
I'm application is throwing this exception, and I cant really determine the cause:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: Unspecified error
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
I don't get it all the time, it's actually very rare. It's happened about 2 times. It seems to me like the application has a problem establishing a connection with the database ... but I don't get why this would only happen sometimes.
My application uses an Access database that is constantly being accessed while using the application.
Anybody know how I can remedy this?
Thanks,
Tommy
|
|
|
|
|
That's a pretty vague error message. If I were you, I'd put a bunch of try catch blocks in there and try to get more information from the exception by popping up message boxes or printing the exception information into a log file or something. If the exception gives you a line number reference to your code at least that would be a place to start. Without more info...who knows what the underlying cause is.
Hope this helps.
|
|
|
|
|
ALTER procedure [dbo].[sp_BankInsert]
(
@bankid varchar(100),
@bankname varchar(100),
@address1 varchar(100),
@address2 varchar(100),
@city varchar(50),
@state varchar(100),
@country varchar(50),
@telephone1 varchar(20),
@telephone2 varchar(20),
@fax varchar(20),
@email varchar(50),
@contactperson varchar(100),
@cphone varchar(20)
)
begin
insert into tblBankMaster(bankid,bankname,address1,address2,city,state,
country,telephone1,telephone2,fax,email,contactperson,cphone)values('@bankid',
'@bankname','@address1','@address2','@city','@state','@country','@telephone1',
'@telephone2','@fax','@email','@contactperson','@cphone');
end
smar
|
|
|
|
|
Remove the ': should look like:
values(@bankid,
etc.
|
|
|
|
|
How to copy database design of a table into another table without copying its values
|
|
|
|
|
.NET- India wrote: How to copy database design of a table into another table without copying its values
If you want to do this manually, you can do this through Management Studio by right-clicking a table and selecting 'Script Table As... -> CREATE TO'.
If you want do do it in code, you could use SMO[^] to obtain metadata for a particular table and create another table based on this metadata.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
You could also
Select * into [NewTable] from [ExistingTable] where 1=0
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hello all,
while i am trying to connect my windows application to mysql database, I am facing problem in opening the connection.
The error msg I am getting is "The 'System.Data.Odbc' provider is not registered on the local machine".
OleDbConnection con = new OleDbConnection("provider=System.Data.Odbc;Data Source=localhost; Initial Catalog=GKBM;Persist Security Info=True");
ANYBODY KNOWS THE SOLUTION TO THIS PROBLEM PLEASE REPLY ME AS SOON AS POSSIBLE.
THANKS & REGARDS
SMAR.
SMAR
|
|
|
|
|
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 40)
i keep getting this error in the Application Log of the event viewer...
I have tried everything like changing the settings in SQL Server Surface Area Configuration...but am still getting the same error..
Am actually making a Windows Service that wud check the database for A DateTime field and if the entry in it matches the System DateTime it wud send an email to the person corresponding to whom this DateTime entry is in the database..
Please help me....
Thanks!!!
|
|
|
|
|
3 quick thoughts:
1: Did you restart the Sql service after making the changes to the surfacey widget?
2: Have you checked users/permisions?
3: Is your connection string correct? (Using the server tab in VSxxxx) Can you connect to the database?
Sorry if you've already done these.
|
|
|
|
|
Hi all,
i just want to know about how to Convert date formate 4th april 2008 to default date formate....
i write
select CONVERT (datetime, '4 July 1997')
it shows 1997-07-04 00:00:00.000
but i need
select CONVERT (datetime, '4th July 1997')
o4th july 1997 to show the default formate...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
The CONVERT function has a number of options for different date formats. Read the documentation and choose the most appropriate format for your requirements.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
if not (@Date IS NULL)
BEGIN
SET @Date = LEFT(@strPhotoDate,patindex('%|%',@strPhotoDate )-1)
SET @Date = replace(@strPhotoDate,'th','')
SET @Date = replace(@strPhotoDate,'nd','')
SET @Date = replace(@strPhotoDate,'rd','')
SET @Date = replace(@strPhotoDate,'st','')
SET @Date = replace(@strPhotoDate,',','')
set @GetPhotoDate = CONVERT(datetime, @Date)
--cast(@GetPhotoDate as DATETIME)
END
END
like that...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
if not (@Date IS NULL)
BEGIN
SET @Date = LEFT(@strPhotoDate,patindex('%|%',@strPhotoDate )-1)
SET @Date = replace(@strPhotoDate,'th','')
SET @Date = replace(@strPhotoDate,'nd','')
SET @Date = replace(@strPhotoDate,'rd','')
SET @Date = replace(@strPhotoDate,'st','')
SET @Date = replace(@strPhotoDate,',','')
set @Date1 = CONVERT(datetime, @Date)
--cast(@GetPhotoDate as DATETIME)
END
END
like that...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
if not (@Date IS NULL)
BEGIN
SET @Date = LEFT(@strPhotoDate,patindex('%|%',@strPhotoDate )-1)
SET @Date = replace(@strPhotoDate,'th','')
SET @Date = replace(@strPhotoDate,'nd','')
SET @Date = replace(@strPhotoDate,'rd','')
SET @Date = replace(@strPhotoDate,'st','')
SET @Date = replace(@strPhotoDate,',','')
set @Date1 = CONVERT(datetime, @Date)
--cast(@Date1 as DATETIME)
END
END
like that...
thanks in advance
Thanks
Subbu.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
