error: 40 with sql server 2000

Hi

I am getting this really annoying error that I cant resolve.

I have created a website that accesses a SQL SERVER 2000 database and it works perfectly on my home machine. The error has occurred when I have transferred it to my hosts server.

This is the error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This is my connection string:

<add key="ConnectionString" value="Provider=sqloledb;Data Source=hostsserver****,1433;Initial

Catalog=db******;User Id=dbo*******;Password=********; "/>

It says I am trying to connect to a SQL SERVER 2000 database as this is what I devloped the site on so I don not know why it thinks I am trying to connect to SQL SERVER 2005.

Can anyone help?

Moving to the Data Access forum.|||

Is this constant or intermittent error?

1) Make sure no typo on the data source (server name)
2) Hostsserver,1433, implys it will use TCP, so make sure TCP is enabled on the sql server. Keep in mind firewall can block the traffic, so put 1433 into exception.

Did you install SQL server 2005 and/or SNAC on your client machine? Client stack does not know whether the target sql server is 2000 or 2005. 2005 is always reported, You can ignore it.

Thanks.

|||

Hi
Thanks for replying.
I have double checked the datasource and that seems right.

How can I check if the host server is TCP enabled?

I have never installed/used 2005 so dont know why.

any help would be really appreciated.

|||On you server machine, Start->All Programs->Microsoft SQL Server -->SQL Server Server Utility. Add TCP to your enabled protocols and restart sql server.sql

Error: 0xC001000E - connection not found

Hello,

Does anyone know what this error means and how to resolve it? I get this error when I attempt to run a Data Flow Task. The Data Flow Task has one subcomponent, a Flat File Source. I have set up the connection manager to access the flat file, which is on a local directory. I have tried everything to fix this. What could possibly be the problem?

Thanks

Error: 0xC001000E at : The connection "{B9E11DD7-21A1-4FD7-986F-6BDC5B81F51D}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

K108 wrote:

Hello,

Does anyone know what this error means and how to resolve it? I get this error when I attempt to run a Data Flow Task. The Data Flow Task has one subcomponent, a Flat File Source. I have set up the connection manager to access the flat file, which is on a local directory. I have tried everything to fix this. What could possibly be the problem?

Thanks

Error: 0xC001000E at : The connection "{B9E11DD7-21A1-4FD7-986F-6BDC5B81F51D}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Go in and edit the flat file source - make sure it is pointing at the correct connection manager.

Out of interest, what is the point of a data-flow with only one component in it?

-Jamie

|||

I am just trying to test for now, that is why there is only one component. Why add more components when I can't even get the one to work correctly.

Anyways I resolved the problem... this error was occuring on a DTS migrated pkg. Apparently there are some ghost connections that are persisting, so by starting the pkg from scratch the problem has disappeared. This is my theory anyways.

|||

K108 wrote:

I am just trying to test for now, that is why there is only one component. Why add more components when I can't even get the one to work correctly.

Ah gotcha. That makes sense.

K108 wrote:

Anyways I resolved the problem... this error was occuring on a DTS migrated pkg. Apparently there are some ghost connections that are persisting, so by starting the pkg from scratch the problem has disappeared. This is my theory anyways.

This error most commonly occurs when you copy tasks/components from one package to another. I know that's not exactly what you've done but its not a huge jump from it. I guess that must be something to do with it.

Glad its fixed anyway.

-Jamie

|||

Yes, the problem (after much testing on my part) ALWAYS crops up when I am trying to run components from a migrated DTS package.

In this case, I have an EXECUTE SQL task, for which I've defined a NEW connection and deleted the old connection completely, and it still throws this error about the connection not existing, even though the sql runs correctly in the database.

I have found that the only solution to this problem is to start the package from scratch, and use the EXECUTE DTS 2000 PACKAGE object instead, instead of trying to run it from the migrated package itself.

Error: [rsInvalidReportParameterDependency]

How should I resolve the following error?
ERROR:
[rsInvalidReportParameterDependency] The report parameter â'Companyâ' has a
DefaultValue or a ValidValue that depends on the report parameter â'Companyâ'.
Forward dependencies are not valid.It would seem that you need to fetch company in a separate query and use its
results to pass values to the second query.
"Terry" wrote:
> How should I resolve the following error?
>
> ERROR:
> [rsInvalidReportParameterDependency] The report parameter â'Companyâ' has a
> DefaultValue or a ValidValue that depends on the report parameter â'Companyâ'.
> Forward dependencies are not valid.
>|||Could you please provide me with an example?
"William" wrote:
> It would seem that you need to fetch company in a separate query and use its
> results to pass values to the second query.
> "Terry" wrote:
> > How should I resolve the following error?
> >
> >
> > ERROR:
> >
> > [rsInvalidReportParameterDependency] The report parameter â'Companyâ' has a
> > DefaultValue or a ValidValue that depends on the report parameter â'Companyâ'.
> > Forward dependencies are not valid.
> >
> >

Error with schedule a DTS package...

Hi experts...

I'm trying to schedule a DTS package (import some tables from Mysql database) but there is
an error and I don't know how to resolve it.

The error is always the same...
"The job failed. The Job was invoked by Schedule 24 (Import RT data). The last step to run was step 1 (Import RT data)."

I have tried changing the all the parameters in the job properties, but I always obtein the same message.

The DTS package works fine, I can execute and it works, the problem is the schedule...

Thanks in advanced...What account is the scheduled task running under? Normally if you run it yourself and it works, you are running the DTS package with YOUR permissions. However, if you run the DTS package under a schedule, it is using the standard SQL Server account (which is either Local System or another specified account). You need to change the account the schedule runs under to have enough permissions to run properly.|||Thanks for your reply...

I have one more question... How can i change the account of the schedule task?... I don't know how can I change it.

The strange thing is that I've done the same with another databases, only one thing is different, the source, the schedules that work fine are between SQL databases and this is from Mysql to sql database... but this is not the problem becouse as I said I can execute it and it works fine.

thanks again.|||If you schedule a job in SQL Server, then on the Job Properties dialog window, click the Steps tab. Highlight the step (here, executing the DTS package job) that you want, and click Edit or properties. In the Job Step properties dialog window, click the Advanced tab, and there is a Run as user dropdownlist. Try changing that to your credentials.|||Hi Again,

There is a problem with this solution... the step type is CmdExec and you can't see this option unless you choose SQL Script type... I don't understand what's happening, I have read the documentation about Package Scheduling and Security Issues but I don't know how to change who invoked the job...

thanks again...|||That's an operating-system command or executable program command step. I'm not familiar with that. Is there any way to change the security context in the DTS package designer?|||Hi again...

The step is an operating system command... but as I said this job is similar to other... the only I can see related to the security context is the owner, and it's the same in all jobs that I have done (and they work fine).

thanks for your interest.|||Aha, I found this from databasejournal.com (link

You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs.|||Thanks a lot, but I haven't resolved the problem, I have seen this choice, and I put an administration user, but It didn't work... I don't know...

Sorry about your lost time.|||An administrative user for the box, or an administrative user on the domain? Domain permissions could be the problem.

You could also try posting to the Microsoft newsgroups, specifically the sqlserver.dts one:

http://communities.microsoft.com/newsgroups/default.asp

Then look for sqlserver.dts in the left frame after it downloads (it takes a while).

Error with membersip and roles Cannot resolve the collation conflict between

I'm getting the following errors when trying to execute the following script on the server, its part of the standard asp.net membership and roles, anybody have any ideas how I get get round this?

Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.

/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIEROFFGOIFNOT EXISTS (SELECT *FROM sys.objectsWHEREobject_id =OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]')AND typein (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]@.ApplicationName nvarchar(256),@.UserNames nvarchar(4000),@.RoleNames nvarchar(4000)ASBEGINDECLARE @.AppId uniqueidentifierSELECT @.AppId = NULLSELECT @.AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@.ApplicationName) = LoweredApplicationNameIF (@.AppId IS NULL)RETURN(2)DECLARE @.TranStarted bitSET @.TranStarted = 0IF( @.@.TRANCOUNT = 0 )BEGINBEGIN TRANSACTIONSET @.TranStarted = 1ENDDECLARE @.tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)DECLARE @.tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)DECLARE @.tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)DECLARE @.Num intDECLARE @.Pos intDECLARE @.NextPos intDECLARE @.Name nvarchar(256)DECLARE @.CountAll intDECLARE @.CountU intDECLARE @.CountR intSET @.Num = 0SET @.Pos = 1WHILE(@.Pos <= LEN(@.RoleNames))BEGINSELECT @.NextPos = CHARINDEX(N'','', @.RoleNames, @.Pos)IF (@.NextPos = 0 OR @.NextPos IS NULL)SELECT @.NextPos = LEN(@.RoleNames) + 1SELECT @.Name = RTRIM(LTRIM(SUBSTRING(@.RoleNames, @.Pos, @.NextPos - @.Pos)))SELECT @.Pos = @.NextPos+1INSERT INTO @.tbNames VALUES (@.Name)SET @.Num = @.Num + 1ENDINSERT INTO @.tbRoles SELECT RoleId FROM dbo.aspnet_Roles ar, @.tbNames t WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @.AppIdSELECT @.CountR = @.@.ROWCOUNTIF (@.CountR <> @.Num)BEGINSELECT TOP 1 N'''', NameFROM @.tbNamesWHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @.tbRoles r WHERE r.RoleId = ar.RoleId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(2)ENDDELETE FROM @.tbNames WHERE 1=1SET @.Num = 0SET @.Pos = 1WHILE(@.Pos <= LEN(@.UserNames))BEGINSELECT @.NextPos = CHARINDEX(N'','', @.UserNames, @.Pos)IF (@.NextPos = 0 OR @.NextPos IS NULL)SELECT @.NextPos = LEN(@.UserNames) + 1SELECT @.Name = RTRIM(LTRIM(SUBSTRING(@.UserNames, @.Pos, @.NextPos - @.Pos)))SELECT @.Pos = @.NextPos+1INSERT INTO @.tbNames VALUES (@.Name)SET @.Num = @.Num + 1ENDINSERT INTO @.tbUsers SELECT UserId FROM dbo.aspnet_Users ar, @.tbNames t WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @.AppIdSELECT @.CountU = @.@.ROWCOUNTIF (@.CountU <> @.Num)BEGINSELECT TOP 1 Name, N''''FROM @.tbNamesWHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @.tbUsers u WHERE u.UserId = au.UserId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(1)ENDSELECT @.CountAll = COUNT(*)FROMdbo.aspnet_UsersInRoles ur, @.tbUsers u, @.tbRoles rWHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleIdIF (@.CountAll <> @.CountU * @.CountR)BEGINSELECT TOP 1 UserName, RoleNameFROM @.tbUsers tu, @.tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles rWHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(3)ENDDELETE FROM dbo.aspnet_UsersInRolesWHERE UserId IN (SELECT UserId FROM @.tbUsers) AND RoleId IN (SELECT RoleId FROM @.tbRoles)IF( @.TranStarted = 1 )COMMIT TRANSACTIONRETURN(0)END 'ENDGO

Any help appreciated thanks, 

What is your database collation set to?

You can check this in MicrosoftSQL Server Management by right clicking the database and selecting "properties".

Now check what collation you have on your aspnet tables. Right click on aspnet_Roles, and select "Script table as","Create to","New query window". Look for the collation settings on each field.

|||

Thank you for your response on the DB I have - Latin1_General_CI_AS

On the table I have -

****** Object:Table [dbo].[aspnet_Roles] Script Date: 05/22/2007 21:28:48 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE TABLE [dbo].[aspnet_Roles]([ApplicationId] [uniqueidentifier]NOT NULL,[RoleId] [uniqueidentifier]NOT NULLCONSTRAINT [DF__aspnet_Ro__RoleI__03F0984C]DEFAULT (newid()),[RoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[LoweredRoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,CONSTRAINT [PK__aspnet_Roles__02084FDA]PRIMARY KEY NONCLUSTERED ([RoleId]ASC)WITH (PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY])ON [PRIMARY]GOALTER TABLE [dbo].[aspnet_Roles]WITH CHECK ADD CONSTRAINT [FK__aspnet_Ro__Appli__02FC7413]FOREIGN KEY([ApplicationId])REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])GOALTER TABLE [dbo].[aspnet_Roles]CHECK CONSTRAINT [FK__aspnet_Ro__Appli__02FC7413]

So what do I do to fix it?|||

Any body tell me how i can change the collation?

|||

ALTER DATABASE {DatabaseName} COLLATE {NewCollationName}

In this case ALTER DATABASE ASPNETDB COLLATE SQL_Latin1_General_CP1_CI_AS

|||

That worked, thank you.