Database Discussion Boards

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.

me, me, me

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.

me, me, me

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.

If you are converting these dates for display purposes, you are much better to do it in your UI code.

Hi there,

I want to know if there is an executing speed difference between a View and a Table-Valued function.

Tnanks!

Regards,
Werries

A programmer's life is good... or is it?? Ek dink nie so nie!

The simple answer is yes, there are performance differences, but it all depends on what you are trying to acheive. Without further details I cannot say more. The best way to find out id to try it.

Bob
Ashfield Consultants Ltd