db datatypes: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Problem with long running report timing out

I have a 'YTD' report that is timing out after 30 minutes, where do I go to
change the default timeout period for reports?
TIA,
Bill Youngman
AnexinetIn SSRS2000, you open the Execution property for the report and select
the "Limit report execution to the following number of seconds:" option
under Report Execution Timeout. Then just set the number of SECONDS to
whatever amount will cover the proc run time.
I don't know if this is the same method used in SSRS2005 but I think
there'd be something similar. Hope this helps.
Bill Youngman wrote:
> I have a 'YTD' report that is timing out after 30 minutes, where do I go to
> change the default timeout period for reports?
> TIA,
> Bill Youngman
> Anexinet

Problem with Linked Server

I am running a linked server "SGSC" from SQL 2k to Oracle 9i

EXEC sp_addlinkedserver @.server='SGSC',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='SGSC'

EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='SGSC',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'bisadmin',
@.rmtpassword = 'bisadmin'

When I am trying to execute the following query

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM GL_BALANCES')

I get this error

<eb1>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

State:01000,Native:7300,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]

State:37000,Native:7399,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]</eb1>

Can somebody help me out?

Assuming that your Oracle client is installed correctly on your SQL Server instance, I think you have a problem with the OPENQUERY statement that being passed to the Oracle server. The table name should be fully qualified with the catalog and schema names, like:

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM THECATALOGNAME.THESCHEMANAME.GL_BALANCES')

SELECT * INTO GL_BALANCES FROM SGSC.THECATALOGNAME.THESCHEMANAME.GL_BALANCES

The catalog and schema names should be visible from within Enterprise Manager when you browse the linked server's database tables.

Mark

|||Hi Mark,

Thanks for replying.
I tried executing the query using catalog and schema names but still it gives the same error|||I assume that the query is valid? (Run SQL Plus on the box where SQL Server resides and login using bisadmin/bisadmin@.SGSC.)
Have you gotten any linked queries to run on this server using OPENQUERY? Can you run 'SELECT * FROM dual'? If you get errors doing this, are the error codes the same?
When logging into SQL Server, are you using Windows Authentication or a SQL Server login?|||Make sure that SQL Server server appears before oracle in the server path statement. The following article helped me tremendousely http://windowsitpro.com/articles/print.cfm?articleid=22264|||Hi All,

Here is my system:

Windows 2003 Server
Oracle 9i client client (9.2.0.1.0)
SQL Server 2000 (SP 3)

Here is my story:

I've setup my Linked Server according to this article http://support.microsoft.com/kb/280106/
and built the test environment according to this article : http://objectsharp.com/Blogs/matt/archive/2005/06/13/2221.aspx

When I issued this command

begin tran

exec CallOracleProcTest

rollback tran

i get this error :

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Can anybody help me ?|||Im getting the same message as you do. I've checked that the DTC is running and i still get the error. Any suggestions?|||Hi, Can anyone tell me if there was ever a sucessful resolution/conclusion to this?

We've been going round in circles trying to link a Sybase db into SQLServer hosted on w W2K3 platform.

Just looking at the other linked articles now but I'd like to hear if anyone found out what was going on or if indeed why?

Thanks

Paul.sql

Problem with Linked Server

I am running a linked server "SGSC" from SQL 2k to Oracle 9i

EXEC sp_addlinkedserver @.server='SGSC',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='SGSC'

EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='SGSC',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'bisadmin',
@.rmtpassword = 'bisadmin'

When I am trying to execute the following query

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM GL_BALANCES')

I get this error

<eb1>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

State:01000,Native:7300,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]

State:37000,Native:7399,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]</eb1>

Can somebody help me out?

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM THECATALOGNAME.THESCHEMANAME.GL_BALANCES')

SELECT * INTO GL_BALANCES FROM SGSC.THECATALOGNAME.THESCHEMANAME.GL_BALANCES

The catalog and schema names should be visible from within Enterprise Manager when you browse the linked server's database tables.

Mark

|||Hi Mark,

Here is my system:

Windows 2003 Server
Oracle 9i client client (9.2.0.1.0)
SQL Server 2000 (SP 3)

Here is my story:

When I issued this command

begin tran

exec CallOracleProcTest

rollback tran

i get this error :

We've been going round in circles trying to link a Sybase db into SQLServer hosted on w W2K3 platform.

Just looking at the other linked articles now but I'd like to hear if anyone found out what was going on or if indeed why?

Thanks

Paul.

Monday, March 26, 2012

Problem with Integrity Check

I have a db on one server that publishes to a subscriber on another server
(which acts as distributor as well). Both are running SQLS2K. I have
maintenance plans in place on both dbs and each does an integrity check. The
check on the publisher runs fine. The check on the subscriber fails and the
log file states:
Starting maintenance plan 'Loc 052 S2 DB Maintenance Plan' on 5/10/2005
9:25:43 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'Unifirst052'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database Unifirst052: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
************
I looked at the properties of both dbs and don't see anything different. I
have this same setup on other pairs of servers in different locations and I
don't have a problem with integrity checks on either the publisher or
subscriber. There are anywhere from 3 to 7 users on each of those systems.
What am I missing on the subscriber of this current system?
Remove the "repair minor problems" from the maint plan. Repairing is *not* something you want to be
done silently and automatically in unlikely case of a corruption. You want to do root cause analysis
etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
news:A7EFDE92-ECB4-4E27-AF90-41BF541D2DE8@.microsoft.com...
>I have a db on one server that publishes to a subscriber on another server
> (which acts as distributor as well). Both are running SQLS2K. I have
> maintenance plans in place on both dbs and each does an integrity check. The
> check on the publisher runs fine. The check on the subscriber fails and the
> log file states:
> Starting maintenance plan 'Loc 052 S2 DB Maintenance Plan' on 5/10/2005
> 9:25:43 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'Unifirst052'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database Unifirst052: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ************
> I looked at the properties of both dbs and don't see anything different. I
> have this same setup on other pairs of servers in different locations and I
> don't have a problem with integrity checks on either the publisher or
> subscriber. There are anywhere from 3 to 7 users on each of those systems.
> What am I missing on the subscriber of this current system?
|||That was it, thanks. Don't know how that got checked but I cleared it and
everything is good.
"Tibor Karaszi" wrote:

> Remove the "repair minor problems" from the maint plan. Repairing is *not* something you want to be
> done silently and automatically in unlikely case of a corruption. You want to do root cause analysis
> etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roger Denison" <RogerDenison@.discussions.microsoft.com> wrote in message
> news:A7EFDE92-ECB4-4E27-AF90-41BF541D2DE8@.microsoft.com...
>
>

Problem with installing SQL Server

Hi All,

I'm running into a problem when trying to install SQL Server onto my laptop that is running Windows XP. The error message that is displayed is:

"An error occurred while creating one or more registry entries. Please see
C:\Windows\sqlstp.log for details. The problem could be caused by a low
registry quota condition."

Does anyone know how to solve this?? Any help would be appreciated.

Thanks,

Pete

Seems to be a complex issue, may need sqlstp.log for trouble-shooting. For a quick check, please take a look at this link, which also mentioned an issue of installing SQL on WinXp:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=182716&SiteId=1

|||Typically this type of error (which isn't SQL Server specific) can/will happen if the account you are using can't access the registry to write/update new entries... or if there aren't enough empty 'slots' available.

Were you running/installing this as admin? (or were you trying to throw this instance on to a DC, etc?)

Try this link as well:
http://www.mcse.ms/archive82-2004-12-1281012.html

Problem with installing sp4

I can't install sp4. I got error :Error running script: replsys.sql (1)
looking in sqllog, I have this lines (at the end of file).
...
12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\setup.iss returned: 0, 0
12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0, 0
12:13:12 SetFileSecuritySQLAndAdmin for returned: 3, 3
12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0, 0
12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlstp.log returned: 0, 0
12:13:12 SetFileSecPerfAndLogUsers for C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\SQLCTR80.DLL returned: 1332, 1332
12:13:12 Begin ServPriv (MSSQLSERVER)
12:13:12 End ServPriv()
12:13:13 Upgrading databases
12:13:13 Upgrading databases on instance 'MSSQLSERVER'
12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
1 -Action 6 -Service MSSQLSERVER
12:13:13 Process Exit Code: (0)
12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
1 -Action 6 -Service MSSQLSERVER
12:13:13 Process Exit Code: (0)
12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
1 -Action 1 -Service MSSQLSERVER -StartupOptions \-T4022 \-T4010 \-m
12:13:34 Process Exit Code: (0)
12:13:34 C:\SQL2KSP4\x86\BINN\osql.exe -Slpc:BOGIBATINA -b -n -d master -o
"C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out" -i
"C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.sql" -Usa -P"
12:13:34 Process Exit Code: (1)
12:14:10 Error running script: replsys.sql (1)
12:14:10 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
1 -Action 6 -Service MSSQLSERVER
12:14:25 Process Exit Code: (0)
12:14:25 Action CleanUpInstall:
12:14:25 Installation Failed.Have you looked in this file?
C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out
R.
"Radovan" <radovan@.servis24.hr> wrote in message
news:OWXdLAeuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I can't install sp4. I got error :Error running script: replsys.sql (1)
> looking in sqllog, I have this lines (at the end of file).
> ...
> 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\setup.iss returned: 0, 0
> 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0, 0
> 12:13:12 SetFileSecuritySQLAndAdmin for returned: 3, 3
> 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0, 0
> 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlstp.log returned: 0, 0
> 12:13:12 SetFileSecPerfAndLogUsers for C:\Program Files\Microsoft SQL
> Server\MSSQL\Binn\SQLCTR80.DLL returned: 1332, 1332
> 12:13:12 Begin ServPriv (MSSQLSERVER)
> 12:13:12 End ServPriv()
> 12:13:13 Upgrading databases
> 12:13:13 Upgrading databases on instance 'MSSQLSERVER'
> 12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 6 -Service MSSQLSERVER
> 12:13:13 Process Exit Code: (0)
> 12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 6 -Service MSSQLSERVER
> 12:13:13 Process Exit Code: (0)
> 12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 1 -Service MSSQLSERVER -StartupOptions \-T4022 \-T4010 \-m
> 12:13:34 Process Exit Code: (0)
> 12:13:34 C:\SQL2KSP4\x86\BINN\osql.exe -Slpc:BOGIBATINA -b -n -d master -o
> "C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out" -i
> "C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.sql" -Usa -P"
> 12:13:34 Process Exit Code: (1)
> 12:14:10 Error running script: replsys.sql (1)
> 12:14:10 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 6 -Service MSSQLSERVER
> 12:14:25 Process Exit Code: (0)
> 12:14:25 Action CleanUpInstall:
> 12:14:25 Installation Failed.
>|||Check what other processes are running on your server. I had the same
problem updating to SP4. I had to stop MOM services, Windows update
processes, and end anything else that may be using SQL. Then the SP4
install will go OK.
Mike
R wrote:
> Have you looked in this file?
> C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out
> R.
> "Radovan" <radovan@.servis24.hr> wrote in message
> news:OWXdLAeuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> > I can't install sp4. I got error :Error running script: replsys.sql (1)
> >
> > looking in sqllog, I have this lines (at the end of file).
> >
> > ...
> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\setup.iss returned: 0, 0
> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0, 0
> >
> > 12:13:12 SetFileSecuritySQLAndAdmin for returned: 3, 3
> >
> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0, 0
> >
> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlstp.log returned: 0, 0
> >
> > 12:13:12 SetFileSecPerfAndLogUsers for C:\Program Files\Microsoft SQL
> > Server\MSSQL\Binn\SQLCTR80.DLL returned: 1332, 1332
> >
> > 12:13:12 Begin ServPriv (MSSQLSERVER)
> >
> > 12:13:12 End ServPriv()
> >
> > 12:13:13 Upgrading databases
> >
> > 12:13:13 Upgrading databases on instance 'MSSQLSERVER'
> >
> > 12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> > 1 -Action 6 -Service MSSQLSERVER
> >
> > 12:13:13 Process Exit Code: (0)
> >
> > 12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> > 1 -Action 6 -Service MSSQLSERVER
> >
> > 12:13:13 Process Exit Code: (0)
> >
> > 12:13:13 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> > 1 -Action 1 -Service MSSQLSERVER -StartupOptions \-T4022 \-T4010 \-m
> >
> > 12:13:34 Process Exit Code: (0)
> >
> > 12:13:34 C:\SQL2KSP4\x86\BINN\osql.exe -Slpc:BOGIBATINA -b -n -d master -o
> > "C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out" -i
> > "C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.sql" -Usa -P"
> >
> > 12:13:34 Process Exit Code: (1)
> >
> > 12:14:10 Error running script: replsys.sql (1)
> >
> > 12:14:10 C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
> > 1 -Action 6 -Service MSSQLSERVER
> >
> > 12:14:25 Process Exit Code: (0)
> >
> > 12:14:25 Action CleanUpInstall:
> >
> > 12:14:25 Installation Failed.
> >
> >|||Thanks that helps.
Radovan
"inqbbus" <inqbbus@.gmail.com> wrote in message
news:1126820621.538655.83620@.g47g2000cwa.googlegroups.com...
> Check what other processes are running on your server. I had the same
> problem updating to SP4. I had to stop MOM services, Windows update
> processes, and end anything else that may be using SQL. Then the SP4
> install will go OK.
> Mike
> R wrote:
>> Have you looked in this file?
>> C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out
>> R.
>> "Radovan" <radovan@.servis24.hr> wrote in message
>> news:OWXdLAeuFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> > I can't install sp4. I got error :Error running script: replsys.sql (1)
>> >
>> > looking in sqllog, I have this lines (at the end of file).
>> >
>> > ...
>> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\setup.iss returned: 0,
>> > 0
>> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0,
>> > 0
>> >
>> > 12:13:12 SetFileSecuritySQLAndAdmin for returned: 3, 3
>> >
>> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlsp.log returned: 0,
>> > 0
>> >
>> > 12:13:12 SetFileSecuritySQLAndAdmin for C:\WINNT\sqlstp.log returned:
>> > 0, 0
>> >
>> > 12:13:12 SetFileSecPerfAndLogUsers for C:\Program Files\Microsoft SQL
>> > Server\MSSQL\Binn\SQLCTR80.DLL returned: 1332, 1332
>> >
>> > 12:13:12 Begin ServPriv (MSSQLSERVER)
>> >
>> > 12:13:12 End ServPriv()
>> >
>> > 12:13:13 Upgrading databases
>> >
>> > 12:13:13 Upgrading databases on instance 'MSSQLSERVER'
>> >
>> > 12:13:13
>> > C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
>> > 1 -Action 6 -Service MSSQLSERVER
>> >
>> > 12:13:13 Process Exit Code: (0)
>> >
>> > 12:13:13
>> > C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
>> > 1 -Action 6 -Service MSSQLSERVER
>> >
>> > 12:13:13 Process Exit Code: (0)
>> >
>> > 12:13:13
>> > C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
>> > 1 -Action 1 -Service MSSQLSERVER -StartupOptions \-T4022 \-T4010 \-m
>> >
>> > 12:13:34 Process Exit Code: (0)
>> >
>> > 12:13:34 C:\SQL2KSP4\x86\BINN\osql.exe -Slpc:BOGIBATINA -b -n -d
>> > master -o
>> > "C:\Program Files\Microsoft SQL Server\MSSQL\install\replsys.out" -i
>> > "C:\Program Files\Microsoft SQL
>> > Server\MSSQL\install\replsys.sql" -Usa -P"
>> >
>> > 12:13:34 Process Exit Code: (1)
>> >
>> > 12:14:10 Error running script: replsys.sql (1)
>> >
>> > 12:14:10
>> > C:\DOCUME~1\radovan\LOCALS~1\Temp\1\SqlSetup\Bin\scm.exe -Silent
>> > 1 -Action 6 -Service MSSQLSERVER
>> >
>> > 12:14:25 Process Exit Code: (0)
>> >
>> > 12:14:25 Action CleanUpInstall:
>> >
>> > 12:14:25 Installation Failed.
>> >
>> >
>

Friday, March 23, 2012

Problem with Index Tuning Wizard

Hi,
Before using the ITW, you should get a load of the queries
you are running. You can capture the events in profiler
and then use that load file for the ITW to use.
hth
DeeJay
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>
I know how to use the ITW. The problem is the tuning part. According to
the error-message, the ITW makes changes. What are these changes and why
are they interfering?
Robbert
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sql

Problem with Index Tuning Wizard

When running the Index Tuning Wizard (ITW) on our SQL Server we got a
error message : 'Could not complete cursor operation because the table
schema changed after the cursor was declared'
The ITW was used only to advice new indexes. I know that it creates
hypothetical indexes in the sysindexes table, but what are the changes
in the user-tables ?
How do you use the ITW without interfering with the real
schema-definitions ?
thanks,
RobbertHi,
Before using the ITW, you should get a load of the queries
you are running. You can capture the events in profiler
and then use that load file for the ITW to use.
hth
DeeJay
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>|||My theory on this is while you were running the ITW
someone in your tream made a change to the structure a
table, which caused it to get an error.
I have run ITW a number of times using our Production
Database, with no problems.
Peter
"We are all worms. But I believe that I am a glow-worm."
Winston Churchill
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>

Problem with Index Tuning Wizard

Hi,
Before using the ITW, you should get a load of the queries
you are running. You can capture the events in profiler
and then use that load file for the ITW to use.
hth
DeeJay
>--Original Message--
>When running the Index Tuning Wizard (ITW) on our SQL
Server we got a
>error message : 'Could not complete cursor operation
because the table
>schema changed after the cursor was declared'
>The ITW was used only to advice new indexes. I know that
it creates
>hypothetical indexes in the sysindexes table, but what
are the changes
>in the user-tables ?
>How do you use the ITW without interfering with the real
>schema-definitions ?
>thanks,
>Robbert
>.
>I know how to use the ITW. The problem is the tuning part. According to
the error-message, the ITW makes changes. What are these changes and why
are they interfering?
Robbert
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Wednesday, March 21, 2012

Problem with high Reproting Services 100% Usage

Currently I am running SQL Server 2005 and Reporting Services 2005 on
the same machine. When no reports are being run the CPU usage stays at
100% it is split between sql server and report services 10%/90%. If
anyone can help me, as this seems like some sort of bug with reporting
services, if there is a path I would greatly appreciate it.
Kind Regards
Nilesh ChauhanOn Mar 14, 6:05 am, nilesh.k.chau...@.googlemail.com wrote:
> Currently I am running SQL Server 2005 and Reporting Services 2005 on
> the same machine. When no reports are being run the CPU usage stays at
> 100% it is split between sql server and report services 10%/90%. If
> anyone can help me, as this seems like some sort of bug with reporting
> services, if there is a path I would greatly appreciate it.
> Kind Regards
> Nilesh Chauhan
I have worked w/the same setup recently without problems. Have you
tried rebooting the machine? That may resolve the problem. How long
have you been seeing this CPU usage?
Regards,
Enrique Martinez
Sr. SQL Server Developer

Tuesday, March 20, 2012

Problem with Form Authentication Example

I installed the Microsoft Forms Authentication in Report Services example in
a VPC running Windows 2003, Active Directory, SQl Server Enterprise and SQL
Reporting Servcie Enterprise Edition.
I registered user "john" and modified RSReportServer.config and added and
changed the value for <UserName> tag to be "john".
Stepping through the debugger the demo fails on GetWebResponse because
cookie is null.
I have tried FQDN in RSWebApplication.config but problem persist.
Any idea what is going wrong causing GetWebResponse to fail?I'm using a solution that's primarily based on the Forms Auth sample and I
can say that it does work. Double-check your web.config modifications for
the ReportServer and ReportManager webs according to the instructions in the
sample.
Adrian M.
"John Amason" <jamason@.ics-associates.com> wrote in message
news:uH%23y30Q$EHA.2580@.TK2MSFTNGP15.phx.gbl...
>I installed the Microsoft Forms Authentication in Report Services example
>in
> a VPC running Windows 2003, Active Directory, SQl Server Enterprise and
> SQL
> Reporting Servcie Enterprise Edition.
> I registered user "john" and modified RSReportServer.config and added and
> changed the value for <UserName> tag to be "john".
> Stepping through the debugger the demo fails on GetWebResponse because
> cookie is null.
> I have tried FQDN in RSWebApplication.config but problem persist.
> Any idea what is going wrong causing GetWebResponse to fail?
>

Monday, March 12, 2012

problem with expoting data to excel

I have a problem while exporting the crystal report to Excel sheet.
I am trying to display running total at the end of my report(In the report footer). It is displayed in several lines using a formula field. I have used appended chr(13) within the formula to break the values into different lines. But finally when I am exporting the report to excel all the lines are displayed in a single cell. What should I do to display them in multiple cells when exported to excel. Is there any ascii value which can be used instead of chr(13) , which will help me print the values in different cellsIf you use chr(13), it will be exported to the same cell. Instead use seperate formula to have the value and place it next to first formula

Problem with ExpireExecutionLogEntries

Can anyone tell me what I could/should do to stop this stored proc from
running. It has been running all day and consuming resources. I've tried
killing the SPID and it just returns. We've stopped and started the SQL
Server service and its there here. Absent rebooting is there anything else
we should be doing. Also, can someone shed some light on why this rogue
process has started. We've been running Reporting Services SP1 for months
now and RS overall since early last year. This is the first problem we've
had. We currently going to SP2. Does SP2 address this issue.
Regards,
Eric FrayerThere must be a SQL job running this procedure. I very much doubt this
has anything to do with RS.
To try and locate the job, in query analyser logon to the msdb database
and run the following script;
SELECT j.job_id, j.name, j.description,
s.step_id, s.step_name, s.command
FROM sysjobsteps s
JOIN sysjobs j ON s.job_id = j.job_id
WHERE s.command LIKE '%MyProcName%'
Then you can investigate whatever the job is and what to do about it.
Chris
EFrayer@.community.nospam wrote:
> Can anyone tell me what I could/should do to stop this stored proc
> from running. It has been running all day and consuming resources.
> I've tried killing the SPID and it just returns. We've stopped and
> started the SQL Server service and its there here. Absent rebooting
> is there anything else we should be doing. Also, can someone shed
> some light on why this rogue process has started. We've been running
> Reporting Services SP1 for months now and RS overall since early last
> year. This is the first problem we've had. We currently going to
> SP2. Does SP2 address this issue. Regards,
> Eric Frayer|||Hi Chris,
Thanks for the reply. We stopped and started the SQL Service on the server
hosting the Reporting Services db's and this seemed to clear up the problem.
Regards,
Eric
"Chris McGuigan" wrote:
> There must be a SQL job running this procedure. I very much doubt this
> has anything to do with RS.
> To try and locate the job, in query analyser logon to the msdb database
> and run the following script;
> SELECT j.job_id, j.name, j.description,
> s.step_id, s.step_name, s.command
> FROM sysjobsteps s
> JOIN sysjobs j ON s.job_id = j.job_id
> WHERE s.command LIKE '%MyProcName%'
> Then you can investigate whatever the job is and what to do about it.
> Chris
>
> EFrayer@.community.nospam wrote:
> > Can anyone tell me what I could/should do to stop this stored proc
> > from running. It has been running all day and consuming resources.
> > I've tried killing the SPID and it just returns. We've stopped and
> > started the SQL Server service and its there here. Absent rebooting
> > is there anything else we should be doing. Also, can someone shed
> > some light on why this rogue process has started. We've been running
> > Reporting Services SP1 for months now and RS overall since early last
> > year. This is the first problem we've had. We currently going to
> > SP2. Does SP2 address this issue. Regards,
> > Eric Frayer
>

Problem With Excel Import in Execute Phase

Hi All

I have a SSIS package running and tested fine on my desktop. However when I deploy the package to my server I get the error given below.

SSIS package "CR Sec Watch List 2.dtsx" starting.

Information: 0x4001100A at Transform Master Files: Starting distributed transaction for this container.

Information: 0x4004300A at Transform Master Files, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Transform Master Files, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Transform Master Files, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Transform Master Files, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

Error: 0xC0208265 at Transform Master Files, Excel Source [1]: Failed to retrieve long data for column "NKoreaPoi".

Error: 0xC020901C at Transform Master Files, Excel Source [1]: There was an error with output "Excel Source Output" (9) on component "Excel Source" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output "Excel Source Output" (9)" failed because error code 0xC0209071 occurred, and the error row disposition on "component "Excel Source" (1)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error: 0xC0047038 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at Transform Master Files, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Transform Master Files, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Transform Master Files, DTS.Pipeline: "component "SQL Server Destination" (20)" wrote 0 rows.

Task failed: Transform Master Files

Information: 0x4001100C at Transform Master Files: Aborting the current distributed transaction.

Warning: 0x80019002 at Process Master Files: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "CR Sec Watch List 2.dtsx" finished: Success

For some reason it tells me that it not able to retrive the long data for a column. The column mentioned in the error contains special characters.

Once again, there seems to be no problem with the package as the package runs fine on my desktop.

Any help or clues as to why this is happening on my server is greatly appreciated.

Regards

Ryan

Jet Engine for Excel guesses data type of a column. Please see threads on TypeGuessRows and IMEX to understand how Jet works, and its limitations, and how to tune it to work for you.

Setting TypeGuessRows to 0 would force Jet to scan all rows in the Excel to guess the type. You may also need to set the Defalut Type to Text.

Using Imex=1 in your Connection string is essential to get mixed types to default to Text.

Friday, March 9, 2012

Problem with DTSX

I am trying to execute an DTSX for Exporting data from SQL 2005 to Access Database. From SQL Wizard, the Export process is running fine.

When I tried to execute the same using an Stored Procedure (
XP_CMDShell 'dtexec /F E:\Shared\Export\Export_Fame_Entity.dtsx') it is getting executed for long time.. but never gets completed and thus, not giving any error also.

Help required.........

regards,
Ravi K
Mascon Global Limited

In SQL Server 2005 the xp_cmdshell is disabled in new installations. This may be your problem. The Books Online topic, Surface Area Configuration for Features, rovides more information about enabling xp_cmdshell.

Also it looks like your command line is missing quotes. Try using "E:\Shared\Export\Export_Fame_Entity.dtsx"
I ran a package successfully from the Query window in SQL Server Management Studio using the following syntax
xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'
The SQL Server 2005 Books Online topic, dtexec Utility, provides a wealth of information about running packages by using dtexec.

Marianne
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem with dtsrun

I am trying to execute a DTS Package from within a stored procedure, however the Execute statement hangs while running the debugger.

The sql is EXECUTE @.SP_Return = master.dbo.xp_cmdshell 'DTSRun /E /SBIGO /NPFW Data Load', NO_OUTPUT

The debugger hangs, SQL Query Analyser does not respond after exiting the debugger and MSSQLSERVER needs to be stopped & restarted.

The DTS Packages runs fine from the cmd prompt as can be seen below

C:\Documents and Settings\Greg>DTSRun /E /SBIGO /NPFW Data Load
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data] Step
DTSRun OnProgress: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data]
Step; 500 Rows have been transformed or copied.; PercentComple te = 0; ProgressCount = 500
DTSRun OnFinish: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Mon
thly_Load_Data] Step
DTSRun: Package execution complete.

C:\Documents and Settings\Greg>

Any help would be great.

Are debugging procedure in QA?

If so comment the dts package during debug there may be disconnect from DTSRun utility and debugger might be not able to handle situation properly...

Wednesday, March 7, 2012

Problem with different version of SQL Server

Dear All,
I have a server that is running SQL Server 6.5 and SQl Server 7.0.
Then I install an instance of SQL Server 2000 and run the SQL Server
Upgrade wizard. But the database selection screen, some of the SQL
Server 6.5 databases are not listed, so I canceled the SQL Server
upgrade wizard.
I need to make SQL Server 6.5 databases are listed in the wizard. So
What should I do?
Thanks
Robert Lie
Hi
Does the user that is logged in have permissions to the databases?
What databases are missing? The upgrade wizard will not upgrade System
databases.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" wrote:

> Dear All,
> I have a server that is running SQL Server 6.5 and SQl Server 7.0.
> Then I install an instance of SQL Server 2000 and run the SQL Server
> Upgrade wizard. But the database selection screen, some of the SQL
> Server 6.5 databases are not listed, so I canceled the SQL Server
> upgrade wizard.
> I need to make SQL Server 6.5 databases are listed in the wizard. So
> What should I do?
> Thanks
> Robert Lie
>
|||I agree with Mike, it is probably a permsission problem... You will only see
the databases you have permissions for... Log in as a system admin and try
again.
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
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1117431463.159386.73310@.g49g2000cwa.googlegro ups.com...
> Dear All,
> I have a server that is running SQL Server 6.5 and SQl Server 7.0.
> Then I install an instance of SQL Server 2000 and run the SQL Server
> Upgrade wizard. But the database selection screen, some of the SQL
> Server 6.5 databases are not listed, so I canceled the SQL Server
> upgrade wizard.
> I need to make SQL Server 6.5 databases are listed in the wizard. So
> What should I do?
> Thanks
> Robert Lie
>
|||No, I have a full access since i'm the administrator.
Should I run MS Server-Switch application then rerun the SQL Server
upgrade wizard?
Pls some advice.
Thanks
Wayne Snyder wrote:
> I agree with Mike, it is probably a permsission problem... You will only see
> the databases you have permissions for... Log in as a system admin and try
> again.
>

Problem with different version of SQL Server

Dear All,
I have a server that is running SQL Server 6.5 and SQl Server 7.0.
Then I install an instance of SQL Server 2000 and run the SQL Server
Upgrade wizard. But the database selection screen, some of the SQL
Server 6.5 databases are not listed, so I canceled the SQL Server
upgrade wizard.
I need to make SQL Server 6.5 databases are listed in the wizard. So
What should I do?
Thanks
Robert LieHi
Does the user that is logged in have permissions to the databases?
What databases are missing? The upgrade wizard will not upgrade System
databases.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" wrote:

> Dear All,
> I have a server that is running SQL Server 6.5 and SQl Server 7.0.
> Then I install an instance of SQL Server 2000 and run the SQL Server
> Upgrade wizard. But the database selection screen, some of the SQL
> Server 6.5 databases are not listed, so I canceled the SQL Server
> upgrade wizard.
> I need to make SQL Server 6.5 databases are listed in the wizard. So
> What should I do?
> Thanks
> Robert Lie
>|||I agree with Mike, it is probably a permsission problem... You will only see
the databases you have permissions for... Log in as a system admin and try
again.
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
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1117431463.159386.73310@.g49g2000cwa.googlegroups.com...
> Dear All,
> I have a server that is running SQL Server 6.5 and SQl Server 7.0.
> Then I install an instance of SQL Server 2000 and run the SQL Server
> Upgrade wizard. But the database selection screen, some of the SQL
> Server 6.5 databases are not listed, so I canceled the SQL Server
> upgrade wizard.
> I need to make SQL Server 6.5 databases are listed in the wizard. So
> What should I do?
> Thanks
> Robert Lie
>|||No, I have a full access since i'm the administrator.
Should I run MS Server-Switch application then rerun the SQL Server
upgrade wizard?
Pls some advice.
Thanks
Wayne Snyder wrote:
> I agree with Mike, it is probably a permsission problem... You will only s
ee
> the databases you have permissions for... Log in as a system admin and try
> again.
>

Friday, March 30, 2012

Problem with long running report timing out

Problem with Linked Server

Problem with Linked Server

Monday, March 26, 2012

Problem with Integrity Check

Problem with installing SQL Server

Problem with installing sp4

Problem with installing sp4

Problem with installing sp4

Friday, March 23, 2012

Problem with Index Tuning Wizard

Problem with Index Tuning Wizard

Problem with Index Tuning Wizard

Wednesday, March 21, 2012

Problem with high Reproting Services 100% Usage

Tuesday, March 20, 2012

Problem with Form Authentication Example

Monday, March 12, 2012

problem with expoting data to excel

Problem with ExpireExecutionLogEntries

Problem With Excel Import in Execute Phase

Friday, March 9, 2012

Problem with DTSX

Problem with dtsrun

Wednesday, March 7, 2012

Problem with different version of SQL Server

Problem with different version of SQL Server

db datatypes

Blog Archive

About Me