Comments by Abhi1 Kanobi

Abhi1 Kanobi 23-Jul-18 10:19am View

Thank you, for your response.

Any way I can encode it before it loads?

Abhi1 Kanobi 23-Jul-18 10:19am View

Thank you, for your response.

Any way I can encode it before it loads?

Abhi1 Kanobi 16-Mar-18 3:10am View

whats this?

Abhi1 Kanobi 16-Mar-18 1:36am View

Perfect!!! this solution works.
Thanks for the answer

Abhi1 Kanobi 15-Mar-18 10:01am View

Actually , the problem is, I am making a dynamic query based values supplied by the user . @sqlquery in the problem is a string that is executed. All my columns are datetime .

when I am not casting and comparing the @createdDate directly , it gives error as mentioned below "The data types nvarchar and date are incompatible in the add operator."

Abhi1 Kanobi 15-Mar-18 10:00am View

Actually , the problem is, I am making a dynamic query based values supplied by the user . @sqlquery in the problem is a string that is executed. All my columns are datetime .

when I am not casting and comparing the @createdDate directly , it gives error as mentioned below "The data types nvarchar and date are incompatible in the add operator."

Abhi1 Kanobi 3-Jul-17 16:18pm View

For now yes . I need to achieve it using javascript only

Abhi1 Kanobi 3-Jul-17 16:18pm View

Deleted

For now yes . I need to achieve it using javascript only

Abhi1 Kanobi 12-Jun-17 6:53am View

Ok , I am trying

Abhi1 Kanobi 12-Jun-17 6:12am View

OK , but will it attach the file or will it embed it in the body ,
I need to have it embedded in the body

Abhi1 Kanobi 12-Jun-17 5:59am View

Yes , you are right . the way should not be followed .
What should be the right way?

Abhi1 Kanobi 12-Jun-17 5:51am View

can the image path be some url and passing the same will work ?

Abhi1 Kanobi 12-Jun-17 5:36am View

I posted that monster just for clarity sake .. :)

I have tried to implement it like this but without success .
SET @IMAGEFILE = 'test.jpg'
SET @IMAGEPATH = 'C:\Users\----\Desktop\test.jpg'

--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail

-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

DECLARE @body varchar(max) =
--'<a href=''http://newsletter.qendidate.com/?id=' + CAST(@ID As varchar(20))+'''>Please click here to fill your missing details';
--'<a href=''someurl.com/?id=1''>Please click here to fill your missing details';
'<img src="cid:'+@IMAGEFILE+'"" width="235" height="70" border="0" alt="no image"><a href=''someurl.aspx/?id=' + CAST(@ID As varchar(20))+'''>Please click here to fill your missing details';

Abhi1 Kanobi 12-Jun-17 5:31am View

I have created a separate SP for the same .

ALTER PROCEDURE [dbo].[sp_send_mail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@bodytype varchar(10),
@output_mesg varchar(10) output,
@output_desc varchar(1000) output
AS
DECLARE @imsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server Details
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
'------'

--SMTP user name
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
'----'

--Gmail Password
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
'----'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
'---'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
'465'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
"1"

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
EXEC @hr = sp_oasetproperty @imsg, 'to', @to
EXEC @hr = sp_oasetproperty @imsg, 'from', @from
EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

IF @hr <>0
SELECT @hr
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
BEGIN

set @output_desc = @description
END
ELSE
BEGIN
SET @output_desc = ' sp_oageterrorinfo failed'
END
IF not @output_desc is NULL
SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg

Abhi1 Kanobi 12-Jun-17 5:26am View

Deleted

I have created a separate SP for the same .

ALTER PROCEDURE [dbo].[sp_send_mail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@bodytype varchar(10),
@output_mesg varchar(10) output,
@output_desc varchar(1000) output
AS
DECLARE @imsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server Details
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
'------'

--SMTP user name
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
'----'

--Gmail Password
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
'----'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
'---'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
'465'

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
"1"

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
EXEC @hr = sp_oasetproperty @imsg, 'to', @to
EXEC @hr = sp_oasetproperty @imsg, 'from', @from
EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

IF @hr <>0
SELECT @hr
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
BEGIN

set @output_desc = @description
END
ELSE
BEGIN
SET @output_desc = ' sp_oageterrorinfo failed'
END
IF not @output_desc is NULL
SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg

Abhi1 Kanobi 12-Jun-17 5:18am View

Thanks but I am using a separate stored procedure , not the one given by sql server .
How can it be done in my case '?

Thanks

Abhi1 Kanobi 2-Jun-17 4:31am View

Hi Thanks for ur input ,
but alas , the link generated is still not redirecting to the page i want to .

It's getting redireted to - https://aydacfu.xyz/?affiliate_id=12&origin=newsletter.qendidate.

Abhi1 Kanobi 2-Jun-17 4:31am View

Hi Thanks for ur input ,
but alas , the link generated is still not redirecting to the page i want to .

It's getting redireted to - https://aydacfu.xyz/?affiliate_id=12&origin=newsletter.qendidate.

Abhi1 Kanobi 1-Jun-17 10:33am View

i guess already , uploaded the whole code

Abhi1 Kanobi 1-Jun-17 10:29am View

do u want me to share the complete code?.

Abhi1 Kanobi 1-Jun-17 10:25am View

http://imgur.com/6677LfG

Abhi1 Kanobi 1-Jun-17 9:37am View

That's exactly what I am doing sir , copying the code from commnt .
is there anyway i can show u what I see? , this error is driving me nuts .:)

Abhi1 Kanobi 1-Jun-17 9:28am View

hello sir,
I am using it exactly the way u have specified sir

Abhi1 Kanobi 1-Jun-17 9:18am View

the concatenation error is same , incorrect syntax near '+'

incorrect syntax near @id , expecting '(' or select ..

incorrect syntax near 20 , expecting '(' or select ..

Abhi1 Kanobi 1-Jun-17 9:12am View

the error still exists , same with the '+'and now along with @ID also

Abhi1 Kanobi 1-Jun-17 9:07am View

ok,
but I used it the way you specified . please see below , the error is still there

EXEC [dbo].[sp_send_mail]
'a@a.com',
'a@a.com',
'Submission',
'<a href=''http://someurl.com?id=' + @ID + '''>Please click here to fill your missing details ',
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print 'Email id is ' + @Email
print 'Email id is ' + @ID
End;

Abhi1 Kanobi 1-Jun-17 8:57am View

USE [orion]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spc_getemail]
As
Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name varchar(255)
DECLARE @ID int
--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail
-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

EXEC [dbo].[sp_send_mail]
'2@a.com',
'a@a.com',
'Newsletter Submission',
'<a href=''someurl.com?id='+ID'''>Please click here to fill your missing details ',
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print 'Email id is ' + @Email
print 'Email id is ' + @ID
End;
Close EmailCursor
Deallocate EmailCursor
End;

Abhi1 Kanobi 1-Jun-17 8:56am View

It's still giving error as "incorrect sysntax near +"

Abhi1 Kanobi 1-Jun-17 8:48am View

Deleted

USE [orion]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spc_getemail]
As
Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name varchar(255)
DECLARE @ID int
--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail
-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

EXEC [dbo].[sp_send_mail]
'2@a.com',
'a@a.com',
'Newsletter Submission',
'<a href=''someurl.com?id='+ID'''>Please click here to fill your missing details ',
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print 'Email id is ' + @Email
print 'Email id is ' + @ID
End;
Close EmailCursor
Deallocate EmailCursor
End;

Abhi1 Kanobi 1-Jun-17 8:44am View

Deleted

It's still giving error as "incorrect sysntax near +"

Abhi1 Kanobi 1-Jun-17 8:28am View

the declaration is below.

Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name varchar(255)
DECLARE @ID int

--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select email From demoemail

DECLARE NameCursor Cursor For
Select name from demoemail

DECLARE IDCursor Cursor For
Select ID from demoemail

-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @Email

open NameCursor
Fetch Next from NameCUrsor into @Name

open IDCursor
Fetch Next from IDCUrsor into @ID

Abhi1 Kanobi 22-May-17 8:50am View

Hello ,

Thank you for your reply .
Actually what I need to extract is the user profiles based on the the emails I have in the database.

Thanks

Abhi1 Kanobi 4-May-17 5:59am View

Thanks a lot.
I will close my question then.
Thanks a lot for ur input

Abhi1 Kanobi 4-May-17 5:12am View

Thanks for your reply.
The application is targeted towards , employee management , into millions of data.
The features in it require a lot of cross table references and relationships.

The web application which I am developing will be both into CRUD operations and data analytics.

Comments by Abhi1 Kanobi (Top 34 by date)