Showing posts with label errors. Show all posts
Showing posts with label errors. Show all posts

Wednesday, March 21, 2012

Replace Errors with NULL using Convert

I have a table with a varchar field that for the most part contains valid
dates ie (mm/dd/yyyy). There are some items that are not dates. I would
like a SQL statement that converts the varchar to a datetime and where there
is an error for a particular field will return a null for that field.
For example if my table contains the following items in Field1:
2/1/2005
1/1/2004
other data
3/1/2005
The query should return:
2005-2-1 00:00:00
2004-1-1 00:00:00
NULL
2005-3-1 00:00:00
If I use:
SELECT convert(datetime, Field1) as Field1
FROM table1
Then I get a conver error. Any ideas or suggestions would be helpful.
Thanks!
Dan,
See if this works:
select
case when ISDATE(Field1) = 1
then cast(Field1 as datetime)
else NULL end
from table1
If you can't live with the fact that some garbled
data may convert unexpectedly (the string '110919'
will convert to September 19, 2011, for example),
you'll have to do some pattern matching of your own
as well, such as
case when Field1 like '%/%/%' and Field1 not like '%/%/%/%' ...
and isdate(Field1) = 1 then ...
Steve Kass
Drew University
Dan wrote:

>I have a table with a varchar field that for the most part contains valid
>dates ie (mm/dd/yyyy). There are some items that are not dates. I would
>like a SQL statement that converts the varchar to a datetime and where there
>is an error for a particular field will return a null for that field.
>For example if my table contains the following items in Field1:
>2/1/2005
>1/1/2004
>other data
>3/1/2005
>The query should return:
>2005-2-1 00:00:00
>2004-1-1 00:00:00
>NULL
>2005-3-1 00:00:00
>If I use:
>SELECT convert(datetime, Field1) as Field1
>FROM table1
>Then I get a conver error. Any ideas or suggestions would be helpful.
>Thanks!
>
>

Monday, March 12, 2012

Repeated Errors in SQL Server log file

I have hundreds of these errors saying 'Login failed for user 'Reporting' The user is not associated with a trusted SQL Server connection [CLIENT: ip address]

The ip address is that of the server that sql server is installed on.

Looking in my log file, all looks good until I get to Service Broker manager has started, then I get Error: 18452, Severity: 14 State: 1 then these two lines repeat about every minute, for the last 3 days!

I think I must have just missed a tick box somewhere, but where?

I have been into one of the databases, and input and checked data, both via an application I wrote and SQL Server Management Studio.

I am also having trouble connecting using my application to connect to the database, I can only connect if I use a Windows administrator account (this SQL Server 2005 running on a Windows 2003 Server, with the app on PC running Windows 2000)

Hello Graham,

did you find any solutions for that problem? I'm discovering a similar problem. The state of my error message is 5. According to the following source http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx this means the user is not known. This is correct. Neither the SQL-Server nor the Windows system has such a userid.

The faulty user name is 'Reporting'. What is that user used for? I have set up other SQL Server 2005 servers but was never asked for such a name.

Greetings,

Frank

|||

Frank

No, but I haven't had much chance to chase this, too many other issues!

|||

I've run into the same problem. As far as I can tell the "Reporting" userid is coming from Symantec Reporting Server for Antivirus or Client Security. Everything seems to be functioning in the Reporting console in Symantec Client security just fine, yet I still get the error. I've tried most of what I can think of to resolve this, but will let you know if I find anything else out. Please do likewise.

Jim

Repeated Errors in SQL Server log file

I have hundreds of these errors saying 'Login failed for user 'Reporting' The user is not associated with a trusted SQL Server connection [CLIENT: ip address]

The ip address is that of the server that sql server is installed on.

Looking in my log file, all looks good until I get to Service Broker manager has started, then I get Error: 18452, Severity: 14 State: 1 then these two lines repeat about every minute, for the last 3 days!

I think I must have just missed a tick box somewhere, but where?

I have been into one of the databases, and input and checked data, both via an application I wrote and SQL Server Management Studio.

I am also having trouble connecting using my application to connect to the database, I can only connect if I use a Windows administrator account (this SQL Server 2005 running on a Windows 2003 Server, with the app on PC running Windows 2000)

Hello Graham,

did you find any solutions for that problem? I'm discovering a similar problem. The state of my error message is 5. According to the following source http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx this means the user is not known. This is correct. Neither the SQL-Server nor the Windows system has such a userid.

The faulty user name is 'Reporting'. What is that user used for? I have set up other SQL Server 2005 servers but was never asked for such a name.

Greetings,

Frank

|||

Frank

No, but I haven't had much chance to chase this, too many other issues!

|||

I've run into the same problem. As far as I can tell the "Reporting" userid is coming from Symantec Reporting Server for Antivirus or Client Security. Everything seems to be functioning in the Reporting console in Symantec Client security just fine, yet I still get the error. I've tried most of what I can think of to resolve this, but will let you know if I find anything else out. Please do likewise.

Jim

Friday, March 9, 2012

Repeat a field value(text box) on each page or display value in header

How can I accomplish putting a data driven title on each page of a
report? Header errors say you can't put a field value in a header or
footer, so I tried(from another post) putting a text box in the body of
the report, making it white and setting another text box inthe header = to the value of the text box in the body(=ReportItems!textBox1.Value).
This works until you have a LARGE text field object on a report, whose
contents span multiple pages. Then subsequent pages do not display this
field, I guess because the original text box is out "range" once the
hidden data field is no longer being displayed. I don't have a table.
It's a single record report. I have a bunch of List objects with
textboxes organized on the layout.
Any ideas on how to solve?I have the same problem in SRS 2000. Any ideas on how to solve this would be
greatly appreciated!
"pauldes@.kpdsys.com" wrote:
> How can I accomplish putting a data driven title on each page of a
> report? Header errors say you can't put a field value in a header or
> footer, so I tried(from another post) putting a text box in the body of
> the report, making it white and setting another text box inthe header => to the value of the text box in the body(=ReportItems!textBox1.Value).
> This works until you have a LARGE text field object on a report, whose
> contents span multiple pages. Then subsequent pages do not display this
> field, I guess because the original text box is out "range" once the
> hidden data field is no longer being displayed. I don't have a table.
> It's a single record report. I have a bunch of List objects with
> textboxes organized on the layout.
> Any ideas on how to solve?
>|||I wonder if you put the textbox and the table (or other fields you want shown
on your report) inside a list box and set the textbox to "repeat item with
data area on each page" (although this is supposed to work ... I have had
problems with the "repeat" property). Might be worth a try ...
"Patrik Ylén" wrote:
> I have the same problem in SRS 2000. Any ideas on how to solve this would be
> greatly appreciated!
> "pauldes@.kpdsys.com" wrote:
> > How can I accomplish putting a data driven title on each page of a
> > report? Header errors say you can't put a field value in a header or
> > footer, so I tried(from another post) putting a text box in the body of
> > the report, making it white and setting another text box inthe header => > to the value of the text box in the body(=ReportItems!textBox1.Value).
> >
> > This works until you have a LARGE text field object on a report, whose
> > contents span multiple pages. Then subsequent pages do not display this
> > field, I guess because the original text box is out "range" once the
> > hidden data field is no longer being displayed. I don't have a table.
> > It's a single record report. I have a bunch of List objects with
> > textboxes organized on the layout.
> >
> > Any ideas on how to solve?
> >
> >

Wednesday, March 7, 2012

Repairing indexes in a table but getting errors

Hi ALL,

Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.

Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.

Server: Msg 8936, Level 16, State 1, Line 1

Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).

Not able to reindex the index of table. I can not even export the data to any other table with same structure.

Please help me.........

Thanks,
PracheerPls take a look at
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6046

:)|||Thanks for the help. I will try all the scenarios. Hope that will solve my problem.

:)