Error accessing report: created

Showing posts with label created. Show all posts

Thursday, March 29, 2012

Error executing xp_cmdshell from within a SQL task in a DTS package...

Hello

I am trying to execute xp_cmdshell' from within a DTS package that
was created by another person. When I try to execute that SQL Task'
selectively from within the package, I get the following error
message:

Error Title: Package Error
Error Details:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: xpsql.cpp: Error 87 from GetProxyAccount on line
604

Why is this error message popping up? When I create a new package
(myself) and create an exact same SQL task as above and run it, the
SQL task runs fine.

Appreciate any help / feedback.

Thanks in Advance
Jagannathan SanthanamWhen a sysadmin role member executes xp_cmdshell, it runs under the
security context of the SQL Server service account. However, when a
non-sysadmin user executes the proc, it runs under the SQL Agent proxy
account.

It looks like there is an issue with your configuration of the proxy
account and/or SQL Server service account security. Enterprise Manager
automatically assigns the necessary rights to these accounts when these
are specified or changed via the GUI. Alternatively, you can manually
assign the following Windows rights to the accounts.

<Excerpt
href="http://support.microsoft.com/default.aspx?scid=kb;en-us;264155"
MSSQLServer and SQLServerAgent Services
- Act as part of the Operating System.
- Increase Quotas.
- Replace a process level token.
- Log on as a batch job.
SQLAgentCmdExec Account
- Log on as a batch job.

NOTE: You must restart the entire server, not just the SQL Services, in
order for any changes made to user rights permissions to take effect.

</Excerpt
--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jagannathan Santhanam" <jags_32@.yahoo.com> wrote in message
news:605df08e.0311170307.39d9b1f1@.posting.google.c om...
> Hello
> I am trying to execute 'xp_cmdshell' from within a DTS package that
> was created by another person. When I try to execute that 'SQL Task'
> selectively from within the package, I get the following error
> message:
> Error Title: Package Error
> Error Details:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: xpsql.cpp: Error 87 from GetProxyAccount on line
> 604
>
> Why is this error message popping up? When I create a new package
> (myself) and create an exact same SQL task as above and run it, the
> SQL task runs fine.
> Appreciate any help / feedback.
> Thanks in Advance
> Jagannathan Santhanam

Error executing SSIS package

Hello,
I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
created several SSIS packages and run them under proxy account. At first
they worked just fine, but then all of them started to fail with the error
messages like this one:
Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
NonTransactableSql Execute SQL Task Description: An error occurred with
the following error message: "Could not find file 'I:\Users\Peter
Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
step failed.
If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
deleting my tmp files periodically, so I don't have these files. It's hard
to believe that SQL Server relies on tmp files that are often deleted. Plus,
I was running the same packages on Windows XP for over a year and never had
problems like this.
What else could it be and how to fix it? Is it a security issue?
I would appreciate your suggestions very much.
Thank you,
--
Peter AfoninHi Peter
"Peter Afonin" wrote:
> Hello,
> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
> created several SSIS packages and run them under proxy account. At first
> they worked just fine, but then all of them started to fail with the error
> messages like this one:
> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
> NonTransactableSql Execute SQL Task Description: An error occurred with
> the following error message: "Could not find file 'I:\Users\Peter
> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
> step failed.
> If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
> deleting my tmp files periodically, so I don't have these files. It's hard
> to believe that SQL Server relies on tmp files that are often deleted. Plus,
> I was running the same packages on Windows XP for over a year and never had
> problems like this.
> What else could it be and how to fix it? Is it a security issue?
> I would appreciate your suggestions very much.
> Thank you,
> --
> Peter Afonin
>
I would suspect that the issue is probably with 'I:\Users\Peter
Afonin\AppData\Local\Temp' not existing where is your temp directory set to?
John|||Hi John,
No, this directory is real. This is a default directory in Vista (I have it
on partition I).
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>> Hello,
>> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
>> created several SSIS packages and run them under proxy account. At first
>> they worked just fine, but then all of them started to fail with the
>> error
>> messages like this one:
>> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
>> NonTransactableSql Execute SQL Task Description: An error occurred
>> with
>> the following error message: "Could not find file 'I:\Users\Peter
>> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
>> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
>> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
>> step failed.
>> If the cause is really in this tmp file, it doesn't make any sense. Yes,
>> I'm
>> deleting my tmp files periodically, so I don't have these files. It's
>> hard
>> to believe that SQL Server relies on tmp files that are often deleted.
>> Plus,
>> I was running the same packages on Windows XP for over a year and never
>> had
>> problems like this.
>> What else could it be and how to fix it? Is it a security issue?
>> I would appreciate your suggestions very much.
>> Thank you,
>> --
>> Peter Afonin
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||John, I can confirm that SQL Server is indeed looking for these tmp files.
1. I've recreated my packages, they worked fine.
2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
The packages stopped working, giving me the error I mentioned before.
3. I put these tmp files back to this folder, the packages work fine again.
This is something that I've never heard before and that doesn't make any
sense. Why this never happened on Windows XP, only Vista? What should I do -
it looks like I cannot cleanup my computer anymore?
As a last resort - can I change the folder for tmp files that are needed for
SSIS packages execution, so I could exclude it somehow from the disk
cleanup?
Any advice would be greatly appreciated.
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>> Hello,
>> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
>> created several SSIS packages and run them under proxy account. At first
>> they worked just fine, but then all of them started to fail with the
>> error
>> messages like this one:
>> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
>> NonTransactableSql Execute SQL Task Description: An error occurred
>> with
>> the following error message: "Could not find file 'I:\Users\Peter
>> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
>> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
>> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
>> step failed.
>> If the cause is really in this tmp file, it doesn't make any sense. Yes,
>> I'm
>> deleting my tmp files periodically, so I don't have these files. It's
>> hard
>> to believe that SQL Server relies on tmp files that are often deleted.
>> Plus,
>> I was running the same packages on Windows XP for over a year and never
>> had
>> problems like this.
>> What else could it be and how to fix it? Is it a security issue?
>> I would appreciate your suggestions very much.
>> Thank you,
>> --
>> Peter Afonin
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||Hi Peter
"Peter Afonin" wrote:
> John, I can confirm that SQL Server is indeed looking for these tmp files.
> 1. I've recreated my packages, they worked fine.
> 2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
> The packages stopped working, giving me the error I mentioned before.
> 3. I put these tmp files back to this folder, the packages work fine again.
> This is something that I've never heard before and that doesn't make any
> sense. Why this never happened on Windows XP, only Vista? What should I do -
> it looks like I cannot cleanup my computer anymore?
> As a last resort - can I change the folder for tmp files that are needed for
> SSIS packages execution, so I could exclude it somehow from the disk
> cleanup?
> Any advice would be greatly appreciated.
> Thank you,
> Peter
>
SSIS may use files in a users temporary directory, if they have been
automatically generated or converted. You will need to check the datasources
and the package steps to see what they are. If necessary they can be moved to
a "common" directory that has the correct permissions for all users that will
run the package.
You may have found that removing the equivalent files in windows XP would
have had a similar result.
John|||Thank you, John, I'll edit my packages and see what's going on.
Peter
On Mar 22, 1:44 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> HiPeter
>
>
> "PeterAfonin" wrote:
> > John, I can confirm that SQL Server is indeed looking for these tmp files.
> > 1. I've recreated my packages, they worked fine.
> > 2. I removed all files from I:\Users\PeterAfonin\AppData\Local\Temp folder.
> > The packages stopped working, giving me the error I mentioned before.
> > 3. I put these tmp files back to this folder, the packages work fine again.
> > This is something that I've never heard before and that doesn't make any
> > sense. Why this never happened on Windows XP, only Vista? What should I do -
> > it looks like I cannot cleanup my computer anymore?
> > As a last resort - can I change the folder for tmp files that are needed for
> > SSIS packages execution, so I could exclude it somehow from the disk
> > cleanup?
> > Any advice would be greatly appreciated.
> > Thank you,
> >Peter
> SSIS may use files in a users temporary directory, if they have been
> automatically generated or converted. You will need to check the datasources
> and the package steps to see what they are. If necessary they can be moved to
> a "common" directory that has the correct permissions for all users that will
> run the package.
> You may have found that removing the equivalent files in windows XP would
> have had a similar result.
> John- Hide quoted text -
> - Show quoted text -

error executing sprocedure

Hello,
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regards
You can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
....
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>
|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards

error executing sprocedure

Hello,
I've created one stored procedure that has one input parameter(number of days), if i don=B4t pass any value when executing this procedure sql reports one error. I would like to execute this procedure and don't pass the input paramenter to the stored procedure but if the number of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
--
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in which case you don't >have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message >news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards >
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regardssql

error executing sprocedure

Hello,
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards

Tuesday, March 27, 2012

Error executing Packaga from job

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

How can I change it?

please delete this post. sorry for repeating|||I can't, but can someone merge some of Luis' threads that all revolve around the same issue?

Thanks,
Phil|||

this article describe solution to most comon issues when executing the package:

http://support.microsoft.com/kb/918760

|||

Hello, when I try to change the package protection level to server storage I got this

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.

|||Are you storing the packages as XML files or inside the DB? if you are using xmls files you can use DonSaveSensitive and the use package configurations to set the conection strings at run time as described in method 4 in that article.

Error during sqlDataSource.Update() instruction

Hi, I have such a problem:

I try to update (or insert) a row in my table and i fail althought i,ve read many posts here. I.ve created a button and "on_click" event to this button and want that event to update or insert a record in my table. I wrote:

protected void selectButton_Click(object sender, EventArgs e)
{
String taskID = projectsGridView.SelectedRow.Cells[0].Text;
usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@.task where [UserID]=1";
usersSqlDataSource.UpdateParameters.Add("task", taskID);
usersSqlDataSource.Update();
}

The application creates error in the last line of code (usersSqlDataSource.Update();) and i receive such an error:

You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty.

Pass in a valid dictionary for update or change your mode to OverwriteChanges.

For me it looks like there is a problem while setting parameters. Shall i change some properties of the sqlDataSource or GridView? Please help..

Not seeing the code for your SqlDataSource, it's hard to tell. However, I suspect that you need to change the SqlDataSource.ConflictDetection property to OverwriteChanges instead of CompareAllValues.

|||

Yes!! It works!!!

The beginnings are difficult, thank you very much ;)

Monday, March 26, 2012

Error during FormsAuthentication.RedirectFromLoginPage

Greetings,

I created a custom security extenstion that worked great - except every call to

FormsAuthentication.RedirectFromLoginPage would result in a

"The return URL specified for request redirection is invalid." error type message.

I reverted back to windows security with no problem. Then used the Microsoft Forms Security example thinking maybee I had improperly implemented the interface.

When the forms security example provided by microsoft was configured I still get

"The return URL specified for request redirection is invalid." error type message.

Here is the actual query string when loging into the reportserver using the custom security extension:

http://<MACHINE_NAME>/ReportServer/logon.aspx?ReturnUrl=%2fReportServer%2fPages%2fReportViewer.aspx%3f%252fMathReports%252fClass%2bProgress%2bChart%26ClassID%3d1514%26rs%3aCommand%3dRender&%2fMathReports%2fClass+Progress+Chart&ClassID=1514&rs:Command=Render

I have replaced the ip with <machine_name>. I have been working on this problem for 2 days with no solution. Help would be nice.

Thanks,

Ross B.

I noticed if I manually changed the ReturnURL in the address bar from

http://<MACHINE_NAME>/ReportServer/logon.aspx?ReturnUrl=%2fReportServer%2fPages%2fReportViewer.aspx%3f%2fMathReports%2fClass+Progress+Chart&ClassID=1514&rs:Command=Render

It almost looks like the redirect is duplicated in the query string - anyone know why this is?

|||

If I change functionality from

FormsAuthentication.RedirectFromLoginPage(TxtUser.Text, false);

To:

aUrl=Request.QueryString["ReturnUrl"];

FormsAuthentication.SetAuthCookie(TxtUser.Text,false);

Response.Redirect(aURL);

This works...however I am still puzzled as to what is causing the recommended way to fail in my setup.

Ross B.

|||

Hi,

I am facing a similar problem. However, I tried your solution but it did not work.

In my case the http://<Site Name>/_layouts/login.aspx?ReturnUrl=%2flists%2ffeedback%2fnewform.aspx%3fsource%3d%2fpages%2fconfirmation.aspx%3fsource%3dhttp%3a%2f%2f<Site Name&source=/pages/confirmation.aspx?source=http://<Site Name>

When I replace the return url from realtive to absolute link it starts working. Can you please suggest me where should i change to get this working?

Thanks in advance,

sql

Error during FormsAuthentication.RedirectFromLoginPage

Greetings,

I created a custom security extenstion that worked great - except every call to

FormsAuthentication.RedirectFromLoginPage would result in a

"The return URL specified for request redirection is invalid." error type message.

I reverted back to windows security with no problem. Then used the Microsoft Forms Security example thinking maybee I had improperly implemented the interface.

When the forms security example provided by microsoft was configured I still get

"The return URL specified for request redirection is invalid." error type message.

Here is the actual query string when loging into the reportserver using the custom security extension:

I have replaced the ip with <machine_name>. I have been working on this problem for 2 days with no solution. Help would be nice.

Thanks,

Ross B.

I noticed if I manually changed the ReturnURL in the address bar from

http://<MACHINE_NAME>/ReportServer/logon.aspx?ReturnUrl=%2fReportServer%2fPages%2fReportViewer.aspx%3f%2fMathReports%2fClass+Progress+Chart&ClassID=1514&rs:Command=Render

It almost looks like the redirect is duplicated in the query string - anyone know why this is?

|||

If I change functionality from

FormsAuthentication.RedirectFromLoginPage(TxtUser.Text, false);

To:

aUrl=Request.QueryString["ReturnUrl"];

FormsAuthentication.SetAuthCookie(TxtUser.Text,false);

Response.Redirect(aURL);

This works...however I am still puzzled as to what is causing the recommended way to fail in my setup.

Ross B.

|||

Hi,

I am facing a similar problem. However, I tried your solution but it did not work.

When I replace the return url from realtive to absolute link it starts working. Can you please suggest me where should i change to get this working?

Thanks in advance,

Error during Export To Excel - Matrix Report

Hi All,

I have created a Report using Sql Server 2005 Reporting Services. It's a matrix report. When I try to download the report to excel, it gives me the message "

Data Regions within table/matrix cells are ignored.

" in the top left hand side header.

Any pointers on how to resolve it.

Thanks,

S Suresh

This is a limitation of the Excel Renderer. It's not so much an error message as just information about why your data is missing. The Renderer cannot render data regions (list, table, matrix, subreport) nested inside other data regions (subreports inside lists are an exception to this rule). Instead of just displaying a blank cell, it puts the message you have seen in.

You will need to redesign your report if Excel is your desired output format for this report.

|||

Geoff,

In your last statement in this thread,

"The Renderer cannot render data regions (list, table, matrix, subreport) nested inside other data regions (subreports inside lists are an exception to this rule)".

I dont understang this statement,currently i am facing with this type of issue only. I have a matrix ,in the data cell of the matrix ,i placed a list and placed a subreport inside it(SubReport has only one small table which acceopts a parameter from value in the column header).

Will it work for me as you said some thing "Execption" or Do i need to search for another alternative?

your suggestions are most valuable

Thank you,

Raj Deep.A

Error during Export To Excel - Matrix Report

Hi All,

I have created a Report using Sql Server 2005 Reporting Services. It's a matrix report. When I try to download the report to excel, it gives me the message "

Data Regions within table/matrix cells are ignored.

" in the top left hand side header.

Any pointers on how to resolve it.

Thanks,

S Suresh

You will need to redesign your report if Excel is your desired output format for this report.

|||

Geoff,

In your last statement in this thread,

"The Renderer cannot render data regions (list, table, matrix, subreport) nested inside other data regions (subreports inside lists are an exception to this rule)".

Will it work for me as you said some thing "Execption" or Do i need to search for another alternative?

your suggestions are most valuable

Thank you,

Raj Deep.A

|||i have a matrix report that has 3 column groupings. Daily Date range across the top(Ex. 11/24,11/25, etc). The column groupings are numbers for WeekToDate, MonthToDate,ProjectToDate. So dates go across the top, then subtotals are grouped based up on the above 3 groups. Problem is when it switches from Nov to Dec 12/1 is missing and looks like it is moved to the end of the report right before the ProjectToDate subtotal. This 'problem' happens when exporting from Report Manager. Export from VS is fine.

Error during Execute sp with IMAGE parameter

Hello.
It is the first time that Im using IMAGE field type..

I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@.OutImg IMAGE declared as an output parameter of the procedure.)
(Select @.OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.

Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:ofrom 2005 BOL:

text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure.

So you'll have to return this in a record set, not in an OUT param.

Thursday, March 22, 2012

Error Dumping Data in SQl Server

Im dumping data from a PostgreSql DB into SQL Server. After cleaning up the generated SQL I created my table as such:

<code>

CREATE TABLE certifications (
cert_id int identity(1,1),
seminar_date datetime NOT NULL,
last_name varchar NOT NULL,
first_name varchar NOT NULL,
housing_agency varchar NOT NULL,
seminar varchar NOT NULL,
UNIQUE (cert_id)
);

</code>

When I try to insert like so:

<code>

Set identity_insert certifications on
Go
INSERT INTO certifications (cert_id, seminar_date, last_name, first_name, housing_agency, seminar) VALUES(580, '2001-03-23', 'Dean', 'James', 'Some Finance Agency', Some Certification');

INSERT INTO certifications (cert_id, seminar_date, last_name, first_name, housing_agency, seminar) VALUES(580, '2001-03-23', 'Dean2', 'James2', 'Some Finance Agency2', Some Certification2');

Set identity_insert certifications off
Go

</code>

(There are a few thousand records)
I get this error:

<error>

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

</error>

Any help would be greatly appreciated

TIACREATE TABLE certifications (
cert_id int identity(1,1),
seminar_date datetime NOT NULL,
last_name varchar NOT NULL,
first_name varchar NOT NULL,
housing_agency varchar NOT NULL,
seminar varchar NOT NULL,
UNIQUE (cert_id)
);

You will need to specify the width of varchar colums .. i believe that is the source of your problem|||You were correct. Thanks a bunch! -Esql

Error deploying report with SQL Server (Visual Studio) 2005

Hi All,
I'm trying to deploy a report that have just been created in Visual
Studio 2005 (for Microsoft CRM) I'm getting an error message
******************************
The project cannot be deployed because no target server is specified.
Provide a value for the TargetServerURL property in the property page
of this project.
******************************
I'm sorry I'm new with this Visual Studio 2005 ... I'm not finding the
place to put the TargetServerURL value in that property page ... The
worst thing is that it is probably right under my nose ... ;-)
Anybody can help a dummy ? ... ThanksProject -> your Project Name Properties (Last Menu Item) on the pop up
window you'll find what you're looking for.
Manny
Frank wrote:
>Hi All,
>I'm trying to deploy a report that have just been created in Visual
>Studio 2005 (for Microsoft CRM) I'm getting an error message
>******************************
>The project cannot be deployed because no target server is specified.
>Provide a value for the TargetServerURL property in the property page
>of this project.
>******************************
>I'm sorry I'm new with this Visual Studio 2005 ... I'm not finding the
>place to put the TargetServerURL value in that property page ... The
>worst thing is that it is probably right under my nose ... ;-)
>Anybody can help a dummy ? ... Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200701/1

Error Deploying Cube

I have just installed Evaluation version of SQL Server 2005. Created a new database with two tables. Created new project with data source pointing to localhost and build a data source view containning tables from database created earlier. After creating the cube, I get following error while deploying the Cube:

OLE DB error: OLE DB or ODBC error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Any help!

Regards.

Are you deploying locally or remotely?

Is the user you are trying to deploy as set up in the SQLSERVER2005MSOLAPUser group on the AS server?

|||

It is local and user is member of group you mentioned.

My problem was due to the wrong impersonation information in data source. I have corrected it and now everything is fine.

Thnks.

Wednesday, March 21, 2012

Error creating new push subscription after deleting the existing o

I created a transactional replication publication and pushed a subscriber,
and that works fine. I deleted existing push subscription in the publisher
with enterprise manager, and also deleted automatically generated replication
jobs related to that push subscription. I noticed that the push subscription
was not automatically deleted in the subscriber as it should. Moreover, when
I created a new push subscription with the same article, I encountered an
error. The error message is as following.
Subscriptions were created successfully at the following subscribers.
…
SQL Server Enterprise manager could not start the snapshot agent.
Error 14262:the specified @.job_id … does not exist.
Please help.
The error indicates that a replication agent job was deleted accidentally.
When this issue occurs, the most efficient way would be rebuilding the
replication from scratch.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.

Error creating new database diagrams in upgraded database

(I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
I restored a database originally created in sql 2000 to a sql 2005 server. I
also set the compatibility level to sql 2005 (90). The owner is sa. When I
try to create a new database diagram, I first get the message "This database
does not have one or more of the support objects required to use database
diagramming. Do you wish to create them?" I click Yes to that. Then I get
the message "Violation of UNIQUE KEY constraint 'UK_principal_name'. Cannot
insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
terminated. (Microsoft SQL Server, Error: 2627)".
I can create diagrams for AdventureWorks. Is there a way to fix this other
than recreating the database from scratch as sql 2005?
Thanks.
WWW.ROVA.COMM
Thankfully, the diagramming objects are benign and you can remove them with
little ill effect. I'm not sure if some of these will fail if they didn't
come over from 2000, but the whole script should run and then you should be
able to say yes to that prompt sufccessfully.
DROP PROCEDURE sp_upgraddiagrams;
GO
DROP PROCEDURE sp_helpdiagrams;
GO
DROP PROCEDURE sp_helpdiagramdefinition;
GO
DROP PROCEDURE sp_creatediagram;
GO
DROP PROCEDURE sp_renamediagram;
GO
DROP PROCEDURE sp_alterdiagram;
GO
DROP PROCEDURE sp_dropdiagram;
GO
DROP FUNCTION fn_diagramobjects;
GO
DROP TABLE sysdiagrams;
GO
I asked for a button to facilitate this a long time ago, but not too many
people seem to think it is a good idea.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125014
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
> (I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
> I restored a database originally created in sql 2000 to a sql 2005 server.
> I
> also set the compatibility level to sql 2005 (90). The owner is sa. When I
> try to create a new database diagram, I first get the message "This
> database
> does not have one or more of the support objects required to use database
> diagramming. Do you wish to create them?" I click Yes to that. Then I get
> the message "Violation of UNIQUE KEY constraint 'UK_principal_name'.
> Cannot
> insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
> terminated. (Microsoft SQL Server, Error: 2627)".
> I can create diagrams for AdventureWorks. Is there a way to fix this other
> than recreating the database from scratch as sql 2005?
> Thanks.
> --
> WWW.ROVA.COMM
|||I was unable to reproduce this issue, btw. I created a database in 2000,
created a diagram, backed it up, then restored on 2005, set a valid owner,
changed cmptlevel to 90, expanded database diagrams node in object explorer,
said yes to the prompt, and I was able to modify the existing diagram and
create a new one. How many diagrams had you created in 2000 before you
backed up the database and restored it in 2005?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
> (I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
> I restored a database originally created in sql 2000 to a sql 2005 server.
> I
> also set the compatibility level to sql 2005 (90). The owner is sa. When I
> try to create a new database diagram, I first get the message "This
> database
> does not have one or more of the support objects required to use database
> diagramming. Do you wish to create them?" I click Yes to that. Then I get
> the message "Violation of UNIQUE KEY constraint 'UK_principal_name'.
> Cannot
> insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
> terminated. (Microsoft SQL Server, Error: 2627)".
> I can create diagrams for AdventureWorks. Is there a way to fix this other
> than recreating the database from scratch as sql 2005?
> Thanks.
> --
> WWW.ROVA.COMM
|||I tried that script, running it against my database (and against master (!)
just in case). None of those procedures existed, I think. I got this for each
one:
Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist or
you do not have permission. (I'm running as 'SA' by the way.)
I tried creating diagrams again but got the same error.
I only had 4 or 5 diagrams, which I don't really care about. I just wanna
make some new ones.
Thanks for the quick response.
WWW.ROVA.COMM
"Aaron Bertrand [SQL Server MVP]" wrote:

> Thankfully, the diagramming objects are benign and you can remove them with
> little ill effect. I'm not sure if some of these will fail if they didn't
> come over from 2000, but the whole script should run and then you should be
> able to say yes to that prompt sufccessfully.
> DROP PROCEDURE sp_upgraddiagrams;
> GO
> DROP PROCEDURE sp_helpdiagrams;
> GO
> DROP PROCEDURE sp_helpdiagramdefinition;
> GO
> DROP PROCEDURE sp_creatediagram;
> GO
> DROP PROCEDURE sp_renamediagram;
> GO
> DROP PROCEDURE sp_alterdiagram;
> GO
> DROP PROCEDURE sp_dropdiagram;
> GO
> DROP FUNCTION fn_diagramobjects;
> GO
> DROP TABLE sysdiagrams;
> GO
> I asked for a button to facilitate this a long time ago, but not too many
> people seem to think it is a good idea.
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125014
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "MCG" <MCG@.discussions.microsoft.com> wrote in message
> news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
>
>
|||Sorry, it should have been
DROP <object> SYS.<object_name>, e.g. (most importantly, I think):
DROP TABLE sys.sysdiagrams;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...[vbcol=seagreen]
>I tried that script, running it against my database (and against master (!)
> just in case). None of those procedures existed, I think. I got this for
> each
> one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or
> you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
> Thanks for the quick response.
> --
> WWW.ROVA.COMM
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||> Sorry, it should have been
> DROP <object> SYS.<object_name>, e.g. (most importantly, I think):
> DROP TABLE sys.sysdiagrams;
Ignore that, I need more sleep.
|||Oh, and what happens when you run:
SELECT * FROM dbo.sysdiagrams
?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...[vbcol=seagreen]
>I tried that script, running it against my database (and against master (!)
> just in case). None of those procedures existed, I think. I got this for
> each
> one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or
> you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
> Thanks for the quick response.
> --
> WWW.ROVA.COMM
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||When I run SELECT * FROM dbo.sysdiagrams on my app's database I get this:
Invalid object name 'dbo.sysdiagrams'. There's only one sys table in my
database: dbo.dtproperties.
Thanks again for your help.
WWW.ROVA.COMM
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, and what happens when you run:
> SELECT * FROM dbo.sysdiagrams
> ?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "MCG" <MCG@.discussions.microsoft.com> wrote in message
> news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...
>
>
|||MCG (MCG@.discussions.microsoft.com) writes:
> I tried that script, running it against my database (and against master
> (!) just in case). None of those procedures existed, I think. I got this
> for each one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
Sounds like there is some funny data in dtproperties, which causes the
index violation. While it could be interesting to file a bug for it,
the quickest way to get it working is probably to simply drop
dtproperties:
DROP TABLE dtproperties
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland, the table that is coming up with the error is sysdiagrams, not
dtproperties. At least, that's what the error message in the OP said.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9954F23EE870DYazorman@.127.0.0.1...
> MCG (MCG@.discussions.microsoft.com) writes:
> Sounds like there is some funny data in dtproperties, which causes the
> index violation. While it could be interesting to file a bug for it,
> the quickest way to get it working is probably to simply drop
> dtproperties:
> DROP TABLE dtproperties
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Monday, March 19, 2012

Error Creating Function

I want to calculate a string, so I have created this Function, but its
giving error any idea please ?
Best Regards,
Luqman
Create function eval
(@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
declare @.myString varchar(50)
select @.mystring=exec('select '+@.Mycalcstring)
return(@.myString)
endHi
I am not sure why you don't use cast/convert directly, although this would
depend on the quality of what you are passing to the function.
Create function eval (@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
return(CAST(@.Mycalcstring AS decimal(12,2)))
end
SELECT dbo.eval('12.2')
John
"Luqman" wrote:

> I want to calculate a string, so I have created this Function, but its
> giving error any idea please ?
> Best Regards,
> Luqman
> Create function eval
> (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> declare @.myString varchar(50)
> select @.mystring=exec('select '+@.Mycalcstring)
> return(@.myString)
> end
>
>|||Say, I want to calculate this string : '(10-5)*7/2'
select cast('(10-5)*7/2' AS decimal(12,2))
will give error :
Error converting data type varchar to numeric.
But if I use:
Select (10-5)*7/2 ' It will return 17 which is Correct Result.
So, please advise whats wrong with my function.
Best Regards,
Luqman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:CD3C2647-4BAF-4499-A6B9-06D275405010@.microsoft.com...
> Hi
> I am not sure why you don't use cast/convert directly, although this would
> depend on the quality of what you are passing to the function.
> Create function eval (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> return(CAST(@.Mycalcstring AS decimal(12,2)))
> end
> SELECT dbo.eval('12.2')
> John
> "Luqman" wrote:
>|||Luqman wrote:
> Say, I want to calculate this string : '(10-5)*7/2'
> select cast('(10-5)*7/2' AS decimal(12,2))
> will give error :
> Error converting data type varchar to numeric.
> But if I use:
> Select (10-5)*7/2 ' It will return 17 which is Correct Result.
> So, please advise whats wrong with my function.
> Best Regards,
> Luqman
Dont use the ' because they indicate a character column.
SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||Such calculations are stored in a Table with Varchar Type, so I need to
calculate their values, and as such I need this function.
Following lines will work, but how can I put the select result in Variable.
declare @.myString as varchar(50)
declare @.myValue as Decimal(12,2)
set @.myString='(10-5)/2'
exec ('select ' + @.myvalue) 'This works
set @.myValue=exec ('select ' + @.myvalue) 'This does not work
Best Regards,
Luqman
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
> Luqman wrote:
> Dont use the ' because they indicate a character column.
> SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue
hth,
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Such calculations are stored in a Table with Varchar Type, so I need to
> calculate their values, and as such I need this function.
> Following lines will work, but how can I put the select result in
Variable.
> declare @.myString as varchar(50)
> declare @.myValue as Decimal(12,2)
> set @.myString='(10-5)/2'
> exec ('select ' + @.myvalue) 'This works
> set @.myValue=exec ('select ' + @.myvalue) 'This does not work
> Best Regards,
> Luqman
>
>
> "Jo Segers" <jo.segers@.alro.be> wrote in message
> news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
>|||avnrao wrote:
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
>
Or if you realy want your 2 decimals:
declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = 1.0*' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue|||Hi,
I have created following function in master database and when select it with
:
Select dbo.eval('10-5')
Error occured:
Server: Msg 557, Level 16, State 2, Procedure eval, Line 9
Only functions and extended stored procedures can be executed from within a
function.
Any idea please?
create function eval
(@.myString as varchar(50))
returns decimal(12,2)
as
begin
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
return(@.myValue)
end
"avnrao" <avn@.newsgroups.com> wrote in message
news:u8v6gEQDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Variable.
>|||You cannot run dynamic SQL from within a function. EXEC and
sp_executesql are not allowed. Either use an SP or, better still IMO,
do it in some external code. I don't understand why you would want to
do this in a database.
Also, I wouldn't recommend putting functions or other objects in Master
on a production system.
David Portas
SQL Server MVP
--|||Hi,
I just want to create a function which should calculate a String, and return
the result, someone recommended this way ?
If you have better solution, please adv.
Best Regards,
Luqman
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107775757.602219.248630@.o13g2000cwo.googlegroups.com...
> You cannot run dynamic SQL from within a function. EXEC and
> sp_executesql are not allowed. Either use an SP or, better still IMO,
> do it in some external code. I don't understand why you would want to
> do this in a database.
> Also, I wouldn't recommend putting functions or other objects in Master
> on a production system.
> --
> David Portas
> SQL Server MVP
> --
>

Sunday, March 11, 2012

Error converting data type varchar to numeric

Hi,

Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.

Here is the syntax that I am currently using:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]

FROM dbo.F_Report_Data AS a

WHERE (szF3 <> 'Blank')

When I open the view I get the error message about converting varchar to numeric.

Thanks,

Erik

Try running this query:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time],
szF2 AS [User Name],
-- CAST(szF3 AS numeric) AS [Documents Indexed],
szF3,
CAST(szF4 AS datetime) AS [Logout Date/Time],
-- CAST(szF5 AS numeric) AS [Documents Sent to QC],
-- CAST(szF6 AS numeric) AS [Documents Reconciled],
szF5,
szF6,
szF7 AS [Reject Reason],
CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
where isNumeric (szF3 + 'D0') = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0

And post any results that get returned.|||

I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.

|||

It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.

-Sue

|||

I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a

select top 5 * from F_Report_Data

|||

The 3 columns that I want to cast as numeric have only numbers in them.

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

|||

The 3 columns that I want to cast as numeric have only numbers in them.

Code Snippet

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).

I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.

|||

Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:

Code Snippet

select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0

|||

Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!

|||

Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.

|||

Here are the results:

Code Snippet

01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 20070124
01/24/2007 09:59:03 AM admin 0 01/24/2007 04:09:25 PM 1 0 Unable to read case numbe 20070124
01/24/2007 09:56:03 AM admin 0 01/24/2007 04:26:33 PM 0 3 20070124
01/25/2007 01:55:19 PM admin 3 01/25/2007 03:32:51 PM 0 0 20070125

01/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125

| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|

I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||

Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.

Erik:

You can try this query; it will exhibit which test is failing:

Code Snippet

select isDate(rtrim(szF1)) as szF1isDate,
isNumeric (rtrim(szF3) + 'D0') as szF3IsNumeric,
isDate(rtrim(szF4)) as szF4IsDate,
isNumeric (rtrim(szF5) + 'D0') as szF5IsNumeric,
isNumeric (rtrim(szF6) + 'D0') as szF6IsNumeric,
isDate(rtrim(szF8)) as szF8isDate,
left(szF1, 30) as szF1,
left(szF2, 30) as szF2,
left(szF3, 30) as szF3,
left(szF4, 30) as szF4,
left(szF5, 30) as szF5,
left(szF6, 30) as szF6,
left(szF7, 30) as szF7,
left(szF8, 30) as szF8
from dbo.F_Report_data
where isDate(rtrim(szF1)) = 0
or isNumeric (rtrim(szF3) + 'D0') = 0
or isDate(rtrim(szF4)) = 0
or isNumeric (rtrim(szF5) + 'D0') = 0
or isNumeric (rtrim(szF6) + 'D0') = 0
or isDate(rtrim(szF8)) = 0

I feel like I need a fresh set of eyes on this at this point. Help?

|||

Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?

0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518

|||

Erik:

To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:

0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)

1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)

]

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Thursday, March 29, 2012

Error executing xp_cmdshell from within a SQL task in a DTS package...

Error executing SSIS package

Error executing SSIS package

error executing sprocedure

error executing sprocedure

error executing sprocedure

Tuesday, March 27, 2012

Error executing Packaga from job

Error during sqlDataSource.Update() instruction

Monday, March 26, 2012

Error during FormsAuthentication.RedirectFromLoginPage

Error during FormsAuthentication.RedirectFromLoginPage

Error during Export To Excel - Matrix Report

Error during Export To Excel - Matrix Report

Error during Execute sp with IMAGE parameter

Thursday, March 22, 2012

Error Dumping Data in SQl Server

Error deploying report with SQL Server (Visual Studio) 2005

Error Deploying Cube

Wednesday, March 21, 2012

Error creating new push subscription after deleting the existing o

Error creating new database diagrams in upgraded database

Monday, March 19, 2012

Error Creating Function

Sunday, March 11, 2012

Error converting data type varchar to numeric

Error accessing report

Blog Archive

About Me