BULK INSERT and collation problem - Database Discussion Boards

Re: How to set the timer for SSIS package

Marek Grzenkowicz15-Jul-08 22:58

Marek Grzenkowicz

15-Jul-08 22:58

What do you mean?
Do you want this package to execute every 10 seconds?

Re: How to set the timer for SSIS package

nagveni16-Jul-08 10:05

nagveni

16-Jul-08 10:05

Yes. I need that package to be executed by itself for every 10 seconds.

Thanks,
Veni

Re: How to set the timer for SSIS package

Marek Grzenkowicz19-Jul-08 9:50

Marek Grzenkowicz

19-Jul-08 9:50

Maybe you could use this trick[^].

But the idea of an SSIS package executed every 10 seconds keeps bugging me. What kind of package is it? What does it do? Does it take less to 10 seconds to execute it?

convert datetime format

tai-fun14-Jul-08 11:01

tai-fun

14-Jul-08 11:01

(i can speak some engish so i hope you are understand o my writes)
using sql server 2005;

how to convertdatetime as yyyy/mm/dd hh:mm

i write a user defined function as below but that function result show as 2008-07-14 23:07:00.000.... i want result of function show as 2008-07-14 23:07 or 14-07-2008 23:07

CREATE FUNCTION [dbo].[TarihDuzenle] (@TARIH DATETIME)
RETURNS DATETIME
AS
BEGIN
SET @TARIH = CONVERT(
DATETIME, CONVERT(VARCHAR, DATEPART(YYYY,@TARIH)) +'/'+
CONVERT(VARCHAR, DATEPART(MM,@TARIH)) +'/'+
CONVERT(VARCHAR, DATEPART(DD,@TARIH)) + ' '+
CONVERT(VARCHAR, DATEPART(HH,@TARIH)) + ':'+
CONVERT(VARCHAR, DATEPART(MM,@TARIH)) + ':00'
)
RETURN @TARIH
END

Re: convert datetime format

Arindam Tewary14-Jul-08 22:09

Arindam Tewary

14-Jul-08 22:09

Try this

select convert(varchar,getdate(),120)

It returns date in yyyy-mm-dd hh:mi:ss(24h) format. Smile | :)

Thanks,
Arindam D Tewary

Re: convert datetime format

tai-fun15-Jul-08 1:15

tai-fun

15-Jul-08 1:15

thanks for your answer.

is possible not show second?

Re: convert datetime format

leoinfo15-Jul-08 5:35

leoinfo

15-Jul-08 5:35

select convert(varchar<big>(16)</big>,getdate(),120)

modified on Tuesday, July 15, 2008 9:41 PM

BULK INSERT and collation problem

msx2314-Jul-08 10:56

msx23

14-Jul-08 10:56

I'm trying to use BULK INSERT to import a text file and then check its contents against a table in the database. I'm getting a problem when I compare the results however.

I'm making a temp table to hold the text file's contents

CREATE TABLE #Temp(Code varchar(5), number varchar(2))

Then using BULK INSERT to get the contents in

BULK INSERT #Temp FROM 'PATH.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

And then checking if any matches occur

SELECT Code, (CASE WHEN
				(SELECT Count(DBTable.Code) From DBTableWHERE DBTable.Code= #Temp.Code) > 0	
			 THEN "Yes"
			 ELSE "No" END) AS FLAG
From #TempCCServs

The problem is that I get the following error message now:
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

Anyone have an idea what I'm doing wrong?
Thanks for any help.

Re: BULK INSERT and collation problem

Syed Mehroz Alam15-Jul-08 1:08

Syed Mehroz Alam

15-Jul-08 1:08

The two columns in the comparison differ in collation settings.

The following code applies sames collation to both sides before the comparison.
SELECT Count(DBTable.Code) From DBTable WHERE DBTable.Code collate Latin1_General_CI_AS = #Temp.Code collate Latin1_General_CI_AS

Hope that helps.

Regards,
Syed Mehroz Alam

My Articles

Where To Buy SQL 2000 Reporting Service? [modified]

thundernow14-Jul-08 4:21

thundernow

14-Jul-08 4:21

My eval version that has been serving about 10 users in an intranet setup expired yesterday. I searched online and seems now it's all the 2005 versions on the market. Is there still anywhere I can find the 2000 version? Thanks.
btw for now my work around is just to reinstall the eval to get another 120 days.

run forest, run...

modified on Monday, July 14, 2008 2:20 PM

Re: Where To Buy SQL 2000 Reporting Service?

DerekFL14-Jul-08 7:51

DerekFL

14-Jul-08 7:51

I think Reporting Services comes with SQL 2005 Express Edition. http://www.microsoft.com/sql/editions/express/default.mspx[^]

Find Last week dates?

Member 387988114-Jul-08 3:04

Member 3879881

14-Jul-08 3:04

Using sql query its possible to find the last week dates?,

Mean Current date is 7/14/2008...,

So last week dates 7/7/2008 to 7/12/2008 have to come...,

Thanks & Regards,
NeW OnE,
please don't forget to vote on the post

Re: Find Last week dates?

subai14-Jul-08 3:21

subai

14-Jul-08 3:21

to find first day of week use DATEPART() with the "dw" parameter

I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe
www.subaitech.blogspot.com

Re: Find Last week dates?

leoinfo14-Jul-08 3:35

leoinfo

14-Jul-08 3:35

SET DATEFIRST 1; 
DECLARE @d DATETIME; SET @d = CONVERT(NVARCHAR(10), GETDATE(), 121); 
DECLARE @x INT; SET @x = DATEPART(dw, @d) ; 
WHILE @x > 1 
BEGIN 
  SET @d=@d-1 ; 
  SET @x=DATEPART(dw, @d) ; 
END ; 


SELECT 
    CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AS FirstDayOfLastWeek  
  , CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101) AS LastDayOfLastWeek 
;

Re: Find Last week dates?

TheFM23414-Jul-08 6:26

TheFM234

14-Jul-08 6:26

Select Cast(Cast(GetDate() as int) as datetime) As Today
	,Cast(Cast(DateAdd(dd, -7, 
		DateAdd(dd, - DatePart(dw, Getdate()) + 1,
		GetDate())) as int) as datetime) As LastWeekStart
	,Cast(Cast(DateAdd(dd, -1,
		DateAdd(dd, - DatePart(dw, Getdate()) + 1, 
		GetDate())) as int) as datetime) As LastWeekEnd

If your wondering about the casting, it sets the time to 00:00:00.000

Re: Find Last week dates?

leoinfo14-Jul-08 18:27

leoinfo

14-Jul-08 18:27

Hmm... I think this version is working only on Mondays... but that's OK, this was in fact the question about. Smile | :)

Re: Find Last week dates?

TheFM23415-Jul-08 3:24

TheFM234

15-Jul-08 3:24

The answer before was a Monday morning answer, so I've got a better one now Big Grin | :-D

select
Convert(varchar,DateAdd(dd, -(Datepart(dw, <code>Getdate()</code>) +6), <code>GETDATE()</code>), 101) 
    As PreviousWeekStart
,Convert(varchar,DateAdd(dd, -Datepart(dw, <code>Getdate()</code>), <code>GETDATE()</code>),101) 
    As PreviousWeekEnd

You can always replace the GETDATE() that I highlighted in the dark red with another date. This one works for any day of the week.

Re: Find Last week dates? [modified]

Member 387988114-Jul-08 18:47

Member 3879881

14-Jul-08 18:47

Thank u,And one more thing i want to find the last week is which saturday...,

It mean its 2nd or 4th saturday or odd saturday...,

Becoz based on that i want to calculate workdays

If its even(2nd or 4th) saturday then i want to show workdays 5

else if its odd(1st or 3rd) saturday then i have to show workdays 6 how to do that?,</b>

Plz help me

Thanks & Regards,
NeW OnE,
please don't forget to vote on the post

modified on Tuesday, July 15, 2008 1:10 AM

How to write query

BalasubramanianK14-Jul-08 0:23

BalasubramanianK

14-Jul-08 0:23

Hi,

My Table structure is look like this.....Code, Q1, Q2, Q3 and records are like...

Code Q1 Q2 Q3
1 Good Good Average
2 Poor Average Average
3 Good Excellent Average
4 Excellent Good Good
etc.....

I need to write a single query, which will give result look like.

Excellent Good Average Poor
Q1 1 2 0 1
Q2 1 2 1 0
Q3 0 1 3 0

Actually, this table is used to get the feedback from the users. User has given their feedback by selecting options. I need the details that how many persons answered Excellent for Question1 (Q1), Question2 (Q2), etc...As well as How many answered Good for Q1,Q2,Q3, etc....I need this format. Please help me.

Balasubramanian K.

Re: How to write query

TheFM23414-Jul-08 6:53

TheFM234

14-Jul-08 6:53

SQL Server has a Pivot/Unpivot syntax, I used the unpivot for this. If your using SQL server, then this should work for you.

Select Question
,Sum(Case When Response = 'Excellent' Then 1 Else 0 End) as Excellent
,Sum(Case When Response = 'Good' Then 1 else 0 End) as Good
,Sum(Case When Response = 'Average' Then 1 Else 0 End) as Average
,Sum(Case When Response = 'Poor' Then 1 Else 0 End) as Poor
From
(Select Code, Q1, Q2, Q3
From <code>#t</code>) p 
	Unpivot
	(Response For Question In
		(Q1, Q2, Q3)) as unpvt
Group By Question

Just replace the #t (highlighted in the dark red) with your table name.

Re: How to write query

BalasubramanianK14-Jul-08 18:17

BalasubramanianK

14-Jul-08 18:17

Thanks for your reply Mr.Scott.
I am using Sql Server 2000. Will it work? I am getting error such as
Incorrect syntax near 'Unpivot'. What I have to do?

Balasubramanian K.

Delete Query

sjs4u14-Jul-08 0:10

sjs4u

14-Jul-08 0:10

Hi,

I am having 2 table. I want to delete records having same ID within 1 query.

e.g.

ID name
table1 1 a
table2 1 b

How can I do?

Thanks

Re: Delete Query

subai14-Jul-08 0:29

subai

14-Jul-08 0:29

if you want to remove only from one table you can use this

DELETE FROM TABLE_1 WHERE ID IN (SELECT ID FROM TABLE_2)

DELETE FROM Table_1
FROM         Table_2 AS t2 INNER JOIN
                      Table_1 as t1 ON t1.ID = t2.ID

but i don't know if it's possible to remove records from 2 different tables by one single query

I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe
www.subaitech.blogspot.com

Re: Delete Query

Declan Bright14-Jul-08 1:19

Declan Bright

14-Jul-08 1:19

It you have a relationship setup between the 2 tables on the id column with the cascading delete property set, then deleting the records for one table will delete the corresponding records from the other table

Declan Bright
www.declanbright.com

Re: Delete Query

TheFM23414-Jul-08 7:20

TheFM234

14-Jul-08 7:20

Write a stored procedure:

Create Proc usp_DeleteFromTables
@Id int
As

Delete From Table1
Where Id = @Id

Delete From Table2
Where Id = @Id

Then execute it. For example, if you wanted to delete id # 2:

Exec usp_DeleteFromTables 2

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.