March 31, 2008 at 2:38 am
[font="Verdana"]
INSERT INTO #financial_consult
SELECT
accountno,
lastname1,
name1,
name2,
city,
street,
number,
zip,
phone1,
phone2,
phone3,
date,
date_of_birth1,
FROM ' +
@source_file +
'
WHERE
date>=' + @mindate + ' AND
date<=' + @maxdate
It is obvious. It happens when developer tries to insert non-date values into date. First convert the date, in SELECT and WHERE clause as well.
Let us know.
Thanks,
Mahesh[/font]
MH-09-AM-8694
March 31, 2008 at 7:28 am
The date time problem's easy to fix. The variables need to be converted to string to concat them with the rest of the query. They also need to be wrapped in quotes in your dynamicaly built string, so that SQL sees them as date literals.
WHERE
date BETWEEN ''' + CONVERT(VARCHAR(20), @mindate, 121) + ''' AND ''' + CONVERT(VARCHAR(20), @maxdate, 121) + ''''
I'll get you some code for the rest later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2008 at 8:46 am
Hi Gail
And thanks.
You think i didnt try to convert into date ? I did (before i saw your notification) and then i went into scope problems and now i'm learning how to work with sp_executesql which apparently is the only way to deal with "Dynamic sql", for me however.
The time you send your updated code maybe my Dynamic code will do something more then just error messages..
Thanks
April 7, 2008 at 1:33 pm
Apologies, this is more than a bit later...
How does this look?
There is some stuff left out, I don't know how you're doing the export of mailing, but the comments should make sense. It's not tested, cause I don't have any sample data. It does parse fine.
ALTER PROCEDURE dbo.sp_financial_consult
@list_of_consultants VARCHAR(MAX),
@list_of_emails VARCHAR(MAX),
@mindate DATETIME,
@maxdate DATETIME,
@source_file VARCHAR(50)
AS
DECLARE @NoOfConsulants INT
/* Not necessary
DECLARE @array_of_parameters TABLE
(
ordinal INT,
name VARCHAR(50),
email VARCHAR(250)
)*/
/* -- Not used
DECLARE @array_of_names TABLE
(
ordinal INT,
name VARCHAR(50)
)
INSERT INTO @array_of_names
SELECT occurenceid, splitvalue
FROM dbo.make_table_from_string(@list_of_consultants,',')
*/
DECLARE @array_of_mails TABLE
(
ordinal INT,
mail VARCHAR(250)
)
INSERT INTO @array_of_mails
SELECT occurenceid, splitvalue
FROM dbo.make_table_from_string(@list_of_emails,',')
SELECT @NoOfConsulants = @@RowCount -- How many emails do we have
/* -- Not used
INSERT INTO @array_of_parameters
SELECT
a.ordinal,
a.name,
b.mail
FROM
@array_of_names AS a LEFT JOIN @array_of_mails AS b
ON
a.ordinal=b.ordinal*/
CREATE TABLE #financial_consult (
ordinal INT IDENTITY(1,1),
accountno VARCHAR(20),
lastname VARCHAR(50),
name VARCHAR(50),
spouse_name VARCHAR(50),
city varchar(50),
address1 varchar(50),
address3 varchar(10),
zip varchar(10),
phone1 VARCHAR(50),
phone2 VARCHAR(50),
phone3 VARCHAR(25),
date_of_join datetime,
date_of_birth datetime,
email VARCHAR(80),
AllocatedConsultant INT
)
DECLARE @myDynamic VARCHAR(MAX)
SET @myDynamic=
'
INSERT INTO #financial_consult
SELECT
accountno,
lastname1,
name1,
name2,
city,
street,
number,
zip,
phone1,
phone2,
phone3,
date,
date_of_birth1,
email,
NTILE(' + CAST(@NoOfConsulants AS VARCHAR(3)) + ')
OVER (Order By accountno) as AllocatedConsultant
FROM ' +
@source_file +
' WHERE
date BETWEEN ''' + CONVERT(VARCHAR(20), @mindate, 121) + '''
AND ''' + CONVERT(VARCHAR(20), @maxdate, 121) + ''''
PRINT @myDynamic
EXEC (@myDynamic)
--By this point, the table #financial_consult should have a
-- column listing which consultant (by number) the telephone no goes to
-- Now, we have to mail, and I can't think of a set-based way to send
-- emails *g* so, cursor. *shudder*
DECLARE @emailAddress varchar(250), @Grouping INT
SET @Grouping = 1
DECLARE curMailConsultants CURSOR LOCAL FAST_FORWARD
FOR SELECT mail FROM @array_of_mails
OPEN curMailConsultants
FETCH NEXT FROM curMailConsultants INTO @emailAddress
WHILE @@FETCH_STATUS = 0
BEGIN
-- Export the results of the following query to a file
SELECT * FROM #financial_consult WHERE AllocatedConsultant = @Grouping
-- And mail it to the email address contained in @emailAddress
SET @Grouping = @Grouping + 1
FETCH NEXT FROM curMailConsultants INTO @emailAddress
END
CLOSE curMailConsultants
DEALLOCATE curMailConsultants
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 12:41 am
Ho Gail,
Thanks a lot and you are looking great !
I haven't checked all the in and outs of your code but i noticed it contains a half the size of mine, half the parameters and you were using ntile function which i'm not acquainted to. Since my cumbersome sp works and i'm into other difficulties now (2 table, the one with foreign key to the other, when i insert value to that fk can i get radio buttons of all values possible and select one rather then write it explicitly ? ) in a day or to i'll get into your code and maybe i'll need to ask you about ntile if i dont have good explanation in BOL.
Thank you very much for your aid, time and efforts !
April 8, 2008 at 1:37 am
Hi Gail ,
Despite what i had notify i couldnt deny checking your code. Attached is the first part of your code with the last row causes an error message. It is about Dynamic code where i dont know how to assimilate date variable with the rest of the string. I prefer to use sp_executesql and it generally works. It seems you prefer to "do it yourself" but there is an error. This is the part of the code that works except the last line:
/*exec dbo.sp_gailscode
'name1, name2,name3',
'name1@mail.com,name2@mail.com,name3@mail.com',
'20080131',
'20080331',
'vip_cancel_statistics.dbo.vips'*/
ALTER PROCEDURE dbo.sp_gailscode
@list_of_consultants VARCHAR(MAX),
@list_of_emails VARCHAR(MAX),
@mindate DATETIME,
@maxdate DATETIME,
@source_file VARCHAR(50)
AS
DECLARE @NoOfConsulants INT
DECLARE @array_of_mails TABLE
(
ordinal INT,
mail VARCHAR(250)
)
INSERT INTO @array_of_mails
SELECT occurenceid, splitvalue
FROM dbo.make_table_from_string(@list_of_emails,',')
SELECT @NoOfConsulants = @@RowCount -- How many emails do we have
CREATE TABLE #financial_consult (
ordinal INT IDENTITY(1,1),
accountno VARCHAR(20),
lastname VARCHAR(50),
name VARCHAR(50),
spouse_name VARCHAR(50),
city varchar(50),
address1 varchar(50),
address3 varchar(10),
zip varchar(10),
phone1 VARCHAR(50),
phone2 VARCHAR(50),
phone3 VARCHAR(25),
date_of_join datetime,
date_of_birth datetime,
email VARCHAR(80),
AllocatedConsultant INT
)
DECLARE @myDynamic VARCHAR(MAX)
SET @myDynamic=
'
INSERT INTO #financial_consult
SELECT
accountno,
lastname1,
name1,
name2,
city,
street,
number,
zip,
phone1,
phone2,
phone3,
date,
date_of_birth1,
email,
NTILE(' + CAST(@NoOfConsulants AS VARCHAR(3)) + ')
OVER (Order By accountno) as AllocatedConsultant
FROM ' +
@source_file +
' WHERE
date BETWEEN ''' + CONVERT(VARCHAR(20), @mindate, 121) + '''
AND ''' + CONVERT(VARCHAR(20), @maxdate, 121) + ''''
PRINT @myDynamic
EXEC (@myDynamic)
xxxxxxxxxxxxxxxxxxxxxxxxxx
executing @myDynamic yields the following error:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
I havnt got yet to sending mails through that procedure but it seems to be the easy part.
Would you like me to send you the data table? Is it possible to attach Excel file? Flat file?
And Thank you again for your aid and time.
April 8, 2008 at 1:42 am
Hmm. If you look at the printed out dynamic string, what does that last part (with the dates) look like?
Regardng attaching excel, it depends how you're doing the mailing what files you can attach and the like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 2:03 am
GilaMonster (4/8/2008)
Hmm. If you look at the printed out dynamic string, what does that last part (with the dates) look like?Regardng attaching excel, it depends how you're doing the mailing what files you can attach and the like.
Hi,
This is what i get printing the dynamic
INSERT INTO #financial_consult
SELECT
accountno,
lastname1,
name1,
name2,
city,
street,
number,
zip,
phone1,
phone2,
phone3,
date,
date_of_birth1,
email,
NTILE(3)
OVER (Order By accountno) as AllocatedConsultant
FROM vip_cancel_statistics.dbo.vips WHERE
date BETWEEN '2008-01-31 00:00:00.'
AND '2008-03-31 00:00:00.'
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
hope it helps
Thanks
April 8, 2008 at 2:16 am
Change CONVERT(VARCHAR(20)... to CONVERT(VARCHAR(25)... Some of the converted date is getting cut off.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 2:37 am
GilaMonster (4/8/2008)
Hmm. If you look at the printed out dynamic string, what does that last part (with the dates) look like?Regardng attaching excel, it depends how you're doing the mailing what files you can attach and the like.
I tried to mail Exel file but it doesnt seem the system here enables that.
April 8, 2008 at 2:41 am
How are you trying to mail the files? I can't offer much help unless I know what you're doing...
With SQL 2005, Database Mail's probably the easiest to set up if you don't have a mailing solution set up already.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 3:07 am
GilaMonster (4/8/2008)
How are you trying to mail the files? I can't offer much help unless I know what you're doing...With SQL 2005, Database Mail's probably the easiest to set up if you don't have a mailing solution set up already.
Left click on your name, menu item: "send email to member".
No "attach file" proposal..
April 8, 2008 at 3:11 am
Why are you trying to send me the excel spreadsheet?
I was talking about sending the exported file to your consultants.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 3:12 am
GilaMonster (4/8/2008)
How are you trying to mail the files? I can't offer much help unless I know what you're doing...With SQL 2005, Database Mail's probably the easiest to set up if you don't have a mailing solution set up already.
I seem to misunderstood, i meant sending you the data table, mailing list through SQL i'm not supposed to have problems with.
Sorry for misinforming you...
April 8, 2008 at 3:16 am
GilaMonster (4/7/2008)
Apologies, this is more than a bit later........... It's not tested, cause I don't have any sample data. It does parse fine.
[/code]
Thats why i wanted to send you the data...
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply