Showing posts with label error. Show all posts

Tuesday, March 27, 2012

count number of lines in a clumn

The SP returns a column which contains
Error: 1212121
Error: 3434fsfa
Error: fdfdfd
Each above line has Char(10) + Char(13) attached so they will start a new line
Now I want to know the number of errors returns by using pattern "Error". Is
there any function I can use to do this?
thanks.In SQL you could do something like:
(LEN(columname) - LEN(REPLACE(columnname, 'Error' , '') ) / 5
I am not sure what the equivalent VB.Net syntax would look like to do it in
SRS.
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:B2FA56E5-AD24-4100-AD8B-B4CD7E771606@.microsoft.com...
> The SP returns a column which contains
> Error: 1212121
> Error: 3434fsfa
> Error: fdfdfd
> Each above line has Char(10) + Char(13) attached so they will start a new
> line
> Now I want to know the number of errors returns by using pattern "Error".
> Is
> there any function I can use to do this?
> thanks.

Count is different on same table? Datetime column error?

Hi,
I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
server. A Full Backup of the db (prototype) was taken from SV1 was
applied to SV2. One of the tables in the database has over 40 million
rows in it. My task is to keep any rows no older than 18 months.
SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
this is correct as SV1 is constantly in use, so SV2 will always be a
day or two behind. The issue arrises when I perform my count on both
servers, when I run the following on SV1:
select count(*) from tblConsignments
where [Date] >= '2003-08-01 00:00:00.000'
the count is approx 25,500,000 rows, however when I run the exact same
statement on SV2 the count is 19,500,000. Why would there be a
discrepancy of 6,000,000? The collation is the same for both servers
(SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
standard, both SP3a).
When I do a count year by year (on both servers) it comes to aprrox.
35,000,000 however the total for the tblConsignments is 42million.
Possibly the answer would be to check the data more throughly, however
I would like to know if Datetime columns can be set to different
versions?
Thanks
qhHave you broken the data into smaller chunks? E.g. maybe a week or a month
is missing on one server...
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1115224865.478078.231810@.o13g2000cwo.googlegroups.com...
> Hi,
> I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
> server. A Full Backup of the db (prototype) was taken from SV1 was
> applied to SV2. One of the tables in the database has over 40 million
> rows in it. My task is to keep any rows no older than 18 months.
> SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
> SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
> this is correct as SV1 is constantly in use, so SV2 will always be a
> day or two behind. The issue arrises when I perform my count on both
> servers, when I run the following on SV1:
> select count(*) from tblConsignments
> where [Date] >= '2003-08-01 00:00:00.000'
> the count is approx 25,500,000 rows, however when I run the exact same
> statement on SV2 the count is 19,500,000. Why would there be a
> discrepancy of 6,000,000? The collation is the same for both servers
> (SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
> standard, both SP3a).
> When I do a count year by year (on both servers) it comes to aprrox.
> 35,000,000 however the total for the tblConsignments is 42million.
> Possibly the answer would be to check the data more throughly, however
> I would like to know if Datetime columns can be set to different
> versions?
> Thanks
> qh
>|||Whenever you perform a COUNT(*), I'd recommend you use a MAXDOP (1) in
the SELECT to make sure the parallelism "issue" doesn't cause the
rowcount to be off.
--
David Gugick
Imceda Software
www.imceda.com|||Hi,
Can you update the statistics and see.
UPDATE STATISTICS <TABLE NAME>
Otherwise execute the below command in both databases replacing count(*)
with *, but you need more space in ur hard disk on both servers
select * into old_records from tblConsignments where [Date] >= '2003-08-01
00:00:00.000'
Once the statement is completed try
select count(*) from old_records
But first try updating the statistics and see the difference.
Thanks
Hari
SQL Server MVP
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1115224865.478078.231810@.o13g2000cwo.googlegroups.com...
> Hi,
> I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
> server. A Full Backup of the db (prototype) was taken from SV1 was
> applied to SV2. One of the tables in the database has over 40 million
> rows in it. My task is to keep any rows no older than 18 months.
> SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
> SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
> this is correct as SV1 is constantly in use, so SV2 will always be a
> day or two behind. The issue arrises when I perform my count on both
> servers, when I run the following on SV1:
> select count(*) from tblConsignments
> where [Date] >= '2003-08-01 00:00:00.000'
> the count is approx 25,500,000 rows, however when I run the exact same
> statement on SV2 the count is 19,500,000. Why would there be a
> discrepancy of 6,000,000? The collation is the same for both servers
> (SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
> standard, both SP3a).
> When I do a count year by year (on both servers) it comes to aprrox.
> 35,000,000 however the total for the tblConsignments is 42million.
> Possibly the answer would be to check the data more throughly, however
> I would like to know if Datetime columns can be set to different
> versions?
> Thanks
> qh
>|||Hari,
Just for the record, the date string you give will be
interpreted as August 1, 2003 in some cases (such as us_english language
settings),
and as January 8, 2003 in others (such as french language setting). The two
SQL Server safe formats to use are (for August 1) '20030801' and
'2003-08-01T00:00:00.000'
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Can you update the statistics and see.
>UPDATE STATISTICS <TABLE NAME>
>Otherwise execute the below command in both databases replacing count(*)
>with *, but you need more space in ur hard disk on both servers
>select * into old_records from tblConsignments where [Date] >= '2003-08-01
>00:00:00.000'
>Once the statement is completed try
>select count(*) from old_records
>
>But first try updating the statistics and see the difference.
>Thanks
>Hari
>SQL Server MVP
>
>"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
>news:1115224865.478078.231810@.o13g2000cwo.googlegroups.com...
>
>>Hi,
>>I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
>>server. A Full Backup of the db (prototype) was taken from SV1 was
>>applied to SV2. One of the tables in the database has over 40 million
>>rows in it. My task is to keep any rows no older than 18 months.
>>SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
>>SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
>>this is correct as SV1 is constantly in use, so SV2 will always be a
>>day or two behind. The issue arrises when I perform my count on both
>>servers, when I run the following on SV1:
>>select count(*) from tblConsignments
>>where [Date] >= '2003-08-01 00:00:00.000'
>>the count is approx 25,500,000 rows, however when I run the exact same
>>statement on SV2 the count is 19,500,000. Why would there be a
>>discrepancy of 6,000,000? The collation is the same for both servers
>>(SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
>>standard, both SP3a).
>>When I do a count year by year (on both servers) it comes to aprrox.
>>35,000,000 however the total for the tblConsignments is 42million.
>>Possibly the answer would be to check the data more throughly, however
>>I would like to know if Datetime columns can be set to different
>>versions?
>>Thanks
>>qh
>>
>
>|||Hi ,
Update Statistics is used for statistics generation which is used for
determining the cost of the query , not sure how this will affect count(*) ,
please let me know if update statistics has any thing to do with count(*).
Regards
Vishal
"Hari Prasad" wrote:
> Hi,
> Can you update the statistics and see.
> UPDATE STATISTICS <TABLE NAME>
> Otherwise execute the below command in both databases replacing count(*)
> with *, but you need more space in ur hard disk on both servers
> select * into old_records from tblConsignments where [Date] >= '2003-08-01
> 00:00:00.000'
> Once the statement is completed try
> select count(*) from old_records
>
> But first try updating the statistics and see the difference.
> Thanks
> Hari
> SQL Server MVP
>
> "Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:1115224865.478078.231810@.o13g2000cwo.googlegroups.com...
> > Hi,
> >
> > I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
> > server. A Full Backup of the db (prototype) was taken from SV1 was
> > applied to SV2. One of the tables in the database has over 40 million
> > rows in it. My task is to keep any rows no older than 18 months.
> >
> > SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
> > SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
> >
> > this is correct as SV1 is constantly in use, so SV2 will always be a
> > day or two behind. The issue arrises when I perform my count on both
> > servers, when I run the following on SV1:
> >
> > select count(*) from tblConsignments
> > where [Date] >= '2003-08-01 00:00:00.000'
> >
> > the count is approx 25,500,000 rows, however when I run the exact same
> > statement on SV2 the count is 19,500,000. Why would there be a
> > discrepancy of 6,000,000? The collation is the same for both servers
> > (SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
> > standard, both SP3a).
> >
> > When I do a count year by year (on both servers) it comes to aprrox.
> > 35,000,000 however the total for the tblConsignments is 42million.
> > Possibly the answer would be to check the data more throughly, however
> > I would like to know if Datetime columns can be set to different
> > versions?
> >
> > Thanks
> >
> > qh
> >
>
>|||Hi ,
Update Statistics is used for statistics generation which is used for
determining the cost of the query and selecting the appropriate index , not
sure how this will affect count(*) , please let me know if update statistics
has any thing to do with count(*).
Regards
Vishal|||Hi Guys,
many thanks for the replies, I have taken your suggestions and tried
both UPDATE STATISTICS and the OPTION (MAXDOP 1) however I am still
returning the same rowcount anomoly. The main thing that has me
stumped is that the db on SV2 is a backup from the db on SV1!! I have
even created a small table based on a rowcount between certain years.
SV1 SV2
older 6,486,356 6,486,356
2002 9,893,118 9,893,118
2003 10,402,588 10,402,588
2004 11,268,420 11,268,419
2005+ 4,014,450 3,862,644
Totals 35,578,576 35,426,769
However as I originally mentioned there is over 41,000,000 rows in both
the corresponding tables? My next plan is to perform a simple data
complare on each table.
Thanks again
qhsql

Count is different on same table? Datetime column error?

Sunday, March 25, 2012

Count Distinct

Hello,

I was wondering does count(distinct) work on multiiple columns? I get syntax error on "," with the following code.

select count(distinct cookie1, cookie2) from BookersLookers_DataSet

Thanks,

-Lawrence

You cant pass more than one column in COUNT function..

Use the following query...

Code Snippet

Select Count(*) From

(Select cookie1,cookie2 From BookersLookers_DataSet Group By cookie1,cookie2) as Data

--Or

Select Count(*) From

(Select Distinct cookie1,cookie2 From BookersLookers_DataSet) as Data

|||

Actually, to get the count(), you need to use a GROUP BY, and if you use a GROUP BY, then DISTINCT is not necessary (redundent).

Code Snippet

SELECT
Cookie1,
Cookie2,
[Count] = count(1)
FROM BookersLookers_Dataset
GROUP BY
Cookie1,
Cookie2

Thursday, March 22, 2012

Count ( Distinct Case ..) syntax error

I am getting a syntax error for the following piece of code:

I don't understand what's wrong with it. I came across constructs like

Select Count(Distinct Case When ...Then ID End ) ...

and also

Select Distinct Count(Case When ...)...

What I want is the first one, a count of the distinct IDs.

What am I missing?

Magic:

I am not sure what you are getting; could you post your error message? When I run what follows it compiles OK and seems to run OK:

Code Snippet

select Count(Distinct Case When (StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0)
and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52)
Then ProjectID end
) as distinctCount
from ( select 1 as startDate, 1 as ProjectStatusId, 1 as projectId) x

/*
distinctCount
-
0

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
*/

|||My code is below. I can't use the insert code feature because whenever I click on that it makes my whole post dissappear. I'm using Firefox.

The error is:
Incorrect syntax near 'distinct'.

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End) Over (Partition By DepartmentDetails.DepartmentName, ProjectCategory) [NewRequests]

From #tempResourceAllocation
Inner join dbo.DepartmentDetails
On (#tempResourceAllocation.ParentDepartmentID = DepartmentDetails.DepartmentID)

Order By ProjectCategory

|||

The problem seems to be when using "distinct" inside an aggregate function and the "over" clause. I haven't be able to find anything related to this in BOL.

AMB

|||

I fully agree with AMB; however, I was able to get my version to work using GROUP BY instead of OVER. Give GROUP BY a try instead. What I have looks like this:

Code Snippet

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)
as [NewRequests]
from ( select 1 as startDate,
1 as projectStatusId,
1 as departmentName,
1 as projectCategory,
1 as projectId
) as departmentDetails
group by DepartmentDetails.DepartmentName, ProjectCategory
order by ProjectCategory

/*
DepartmentName ProjectCategory NewRequests
-- --
1 1 0
*/

|||

I also see a potential problem with your date range. It looks to me like you have the same TO and FROM date if you are trying to get data from the previous month, change

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate()), 0))

|||Yes, thanks, I was playing around getting some values from the previous one month intervals and when I switched it back I missed that.

Thanks for the input everyone.

I don't really know what to do since I was using Partition Over as another way to create subtotals based on the category but only for a subset of the columns in the table. I tried with Rollup but I couldn't get this functionality because it forces me to put all the columns in Group By and it messes up my layout giving me summary totals based on different criteria rather than solely on the Category field.

Even so Rollup doesn't work with Distinct aggregates which is a problem because I do have several entries with the same key of interest in my table just because in someother column I have distinct values for the same key and counting will include duplicates also.

|||

hi, did you try this?

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID else 0 End)

|||Whether it counts a 1 or a 0 isn't the result still one? As in one item that got counted?
|||

I think Tolga has a good point NULL does not help you towards a distinct count. Notice for this two-record select that that one of the entries is null. Also, note that the count is "1" and not "2":

Code Snippet

select count(distinct what) as theCount from (
select 1 as what union select null
) a

/*
theCount
--
1
*/

|||

yes you are right, result still one,

okey, try to sum,

sum(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then 1 else 0 End)

Couldn't connect to reports

Hi,

I'm not able to browse through http://localhost/reports.

When I'm trying to connect it gives me "Server Error in '/Reports' Application."At the same time I'm able to view Reportserver.At the same time I'm able to view Reportserver.(i.e)http://localhost/reportserver

please can anyone suggest me some solution to this problem..

Server Error in '/Reports' Application.

The trust relationship between the primary domain and the trusted domain failed.

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.SystemException: The trust relationship between the primary domain and the trusted domain failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SystemException: The trust relationship between the primary domain and the trusted domain failed.

]

System.Security.Principal.NTAccount.TranslateToSids(IdentityReferenceCollection sourceAccounts, Boolean& someFailed) +1167

System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean& someFailed) +42

System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean forceSuccess) +46

System.Security.Principal.WindowsPrincipal.IsInRole(String role) +205

System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120

System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300

System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191

System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3403086

System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Hi,

What are the security settings in IIS for both sites?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

we had given full control to the administrators as well as system users.

And read and execute permission to reportserveruser as well as webservice user.

Thanks

Anandhi

|||

That is for NTFS security. What are the permissions set on the virtual directory?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Execute Scripts only permission for both

Thanks

Anandhi

|||

I mean in the Directory Security Tab.

There you can see whether the site is accessed via:

anonymous (+ which account)|||

Sorry for misunderstanding,

We have chosen for Integrated Windows Authentication

Anonymous is not enabled.

Thanks,

Anandhi

|||

Is this the same as for the report server?

By default this virtual directory is mapping to a folder in your C:\Program Files\... (for me it is C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager). This means that the users of the http://localhost/reports url, must have read permissions to it.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Permissions for both reports and reportserver are one and the same.Read permission is there.And it is mapped to the folder as you had specified.

Thanks,

Anandhi

|||

Did you migrate from one domain to another?

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

No we havn't migrated to any other domain.

Thanks,

Anandhi

Couldn't connect to my server from client

I have a communication error from my client when I try to connect to my SQL
server 2000 SP4 on a Windows 2003 server. Is there any method that I can get
to see if I don't miss something when I have made the installation on the
server part ?
Every help will be welcome
Regards,
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200609/1I dont believe that SQL server keeps track of that type of information
unless you have C2 auditing enabled. You could also pick that up in a trace
file had you been tracing.
http://www.microsoft.com/technet/pr...tain/sqlc2.mspx
I could be wrong though. Does anyone else have any information?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Silver via droptable.com" <u26873@.uwe> wrote in message
news:669141b5c2b48@.uwe...
>I have a communication error from my client when I try to connect to my SQL
> server 2000 SP4 on a Windows 2003 server. Is there any method that I can
> get
> to see if I don't miss something when I have made the installation on the
> server part ?
> Every help will be welcome
> Regards,
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...curity/200609/1
>sql

Tuesday, March 20, 2012

Could this be run using OSQL script

If there a way to detect if backup fails? I like to add better error
handling... I need to be able to run using a script not thru the Sql
Scheduler.
DECLARE @.name VARCHAR(50) -- database name
DECLARE @.path VARCHAR(256) -- path for backup files
DECLARE @.fileName VARCHAR(256) -- filename for backup
DECLARE @.fileDate VARCHAR(20) -- used for file name
SET @.path = 'C:\Backup\'
SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
BACKUP DATABASE @.name TO DISK = @.fileName
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursorHi
You could test @.@.ERROR after performing the backup to see if the backup
command worked and store a list of failures, but some errors will abort the
batch and therefore subsequent databases will not be backed up. SQL2005 has
better error handling. The script can be run using OSQL with the -i flag if
you save the script to a file. The -b flag reports as a command line error
any error value so it could be trapped using ERRORLEVEL it will be 1 if one
of the backups failed. You may also want to use the -n flag to suppress line
numbers. If using SQL Agent you do not need to use a command prompt.
Your backups will append to any files that are already present with the
given name you may want to specify the INIT command if you want to overwrite
these.
John
"AHartman" wrote:
> If there a way to detect if backup fails? I like to add better error
> handling... I need to be able to run using a script not thru the Sql
> Scheduler.
> DECLARE @.name VARCHAR(50) -- database name
> DECLARE @.path VARCHAR(256) -- path for backup files
> DECLARE @.fileName VARCHAR(256) -- filename for backup
> DECLARE @.fileDate VARCHAR(20) -- used for file name
> SET @.path = 'C:\Backup\'
> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master.dbo.sysdatabases
> WHERE name NOT IN ('master','model','msdb','tempdb')
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
> BACKUP DATABASE @.name TO DISK = @.fileName
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||Thanks... for the info
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:45510EB6-452B-4597-8775-99F7FC501054@.microsoft.com...
> Hi
> You could test @.@.ERROR after performing the backup to see if the backup
> command worked and store a list of failures, but some errors will abort
> the
> batch and therefore subsequent databases will not be backed up. SQL2005
> has
> better error handling. The script can be run using OSQL with the -i flag
> if
> you save the script to a file. The -b flag reports as a command line error
> any error value so it could be trapped using ERRORLEVEL it will be 1 if
> one
> of the backups failed. You may also want to use the -n flag to suppress
> line
> numbers. If using SQL Agent you do not need to use a command prompt.
> Your backups will append to any files that are already present with the
> given name you may want to specify the INIT command if you want to
> overwrite
> these.
> John
> "AHartman" wrote:
>> If there a way to detect if backup fails? I like to add better error
>> handling... I need to be able to run using a script not thru the Sql
>> Scheduler.
>> DECLARE @.name VARCHAR(50) -- database name
>> DECLARE @.path VARCHAR(256) -- path for backup files
>> DECLARE @.fileName VARCHAR(256) -- filename for backup
>> DECLARE @.fileDate VARCHAR(20) -- used for file name
>> SET @.path = 'C:\Backup\'
>> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
>> DECLARE db_cursor CURSOR FOR
>> SELECT name
>> FROM master.dbo.sysdatabases
>> WHERE name NOT IN ('master','model','msdb','tempdb')
>> OPEN db_cursor
>> FETCH NEXT FROM db_cursor INTO @.name
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
>> BACKUP DATABASE @.name TO DISK = @.fileName
>> FETCH NEXT FROM db_cursor INTO @.name
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>>sql

Could not start the SQL Server Reporting Services (REPORTS) service on local computer.

Error 1069. The service did not start due to login failure.

I can't get a domain login account with domain admin rights to start the service. The only way I can start the service is with the LocalSystem account, which isn't a good solution because it's not networkable.

Thank you for any advice you have.

Does your domain account has "Log on as a service" right?|||Yes. My domain account has it in Active Directory and on the server under Local Security Policy.|||Interesting ... can you run any other service with your domain account? Is this problem specific to RS service?|||I just tested stopping and starting another service with the same domain account and it ran that service successfully. It appears that the problem is just related to SQL Server Reporting Services.|||Perhaps the password changed and your RS service still has the old password? Sorry I can't think of anything else.|||

How do I know what password the RS Service has or should be? I know the password hasn't changed for our domain account. I verified it works for other services.

Here is more information:

I have two servers:

One database server: SQL Server 2005 SP1

One web server with IIS running multiple websites with ASP 1.1 and ASP 2.0 and SQL Server 2005 Reporting Services. I configured Reporting Services from my web server where my SQL Server Reporting Services account is that won't login as a domain administrator account with administrator rights and log on as a service rights.

Is there some other type of rights that may be missing related to SQL Server Reporting services?

|||Also, I found that the WebServiceAccount was not set in the RSreportserver.config file in Program Fiels\Microsoft SQL Servre\MSSQL.1\Reporting Services\ReportManager so I set it to IWAM_ComputerName with the ComputerName as the name of my computer. Then I tried restarted the Reporting Services service, but it didn't start. I also can't get the ASP.NET service to start. I tried to start it using the IWAM_ComputerName account, but I need to figure out what the password is for that account to start the service.|||

rsreportserver.config file should be in ...\MSSQL.1\Reporting Services\ReportServer directory. To set web service account, please use the configuration tool. Setting it to IWAM_ account is incorrect. On XP/Win2K, the web service account is by default <MachineName>\ASPNET. On Win2K3 it is the app pool identity of your report server virtual directory.

When you say you can't get ASP.NET to start, what did you try? You shouldn't do anything. The ASP.NET worker process should start when the first request is received.

|||

The configuration tool doesn't grab the ASP.NET account web service. It won't pick it up or let me type it in. The service is listed in Services, but wasn't started. The only way it would start was with a login of LocalSystem.

In another website, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17756&SiteID=1, a Microsoft Lead Reporting Services person, Tudor Trufinescu, recommended added the Web Service Account if it was missing in the config file.

I'll remove it though.

The article also said that if I'm using IIS 5, which I assume I'm using since I have Windows 2000 Advanced Server, that the ASP.NET account should login using MACHINE\ASPNET account. This never worked. I had to change it to the LocalSystem login to work. I thought this problem may be related to the Reporting Services problem since it's an option in the Configuration Tools that isn't configured.

Thank you.

|||

The configuration tool should pick up ASP.NET account from machine.config file. It will not let you type in if you are on IIS5. Are you seeing blank "ASP.NET service account" box?

I am still confused when you talk about "starting the service". Which service is this? (What is the name of the service in service controller manager?) RS has a windows service that is named "SQL Server Reporting Services". On IIS side, you don't control ASP.NET worker process identity since you are on Win2K/IIS5.

|||

Yes. I am seeing a blank ASP.NET service account box.

The two services I was referencing were:

SQL Server Rerporting Service - will only start with a logon of LocalSystem account - not a good solution because I need to use a domain account that will login across the network into a database.

ASPNET service - will only start with the LocalSystem account. I just mentioned this to note it. Wasn't sure if it also needed to start using some other type of login.

Could not start the MSSQLServer Service on local computer. Error: 1068: the dependency ser

We installed SQL Server 2000 on a Win2000 server (call it serverA). This
Win2000 server used to be a member server of a Domain Controller (call it
serverDC with domain name DCName). We had a problem with that Domain
Controller today, so we took out serverA from the domain controller. Now, we
cannot start the SQL Server Service Manager. It gave us an error that says
"Dependency service or group failed to start. Error 1068 (Dependency service
or group failed to start) occured while performing this service operation on
the MSSQLServer Service.".
When I tried to srtart the MSSQLServer Service, it gave me an error "Could
not start the MSSQLServer Service on local computer. Error: 1068: the
dependency service or group failed to start". When I clicked on the tab
"Dependencies", there is no dependency for this service.
On the "Log On" tab of MSSQLServer Service, the user name used to say
"DCName\Administrator", and after we took out serverA from the domain
controller, we changed the user name to "\Administrator".
How can I fix this problem ? Thank you.Try changing the services (SQL Server, SQL Agent, etc) to
run under Local System - from the Log On tab of the service.
You wouldn't necessarily want to keep it set to this but you
can see if it's starts up with the services running under
local system.
-Sue
On Tue, 7 Sep 2004 14:49:06 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>We installed SQL Server 2000 on a Win2000 server (call it serverA). This
>Win2000 server used to be a member server of a Domain Controller (call it
>serverDC with domain name DCName). We had a problem with that Domain
>Controller today, so we took out serverA from the domain controller. Now, we
>cannot start the SQL Server Service Manager. It gave us an error that says
>"Dependency service or group failed to start. Error 1068 (Dependency service
>or group failed to start) occured while performing this service operation on
>the MSSQLServer Service.".
>When I tried to srtart the MSSQLServer Service, it gave me an error "Could
>not start the MSSQLServer Service on local computer. Error: 1068: the
>dependency service or group failed to start". When I clicked on the tab
>"Dependencies", there is no dependency for this service.
>On the "Log On" tab of MSSQLServer Service, the user name used to say
>"DCName\Administrator", and after we took out serverA from the domain
>controller, we changed the user name to "\Administrator".
>How can I fix this problem ? Thank you.
>

Could not start the ASP.NET service on local computer.

Error 1069. The service did not start due to login failure.

Thank you for any advice you have.

How do I know what password the RS Service has or should be? I know the password hasn't changed for our domain account. I verified it works for other services.

Here is more information:

I have two servers:

One database server: SQL Server 2005 SP1

Is there some other type of rights that may be missing related to SQL Server Reporting services?

When you say you can't get ASP.NET to start, what did you try? You shouldn't do anything. The ASP.NET worker process should start when the first request is received.

|||

I'll remove it though.

Thank you.

|||

The configuration tool should pick up ASP.NET account from machine.config file. It will not let you type in if you are on IIS5. Are you seeing blank "ASP.NET service account" box?

|||

Yes. I am seeing a blank ASP.NET service account box.

The two services I was referencing were:

SQL Server Rerporting Service - will only start with a logon of LocalSystem account - not a good solution because I need to use a domain account that will login across the network into a database.

ASPNET service - will only start with the LocalSystem account. I just mentioned this to note it. Wasn't sure if it also needed to start using some other type of login.

sql

Could not start the ASP.NET service on local computer.

Error 1069. The service did not start due to login failure.

Thank you for any advice you have.

How do I know what password the RS Service has or should be? I know the password hasn't changed for our domain account. I verified it works for other services.

Here is more information:

I have two servers:

One database server: SQL Server 2005 SP1

Is there some other type of rights that may be missing related to SQL Server Reporting services?

When you say you can't get ASP.NET to start, what did you try? You shouldn't do anything. The ASP.NET worker process should start when the first request is received.

|||

I'll remove it though.

Thank you.

|||

The configuration tool should pick up ASP.NET account from machine.config file. It will not let you type in if you are on IIS5. Are you seeing blank "ASP.NET service account" box?

|||

Yes. I am seeing a blank ASP.NET service account box.

The two services I was referencing were:

SQL Server Rerporting Service - will only start with a logon of LocalSystem account - not a good solution because I need to use a domain account that will login across the network into a database.

ASPNET service - will only start with the LocalSystem account. I just mentioned this to note it. Wasn't sure if it also needed to start using some other type of login.

Could not start SQL Server

SQL server is not starting, logs following error in
windows log
Error: 9003, Severity: 20,State: 1
The LSN (4:482:1) passed to log scan in database 'model'
is invalid
Is any one having solution for this?Manoj
It looks like the model database has become corrupted due to the power
failure.
Try to start the SQL Server as an application from a command prompt. For
more information, please refer to the topics: 'Starting SQL Server
Manually' and 'Using Startup Options' on Microsoft SQL Server Books Online.
Look if it does not help you so in this situation your best bet is to call
MS PSS
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> SQL server is not starting, logs following error in
> windows log
> Error: 9003, Severity: 20,State: 1
> The LSN (4:482:1) passed to log scan in database 'model'
> is invalid
> Is any one having solution for this?
>|||Hi,
Seems your Model database is corrupt,
1. Stop SQL server
2. Copy the .MDF and .LDF files (Belongs to MODEL database) to a different
ent location
3. From a different SQL Serevr copy the Model databases .MDF and .LDF files
to the same folder. Overwrite the old files
4. Now start the SQL server.
Most probably this will work out.
Thanks
Hari
MCDBA
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> SQL server is not starting, logs following error in
> windows log
> Error: 9003, Severity: 20,State: 1
> The LSN (4:482:1) passed to log scan in database 'model'
> is invalid
> Is any one having solution for this?
>|||Thanks Hari !! I will try this option
>--Original Message--
>Hi,
>Seems your Model database is corrupt,
>1. Stop SQL server
>2. Copy the .MDF and .LDF files (Belongs to MODEL
database) to a different
>ent location
>3. From a different SQL Serevr copy the Model
databases .MDF and .LDF files
>to the same folder. Overwrite the old files
>4. Now start the SQL server.
>
>Most probably this will work out.
>Thanks
>Hari
>MCDBA
>
>"Manoj" <manoj_raheja@.hotmail.com> wrote in message
>news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
>> SQL server is not starting, logs following error in
>> windows log
>> Error: 9003, Severity: 20,State: 1
>> The LSN (4:482:1) passed to log scan in database 'model'
>> is invalid
>> Is any one having solution for this?
>
>.
>|||Make sure that other SQL Server has the same collation. SQL Server does not
support different collations across the system databases.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:d07801c3eeee$ed440170$a401280a@.phx.gbl...
> Thanks Hari !! I will try this option
> >--Original Message--
> >Hi,
> >
> >Seems your Model database is corrupt,
> >
> >1. Stop SQL server
> >2. Copy the .MDF and .LDF files (Belongs to MODEL
> database) to a different
> >ent location
> >3. From a different SQL Serevr copy the Model
> databases .MDF and .LDF files
> >to the same folder. Overwrite the old files
> >4. Now start the SQL server.
> >
> >
> >Most probably this will work out.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >"Manoj" <manoj_raheja@.hotmail.com> wrote in message
> >news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> >> SQL server is not starting, logs following error in
> >> windows log
> >>
> >> Error: 9003, Severity: 20,State: 1
> >> The LSN (4:482:1) passed to log scan in database 'model'
> >> is invalid
> >>
> >> Is any one having solution for this?
> >>
> >
> >
> >.
> >|||Hi! Hari Prasad,
Heartly thanks for your comment. I was stuck with the same problem & after googling for quite sometime i came across your comment. I am all set with my DB now. Thanks again.
From http://www.developmentnow.com/g/118_2004_2_0_0_469115/Could-not-start-SQL-Server.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||What's the error? Could you post the error form the error log?
--
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Community)
SQLTips, SQL Scripts, SQL Forums, SQL Articles, SQL Radio, SQL Server 2008,
SQL Blogs, SQL Clinic and a lot of SQL Server fun.
"Girish Sharma (QA Engg.)" wrote:
> Hi! Hari Prasad,
> Heartly thanks for your comment. I was stuck with the same problem & after googling for quite sometime i came across your comment. I am all set with my DB now. Thanks again.
>
> From http://www.developmentnow.com/g/118_2004_2_0_0_469115/Could-not-start-SQL-Server.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Could not start SQL Agent

Hello.
I have SQL Server MSDE running Windows 2000.
When I start SQL Agent gives me the following error:"Could not start
the SQLAgent$SPSQL service on Local Computer. The service did not
return an error..."
An
Can anyone help me?
Thanks.
ticha
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message930459.html
Hi
Look in the Windows Event Log and the Agent log file in the LOGs directory.
You may get more information there.
Regards
Mike
"ticha" wrote:

> Hello.
> I have SQL Server MSDE running Windows 2000.
> When I start SQL Agent gives me the following error:"Could not start
> the SQLAgent$SPSQL service on Local Computer. The service did not
> return an error..."
> An
> Can anyone help me?
> Thanks.
>
> --
> ticha
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message930459.html
>

Could not start DTS services in SQL SERVER 2005 developper edition

Hello,

I tried to use the SQL SERVER DTS on SQL SERVER 2005, but when I wan't to start the service i have the next error :

The SQL Network Interface library was unable to register SPN. Error: 0x54b.

Coul you help me please.

Thanks

DraganPlease check the following post for the answer:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76142

Could not start DTS services in SQL SERVER 2005 developper edition

Hello,

I tried to use the SQL SERVER DTS on SQL SERVER 2005, but when I wan't to start the service i have the next error :

The SQL Network Interface library was unable to register SPN. Error: 0x54b.

Coul you help me please.

Thanks

Dragan

Please check the following post for the answer:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76142

Could not setup Web Service Identity --> Error Code 80070003

Hello!
I′m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity

ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070003
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)

The default website contains an office sharepoint 2007 beta solution if that could cause the error..

Would be real grateful for some hints...

Regards

Erik B

Have you found a solution to this? I'm getting the same error after moving the Identities from one Website to another.|||

8007003 is ERROR_PATH_NOT_FOUND. Are you on Win2K3? I suspect this has something to do with ASP.NET setup. Either the WMI provider could not open the ASP.NET registry keys, or it could not find the machine.config file. Make sure you have run aspnet_regiis -i -enable.

If it doesn't work, I would start using regmon and filemon to looks for suspicious errors that could lead to 8007003. Unfortunately at this point the error code is too general to provide any clue.

|||

Hi... I reinstalled SQL 2005 and then it worked fine.. However I think it had to do something with the Encryption Keys... Gwired if I were you I′d delete those from the Reporting Services Configuration Manager, create new ones and then setup a Web Service Identity.

Have a nice day

/Erk

|||

I ran into the same problem, and discovered the issue had to do with the fact that I installed SQL Server 2005, SP1, SP2 and THEN installed Reporting Services. Because I had installed SP2 before Reporting Services, SP2 was not applied to Reporting Services.

The only other thing of note that happened was that when I tried to run the User Provisioning Tool after installing SP2 I got a 'object not set to an instance of an object error'. I didn't really think anything about it at the time, but I think it was only because I figured I'd find the problem down the road. I guess you can say that I did.

Could not setup Web Service Identity --> Error Code 80070003

Hello!
I′m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity

The default website contains an office sharepoint 2007 beta solution if that could cause the error..

Would be real grateful for some hints...

Regards

Erik B

Have you found a solution to this? I'm getting the same error after moving the Identities from one Website to another.|||

If it doesn't work, I would start using regmon and filemon to looks for suspicious errors that could lead to 8007003. Unfortunately at this point the error code is too general to provide any clue.

|||

Have a nice day

/Erk

|||

Tuesday, March 27, 2012

count number of lines in a clumn

Count is different on same table? Datetime column error?

Count is different on same table? Datetime column error?

Count is different on same table? Datetime column error?

Sunday, March 25, 2012

Count Distinct

Thursday, March 22, 2012

Count ( Distinct Case ..) syntax error

Couldn't connect to reports

Server Error in '/Reports' Application.

The trust relationship between the primary domain and the trusted domain failed.

Couldn't connect to my server from client

Tuesday, March 20, 2012

Could this be run using OSQL script

Could not start the SQL Server Reporting Services (REPORTS) service on local computer.

Could not start the MSSQLServer Service on local computer. Error: 1068: the dependency ser

Could not start the ASP.NET service on local computer.

Could not start the ASP.NET service on local computer.

Could not start SQL Server

Could not start SQL Server

Could not start SQL Agent

Could not start DTS services in SQL SERVER 2005 developper edition

Could not start DTS services in SQL SERVER 2005 developper edition

Could not setup Web Service Identity --> Error Code 80070003

Could not setup Web Service Identity --> Error Code 80070003

Counter settings

Blog Archive

About Me