connect from ASP app: working

Showing posts with label working. Show all posts

Thursday, March 29, 2012

Can't get mirroring working for all DBs?

I'm getting the following errors when trying to set up mirroring for a DB.
[PRINCIPAL]
Date 3/2/2007 2:52:06 PM
Log SQL Server (Current - 3/5/2007 8:44:00 AM)
Source spid99
Message
Communications to the remote server instance 'TCP://SQL2:5022' failed before
database mirroring was fully started. The ALTER DATABASE command failed.
Retry the command when the remote database is started.
Date 3/2/2007 2:52:06 PM
Log SQL Server (Current - 3/5/2007 8:44:00 AM)
Source spid99
Message
Error: 1413, Severity: 16, State: 1.
Date 3/2/2007 2:52:06 PM
Log SQL Server (Current - 3/5/2007 8:44:00 AM)
Source spid37s
Message
The mirroring connection to "TCP://SQL2:5022" has timed out for database
"DB1" after 10 seconds without a response. Check the service and network
connections.
Date 3/2/2007 2:52:06 PM
Log SQL Server (Current - 3/5/2007 8:44:00 AM)
Source spid37s
Message
Error: 1479, Severity: 16, State: 1.
[MIRROR]
Date 3/2/2007 2:52:49 PM
Log SQL Server (Current - 3/5/2007 8:46:00 AM)
Source spid35s
Message
The mirroring connection to "TCP://SQL1:5022" has timed out for database
"DB1" after 5 seconds without a response. Check the service and network
connections.
Date 3/2/2007 2:52:49 PM
Log SQL Server (Current - 3/5/2007 8:46:00 AM)
Source spid35s
Message
Error: 1479, Severity: 16, State: 1.
This only occurs for this particular DB. I have mirroring running
successfully for 4 other DBs. The only difference I can see is that the
problematic DB is significantly larger than the other mirrored DBs... it's
~70GB.
I'm running SQL 2005 Enterprise x64 for both principal and mirror SQL
instances.
Please help...interesting. cant say I've seen this before but trying running consistancy
check DBCC CHECKDB (refer to BOL) perhaps a corruption in the db is causing
the timeout.
M.
"slin" <slin@.discussions.microsoft.com> wrote in message
news:E52523C6-0B5C-4507-B6FA-9439EFD1C02E@.microsoft.com...
> I'm getting the following errors when trying to set up mirroring for a DB.
> [PRINCIPAL]
> Date 3/2/2007 2:52:06 PM
> Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> Source spid99
> Message
> Communications to the remote server instance 'TCP://SQL2:5022' failed
> before
> database mirroring was fully started. The ALTER DATABASE command failed.
> Retry the command when the remote database is started.
> Date 3/2/2007 2:52:06 PM
> Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> Source spid99
> Message
> Error: 1413, Severity: 16, State: 1.
> Date 3/2/2007 2:52:06 PM
> Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> Source spid37s
> Message
> The mirroring connection to "TCP://SQL2:5022" has timed out for database
> "DB1" after 10 seconds without a response. Check the service and network
> connections.
> Date 3/2/2007 2:52:06 PM
> Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> Source spid37s
> Message
> Error: 1479, Severity: 16, State: 1.
>
> [MIRROR]
> Date 3/2/2007 2:52:49 PM
> Log SQL Server (Current - 3/5/2007 8:46:00 AM)
> Source spid35s
> Message
> The mirroring connection to "TCP://SQL1:5022" has timed out for database
> "DB1" after 5 seconds without a response. Check the service and network
> connections.
>
> Date 3/2/2007 2:52:49 PM
> Log SQL Server (Current - 3/5/2007 8:46:00 AM)
> Source spid35s
> Message
> Error: 1479, Severity: 16, State: 1.
>
> This only occurs for this particular DB. I have mirroring running
> successfully for 4 other DBs. The only difference I can see is that the
> problematic DB is significantly larger than the other mirrored DBs... it's
> ~70GB.
> I'm running SQL 2005 Enterprise x64 for both principal and mirror SQL
> instances.
> Please help...|||Already did a DBCC CHECKDB... No errors. I'm using Profiler to see if it'll
show me what's failing, but not having any luck so far...
-S
"Mark Broadbent" wrote:
> interesting. cant say I've seen this before but trying running consistancy
> check DBCC CHECKDB (refer to BOL) perhaps a corruption in the db is causing
> the timeout.
> M.
>
> "slin" <slin@.discussions.microsoft.com> wrote in message
> news:E52523C6-0B5C-4507-B6FA-9439EFD1C02E@.microsoft.com...
> > I'm getting the following errors when trying to set up mirroring for a DB.
> >
> > [PRINCIPAL]
> >
> > Date 3/2/2007 2:52:06 PM
> > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > Source spid99
> > Message
> > Communications to the remote server instance 'TCP://SQL2:5022' failed
> > before
> > database mirroring was fully started. The ALTER DATABASE command failed.
> > Retry the command when the remote database is started.
> >
> > Date 3/2/2007 2:52:06 PM
> > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > Source spid99
> > Message
> > Error: 1413, Severity: 16, State: 1.
> >
> > Date 3/2/2007 2:52:06 PM
> > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > Source spid37s
> > Message
> > The mirroring connection to "TCP://SQL2:5022" has timed out for database
> > "DB1" after 10 seconds without a response. Check the service and network
> > connections.
> >
> > Date 3/2/2007 2:52:06 PM
> > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > Source spid37s
> > Message
> > Error: 1479, Severity: 16, State: 1.
> >
> >
> > [MIRROR]
> >
> > Date 3/2/2007 2:52:49 PM
> > Log SQL Server (Current - 3/5/2007 8:46:00 AM)
> > Source spid35s
> > Message
> > The mirroring connection to "TCP://SQL1:5022" has timed out for database
> > "DB1" after 5 seconds without a response. Check the service and network
> > connections.
> >
> >
> > Date 3/2/2007 2:52:49 PM
> > Log SQL Server (Current - 3/5/2007 8:46:00 AM)
> > Source spid35s
> > Message
> > Error: 1479, Severity: 16, State: 1.
> >
> >
> > This only occurs for this particular DB. I have mirroring running
> > successfully for 4 other DBs. The only difference I can see is that the
> > problematic DB is significantly larger than the other mirrored DBs... it's
> > ~70GB.
> >
> > I'm running SQL 2005 Enterprise x64 for both principal and mirror SQL
> > instances.
> >
> > Please help...
>
>|||Well... I finally got it working. It seemed like the large physical size of
the transaction log file was causing the problem. The log file was 200GB,
but it was only 1% used. I shrunk the log file size back down to 1GB and did
the whole restore/backup process and mirroring started right up.
"slin" wrote:
> Already did a DBCC CHECKDB... No errors. I'm using Profiler to see if it'll
> show me what's failing, but not having any luck so far...
> -S
> "Mark Broadbent" wrote:
> > interesting. cant say I've seen this before but trying running consistancy
> > check DBCC CHECKDB (refer to BOL) perhaps a corruption in the db is causing
> > the timeout.
> >
> > M.
> >
> >
> > "slin" <slin@.discussions.microsoft.com> wrote in message
> > news:E52523C6-0B5C-4507-B6FA-9439EFD1C02E@.microsoft.com...
> > > I'm getting the following errors when trying to set up mirroring for a DB.
> > >
> > > [PRINCIPAL]
> > >
> > > Date 3/2/2007 2:52:06 PM
> > > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > > Source spid99
> > > Message
> > > Communications to the remote server instance 'TCP://SQL2:5022' failed
> > > before
> > > database mirroring was fully started. The ALTER DATABASE command failed.
> > > Retry the command when the remote database is started.
> > >
> > > Date 3/2/2007 2:52:06 PM
> > > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > > Source spid99
> > > Message
> > > Error: 1413, Severity: 16, State: 1.
> > >
> > > Date 3/2/2007 2:52:06 PM
> > > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > > Source spid37s
> > > Message
> > > The mirroring connection to "TCP://SQL2:5022" has timed out for database
> > > "DB1" after 10 seconds without a response. Check the service and network
> > > connections.
> > >
> > > Date 3/2/2007 2:52:06 PM
> > > Log SQL Server (Current - 3/5/2007 8:44:00 AM)
> > > Source spid37s
> > > Message
> > > Error: 1479, Severity: 16, State: 1.
> > >
> > >
> > > [MIRROR]
> > >
> > > Date 3/2/2007 2:52:49 PM
> > > Log SQL Server (Current - 3/5/2007 8:46:00 AM)
> > > Source spid35s
> > > Message
> > > The mirroring connection to "TCP://SQL1:5022" has timed out for database
> > > "DB1" after 5 seconds without a response. Check the service and network
> > > connections.
> > >
> > >
> > > Date 3/2/2007 2:52:49 PM
> > > Log SQL Server (Current - 3/5/2007 8:46:00 AM)
> > > Source spid35s
> > > Message
> > > Error: 1479, Severity: 16, State: 1.
> > >
> > >
> > > This only occurs for this particular DB. I have mirroring running
> > > successfully for 4 other DBs. The only difference I can see is that the
> > > problematic DB is significantly larger than the other mirrored DBs... it's
> > > ~70GB.
> > >
> > > I'm running SQL 2005 Enterprise x64 for both principal and mirror SQL
> > > instances.
> > >
> > > Please help...
> >
> >
> >

Tuesday, March 27, 2012

Can't get Dynamic Security working ...

I'm trying to get dynamic security working using a member property which contains a list of usernames that are allowed to access that level of the hierarchy. I'm using the following setup:

Dimension: DynManager

Hierarchy: DynManager

Levels (in order): SVP, DVP, RVP, ROM, DDO, Store

Attributes: SVP -> SVP Access List, DVP -> DVP Access List, RVP -> RVP Access List, ... etc

When I run the following test query, I get the set back that I expect:

SELECT FILTER([DynManager].[DDO].Members, INSTR([DynManager].CurrentMember.Properties("DDO Access List"), "JJester")) ON 0 FROM BiCube

However, when I attempt to use the same FILTER expression in the role's "Allowed Member Set" expression, I get the following error message:

An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (1, 56) The 'DynManager' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

The DynManager dimension only has one hierarchy, so I don't understand where the error is coming from. However, I did what it says and changed my query to the following:

FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester"))

The syntax checks out but when I go to browse the cube as this dynamic role, I get the following error message:

The DynManager hierarchy is used more than once in the Crossjoin function

Can anyone please provide some insight!? Thank you!

Jeff

What is the attribute that you are setting this as AllowedSet on ? Is it DDO attribute ? If it is not DDO attribute, then the error can be because of it, but if it is, then the error doesn't look right. Are you on SP2 of AS ? I vaguely recall a fix in this area...

|||

Hi Mosha! Thanks for the quick reply. I just upgraded to SP2 this morning hoping it would clear up some issues ... but no luck.

I'm setting the "AllowedSet" on the SVP, DVP, RVP, ROM, and DDO attributes. All other attributes are set to "Select all members".

Thanks again,
Jeff

|||The expression that you provided above should only be set on DDO attribute. Please remove it from all other attributes.|||

My filter expression in the AllowedSet is different for each level of the hierarchy.

For DDO, it's:

FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester"))

For ROM, it's:

FILTER([DynManager].[ROM].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("ROM Access List"), "JJester"))

For RVP it's:

FILTER([DynManager].[RVP].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("RVP Access List"), "JJester"))

and on and on ....

Do you have any idea why my test query would work in SQL Server Management Studio, but then I get the "more than one hierarchy" error when I try to apply the same FILTER expression in the AllowedSet?

|||

Ok, I think I figured out the issue. The parameters in my filter expression are too specific (if that's even possible). I tried removing the Dimension name and it works. So, the new expression looks like this:

FILTER([DDO].Members, INSTR([DDO].CurrentMember.Properties("DDO Access List"), "JJester"))

Anyhow, thanks for your help Mosha!

sql

Thursday, March 22, 2012

Can't find AdventureWorks in order to connect

I am working with SQL Server 2005 Express installed on my workstation under
XP SP2.
I am trying to work through the online tutorial. I am on
http://msdn2.microsoft.com/en-us/library/ms170681.aspx
I downloaded the AdventureWorksDB.msi and installed it. The files
AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
I am now trying to register the AdventureWorks server. Everything looks as
described in the tutorial until I get to the Connection Properties in Step 5.
There are no options for me to select. There are no databases other than
the initial system databases (no User Databases) when I try to browse.
Did I do something wrong? How do I locate the database so I can register it?Ensure you attached the AdventureWorks database to your SQL Server instance
which you choose in the New Server Registration window as the Server Name
editbox (e.g. EKREM-PC\test2)
If the AdventureWorks database is attached to your SQL Server instance and
if you type this SQL Server instance' s name in the Server Name editbox in
the New Server Registration, then you should be seeing the AdventureWorks
database in the combobox in Connection Properties tab in the New Server
Registration window.
--
Ekrem Ã?nsoy
"epignosis" <epignosis@.discussions.microsoft.com> wrote in message
news:C6DBA92F-A5A1-4333-993C-46E08CE8FCF8@.microsoft.com...
>I am working with SQL Server 2005 Express installed on my workstation under
> XP SP2.
> I am trying to work through the online tutorial. I am on
> http://msdn2.microsoft.com/en-us/library/ms170681.aspx
> I downloaded the AdventureWorksDB.msi and installed it. The files
> AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
> File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
> I am now trying to register the AdventureWorks server. Everything looks
> as
> described in the tutorial until I get to the Connection Properties in Step
> 5.
> There are no options for me to select. There are no databases other than
> the initial system databases (no User Databases) when I try to browse.
> Did I do something wrong? How do I locate the database so I can register
> it?
>
>|||"Installing" from the MSI just extracts the database files. You then need
to run CREATE DATABASE ... FOR ATTACH. See Books Online for exact syntax.
"epignosis" <epignosis@.discussions.microsoft.com> wrote in message
news:C6DBA92F-A5A1-4333-993C-46E08CE8FCF8@.microsoft.com...
>I am working with SQL Server 2005 Express installed on my workstation under
> XP SP2.
> I am trying to work through the online tutorial. I am on
> http://msdn2.microsoft.com/en-us/library/ms170681.aspx
> I downloaded the AdventureWorksDB.msi and installed it. The files
> AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
> File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
> I am now trying to register the AdventureWorks server. Everything looks
> as
> described in the tutorial until I get to the Connection Properties in Step
> 5.
> There are no options for me to select. There are no databases other than
> the initial system databases (no User Databases) when I try to browse.
> Did I do something wrong? How do I locate the database so I can register
> it?
>
>

Can't find AdventureWorks in order to connect

I am working with SQL Server 2005 Express installed on my workstation under
XP SP2.
I am trying to work through the online tutorial. I am on
http://msdn2.microsoft.com/en-us/library/ms170681.aspx
I downloaded the AdventureWorksDB.msi and installed it. The files
AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
I am now trying to register the AdventureWorks server. Everything looks as
described in the tutorial until I get to the Connection Properties in Step 5.
There are no options for me to select. There are no databases other than
the initial system databases (no User Databases) when I try to browse.
Did I do something wrong? How do I locate the database so I can register it?
Ensure you attached the AdventureWorks database to your SQL Server instance
which you choose in the New Server Registration window as the Server Name
editbox (e.g. EKREM-PC\test2)
If the AdventureWorks database is attached to your SQL Server instance and
if you type this SQL Server instance' s name in the Server Name editbox in
the New Server Registration, then you should be seeing the AdventureWorks
database in the combobox in Connection Properties tab in the New Server
Registration window.
Ekrem ?nsoy
"epignosis" <epignosis@.discussions.microsoft.com> wrote in message
news:C6DBA92F-A5A1-4333-993C-46E08CE8FCF8@.microsoft.com...
>I am working with SQL Server 2005 Express installed on my workstation under
> XP SP2.
> I am trying to work through the online tutorial. I am on
> http://msdn2.microsoft.com/en-us/library/ms170681.aspx
> I downloaded the AdventureWorksDB.msi and installed it. The files
> AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
> File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
> I am now trying to register the AdventureWorks server. Everything looks
> as
> described in the tutorial until I get to the Connection Properties in Step
> 5.
> There are no options for me to select. There are no databases other than
> the initial system databases (no User Databases) when I try to browse.
> Did I do something wrong? How do I locate the database so I can register
> it?
>
>
|||"Installing" from the MSI just extracts the database files. You then need
to run CREATE DATABASE ... FOR ATTACH. See Books Online for exact syntax.
"epignosis" <epignosis@.discussions.microsoft.com> wrote in message
news:C6DBA92F-A5A1-4333-993C-46E08CE8FCF8@.microsoft.com...
>I am working with SQL Server 2005 Express installed on my workstation under
> XP SP2.
> I am trying to work through the online tutorial. I am on
> http://msdn2.microsoft.com/en-us/library/ms170681.aspx
> I downloaded the AdventureWorksDB.msi and installed it. The files
> AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
> File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
> I am now trying to register the AdventureWorks server. Everything looks
> as
> described in the tutorial until I get to the Connection Properties in Step
> 5.
> There are no options for me to select. There are no databases other than
> the initial system databases (no User Databases) when I try to browse.
> Did I do something wrong? How do I locate the database so I can register
> it?
>
>
sql

Can't find AdventureWorks in order to connect

I am working with SQL Server 2005 Express installed on my workstation under
XP SP2.
I am trying to work through the online tutorial. I am on
http://msdn2.microsoft.com/en-us/library/ms170681.aspx
I downloaded the AdventureWorksDB.msi and installed it. The files
AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
I am now trying to register the AdventureWorks server. Everything looks as
described in the tutorial until I get to the Connection Properties in Step 5
.
There are no options for me to select. There are no databases other than
the initial system databases (no User Databases) when I try to browse.
Did I do something wrong? How do I locate the database so I can register it
?Ensure you attached the AdventureWorks database to your SQL Server instance
which you choose in the New Server Registration window as the Server Name
editbox (e.g. EKREM-PC\test2)
If the AdventureWorks database is attached to your SQL Server instance and
if you type this SQL Server instance' s name in the Server Name editbox in
the New Server Registration, then you should be seeing the AdventureWorks
database in the combobox in Connection Properties tab in the New Server
Registration window.
Ekrem ?nsoy
"epignosis" <epignosis@.discussions.microsoft.com> wrote in message
news:C6DBA92F-A5A1-4333-993C-46E08CE8FCF8@.microsoft.com...
>I am working with SQL Server 2005 Express installed on my workstation under
> XP SP2.
> I am trying to work through the online tutorial. I am on
> http://msdn2.microsoft.com/en-us/library/ms170681.aspx
> I downloaded the AdventureWorksDB.msi and installed it. The files
> AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
> File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
> I am now trying to register the AdventureWorks server. Everything looks
> as
> described in the tutorial until I get to the Connection Properties in Step
> 5.
> There are no options for me to select. There are no databases other than
> the initial system databases (no User Databases) when I try to browse.
> Did I do something wrong? How do I locate the database so I can register
> it?
>
>|||"Installing" from the MSI just extracts the database files. You then need
to run CREATE DATABASE ... FOR ATTACH. See Books Online for exact syntax.
"epignosis" <epignosis@.discussions.microsoft.com> wrote in message
news:C6DBA92F-A5A1-4333-993C-46E08CE8FCF8@.microsoft.com...
>I am working with SQL Server 2005 Express installed on my workstation under
> XP SP2.
> I am trying to work through the online tutorial. I am on
> http://msdn2.microsoft.com/en-us/library/ms170681.aspx
> I downloaded the AdventureWorksDB.msi and installed it. The files
> AdventureWorks_Data and AdventureWorks_Log are in my C:\Program
> File\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory.
> I am now trying to register the AdventureWorks server. Everything looks
> as
> described in the tutorial until I get to the Connection Properties in Step
> 5.
> There are no options for me to select. There are no databases other than
> the initial system databases (no User Databases) when I try to browse.
> Did I do something wrong? How do I locate the database so I can register
> it?
>
>

Tuesday, March 20, 2012

Can't Excute SSIS package

So everything was working great. Now I have a few problems. The little play button is no longer available for me to puch and when I try a right click execute package a get a "Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)". Does anyone know how to fix this? Also when I try and copy tasks from within my package I get a "An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)"
followed by "Could not copy object 'package name' to the clipboard.
(Microsoft.DataTransformationServices.Design)" and then "Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)"

Does anyone what might be wrong

Thanks in advance

good news is I have the same problem, bad news is I don't know why :s

If that can help, the details are

Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)

Emplacement du programme :

|||This seems to be a VS problem. I reinstalled Visual Studio and that fixed the problem. Well... at least it works now ;)
|||Yeah, my machine ran automatic updates and all of a sudden it came right.

Can't Excute SSIS package

Does anyone what might be wrong

Thanks in advance

good news is I have the same problem, bad news is I don't know why :s

If that can help, the details are

Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)

Emplacement du programme :

at Microsoft.DataWarehouse.ProgrammingByContract.State.NullCheck(Object obj)
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)|||This seems to be a VS problem. I reinstalled Visual Studio and that fixed the problem. Well... at least it works now ;)|||Yeah, my machine ran automatic updates and all of a sudden it came right.

Can't drop procedure ... Snapshot won't run... Help!

Hi,
I have a server set up for Merge replication. It is the publisher and the
distributer. Has been working fine.
It has two publications.
I wanted the subscribers to get a new copy of the data next time they
replicate, so I went to the first publication, which is stored procs and
view, went to Status tab of the publication properties, and clicked the run
agent now. When it was done, I right clicked on the publication and selected
reinitialize subscriptions.
Then I went to the publication for tables. Right clicked and selected
Re-initialize subscritions. Then I went to the status tab and selected run
agent now. When it came back with a timestamp, red x's appeared on the
replication monitor on the snap shot agents.
The error message it give is :
Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
because it is being used for replication.
As a last resort, I tried to delete the publication, I was going to re do
it. When I try to delete it
it gives me the same error message as above, but with a different sp_sel_
name.
What should I do, or what did I do. I really need it back to working today.
.. .
Any help,
Thanks,
Steve
Well, here is what I did.
I kept trying to delete the publication that I could not get the snapshot
agent to run on, and kept getting the error above. So I delete the stored
proc publication, it went OK. Then I deleted the table publication, and this
time it let me. Then I went to each of the clients, deleted both
subscriptions, and re-created them. Ran fine then, and am able to do manual
pull replications from the subscriber boxes now.
If any one can help me understand what happened, please do. How did I get
in that mess, and what would have been a good way to get out. I was lucky
that I could get to all subscriber boxes and re-create the subscriptions.
Thanks,
Steve
"SteveInBeloit" wrote:

> Hi,
> I have a server set up for Merge replication. It is the publisher and the
> distributer. Has been working fine.
> It has two publications.
> I wanted the subscribers to get a new copy of the data next time they
> replicate, so I went to the first publication, which is stored procs and
> view, went to Status tab of the publication properties, and clicked the run
> agent now. When it was done, I right clicked on the publication and selected
> reinitialize subscriptions.
> Then I went to the publication for tables. Right clicked and selected
> Re-initialize subscritions. Then I went to the status tab and selected run
> agent now. When it came back with a timestamp, red x's appeared on the
> replication monitor on the snap shot agents.
> The error message it give is :
> Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
> because it is being used for replication.
> As a last resort, I tried to delete the publication, I was going to re do
> it. When I try to delete it
> it gives me the same error message as above, but with a different sp_sel_
> name.
> What should I do, or what did I do. I really need it back to working today.
> . .
> Any help,
> Thanks,
> Steve
>
sql

Sunday, March 11, 2012

Can't deploy a cube project to the Analysis Server using BIDS

I am working through the Microsoft SQL Server 2005 Analysis Services Step by Step book and I can't deploy the first cube project to my Analysis Server from my workstation.

I have a server running Windows 2000 Server SP4 and a laptop running Windows 2000 Pro SP4. On the server I am running SQL Server 2005 SP2 complete installation, including workstation components. On my laptop I am running SQL Server 2005 SP2 workstation components.

I have used the Cube Wizard to create a cube without a Data Source (as described in Chapter 3) and then used the Schema Generation Wizard to create the schema which I then populated with data.

When I go to Deploy the cube using BIDS I see the status bar message Build started... and then Deploy started... and then I get an error that says The connection either timed out or was lost.

If I perform the same actions on the server so that BIDS is running on the server and all connections are to localhost the deploy works and I can then browse the cube.

From my laptop I can see all the AS objects on the server through SSMS, including the AS database I created from the server.

Thinking that there maybe some incompatability between the software on my laptop and the server, I did a clean install of the workstation components to a new machine, created a new project and I still get the same error.

I have followed all the advice from Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/cisql2005ascs.mspx) but I have not found any solution yet. I also tried increasing the connection and query timeout properties in BIDS (Tools/Options/BI Designers/AS Designers) - that didn't work either.

Really stuck now and any help would be greatly appreciated.

TIA
Caryl Wills
Hi,

U need to test the connectivity. Once u pass this test, u will be able to deploy the cube. On the deployment configuration window, make sure u properly give the name of the Server where u want to deploy. Else create the script file and execute it from the Management Studio..

Hope this will help

--Imran|||Hi Imran,
Thanks for your suggestions. I am trying to test connectivity and now seem to have a problem creating an Analysis Services database from my client computer. In SSMS Object Explorer I have the Analysis Services server. If I right click the 'Databases' node and select 'New Database' and then fill in the dialog with a unique database name and Impersonation set to 'Use the service account' and click OK the progress wheel just goes forever. Restarting the Analysis Services service is required to stop the process.

How do I trace the process and do you have any idea what might be preventing it from working?

Thanks for your help.
Caryl
|||Thanks for your help folks - all problems now solved by shutting down ZoneAlarm on server. For some reason ZoneAlarm will not allow connections to Analysis Services over my LAN even though the firewall is turned OFF for the trusted zone.

Can't deliver reports in Web Archive format

I can't deliver reports in web archive format using subscriptions.
If I choose to send only the link it's working.
I used to have SP1 for Reporting Services but I was forced to unintall it
because I am using Romanian lang for my reports and all reports with date
fields where not working when I try to export them to PDF.
Any ideas ?
Sorin Sandu
RomaniaI forgot my error
"Failure sending mail: An error has occurred during report processing" on
the subscriptions page.
"spp" <spp@.nospam.nospam> a scris în mesajul de
ºtiri:%23mJdsxpOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>I can't deliver reports in web archive format using subscriptions.
> If I choose to send only the link it's working.
> I used to have SP1 for Reporting Services but I was forced to unintall it
> because I am using Romanian lang for my reports and all reports with date
> fields where not working when I try to export them to PDF.
> Any ideas ?
> Sorin Sandu
> Romania
>|||I'm having the same problem - is there a log file somewhere I can check? I
checked event viewer, and also looked at the reportserver's tables in SQL but
I didn't find anything around the time of my test email
"spp" wrote:
> I forgot my error
> "Failure sending mail: An error has occurred during report processing" on
> the subscriptions page.
> "spp" <spp@.nospam.nospam> a scris Ã®n mesajul de
> Âºtiri:%23mJdsxpOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> >I can't deliver reports in web archive format using subscriptions.
> > If I choose to send only the link it's working.
> > I used to have SP1 for Reporting Services but I was forced to unintall it
> > because I am using Romanian lang for my reports and all reports with date
> > fields where not working when I try to export them to PDF.
> > Any ideas ?
> > Sorin Sandu
> > Romania
> >
>
>

Can't delete SQL 2005 user from SQL 2000 database

I have a SQL 2000 database from another system. The database has user
IIS_WPG.
I restore a backup of this to a SQL 2005 database (working on a migration to
SQL 2005). Since IIS_WPG is not a Security Login on SQL 2005, when I go to
delete the database user IIS_WPG, it fails with "Drop failed for user
IIS_WPG".
How can I delete this user from the database instance?
Thanks. AmilThe fact that the DB came from SQL 2000 originally ought to be
irrelevant. As soon as you restore it on the SQL 2005 server it becomes
a SQL 2005 database (albeit in 8.0 compatibility mode but that's also
not important). You should be able to drop any users of the DB (except
dbo, which is a special user) with a simple DROP USER
<http://msdn2.microsoft.com/en-us/library/ms189438.aspx> statement in
T-SQL. For example:
use MyDB;
drop user IIS_WPG;
If this doesn't work can you please post the exactly error message that
SQL Server raises. Also posting the result of:
use MyDB;
select current_user;
select
principal_id,
cast(name as varchar(50)) as name,
type_desc
from sys.database_principals
where is_fixed_role = 0;
might help troubleshooting.
*mike hodgson*
http://sqlnerd.blogspot.com
Amil Hanish wrote:

>I have a SQL 2000 database from another system. The database has user
>IIS_WPG.
>I restore a backup of this to a SQL 2005 database (working on a migration t
o
>SQL 2005). Since IIS_WPG is not a Security Login on SQL 2005, when I go to
>delete the database user IIS_WPG, it fails with "Drop failed for user
>IIS_WPG".
>How can I delete this user from the database instance?
>Thanks. Amil
>
>|||Mike, that didn't work.
The iis_wpg user doesn't appear in the Security | Logins so I don't know why
it's complaining. Here is the query info.
use mydb;
drop user iis_wpg
Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
=======================
use mydb;
select current_user;
select
principal_id,
cast(name as varchar(50)) as name,
type_desc
from sys.database_principals
where is_fixed_role = 0;
0 public DATABASE_ROLE
1 dbo WINDOWS_USER
2 guest SQL_USER
3 INFORMATION_SCHEMA SQL_USER
4 sys SQL_USER
5 ASPNET WINDOWS_USER
6 IIS_WPG WINDOWS_GROUP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:ufZs8F%23cGHA.3888@.TK2MSFTNGP02.phx.gbl...
> The fact that the DB came from SQL 2000 originally ought to be
> irrelevant. As soon as you restore it on the SQL 2005 server it becomes
> a SQL 2005 database (albeit in 8.0 compatibility mode but that's also
> not important). You should be able to drop any users of the DB (except
> dbo, which is a special user) with a simple DROP USER
> <http://msdn2.microsoft.com/en-us/library/ms189438.aspx> statement in
> T-SQL. For example:
> use MyDB;
> drop user IIS_WPG;
> If this doesn't work can you please post the exactly error message that
> SQL Server raises. Also posting the result of:
> use MyDB;
> select current_user;
> select
> principal_id,
> cast(name as varchar(50)) as name,
> type_desc
> from sys.database_principals
> where is_fixed_role = 0;
> might help troubleshooting.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Amil Hanish wrote:
>
>|||Ah. Now we're getting somewhere. The IIS_WPG user owns a schema! OK,
you have 2 options: 1) transfer the ownership of the schema(s) owned by
IIS_WPG to a different user in the database or 2) drop the schema(s)
owned by IIS_WPG.
First you need to know which schema(s) IIS_WPG owns (obviously this
should all be done in the context of the database in question):
use myDB;
select s.* from sys.schemas as s
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'IIS_WPG';
Next you should check to see if there are any objects within that/those
schema(s):
select s.[name] as schemaname, o.[name] as objectname, o.type_desc
from sys.objects as o
inner join sys.schemas as s on s.schema_id = o.schema_id
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'IIS_WPG'
order by s.[name], o.type_desc, o.[name];
If there any no objects in the schema then you may as well just drop the
schema and be done with it:
drop schema foo;
where foo is the name of the schema. (Do this for each schema you want
to drop.) However, if the schema contains objects (views, tables,
procs, functions, etc.) that you wish to keep then you should either
transfer them to another schema (so you can drop their original schema,
which is owned by IIS_WPG) with the ALTER SCHEMA statement or transfer
ownership of the schema(s) that IIS_WPG currently owns. Transferring
ownership is easier and less likely to break code referencing those
objects. You transfer ownership with the ALTER AUTHORIZATION statement:
alter authorization on schema::foo to MyOtherDBUser;
where foo is the name of the schema you're transferring ownership of and
MyOtherDBUser is another user in the DB to which you wish to transfer
ownership (the dbo user is always a potential candidate). You'd do this
for each schema owned by IIS_WPG.
After all that the IIS_WPG user shouldn't own any schemas any more and
you should be able to drop the user with a DROP USER statement:
drop user IIS_WPG;
This is all documented in BOL if you need any more info about any of
these individual statements (including the sys.objects, sys.schemas &
sys.database_principals catalog views).
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Amil Hanish wrote:

>Mike, that didn't work.
>The iis_wpg user doesn't appear in the Security | Logins so I don't know wh
y
>it's complaining. Here is the query info.
>use mydb;
>drop user iis_wpg
>Msg 15138, Level 16, State 1, Line 2
>The database principal owns a schema in the database, and cannot be dropped
.
>=======================
>use mydb;
> select current_user;
> select
> principal_id,
> cast(name as varchar(50)) as name,
> type_desc
> from sys.database_principals
> where is_fixed_role = 0;
>0 public DATABASE_ROLE
>1 dbo WINDOWS_USER
>2 guest SQL_USER
>3 INFORMATION_SCHEMA SQL_USER
>4 sys SQL_USER
>5 ASPNET WINDOWS_USER
>6 IIS_WPG WINDOWS_GROUP
>
>"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
>news:ufZs8F%23cGHA.3888@.TK2MSFTNGP02.phx.gbl...
>
>
>|||Mike...much thanks! All your info worked like a charm and without issues.
Since the schema had no object, I was able to simply delete. I'll save your
help in case I need it in the future.
Amil
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:%233VIaFIdGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Ah. Now we're getting somewhere. The IIS_WPG user owns a schema! OK,
> you have 2 options: 1) transfer the ownership of the schema(s) owned by
> IIS_WPG to a different user in the database or 2) drop the schema(s)
> owned by IIS_WPG.
> First you need to know which schema(s) IIS_WPG owns (obviously this
> should all be done in the context of the database in question):
> use myDB;
> select s.* from sys.schemas as s
> inner join sys.database_principals as dp on dp.principal_id =
> s.principal_id
> where dp.[name] = 'IIS_WPG';
> Next you should check to see if there are any objects within that/those
> schema(s):
> select s.[name] as schemaname, o.[name] as objectname, o.type_d
esc
> from sys.objects as o
> inner join sys.schemas as s on s.schema_id = o.schema_id
> inner join sys.database_principals as dp on dp.principal_id =
> s.principal_id
> where dp.[name] = 'IIS_WPG'
> order by s.[name], o.type_desc, o.[name];
> If there any no objects in the schema then you may as well just drop the
> schema and be done with it:
> drop schema foo;
> where foo is the name of the schema. (Do this for each schema you want
> to drop.) However, if the schema contains objects (views, tables,
> procs, functions, etc.) that you wish to keep then you should either
> transfer them to another schema (so you can drop their original schema,
> which is owned by IIS_WPG) with the ALTER SCHEMA statement or transfer
> ownership of the schema(s) that IIS_WPG currently owns. Transferring
> ownership is easier and less likely to break code referencing those
> objects. You transfer ownership with the ALTER AUTHORIZATION statement:
> alter authorization on schema::foo to MyOtherDBUser;
> where foo is the name of the schema you're transferring ownership of and
> MyOtherDBUser is another user in the DB to which you wish to transfer
> ownership (the dbo user is always a potential candidate). You'd do this
> for each schema owned by IIS_WPG.
> After all that the IIS_WPG user shouldn't own any schemas any more and
> you should be able to drop the user with a DROP USER statement:
> drop user IIS_WPG;
> This is all documented in BOL if you need any more info about any of
> these individual statements (including the sys.objects, sys.schemas &
> sys.database_principals catalog views).
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Amil Hanish wrote:
>
>

Can''t Delete Maintenance Report Files (txt) in SQL/2005

I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

did you create a maintenance clean up task and specify the .txt file extension to delete old text files ? but xp_delete_file is corresponding T_SQL of the cleanup task............if you hevent created the cleanup task just try it out and get back

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||Same for me, anyone from MS like to comment on this?|||

There is a support article on the subject, http://support.microsoft.com/kb/938085. The problem appears to be the first line of text in the report files. If it says "NEW COMPONENT OUTPUT", then xp_delete_file will not delete it. The stated workaround is to remove this text. That does work, but it's probably simpler to just manually delete the file. Hope a fix comes out soon.

Can''t Delete Maintenance Report Files (txt) in SQL/2005

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||Same for me, anyone from MS like to comment on this?|||

Can''t Delete Maintenance Report Files (txt) in SQL/2005

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||

Same for me, anyone from MS like to comment on this?|||

Can''t Delete Maintenance Report Files (txt) in SQL/2005

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||Same for me, anyone from MS like to comment on this?|||

Saturday, February 25, 2012

Can't copy a database

I want to make a working copy of a database to a new name on the same server. I use the wizard giving the same name for source and destination, but giving the db copy a new name. I get an error that says "One or m ore arguments are invalid."

But no hint as to what is wrong.

Is it that I can't copy on the same machine?

You want to do a 'backup database' and 'restore database' to create a new database based on the old database.

Here is a script that you can use.

use master
go
declare @.db1 sysname,@.db2 sysname,
@.name1 sysname, @.name2 sysname,
@.file1 sysname,@.file2 sysname,
@.sql nvarchar(1000)

set @.db1='Northwind'
set @.db2='NewBD'

set @.sql='select @.name1=name,@.file1=filename from '+@.db1+'..sysfiles where fileid=1'
exec sp_executesql @.sql,N'@.name1 sysname out,@.file1 sysname out',@.name1 out,@.file1 out

set @.sql='select @.name2=name,@.file2=filename from '+@.db1+'..sysfiles where fileid=2'
exec sp_executesql @.sql,N'@.name2 sysname out,@.file2 sysname out',@.name2 out,@.file2 out

select @.name1=ltrim(rtrim(@.name1)),
@.name2=ltrim(rtrim(@.name2)),
@.file1=substring(@.file1,1,(len(@.file1)-charindex('\',reverse(rtrim(@.file1)))))+'\'+@.db2+'.mdf',
@.file2=substring(@.file2,1,(len(@.file2)-charindex('\',reverse(rtrim(@.file2)))))+'\'+@.db2+'.ldf'

set @.sql='backup database '+@.db1+' to disk=''c:\db1.tmp'' with init'
exec master..sp_executesql @.sql

set @.sql='restore database '+@.db2+' from disk=''c:\db1.tmp''
with replace,
move '+quotename(@.name1,char(39))+' to '+quotename(@.file1,char(39))+',
move '+quotename(@.name2,char(39))+' to '+quotename(@.file2,char(39))+',
recovery'

exec master..sp_executesql @.sql|||

I will give this a try. Thanks!

(But one wonders why simple stuff like this can't be handled by the GUI.)

Sunday, February 19, 2012

Cant connect to SQL server at work from home project

HI All,

i have copied a project from work i am working on and have created the same project at home in visual studio.net - everything is the same as it is at my work - for some reason though i cannot connect to the servers from my home project

can anyone help me with this - i get the message "SQL Server does not exist or access denied"

my web config has exactly the same set up with a key in appSettings as

<add key="My_Connection" value="User ID=myID;Password=myPassword;Initial Catalog=myCatalog;Data Source=xx.xx.xx.xx" />

can anyone tell me why i would not be able to even see my work database from home?

thanks in advance

GilesbAre you trying to point to the databases that are at work or new ones that you have created at home?

If they are at home have you checked to ensure that all the correct logins have been recreated?

If you are pointing to databases at work then it is likely that you can't access from outside of the work network.|||Few things u need to check.
1) Is there a firewall protecting your "Work" LAN? If yes, ask your system administrator to open the SQL port in your firewall and be sure to ask your system administrator to set a rules to restrict for your IP address or connecting by VPN.
2) You ve to configure your connection through Client utility. Specify a server name and External network IP address. That will direct your application to call external IP address.|||Sounds like someone where you work might actually know what they're doing. You shouldn't be able to connect unless connected by VPN. If you can, then you have bigger problems then the fact you copied your work project to a home computer. If you have permissions to work from home, have the network admins grant you remote user access and set you up to use the VPN. If not, either work more hours at work, or explain to your bosses why you can't work from home.|||Thanks for your help guys but i dont think i would need a VPN to merely access a database just to "login" as it were - basically i have an asp.net project that runs fine when testing at work - servers are at work, but when i run it from home i cant get a connection - should be just like running the site from home and hooking in to the database right ?

I am fairly new to the database scene - will ask someone at wotk whether any special configuration is required

thanks once again to all that replied

Gilesb|||Okie, yes and at the same time a resounding no!

If your home computer were part of the work network then yes, it would be exactly the same but it is not. If you VPN in to the work network then yes, you would be part of the same network.

To put a sql server onto your network with open ports to the real world is asking for trouble (yeah, there are probably ways of making sure it is mostly secure, but only mostly...). I'd estimate 90% of SQL Servers are safely hidden behind firewalls to prevent anything other then the machines on the internal network connecting to it.

Thursday, March 29, 2012

Can't get mirroring working for all DBs?

Can't get mirroring working for all DBs?

Can't get mirroring working for all DBs?

Tuesday, March 27, 2012

Can't get Dynamic Security working ...

Thursday, March 22, 2012

Can't find AdventureWorks in order to connect

Can't find AdventureWorks in order to connect

Can't find AdventureWorks in order to connect

Tuesday, March 20, 2012

Can't Excute SSIS package

Can't Excute SSIS package

Can't drop procedure ... Snapshot won't run... Help!

Sunday, March 11, 2012

Can't deploy a cube project to the Analysis Server using BIDS

Can't deliver reports in Web Archive format

Can't delete SQL 2005 user from SQL 2000 database

Can''t Delete Maintenance Report Files (txt) in SQL/2005

Can''t Delete Maintenance Report Files (txt) in SQL/2005

Can''t Delete Maintenance Report Files (txt) in SQL/2005

Can''t Delete Maintenance Report Files (txt) in SQL/2005

Saturday, February 25, 2012

Can't copy a database

Sunday, February 19, 2012

Cant connect to SQL server at work from home project

connect from ASP app

Blog Archive

About Me