Can't change the name of a OLE DB Source component name

Hi,

I've created an application that create package with a dataflow. The package with the dataflow and the ole db Source was created succesfully but for some reason the name of the Ole DB Source dosen't change programmitcally It's stays with the default name "OLE DB Source". This is very problematic because I want to add another Ole Db source and the package won't load because there will be two components with the same name.

Thanks.

This is the Code, It's from msdn exmples:

Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Module Module1

Sub Main()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
New Microsoft.SqlServer.Dts.Runtime.Package()
Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Add an OLEDB connection manager to the package.
Dim cm As ConnectionManager = package.Connections.Add("OLEDB")
cm.Name = "OLEDB ConnectionManager"
cm.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;"

' Add an OLE DB source to the data flow.
Dim component As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection.New()
component.Name = "AdventureWorks;Product"
component.ComponentClassID = "DTSAdapter.OleDbSource.1"
' You can also use the CLSID of the component instead of the PROGID.
'component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";

' Get the design time instance of the component.
Dim instance As CManagedComponentWrapper = component.Instantiate()

' Initialize the component.
instance.ProvideComponentProperties()

' Specify the connection manager.
If component.RuntimeConnectionCollection.Count > 0 Then
component.RuntimeConnectionCollection(0).ConnectionManager = _
DtsConvert.ToConnectionManager90(package.Connections(0))
End If

' Set the custom properties.
instance.SetComponentProperty("AccessMode", 2)
instance.SetComponentProperty("SqlCommand", _
"Select * from Production.Product")

' Reinitialize the metadata.
instance.AcquireConnections(vbNull)
instance.ReinitializeMetaData()
instance.ReleaseConnections()

' Add other components to the data flow and connect them.

End Sub

End Module

If I remember correctly, the Name should be set after the call to

instance.ProvideComponentProperties();

|||Thanks It works

Cant attach database

I've created a database inside my ASP.net project, the database is in the App_Data folder as exepcted.

I can't run the aspnet_regsql until I have attached it using SQL Server Management Studio, but when I try to attach the database it doesn't let me navigate through my folders properly.

The database is in C:\Users\Wardie\Documents\FirstProject\App_Data but SQL SMS only lets me navigate to the folder C:\Users\Wardie and no further for some reason, meaning I can't attach the database and can't use the aspnet_regsql service to create the membership tables.

Does anyone know how to fix this?

I had this problem and discovered that SQL Server Management Studio doesnt navigate long directory paths off windows user directories like "My Documents". I ended up creating all my web projects in a sub directory off C:\ something like C:\My Websites\Web1\App_Data\MyDatabase.MDF will work fine. Sorry, but as far as I'm aware you'll have to move your project directory to a shorter path off the root directory.

|||

I guess i'll just have to do the same then, cheers smc.

A bit of a stupid thing though isn't that really?

|||

I agree. There's probably a logical reason... maybe its to do with virtual paths that are used for user "My Directory" folders not being supported. I.e. the "My Documnets" directory is actually something like "C:\Documents and Settings\UserName\My Documents"

Please mark as answered if I was helpful in someway to you.

cant aquire a managed connection, SSIS

HI I am creating an integration service and have created a connection
manager. I tested it and the connection passed. I then wend to the data
flow source and added a data reader source but get the error , cannot aquire
a managed connection from the runtime connection manager. I am trying to
create a simple package that will read data from a database using a query and
then write it out to an excel file. I am familiar with the old 2000 DTS
package development.
thanks.
--
Paul G
Software engineer.Paul,
I'm in the same boat and ended up having to go buy the Microsoft Press SSIS
book. I'm still going through it.
You probably will have to do the same as SSIS help seems to be limited in
here.
Good luck,
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:722EC193-D7B7-4B20-AB4F-1303F4AB625C@.microsoft.com...
> HI I am creating an integration service and have created a connection
> manager. I tested it and the connection passed. I then wend to the data
> flow source and added a data reader source but get the error , cannot
> aquire
> a managed connection from the runtime connection manager. I am trying to
> create a simple package that will read data from a database using a query
> and
> then write it out to an excel file. I am familiar with the old 2000 DTS
> package development.
> thanks.
> --
> Paul G
> Software engineer.|||Hi Jay I was able to get it working. I now have an Integrated Services
packages that moves data from one database to another and if successful
updates a table in the original database. Also if successful I have it write
out in a message box using vbscript. Also if it fails I have it wright out
an error message in a message box.
If I remember correctly I selected the connection from the properties of the
dataflow task. Also on the connection I used the ADO.net. In addition I
changed the authentication mode to mixed (this is in the properties of the
database). Hope this helps.
--
Paul G
Software engineer.
"Jay" wrote:
> Paul,
> I'm in the same boat and ended up having to go buy the Microsoft Press SSIS
> book. I'm still going through it.
> You probably will have to do the same as SSIS help seems to be limited in
> here.
> Good luck,
> Jay
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:722EC193-D7B7-4B20-AB4F-1303F4AB625C@.microsoft.com...
> > HI I am creating an integration service and have created a connection
> > manager. I tested it and the connection passed. I then wend to the data
> > flow source and added a data reader source but get the error , cannot
> > aquire
> > a managed connection from the runtime connection manager. I am trying to
> > create a simple package that will read data from a database using a query
> > and
> > then write it out to an excel file. I am familiar with the old 2000 DTS
> > package development.
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>

Can't apply transaction logs after restoring a database in enterprise manager

Hi!
I created Full Backup of database and transaction logs in Enterprise manager
.. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.
Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.

Can't apply transaction logs after restoring a database in enterprise manager

Hi!
I created Full Backup of database and transaction logs in Enterprise manager
. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.

cant add sql endpoint to my visual studio project. trying "add as web reference" does

i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.

create endpoint testEndpoint authorization [FINTAN-PC\Fintan]
state=STARTED
as http
(
path ='/sql',
authentication=(INTEGRATED),
ports = (CLEAR),
site='SERVER'
)
For SOAP
(
webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE='adventureworks',
NAMESPACE='http://thenamespace.org'
);
GO

the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?

I'm a little unclear on your mentioning of using Visual Studio to look for this SOAP endpoint. Are you referring to using UDDI?

Regarding "add as web reference" in Visual Studio, the method that has worked best for me is to type in the HTTP URL that points to the WSDL document.

In this scenario it will be: http://SERVER/sql?wsdl

This URL comes from:

site='SERVER'

path='/sql'

So, if the endpoint was configured with:

site='foobar'

path='/myOwnPath/subPath'

then the WSDL document URL will be http://foobar/myOwnPath/subPath?wsdl

One easy way to ensure the user account running Visual Studio has access to retrieve the WSDL document is to use a Web Browser (such as IE) and point to the WSDL document URL.

HTH,

Jimmy|||ok, tiny bit of progress made. when i use the http://sitename/path?wsdl , rather than getting a 404 error, i get a page cannot be displayed. this is in IE7. any ideas?

cant add sql endpoint to my visual studio project. trying "add as web reference" does

i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.

create endpoint testEndpoint authorization [FINTAN-PC\Fintan]
state=STARTED
as http
(
path ='/sql',
authentication=(INTEGRATED),
ports = (CLEAR),
site='SERVER'
)
For SOAP
(
webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE='adventureworks',
NAMESPACE='http://thenamespace.org'
);
GO

the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?

I'm a little unclear on your mentioning of using Visual Studio to look for this SOAP endpoint. Are you referring to using UDDI?

Regarding "add as web reference" in Visual Studio, the method that has worked best for me is to type in the HTTP URL that points to the WSDL document.

In this scenario it will be: http://SERVER/sql?wsdl

This URL comes from:

site='SERVER'

path='/sql'

So, if the endpoint was configured with:

site='foobar'

path='/myOwnPath/subPath'

then the WSDL document URL will be http://foobar/myOwnPath/subPath?wsdl

One easy way to ensure the user account running Visual Studio has access to retrieve the WSDL document is to use a Web Browser (such as IE) and point to the WSDL document URL.

HTH,

Jimmy|||ok, tiny bit of progress made. when i use the http://sitename/path?wsdl , rather than getting a 404 error, i get a page cannot be displayed. this is in IE7. any ideas?

can''t add new Stored Procedure item in Database project

I'm trying to create a CLR stored procedure and went ahead and created a Database Project. But when I click on New Item... it doesn't contain "Stored Procedure" as an item, just script items. How do I add this template? At first I was thinking well maybe it knows my SQL Server doesn't have CLR turned on, so I went ahead and turned that on but still doesn't show up. Any ideas?

Thanks,

Craig

You have probably chosen wrong project type. The project type to create SQLCLR "stuff" is under either C# or the VB node and it is called (IIRC) Database Project. What makes it a bit confuding is that there exists a top level Database Project as well (at the same level as C# and VB). The project type is for T-SQL scripts etc. I assume that is what you have chosen!

Niels

Can't Add New Report

In Visual Studio when I create a new Report Project, once it's created I go
into Solution Explorer, right click on the "Reports" folder and select "Add
New Report", and I get the following error:
Exception from HRESULT: 0x8013141A
I've re-installed Visual Studio, the .NET framework, and Reporting Services,
and after all that it still gives me the error.
Any suggestions?
Michael BodnarSome DLL is failing to load. The only sure way to diagnose the problem is
to attach a debugger and see what exceptions it's throwing.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Bodnar" <blah@.blah.com> wrote in message
news:eX63pJHWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> In Visual Studio when I create a new Report Project, once it's created I
go
> into Solution Explorer, right click on the "Reports" folder and select
"Add
> New Report", and I get the following error:
> Exception from HRESULT: 0x8013141A
> I've re-installed Visual Studio, the .NET framework, and Reporting
Services,
> and after all that it still gives me the error.
> Any suggestions?
> Michael Bodnar
>|||Can you dump out a list of DLLs that are loaded into the VS process when the
error occurs? You can either use a tool like procexp.exe
(http://www.sysinternals.com/ntw2k/freeware/procexp.shtml) or attach another
instance of VS as a debugger. Using VS, display the Modules window, select
all rows, copy, and paste into a text file.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Bodnar" <blah@.blah.com> wrote in message
news:eX63pJHWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> In Visual Studio when I create a new Report Project, once it's created I
go
> into Solution Explorer, right click on the "Reports" folder and select
"Add
> New Report", and I get the following error:
> Exception from HRESULT: 0x8013141A
> I've re-installed Visual Studio, the .NET framework, and Reporting
Services,
> and after all that it still gives me the error.
> Any suggestions?
> Michael Bodnar
>|||The whole problem was I was trying to install it off a network drive. It
must not have been seeing some files over the network or something, or maybe
some files got corrupt. I installed it with the CD and it works fine now.
"Michael Bodnar" <blah@.blah.com> wrote in message
news:eX63pJHWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> In Visual Studio when I create a new Report Project, once it's created I
go
> into Solution Explorer, right click on the "Reports" folder and select
"Add
> New Report", and I get the following error:
> Exception from HRESULT: 0x8013141A
> I've re-installed Visual Studio, the .NET framework, and Reporting
Services,
> and after all that it still gives me the error.
> Any suggestions?
> Michael Bodnar
>

can't access report through report manager...

Here is what is happening.
I have a working project PROG created using VS.Net. I create a new
project for reports RPTPROJ under PROJ.
I create several reports RPT1.RDL, RPT2.RDL under the new project
RPTPROJ.
Now I start the REPORT MANAGER and create a new folder (MYREPORTS) and
upload all my reports (RDLs) into this new folder. Now when I click on
any of the reports from here, it doesn't show up. Only shows up an
"Action Canceled" message.
Here is the URL, it generates for one of the reports:
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fRPTPROJ%2fRPT1
But when I goto,
http://localhost/reportserver?%2fPROJ%2fRPTPROJ
I can see all my reports and run them.
Please can someone explain what I should do to run them from the
report manager?
ThanksWhen you publish to the report server in Report Designer, in the project
properties dialog (where you set your URL to the report server), there is a
target folder property. Set that to MYREPORTS. By default it is the
project name, which is why you're getting link s containing /RPTPROJ/RPT1
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy" <nroy@.iastate.edu> wrote in message
news:ad20302f.0408091310.6e20c373@.posting.google.com...
> Here is what is happening.
> I have a working project PROG created using VS.Net. I create a new
> project for reports RPTPROJ under PROJ.
> I create several reports RPT1.RDL, RPT2.RDL under the new project
> RPTPROJ.
> Now I start the REPORT MANAGER and create a new folder (MYREPORTS) and
> upload all my reports (RDLs) into this new folder. Now when I click on
> any of the reports from here, it doesn't show up. Only shows up an
> "Action Canceled" message.
> Here is the URL, it generates for one of the reports:
> http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fRPTPROJ%2fRPT1
> But when I goto,
> http://localhost/reportserver?%2fPROJ%2fRPTPROJ
> I can see all my reports and run them.
> Please can someone explain what I should do to run them from the
> report manager?
> Thanks|||Lukasz,
I made the changes suggested by you but still the same problem. The URL it shows is:
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMYREPORTS%2fRPT1
What am I doing wrong?
nroy@.iastate.edu (Roy) wrote in message news:<ad20302f.0408091310.6e20c373@.posting.google.com>...
> Here is what is happening.
> I have a working project PROG created using VS.Net. I create a new
> project for reports RPTPROJ under PROJ.
> I create several reports RPT1.RDL, RPT2.RDL under the new project
> RPTPROJ.
> Now I start the REPORT MANAGER and create a new folder (MYREPORTS) and
> upload all my reports (RDLs) into this new folder. Now when I click on
> any of the reports from here, it doesn't show up. Only shows up an
> "Action Canceled" message.
> Here is the URL, it generates for one of the reports:
> http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fRPTPROJ%2fRPT1
> But when I goto,
> http://localhost/reportserver?%2fPROJ%2fRPTPROJ
> I can see all my reports and run them.
> Please can someone explain what I should do to run them from the
> report manager?
> Thanks

Cant access Northwind database

I'm using XP Pro and I have the Developer's version of SQL Server installed. I downloaded SQLXML 3.0 and created a virtual directory named nwind.

I then entered the following on my IE browser:

http://<IIServer>/nwind?sql=SELECT FirstName, LastName FROM Employees FOR XML AUTO&root=root

the result is:
"The page cannot be displayed..."

I'd appreciate any help.

Thanks,
Pathttp://<IIServer>/nwind?sql=SELECT FirstName, LastName FROM Employees FOR XML AUTO&root=root

Is that a typo, or are you trying to pass a variable to a directory? when you say /nwind?sql='foo' then you should get page cannot be displayed, right? Don't you have to say /nwind/default.asp?sql='foo' ??

or will the virtual directory/IIS understand what you mean?sql

Can't access linked server through Query Analyzer

Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
Thanks for your help
Israel,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Manager -- this should open the QA connection with the same credentials with which the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is the linked server name correct? Did you check on your server "B" that "SELECT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from Table') syntax?
4. Can you double check your linked server settings match the settings (e.g. Provider) that you have on the servers which are able to execute the query successfully.
Thanks,
Tea C.
"Israel Perez" wrote:

> Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
> Thanks for your help

Can't access linked server through Query Analyzer

Hi, i created a linked server and can see the tables shown in Enterprise Man
ager. When I log into Query Analyzer, I try to access the remote tables with
the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlse
rver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it wo
rks fine. Other servers have a linked server to this same server B (PGJE_SP_
06) and they work fine.
Thanks for your helpIsrael,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Mana
ger -- this should open the QA connection with the same credentials with wh
ich the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is
the linked server name correct? Did you check on your server "B" that "SELE
CT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from
Table') syntax?
4. Can you double check your linked server settings match the settings (e.g.
Provider) that you have on the servers which are able to execute the query
successfully.
Thanks,
Tea C.
"Israel Perez" wrote:

> Hi, i created a linked server and can see the tables shown in Enterprise M
anager. When I log into Query Analyzer, I try to access the remote tables wi
th the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sql
server authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it
works fine. Other servers have a linked server to this same server B (PGJE_S
P_06) and they work fine.
> Thanks for your helpsql

cannot view wsdl of endpoint.

i created this endpoint in SSMS:

Code Snippet

/****** Object: Endpoint [first_Endpoint] Script Date: 06/19/2007 16:39:22 ******/

CREATEENDPOINT [first_Endpoint]

AUTHORIZATION [Domain\username]--scrubbed my username out of post!

STATE=STARTED

ASHTTP(PATH=N'/sql',

PORTS=(CLEAR),

AUTHENTICATION=(NTLM,KERBEROS,INTEGRATED),

SITE=N'sitename,

CLEAR_PORT= 80,

COMPRESSION=DISABLED)

FORSOAP(

WEBMETHOD 'provideInfo'(NAME=N'[adventureworks].[dbo].[uspGetBillOfMaterials]'

,SCHEMA=DEFAULT

, FORMAT=ALL_RESULTS),BATCHES=DISABLED,

SESSIONS=DISABLED,SESSION_TIMEOUT=60,

DATABASE=N'AdventureWorks',

NAMESPACE=N'http://tempuri.org/',

SCHEMA=STANDARD,

CHARACTER_SET=XML)

When i type http://localhost/sql/provideinfo?wsdl into internet explorer, i just get a 404 page cannot be found error.

When i type http://server/sql/provideinfo?wsdl in to the browser i get a Page cannot be displayd error.

I get the same when i type http://myServerName/sql/provideinfo?wsdl

I get the same when i type http://sitename/sql/provideinfo?wsdl

Sql server is running under an account with admin access to the box and sa access to the sql server. what am i doing wrong here that i cannot view my wsdl. oh, the OS is vista.

Could it be a configuration issue that im not seeing?

Can you try the following?
http://localhost/sql?wsdl

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372924&SiteID=1|||i tried http://localhost/sql?wsdl but still get a 401 not found error. as i am not getting the error speicifed in the link you provided, it would make me think that it is unrelated to my issue. thanks for the reply though.|||

when i use http://server/sql?wsdl i get a 502 error, a connection could not be made as the server actively refused it.

Are there some network settings i should change on my machine possibly?

|||Is server here the same name which you specified in SITE?|||no. the server and the site have different names. I have tried the url both ways though. when i specify the servername in the url, i get an iis page not found error. when i try with the sitename, i get a page cannot be displayed error.|||

The way the 'SITE' keyword value is treated is that it is passed to the OS HTTP.sys service. The HTTP.sys service uses a combination of information to determine which process to redirect the HTTP request to. In this scenario, only HTTP requests send to http://sitename/sql are redirected to this SQL Server endpoint. So, unless machine running SQL Server is named 'sitename', it is very unlikely that the HTTP request will get to SQL Server.

If possible, I recommend changing the SITE value to '*' or the actual machine name.

BTW, I believe the default value for the WSDL generation is NONE, so ?wsdl will get you a 502 HTTP error.

Jimmy

|||

Hi Jimmy.

That indeed was the problem!!! i dropped the endpoint, changed the site name to the name of the pc the endpoint is hosted upon and bingo!!!! cant believe i was so stupid in the first place!!!! it makes total sense now that i look at it! I expected to see the page that comes up when you view the definition of an enpoint in IE, but i also realise that that wont happen. i just see a lot of xml making up the wsdl file, and thats what i required. Thanks for the help and suggestions everyone, and Jimmy, for the answer that gave me the required results.

Cannot view the DTS

Hi ,

I have DTS created in my Development server using the save as option i tried to save it in my production server . All these works fine . But when i logged in to the production server i could not see the DTS . But when we tried loging to the prodcution server using the data base administrator passed we were able to see the DTS and the owner of the DTS is seems to be not a admin person rather it is me.

Why is that i am not able to view the DTS when i logged in with my user id ?

Can any on help us in resolution this issue .

Thanks

K.Karthik Doss

refer this... it may help u ... http://www.sqldts.com/212.aspx

Madhu

|||

Hi Madhu

Thnaks for the efforts . I think i have not made it clear for you .the problem what we have the DTS in the production server but the Owner is not able to view the DTS . but we were able to see the DTS in DB admin access . so what should be done to view the dts in the owners login .

Thanks

K.Karthik Doss

|||

have u checked the owner of this DTS in Production server... Is it still the same ?

Madhu

|||If your login is not a part of SYSADMIN group on the production server, then what you are getting is by default. If that package is owned by SA then only SYSADMIN privileged uses can view or edit.|||

Yes i was able to view the DTS thru the DB Admin userID and Password and the owner is me. But i could not view it when i logged in thru my UserID and Password .

|||

say if the package is owned by SA then how do i change that to a comman , Like if any one logs in to the system can view that package .

|||

AFAIK... there is not direct method in SQL Server 2000 ... yes u have Specific role available in SQL Server 2005(db_dtsadmin,db_dtsltduser etc)... in SQL 2000, if all the users want to view the package owned by others ... then u need to add all of them in SysAdmin Role, which is not recommened atall ..

Madhu

Cannot view log data

I created a perfmon counter log with some SQL server counters and started
the log and ran it for a few minutes and then decided to view the log file
that I created ( .blg) . I could not see anything. Did i do it right ? I
created the log and was viewing it from the same perfmon toolHassan
Did you add the same counters to perfom monitor.
BTW , what is version of the server? (NT or Windows 2000 Server ,etc)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O3nV6n0eDHA.2364@.TK2MSFTNGP09.phx.gbl...
> I created a perfmon counter log with some SQL server counters and started
> the log and ran it for a few minutes and then decided to view the log file
> that I created ( .blg) . I could not see anything. Did i do it right ? I
> created the log and was viewing it from the same perfmon tool
>
>

Cannot view databases in view list for SQL 2005

I have created a couple of new logons/ users and they are dbo's of a few
databases. However it has become apparent that for some reason the databases
are not "browseable" through the SSMS dropdown. Even running sp_databases
does not return their names. The databases can be accessed through text
(e.g. USE dbname) and do look like full access is available.
What on earth is going on? The only difference between this and another
(working) box is that this non-working one is 64 bit windows with 64bit SQL
(x64) wheras the other is 32 bit on 32 bit. I know this because they both
use automated builds which I created.
Regards,
Mark
Hi Mark
"Mark Broadbent" wrote:

> I have created a couple of new logons/ users and they are dbo's of a few
> databases. However it has become apparent that for some reason the databases
> are not "browseable" through the SSMS dropdown. Even running sp_databases
> does not return their names. The databases can be accessed through text
> (e.g. USE dbname) and do look like full access is available.
> What on earth is going on? The only difference between this and another
> (working) box is that this non-working one is 64 bit windows with 64bit SQL
> (x64) wheras the other is 32 bit on 32 bit. I know this because they both
> use automated builds which I created.
> Regards,
> Mark
I assume by dropdown you mean the Object Browser (F8).
Have you changed the permissions for these users at all since they created
the databases?
From BOL:
sp_databases requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY
DEFINITION permission, and must have access permission to the database.
Cannot be denied VIEW ANY DEFINITION permission.
John

Cannot view databases in view list for SQL 2005

I have created a couple of new logons/ users and they are dbo's of a few
databases. However it has become apparent that for some reason the databases
are not "browseable" through the SSMS dropdown. Even running sp_databases
does not return their names. The databases can be accessed through text
(e.g. USE dbname) and do look like full access is available.
What on earth is going on? The only difference between this and another
(working) box is that this non-working one is 64 bit windows with 64bit SQL
(x64) wheras the other is 32 bit on 32 bit. I know this because they both
use automated builds which I created.
Regards,
MarkHi Mark
"Mark Broadbent" wrote:

> I have created a couple of new logons/ users and they are dbo's of a few
> databases. However it has become apparent that for some reason the databas
es
> are not "browseable" through the SSMS dropdown. Even running sp_databases
> does not return their names. The databases can be accessed through text
> (e.g. USE dbname) and do look like full access is available.
> What on earth is going on? The only difference between this and another
> (working) box is that this non-working one is 64 bit windows with 64bit SQ
L
> (x64) wheras the other is 32 bit on 32 bit. I know this because they both
> use automated builds which I created.
> Regards,
> Mark
I assume by dropdown you mean the Object Browser (F8).
Have you changed the permissions for these users at all since they created
the databases?
From BOL:
sp_databases requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY
DEFINITION permission, and must have access permission to the database.
Cannot be denied VIEW ANY DEFINITION permission.
John

Cannot view databases in view list for SQL 2005

I have created a couple of new logons/ users and they are dbo's of a few
databases. However it has become apparent that for some reason the databases
are not "browseable" through the SSMS dropdown. Even running sp_databases
does not return their names. The databases can be accessed through text
(e.g. USE dbname) and do look like full access is available.
What on earth is going on? The only difference between this and another
(working) box is that this non-working one is 64 bit windows with 64bit SQL
(x64) wheras the other is 32 bit on 32 bit. I know this because they both
use automated builds which I created.
Regards,
MarkHi Mark
"Mark Broadbent" wrote:
> I have created a couple of new logons/ users and they are dbo's of a few
> databases. However it has become apparent that for some reason the databases
> are not "browseable" through the SSMS dropdown. Even running sp_databases
> does not return their names. The databases can be accessed through text
> (e.g. USE dbname) and do look like full access is available.
> What on earth is going on? The only difference between this and another
> (working) box is that this non-working one is 64 bit windows with 64bit SQL
> (x64) wheras the other is 32 bit on 32 bit. I know this because they both
> use automated builds which I created.
> Regards,
> Mark
I assume by dropdown you mean the Object Browser (F8).
Have you changed the permissions for these users at all since they created
the databases?
From BOL:
sp_databases requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY
DEFINITION permission, and must have access permission to the database.
Cannot be denied VIEW ANY DEFINITION permission.
John

Cannot use credentials for proxy account

Hi,

because my package does not run with SQL-Server-Agent, but without problems if started by "hand", I created a new credential which contains the information needed for the package. I did this as described on: http://msdn2.microsoft.com/en-us/library/ms190703.aspx .

After that i tried to create an proxy account, but when I chose the created credential, Management Studio says "Der Proxy "[name_of_credential] ist kein gültiger Windows-Benutzer(Microsoft SQL Server, Fehler: 14529)". This means something like: "This Proxy is not a valid windows-user. Error: 14529".

Any hints how to use a credential that is not a windows-user?

Regards,

Jan Wagner

You could try something like the following. It creates the Credential

and proxy. You need to decide what sub-system you want to run - I'm not

sure you'll be able to run the CmdExec subsystem, for example, with a

windows user.

-code start

--###################################################### describe

script

############################################################################

PRINT '>>> This script creates the LOGIN,

USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES

<<<'

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] started at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

PRINT ''

PRINT '>>> Create Objects <<<'

PRINT ''

--################################################## Check and Drop

Existing

########################################################################

DECLARE

@.proxy_name SYSNAME,

@.subsystem_name SYSNAME,

@.UserName SYSNAME,

@.credential_name SYSNAME,

@.RowCount INT

SET @.credential_name = 'TestSQLUserCred'

SET @.proxy_name = 'TestSQLUserProxy'

SET @.subsystem_name = 'CmdExec'

SET @.UserName = 'TestSQLCredUser'

--################################################## CREATE Login

###################################################################################

SET NOCOUNT ON

USE [master]

IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = @.UserName)

CREATE LOGIN TestSQLCredUser WITH Password = 'TestSQLCredUser1', DEFAULT_DATABASE = [IntegrationServices]

--################################################## CREATE User and

Grant Rights on DBS

############################################################

USE [IntegrationServices]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'TestSQLCredUser')

CREATE USER TestSQLCredUser FROM LOGIN TestSQLCredUser

EXEC SP_ADDROLEMEMBER [DB_DataReader], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [DB_DataWriter], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], TestSQLCredUser

GRANT EXECUTE ON [sp_dts_addlogentry] to TestSQLCredUser

USE [msdb]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'TestSQLCredUser')

CREATE USER TestSQLCredUser FROM LOGIN TestSQLCredUser

EXEC SP_ADDROLEMEMBER [db_dtsadmin], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [db_dtsltduser], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [db_dtsoperator], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], TestSQLCredUser

--################################################## CREATE Credential

##############################################################################

USE [master]

IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = @.credential_name)

CREATE CREDENTIAL [TestSQLUserCred] WITH IDENTITY = 'TestSQLCredUser', secret = 't3st'

--################################################## CREATE Proxy

###################################################################################

USE [msdb]

DECLARE @.ProxyTable TABLE (subsystem_id int

,subsystem_name sysname,proxy_id

int,proxy_name sysname)

INSERT INTO @.ProxyTable EXEC

sp_enum_proxy_for_subsystem

--@.proxy_name=@.proxy_name,

@.subsystem_name=@.subsystem_name

select proxy_name from @.ProxyTable WHERE proxy_name = @.proxy_name AND subsystem_name = @.subsystem_name

SELECT @.RowCount = @.@.ROWCOUNT

IF @.RowCount = 0 BEGIN

PRINT 'Creating Proxy'

EXEC SP_ADD_PROXY

@.proxy_name=@.proxy_name,

@.credential_name=@.credential_name

END

DECLARE @.LoginProxyTable TABLE

(proxy_id int,proxy_name sysname,

flags int, [name] sysname, sid varbinary(128),principal_id int)

INSERT INTO @.LoginProxyTable EXEC

sp_enum_login_for_proxy

--@.proxy_name=@.proxy_name, @.name=@.UserName

select proxy_name from @.LoginProxyTable WHERE proxy_name = @.proxy_name AND [name] = @.UserName

SELECT @.RowCount = @.@.ROWCOUNT

IF @.RowCount = 0 BEGIN

PRINT 'Granting Login to Proxy'

EXEC SP_GRANT_LOGIN_TO_PROXY

@.login_name=@.UserName,

@.proxy_name=@.proxy_name

END

EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

--###################################################### end script

##################################################################################

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] ended at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

-code end

It will report and error for the

SP_REVOKE_PROXY_FROM_SUBSYSTEM because I haven't found a

way to check this to see if it needs to be executed (anyone have an

idea?)....
HTH|||

I _believe_ you need to enable both SQL Server and Windows authentication mode. The default is Windows Authentication only.

To do this, go into server properties by right clicking on the server node in the object explorer in Management Studio.

Select the Security tab and click on the SQL Server and Windows Authentication mode option.

See the settings here

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi,

thanks for that script, bu I cant run it. Management Studio says it didnt find database IntegrationServices on sysdatabases :-(. Any idea how to solve this?

@.Kirk: These settings were already set, thanks anyway.

Regards,

Jan Wagner

|||

jwagner20 wrote:

Hi,

thanks for that script, bu I cant run it. Management Studio says it

didnt find database IntegrationServices on sysdatabases :-(. Any idea

how to solve this?

@.Kirk: These settings were already set, thanks anyway.

Regards,

Jan Wagner

:-) Sorry - IntegrationServices database is MY local database. You need

to change it to wherever you plan to have the credential accessing.

Similarly, you should review the rights you may need - the EXAMPLE I

gave was relevant for me....|||

Oh, ok. I thought this database was a systemdatabase, like msdb. So your code does give me credential access to a database, right?

It seems like I did not explain my problem acurately:

What my packagage does is the following: dumping all rows from a database to a MS-Access file that lies on a windows-share. For that windows-share i need the credential login, which is not in my domain, and thats causing my problem...

Regards,

Jan

|||

jwagner20 wrote:

Oh, ok. I thought this database was a

systemdatabase, like msdb. So your code does give me credential access

to a database, right?

It seems like I did not explain my problem acurately:

What my packagage does is the following: dumping all rows from a

database to a MS-Access file that lies on a windows-share. For that

windows-share i need the credential login, which is not in my domain,

and thats causing my problem...

Regards,

Jan

Ok - I think I see now. So your server is in a domain (A), but the

share is outside the domain (in a domain B), and you don't have a trust

relationship, so you are unable to access the target location with a

domain user from your domain (domain A)?

Unfortunately, I can't really help mych other than to say you need to

make sure that the user you use has rights to that share. Does the

share allow everybody full access so that you can access it? I'm really

ignorant around networking, permissions, domain etc. Hopefully someone

else can help out....|||

GethWho wrote:

Ok - I think I see now. So your server is in a domain (A), but the share is outside the domain (in a domain B), and you don't have a trust relationship, so you are unable to access the target location with a domain user from your domain (domain A)?

Yes, thats exactly my problem.

GethWho wrote:

Unfortunately, I can't really help mych other than to say you need to make sure that the user you use has rights to that share. Does the share allow everybody full access so that you can access it? I'm really ignorant around networking, permissions, domain etc. Hopefully someone else can help out....

Thats my problem, I cant use the credential for the user that is allowed to access to the share in my proxy... :-(

Regards,

Jan