connecting to SSAS: unable

Showing posts with label unable. Show all posts

Friday, March 23, 2012

Problem in accessing reporting services web service, unable to set credentials I guess....

I am trying to access the Reporting Services Web Service, but i m not able to make it work. i tried with default credentials and network credentials

ReportingService

service =newReportingService();

service.Credentials =new System.Net.NetworkCredential("dbo","coreobject5","");

service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters);

This is the exception i get

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. --> The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Exception Details:System.Web.Services.Protocols.SoapException: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. --> The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Source Error:

Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")]Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) {Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] {Line 3385: Report,Line 3386: ExtensionSettings,

Tried to solve this and found that its the problem with the datasource I created for the report, tried to use 'sa' user in

data source->credentials->Use a specified user name and password, but did'nt worked.

One more thing i am able to accessGetExtensionSettings("Report Server Fileshare");API with

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

Not able to find out what users to create in Report Server database or the database i am connecting to?

Will appreciate any kind of help.

Problem in accessing reporting services web service, unable to set credentials I guess....

I am trying to access the Reporting Services Web Service, but i m not able to make it work. i tried with default credentials and network credentials

ReportingService service = new ReportingService();

service.Credentials = new System.Net.NetworkCredential("username", "password", "domain");

service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters);

This is the exception i get

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. > The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. > The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.
Source Error:

Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")]Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) {
Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] {
Line 3385: Report,
Line 3386: ExtensionSettings,

Tried to solve this and found that its the problem with the datasource I created for the report, tried to use 'sa' user in

data source->credentials->Use a specified user name and password, but did'nt worked.

Onemore thing i am able to access GetExtensionSettings("Report Server Fileshare"); API with

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

Not able to find out what users to create in Report Server database or the database i am connecting to?

Will appreciate any kind of help.

|||

I believe your problem does not lie with a user in a database; I have used RS WS to Create / Fire Subscriptions and to UpdateSnapshots. Whatever user you pass to the Web Service must have Generate Events permission under Site Settings. Give the user those permissions and I think you will find that it will resolve your problem

Please let me know

Thanks

|||Report's data source must use stored credentials.sql

Problem in accessing reporting services web service, unable to set credentials I guess....

I am trying to access the Reporting Services Web Service, but i m not able to make it work. i tried with default credentials and network credentials

ReportingService service = new ReportingService();

service.Credentials = new System.Net.NetworkCredential("username", "password", "domain");

service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters);

This is the exception i get

Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")]Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) {
Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] {
Line 3385: Report,
Line 3386: ExtensionSettings,

Tried to solve this and found that its the problem with the datasource I created for the report, tried to use 'sa' user in

data source->credentials->Use a specified user name and password, but did'nt worked.

Onemore thing i am able to access GetExtensionSettings("Report Server Fileshare"); API with

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

Not able to find out what users to create in Report Server database or the database i am connecting to?

Will appreciate any kind of help.

|||

Please let me know

Thanks

|||Report's data source must use stored credentials.

Tuesday, March 20, 2012

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comIf the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you cant do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
Message posted via http://www.droptable.com|||This link describes using parameterized queries.
1191519.html" target="_blank">http://www.experts-exchange.com/Pro...>
1191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.SQ
droptable.com...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.droptable.com

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.com
If the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you cant do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.droptable.co m...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.droptable.co m...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
Message posted via http://www.droptable.com
|||This link describes using parameterized queries.
http://www.experts-exchange.com/Prog..._21191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.droptable.co m...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.droptable.com

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comIf the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you can´t do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.SQLMonster.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.SQLMonster.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
--
Message posted via http://www.sqlmonster.com|||This link describes using parameterized queries.
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.SQLMonster.com...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.sqlmonster.com

Friday, March 9, 2012

problem creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

Wednesday, March 7, 2012

problem creating varbinary(max) data type

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))
The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

Monday, February 20, 2012

problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
markIt definitely seems like you have invalid data in there. How does the strings look like? What format? Also,
you might want to check out: http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column>) = 0
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column>) = 0
i tried that and it returned 0 results
mark|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!|||How many rows do you have?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> > How far in do you get before this error is thrown? Have you check ALL
the
> > rows for the data type. If one row is wrong then the whole process is
> rolled
> > back. What are you using to do the conversion?
> >
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total|||Can you post a zipped CSV of the data in the column?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> > How many rows do you have?
> >
> theres 1144 rows in total
>|||select rowid, convert(datetime, column1) from table1 order by rowid
something similar to that should get you the row or rows that have bad data.
mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine|||mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine
select rowid, convert(datetime, column1) from table1 order by rowid
if you have bad data you'll get an error with that select and then you can
easily track down the row with the bad data. "basically dates" is not good
enough for a data type conversion. sorry if i double posted this.|||This only works if you have a column called "ROWID" I do not believe SQL
server has a rowid column inherent to a table there is a ROWGUIDCOL type but
only if you build your table with it originally.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"ch" <ch@.dontemailme.com> wrote in message
news:40B251FC.A1AC90BF@.dontemailme.com...
> mark wrote:
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > It definitely seems like you have invalid data in there. How does the
> > strings look like? What format? Also,
> > > you might want to check out:
> > http://www.karaszi.com/sqlserver/info_datetime.asp
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> >
> > the strings look like
> > 01/01/1900
> > 01/17/2003
> > etc (ie they are basically dates) - ive checked the data and its fine
> select rowid, convert(datetime, column1) from table1 order by rowid
> if you have bad data you'll get an error with that select and then you can
> easily track down the row with the bad data. "basically dates" is not
good
> enough for a data type conversion. sorry if i double posted this.
>
>|||If Query Analyzer says your data is good, but you're having problems in
Enterprise Manager, try using Query Analyzer to change the datatype using
ALTER TABLE. EM might be doing something else behind the scene.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:BXosc.56$Vp5.20@.newsfe2-win...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> > Hi Mark
> >
> > The data may seem fine when you look at it, but SQL Server might
disagree.
> > You can run have SQL Server inspect the data with the ISDATE( ) function
> to
> > see what values it is not happy with.
> >
> > SELECT <nvarchar column>
> > FROM <mytable>
> > WHERE ISDATE(<nvarchar column>) = 0
> i tried that and it returned 0 results
> mark
>
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:O$5HGtaQEHA.2100@.TK2MSFTNGP11.phx.gbl...
> Can you post a zipped CSV of the data in the column?
>
hi thanks for the help, in the end i exported the data to access (no
problems) converted the field types in access
dropped the data from the sql table - and changed the field types and
re-imported back in - without any hangups
or errors - odd but still its done now!
mark|||select rowid, convert(datetime, column1) from table1 order by rowid
it also only works if you have a table named table1 and that table contains at
least two columns, one column named rowid and another column named column1.
Andrew Madsen wrote:
> This only works if you have a column called "ROWID" I do not believe SQL
> server has a rowid column inherent to a table there is a ROWGUIDCOL type but
> only if you build your table with it originally.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B251FC.A1AC90BF@.dontemailme.com...
> > mark wrote:
> >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > > It definitely seems like you have invalid data in there. How does the
> > > strings look like? What format? Also,
> > > > you might want to check out:
> > > http://www.karaszi.com/sqlserver/info_datetime.asp
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > >
> > > the strings look like
> > > 01/01/1900
> > > 01/17/2003
> > > etc (ie they are basically dates) - ive checked the data and its fine
> >
> > select rowid, convert(datetime, column1) from table1 order by rowid
> > if you have bad data you'll get an error with that select and then you can
> > easily track down the row with the bad data. "basically dates" is not
> good
> > enough for a data type conversion. sorry if i double posted this.
> >
> >
> >|||h
I been in same position once, my date format in string was dd/MM/yyyy
to change the date format you must use convert(datetime,'mm/dd/yyyy') if you change the column format from nvarchar to datetime it will not work. what you should to is create another column with datetime format while your current column is in use. then run a procedure to pick date from the nvarchar date column, use convert and save it into column with datetime format.
Test your data
If everything is fine, backup your table and delete column with nvarchar format
Test your application or modules using that table

Friday, March 23, 2012

Problem in accessing reporting services web service, unable to set credentials I guess....

Problem in accessing reporting services web service, unable to set credentials I guess....

Problem in accessing reporting services web service, unable to set credentials I guess....

Tuesday, March 20, 2012

Problem executing Stored Procedure from ASP

Problem executing Stored Procedure from ASP

Problem executing Stored Procedure from ASP

Friday, March 9, 2012

problem creating varbinary(max) data type

Wednesday, March 7, 2012

problem creating varbinary(max) data type

Monday, February 20, 2012

problem converting nvarchar column to datetime

problem converting nvarchar column to datetime

problem converting nvarchar column to datetime

connecting to SSAS

Blog Archive

About Me