Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.|||You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.|||You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.
|||You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error while trying to get predictions from relational DB

I am trying to get predictions and insert them into a DB table.

Following is the code I am using , but I am getting an error saying

'

An error occurred while preparing the query

,

Pl. help.

begin

declare @.v_query varchar(5000);
declare @.full_query varchar(5000);
declare @.v_dbquery varchar (200);

set @.v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);

set @.v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@.v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Are you able to execute other DMX/MDX queries against the linked server to AS?

If not, you may need to make sure the MSOLAP provider is allowed in-proc on your SQL Server 2005 database engine instance. You can do this via SQL Server Management Studio - connect to the database engine, navigate to Server Objects -> Linked Servers -> Providers in the Object Explorer, right-click on MSOLAP to select Properties and check the "Allow inprocess" provider option.

|||

Yes, I was able to run DMX queries against the linked server.

For that purpose I had to check 'Allow Inprocess' as well as I had to go to linked server properties and in server option I had to set Rpc = True.

|||

You had some syntax errors that cause the query to fail (first in T-SQL and then in DMX) - see my comments marked "Raman:" in the corrected query below ( this one ran successfully on my machine):

begin

declare @.v_query varchar(5000);

declare @.full_query varchar(5000);

declare @.v_dbquery varchar (500); -- Raman: you had varchar(200) which was too small for the query

set @.v_dbquery = char(39) + -- Raman: need to escape the enclosing quotes when sending to AS as well

char(39)+'SELECT

[ProspectAlternateKey],

[FirstName],

[LastName],

[MaritalStatus],

[Gender],

[YearlyIncome],

[TotalChildren],

[NumberChildrenAtHome],

[HouseOwnerFlag],

[NumberCarsOwned]

FROM

[dbo].[ProspectiveBuyer]'+char(39)+char(39); -- Raman: need to escape the enclosing quotes when sending to AS as well

set @.v_query = 'SELECT

[TM Clustering].[Bike Buyer],

t.[ProspectAlternateKey],

PredictProbability([TM Clustering].[Bike Buyer])

From

[TM Clustering]

PREDICTION JOIN

OPENQUERY([Adventure Works DW],'

+ @.v_dbquery + -- Raman: @.v_dbquery needs to appended - you had it in the query string where it will not get substituted

')AS t

ON

[TM Clustering].[Marital Status] = t.[MaritalStatus] AND

[TM Clustering].[Gender] = t.[Gender] AND

[TM Clustering].[Yearly Income] = t.[YearlyIncome] AND

[TM Clustering].[Total Children] = t.[TotalChildren] AND

[TM Clustering].[Number Children At Home] = t.[NumberChildrenAtHome] AND

[TM Clustering].[House Owner Flag] = t.[HouseOwnerFlag] AND

[TM Clustering].[Number Cars Owned] = t.[NumberCarsOwned]'

--print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Error while trying to get predictions from relational DB

I am trying to get predictions and insert them into a DB table.

Following is the code I am using , but I am getting an error saying

'

An error occurred while preparing the query

,

Pl. help.

begin

declare @.v_query varchar(5000);
declare @.full_query varchar(5000);
declare @.v_dbquery varchar (200);

set @.v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);

set @.v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@.v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Are you able to execute other DMX/MDX queries against the linked server to AS?

If not, you may need to make sure the MSOLAP provider is allowed in-proc on your SQL Server 2005 database engine instance. You can do this via SQL Server Management Studio - connect to the database engine, navigate to Server Objects -> Linked Servers -> Providers in the Object Explorer, right-click on MSOLAP to select Properties and check the "Allow inprocess" provider option.

|||

Yes, I was able to run DMX queries against the linked server.

For that purpose I had to check 'Allow Inprocess' as well as I had to go to linked server properties and in server option I had to set Rpc = True.

|||

You had some syntax errors that cause the query to fail (first in T-SQL and then in DMX) - see my comments marked "Raman:" in the corrected query below ( this one ran successfully on my machine):

begin

declare @.v_query varchar(5000);

declare @.full_query varchar(5000);

declare @.v_dbquery varchar (500); -- Raman: you had varchar(200) which was too small for the query

set @.v_dbquery = char(39) + -- Raman: need to escape the enclosing quotes when sending to AS as well

char(39)+'SELECT

[ProspectAlternateKey],

[FirstName],

[LastName],

[MaritalStatus],

[Gender],

[YearlyIncome],

[TotalChildren],

[NumberChildrenAtHome],

[HouseOwnerFlag],

[NumberCarsOwned]

FROM

[dbo].[ProspectiveBuyer]'+char(39)+char(39); -- Raman: need to escape the enclosing quotes when sending to AS as well

set @.v_query = 'SELECT

[TM Clustering].[Bike Buyer],

t.[ProspectAlternateKey],

PredictProbability([TM Clustering].[Bike Buyer])

From

[TM Clustering]

PREDICTION JOIN

OPENQUERY([Adventure Works DW],'

+ @.v_dbquery + -- Raman: @.v_dbquery needs to appended - you had it in the query string where it will not get substituted

')AS t

ON

[TM Clustering].[Marital Status] = t.[MaritalStatus] AND

[TM Clustering].[Gender] = t.[Gender] AND

[TM Clustering].[Yearly Income] = t.[YearlyIncome] AND

[TM Clustering].[Total Children] = t.[TotalChildren] AND

[TM Clustering].[Number Children At Home] = t.[NumberChildrenAtHome] AND

[TM Clustering].[House Owner Flag] = t.[HouseOwnerFlag] AND

[TM Clustering].[Number Cars Owned] = t.[NumberCarsOwned]'

--print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end