Accessing more than: error

Showing posts with label error. Show all posts

Tuesday, March 27, 2012

Adding a new report to VS 2005

When I try to add a new Report to a Report Server project I get the
following error and I can't add it to project.
TITLE: Microsoft Visual Studio
--
Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
--
BUTTONS:
OK
--
===================================
Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
(Microsoft Visual Studio)
--
Program Location:
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.OnHierarchyNodeAppending(IHierarchyNode
node, IHierarchyNode parentNode)
at
Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchyNode
node, IHierarchyNode parentNode)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.AddExistingFileToProject(IFileProjectNode&
parentNode, String name, String fullPath, VSADDITEMOPERATION
dwAddItemOperation)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectItemsExt.AddFromFile(String
FileName)
at
Microsoft.ReportDesigner.Wizards.ReportWizardForm.OnFinish(CancelEventArgs
e)Don't know if this is related. In the RTM, there are issues with the add
report wizzard. If you use the add item, then select report everything seems
to works ok.
"Sorin Sandu" wrote:
> When I try to add a new Report to a Report Server project I get the
> following error and I can't add it to project.
> TITLE: Microsoft Visual Studio
> --
> Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
> --
> BUTTONS:
> OK
> --
> ===================================> Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
> (Microsoft Visual Studio)
> --
> Program Location:
> at
> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.OnHierarchyNodeAppending(IHierarchyNode
> node, IHierarchyNode parentNode)
> at
> Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchyNode
> node, IHierarchyNode parentNode)
> at
> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.AddExistingFileToProject(IFileProjectNode&
> parentNode, String name, String fullPath, VSADDITEMOPERATION
> dwAddItemOperation)
> at
> Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectItemsExt.AddFromFile(String
> FileName)
> at
> Microsoft.ReportDesigner.Wizards.ReportWizardForm.OnFinish(CancelEventArgs
> e)
>
>|||If I add a new item and select Report nothing happens !!!!
"Wickherm" <Wickherm@.discussions.microsoft.com> a scris în mesajul de
ºtiri:1C7124B2-4E9D-42D1-AABF-FFD029FC2276@.microsoft.com...
> Don't know if this is related. In the RTM, there are issues with the add
> report wizzard. If you use the add item, then select report everything
> seems
> to works ok.
> "Sorin Sandu" wrote:
>> When I try to add a new Report to a Report Server project I get the
>> following error and I can't add it to project.
>> TITLE: Microsoft Visual Studio
>> --
>> Exception of type 'System.Runtime.InteropServices.COMException' was
>> thrown.
>> --
>> BUTTONS:
>> OK
>> --
>> ===================================>> Exception of type 'System.Runtime.InteropServices.COMException' was
>> thrown.
>> (Microsoft Visual Studio)
>> --
>> Program Location:
>> at
>> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.OnHierarchyNodeAppending(IHierarchyNode
>> node, IHierarchyNode parentNode)
>> at
>> Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchyNode
>> node, IHierarchyNode parentNode)
>> at
>> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.AddExistingFileToProject(IFileProjectNode&
>> parentNode, String name, String fullPath, VSADDITEMOPERATION
>> dwAddItemOperation)
>> at
>> Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectItemsExt.AddFromFile(String
>> FileName)
>> at
>> Microsoft.ReportDesigner.Wizards.ReportWizardForm.OnFinish(CancelEventArgs
>> e)
>>
>>

Sunday, March 25, 2012

Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Tuesday, March 20, 2012

Added SP2 now have linked server error

I added SP2 to my 2005 server and now I can't connect to a previously working
linked Oracle server. I get this:
TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "ISRSREPLICATEDLINK".
OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
message "Error while trying to retrieve text for error ORA-01019
". (Microsoft SQL Server, Error: 7303)
What could have changed? Testing the connection via the ODBC works but
inside of SQL MAnagement Studio it fails.
Also somewhere in troubleshooting this I saw an error about unable to
allocate user side memory.
"TLuebke" wrote:

> I added SP2 to my 2005 server and now I can't connect to a previously working
> linked Oracle server. I get this:
> TITLE: Microsoft SQL Server Management Studio
> --
> "The test connection to the linked server failed."
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "ISRSREPLICATEDLINK".
> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
> message "Error while trying to retrieve text for error ORA-01019
> ". (Microsoft SQL Server, Error: 7303)
> What could have changed? Testing the connection via the ODBC works but
> inside of SQL MAnagement Studio it fails.
>
>
|||TLuebke,
Have you tried reinstalling your Oracle provider? I did some research, but
no clear indication of what your problem is. One interesting thing is that
http://technet.microsoft.com/en-us/library/bb332055.aspx suggests using the
Oracle provider for OLE DB (OraOLEDB.Oracle.1) rather than MSDAORA, in case
that is useful to you.
RLF
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:57778530-E08D-46F2-ADD9-0D7D31236C74@.microsoft.com...[vbcol=seagreen]
> Also somewhere in troubleshooting this I saw an error about unable to
> allocate user side memory.
> "TLuebke" wrote:

Added SP2 now have linked server error

I added SP2 to my 2005 server and now I can't connect to a previously working
linked Oracle server. I get this:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "ISRSREPLICATEDLINK".
OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
message "Error while trying to retrieve text for error ORA-01019
". (Microsoft SQL Server, Error: 7303)
What could have changed? Testing the connection via the ODBC works but
inside of SQL MAnagement Studio it fails.Also somewhere in troubleshooting this I saw an error about unable to
allocate user side memory.
"TLuebke" wrote:
> I added SP2 to my 2005 server and now I can't connect to a previously working
> linked Oracle server. I get this:
> TITLE: Microsoft SQL Server Management Studio
> --
> "The test connection to the linked server failed."
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "ISRSREPLICATEDLINK".
> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
> message "Error while trying to retrieve text for error ORA-01019
> ". (Microsoft SQL Server, Error: 7303)
> What could have changed? Testing the connection via the ODBC works but
> inside of SQL MAnagement Studio it fails.
>
>|||TLuebke,
Have you tried reinstalling your Oracle provider? I did some research, but
no clear indication of what your problem is. One interesting thing is that
http://technet.microsoft.com/en-us/library/bb332055.aspx suggests using the
Oracle provider for OLE DB (OraOLEDB.Oracle.1) rather than MSDAORA, in case
that is useful to you.
RLF
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:57778530-E08D-46F2-ADD9-0D7D31236C74@.microsoft.com...
> Also somewhere in troubleshooting this I saw an error about unable to
> allocate user side memory.
> "TLuebke" wrote:
>> I added SP2 to my 2005 server and now I can't connect to a previously
>> working
>> linked Oracle server. I get this:
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> "The test connection to the linked server failed."
>> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
>> linked server "ISRSREPLICATEDLINK".
>> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
>> message "Error while trying to retrieve text for error ORA-01019
>> ". (Microsoft SQL Server, Error: 7303)
>> What could have changed? Testing the connection via the ODBC works but
>> inside of SQL MAnagement Studio it fails.
>>
>>

Monday, March 19, 2012

Add user to Local Group Error

Guys,

I need help installing SQL Server 2005. I meet all of the pre-req's and setup seems to run normally until it starts installing the services and database engine. I receive the following error:

SQL Server Setup was unable add user NT AUTHORITY\NETWORK SERVICE to local group SQLSERVER2005DTSUser$server.

It repeats the error for for the other groups that are created with SQL install.

Am i missing something that's obvious? I couldn't find anything in the documentation or anywhere else.

Here are a couple of things to try:

1. Check the installation log files, located at %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files for more details on the error.

2. Try to add the service manually to the local groups using the Computer Management Admin tool. Then re-run setup.

Paul

Sunday, March 11, 2012

Add Reporting Services to Trusted Accounts

I installed SQL 2005 SP2 and RS add-in for Sharepoint to a WSS 3.0 site.

I get the error: 'A connection to the server cannot be established' when I try to use the sharepoint account set up for this purpose. It is in the farm administrators, and has the required permissions when I check the database. Are there any other recommended troubleshooting tips that I can do? Thanks.

Dan

FYI - I resolved the problem by adding the account to the local group WSS_RESTRICTED_WPG. This account needs to be in the Local Administrator's group, and the groups starting with WSS.

Thursday, March 8, 2012

Add NT users

After adding NT users as SQL Server logins, I tried to set
them up on their PC using ODBC connections but get the
message:
SQL Server error: 18452
Login failed for user (null). Reason: not associated with
a trusted SQL Server connection
Can someone advise on basic checks?
(Our SQL Server is setup for MIXED MODE and I myself can
use the NT Authentication since I belong to the SQL ADMIN
group via the main DOMAIN server setup)
thks!what's the connection string look like? This error typically means SQL
Server thought you were coming in with a standard security connection...
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:ecb201c40c36$87a17810$a301280a@.phx.gbl...
> After adding NT users as SQL Server logins, I tried to set
> them up on their PC using ODBC connections but get the
> message:
> SQL Server error: 18452
> Login failed for user (null). Reason: not associated with
> a trusted SQL Server connection
> Can someone advise on basic checks?
> (Our SQL Server is setup for MIXED MODE and I myself can
> use the NT Authentication since I belong to the SQL ADMIN
> group via the main DOMAIN server setup)
> thks!|||Good point - Can't remember where I last checked for this
info - can't find it in the Profiler or Performance
Logs/Alerts....any hints?

>--Original Message--
>what's the connection string look like? This error
typically means SQL
>Server thought you were coming in with a standard
security connection...
>--
>Richard Waymire, MCSE, MCDBA
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"wayne" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ecb201c40c36$87a17810$a301280a@.phx.gbl...
set
with
ADMIN
>
>.
>|||it'd be in the application code.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:c10501c40c3f$eb6ae260$a601280a@.phx.gbl...
> Good point - Can't remember where I last checked for this
> info - can't find it in the Profiler or Performance
> Logs/Alerts....any hints?
>
> typically means SQL
> security connection...
> confers no rights.
> message
> set
> with
> ADMIN|||We are not using any development tools - just settingup
the ODBC connection to enabled Access/Excel to make
connections to the SQL Server!
It is where the ODBC Admin tool (setting-up a DSN) that
has failed - any idea?
Cheers!
>--Original Message--
>it'd be in the application code.
>--
>Richard Waymire, MCSE, MCDBA
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Wayne" <anonymous@.discussions.microsoft.com> wrote in
message
>news:c10501c40c3f$eb6ae260$a601280a@.phx.gbl...
this
and
to
the
can
>
>.
>|||ODBC administrator is in the Control Panel
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||when you configure the data source in ODBC as a type for SQL Server,
clicking next on the config will bring you to the page where it asks if you
want to "With Windows NT authentication..." or "With SQL Server
authentication..." - make sure the top option is selected.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:c7f301c40cca$be4c1200$a601280a@.phx.gbl...
> We are not using any development tools - just settingup
> the ODBC connection to enabled Access/Excel to make
> connections to the SQL Server!
> It is where the ODBC Admin tool (setting-up a DSN) that
> has failed - any idea?
> Cheers!
> confers no rights.
> message
> this
> and
> to
> the
> can|||Wayne,
It may be easier to troubleshoot if you open up a case with a SQL
Support Engineer.
Login failed user 'null' could indicate problems with the DC or even DNS.
There's a utility called netdiag.exe that you can get from the WIndows
Resource kit, to validate communication with the DC and your DNS servers.
Run the following test:
isql -Snp:sqlserverNameHere -E -Q"Select @.@.version"
and
isql -Stcp:sqlserverNameHere -E -Q"Select @.@.version"
and post the results.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thks Kevin!

>--Original Message--
>Wayne,
> It may be easier to troubleshoot if you open up a case
with a SQL
>Support Engineer.
>Login failed user 'null' could indicate problems with the
DC or even DNS.
>There's a utility called netdiag.exe that you can get
from the WIndows
>Resource kit, to validate communication with the DC and
your DNS servers.
>
>Run the following test:
>isql -Snp:sqlserverNameHere -E -Q"Select @.@.version"
>and
>isql -Stcp:sqlserverNameHere -E -Q"Select @.@.version"
>and post the results.
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>

Tuesday, March 6, 2012

Add New Database Error "object reference not set to an instance of an object"

Hi,

I'm new to SQL database.

When I add a new item to project in VS2005: Project-->Add New Item --> Sql Database I got a error msg "object reference not set to an instance of an object" after click "Add" button. The SQL Server Express is running. How do I fix this problem?

Any help would be appreceiated.

CO22006

Moving to the SQL forum.

-Tim <MSFT>

Add member failed for DatabaseRole 'db_datareader'

I tried to give myself datareader and datawriter for my domain login and got this error below. I'm logged into the server with my domain account which has admin rights.

TITLE: Microsoft SQL Server Management Studio

Add member failed for DatabaseRole 'db_datareader'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot use the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15405&LinkId=20476

BUTTONS:

Because you are conected as a sysadmin, you are also the dbo of the database. You cannot add dbo as a member of any fixed role. dbo already has the permissions of db_datareader and db_datawriter.

Thanks
Laurentiu

|||Thanks!

add linked server

Hi ,
I have tried to add a linked server(sql2000) residing
at location B from Location A (a server computer ,using
SQL 7.0) but i got the error "general network error , pls
check network documentation" I have gotten the correct
login id & pwd from the user at the other end.
have also configured the port#(under Client Utility)
correctly for client connection and also triedthe
sp_addlinkedservers & sp_addsvrlogin already
Is there any dll file needed ? such as DBNET.dll ? to
add linked server from sql ver 7.0 to sql2000 ?
Does it need any specific version of the odbc driver
for SQL Server ? current it's version is 3.70.10.63 i have
tried to add and odbc link but still have the same error
I have no problem when using Query Analyzer from my
client computer AND the user from the other end is able to
add my sqlserver successfully as a linked server. The odbc
driver for SQL Server is 3.85.xx.xx
Appreciate if somebody could help me out as i am doing
some data migration which i have set up some DTS jobs to
copy data daily over instead of one big chunks the day
before migration
many thksmaxzim
Can you show us your creation script?
This example works without creating a linked server
declare @.pid varchar(30)
set @.pid='2450'
select id from openrowset('sqloledb',
'server';'sa';'pass',database.dbo.table)
where id > @.pid
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> Hi ,
> I have tried to add a linked server(sql2000) residing
> at location B from Location A (a server computer ,using
> SQL 7.0) but i got the error "general network error , pls
> check network documentation" I have gotten the correct
> login id & pwd from the user at the other end.
> have also configured the port#(under Client Utility)
> correctly for client connection and also triedthe
> sp_addlinkedservers & sp_addsvrlogin already
> Is there any dll file needed ? such as DBNET.dll ? to
> add linked server from sql ver 7.0 to sql2000 ?
> Does it need any specific version of the odbc driver
> for SQL Server ? current it's version is 3.70.10.63 i have
> tried to add and odbc link but still have the same error
> I have no problem when using Query Analyzer from my
> client computer AND the user from the other end is able to
> add my sqlserver successfully as a linked server. The odbc
> driver for SQL Server is 3.85.xx.xx
> Appreciate if somebody could help me out as i am doing
> some data migration which i have set up some DTS jobs to
> copy data daily over instead of one big chunks the day
> before migration
> many thks
>|||Hi Uri ,
thks will try out urs
but for my creation script
i have used the Enterprise Manager and just specify the
remote user & pwd
for the query analyzer on the sql server i used :
sp_addlinkedserver 'serverA' -- it's using sql server so
i did not specify the rest of the connection
sp_addsvrlogin 'serverA' ,
false , 'sa', 'userid' , 'userpassword'
for the query analyzer from my local client computer ,
i simply enter the server name , enter the pwd & id and i
get connected
>--Original Message--
>maxzim
>Can you show us your creation script?
>This example works without creating a linked server
>declare @.pid varchar(30)
>set @.pid='2450'
>
>select id from openrowset('sqloledb',
>'server';'sa';'pass',database.dbo.table)
>where id > @.pid
>
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> Hi ,
>> I have tried to add a linked server(sql2000) residing
>> at location B from Location A (a server computer ,using
>> SQL 7.0) but i got the error "general network error ,
pls
>> check network documentation" I have gotten the correct
>> login id & pwd from the user at the other end.
>> have also configured the port#(under Client Utility)
>> correctly for client connection and also triedthe
>> sp_addlinkedservers & sp_addsvrlogin already
>> Is there any dll file needed ? such as DBNET.dll ? to
>> add linked server from sql ver 7.0 to sql2000 ?
>> Does it need any specific version of the odbc driver
>> for SQL Server ? current it's version is 3.70.10.63 i
have
>> tried to add and odbc link but still have the same error
>> I have no problem when using Query Analyzer from my
>> client computer AND the user from the other end is able
to
>> add my sqlserver successfully as a linked server. The
odbc
>> driver for SQL Server is 3.85.xx.xx
>> Appreciate if somebody could help me out as i am doing
>> some data migration which i have set up some DTS jobs to
>> copy data daily over instead of one big chunks the day
>> before migration
>> many thks
>>
>
>.
>|||Hi
Do you have specified Server Type in the first tab when you use EM?
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
> Hi Uri ,
> thks will try out urs
> but for my creation script
> i have used the Enterprise Manager and just specify the
> remote user & pwd
> for the query analyzer on the sql server i used :
> sp_addlinkedserver 'serverA' -- it's using sql server so
> i did not specify the rest of the connection
> sp_addsvrlogin 'serverA' ,
> false , 'sa', 'userid' , 'userpassword'
> for the query analyzer from my local client computer ,
> i simply enter the server name , enter the pwd & id and i
> get connected
> >--Original Message--
> >maxzim
> >Can you show us your creation script?
> >This example works without creating a linked server
> >declare @.pid varchar(30)
> >set @.pid='2450'
> >
> >
> >select id from openrowset('sqloledb',
> >'server';'sa';'pass',database.dbo.table)
> >where id > @.pid
> >
> >
> >
> >
> >
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> >> Hi ,
> >>
> >> I have tried to add a linked server(sql2000) residing
> >> at location B from Location A (a server computer ,using
> >> SQL 7.0) but i got the error "general network error ,
> pls
> >> check network documentation" I have gotten the correct
> >> login id & pwd from the user at the other end.
> >>
> >> have also configured the port#(under Client Utility)
> >> correctly for client connection and also triedthe
> >> sp_addlinkedservers & sp_addsvrlogin already
> >>
> >> Is there any dll file needed ? such as DBNET.dll ? to
> >> add linked server from sql ver 7.0 to sql2000 ?
> >>
> >> Does it need any specific version of the odbc driver
> >> for SQL Server ? current it's version is 3.70.10.63 i
> have
> >> tried to add and odbc link but still have the same error
> >>
> >> I have no problem when using Query Analyzer from my
> >> client computer AND the user from the other end is able
> to
> >> add my sqlserver successfully as a linked server. The
> odbc
> >> driver for SQL Server is 3.85.xx.xx
> >>
> >> Appreciate if somebody could help me out as i am doing
> >> some data migration which i have set up some DTS jobs to
> >> copy data daily over instead of one big chunks the day
> >> before migration
> >>
> >> many thks
> >>
> >>
> >
> >
> >.
> >|||Hi Uri ,
i used SQL Server in the 1st tab
>--Original Message--
>Hi
>Do you have specified Server Type in the first tab when
you use EM?
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
>> Hi Uri ,
>> thks will try out urs
>> but for my creation script
>> i have used the Enterprise Manager and just specify
the
>> remote user & pwd
>> for the query analyzer on the sql server i used :
>> sp_addlinkedserver 'serverA' -- it's using sql server
so
>> i did not specify the rest of the connection
>> sp_addsvrlogin 'serverA' ,
>> false , 'sa', 'userid' , 'userpassword'
>> for the query analyzer from my local client
computer ,
>> i simply enter the server name , enter the pwd & id and
i
>> get connected
>> >--Original Message--
>> >maxzim
>> >Can you show us your creation script?
>> >This example works without creating a linked server
>> >declare @.pid varchar(30)
>> >set @.pid='2450'
>> >
>> >
>> >select id from openrowset('sqloledb',
>> >'server';'sa';'pass',database.dbo.table)
>> >where id > @.pid
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> >> Hi ,
>> >>
>> >> I have tried to add a linked server(sql2000)
residing
>> >> at location B from Location A (a server
computer ,using
>> >> SQL 7.0) but i got the error "general network error ,
>> pls
>> >> check network documentation" I have gotten the
correct
>> >> login id & pwd from the user at the other end.
>> >>
>> >> have also configured the port#(under Client
Utility)
>> >> correctly for client connection and also triedthe
>> >> sp_addlinkedservers & sp_addsvrlogin already
>> >>
>> >> Is there any dll file needed ? such as
DBNET.dll ? to
>> >> add linked server from sql ver 7.0 to sql2000 ?
>> >>
>> >> Does it need any specific version of the odbc
driver
>> >> for SQL Server ? current it's version is 3.70.10.63 i
>> have
>> >> tried to add and odbc link but still have the same
error
>> >>
>> >> I have no problem when using Query Analyzer from my
>> >> client computer AND the user from the other end is
able
>> to
>> >> add my sqlserver successfully as a linked server. The
>> odbc
>> >> driver for SQL Server is 3.85.xx.xx
>> >>
>> >> Appreciate if somebody could help me out as i am
doing
>> >> some data migration which i have set up some DTS
jobs to
>> >> copy data daily over instead of one big chunks the
day
>> >> before migration
>> >>
>> >> many thks
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Hi Uri ,
i have used the openrowset but it says "server does not
exist or access denied" which i am using the
administrator's id & pwd
rdgs
>--Original Message--
>Hi
>Do you have specified Server Type in the first tab when
you use EM?
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
>> Hi Uri ,
>> thks will try out urs
>> but for my creation script
>> i have used the Enterprise Manager and just specify
the
>> remote user & pwd
>> for the query analyzer on the sql server i used :
>> sp_addlinkedserver 'serverA' -- it's using sql server
so
>> i did not specify the rest of the connection
>> sp_addsvrlogin 'serverA' ,
>> false , 'sa', 'userid' , 'userpassword'
>> for the query analyzer from my local client
computer ,
>> i simply enter the server name , enter the pwd & id and
i
>> get connected
>> >--Original Message--
>> >maxzim
>> >Can you show us your creation script?
>> >This example works without creating a linked server
>> >declare @.pid varchar(30)
>> >set @.pid='2450'
>> >
>> >
>> >select id from openrowset('sqloledb',
>> >'server';'sa';'pass',database.dbo.table)
>> >where id > @.pid
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> >> Hi ,
>> >>
>> >> I have tried to add a linked server(sql2000)
residing
>> >> at location B from Location A (a server
computer ,using
>> >> SQL 7.0) but i got the error "general network error ,
>> pls
>> >> check network documentation" I have gotten the
correct
>> >> login id & pwd from the user at the other end.
>> >>
>> >> have also configured the port#(under Client
Utility)
>> >> correctly for client connection and also triedthe
>> >> sp_addlinkedservers & sp_addsvrlogin already
>> >>
>> >> Is there any dll file needed ? such as
DBNET.dll ? to
>> >> add linked server from sql ver 7.0 to sql2000 ?
>> >>
>> >> Does it need any specific version of the odbc
driver
>> >> for SQL Server ? current it's version is 3.70.10.63 i
>> have
>> >> tried to add and odbc link but still have the same
error
>> >>
>> >> I have no problem when using Query Analyzer from my
>> >> client computer AND the user from the other end is
able
>> to
>> >> add my sqlserver successfully as a linked server. The
>> odbc
>> >> driver for SQL Server is 3.85.xx.xx
>> >>
>> >> Appreciate if somebody could help me out as i am
doing
>> >> some data migration which i have set up some DTS
jobs to
>> >> copy data daily over instead of one big chunks the
day
>> >> before migration
>> >>
>> >> many thks
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Hi
Probably this is a permission issue. Make sure that you have full
permissions on destination server.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:673f01c493dc$63f279e0$a601280a@.phx.gbl...
> Hi Uri ,
> i have used the openrowset but it says "server does not
> exist or access denied" which i am using the
> administrator's id & pwd
> rdgs
> >--Original Message--
> >Hi
> >Do you have specified Server Type in the first tab when
> you use EM?
> >
> >
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
> >> Hi Uri ,
> >> thks will try out urs
> >> but for my creation script
> >>
> >> i have used the Enterprise Manager and just specify
> the
> >> remote user & pwd
> >>
> >> for the query analyzer on the sql server i used :
> >>
> >> sp_addlinkedserver 'serverA' -- it's using sql server
> so
> >> i did not specify the rest of the connection
> >>
> >> sp_addsvrlogin 'serverA' ,
> >> false , 'sa', 'userid' , 'userpassword'
> >>
> >> for the query analyzer from my local client
> computer ,
> >> i simply enter the server name , enter the pwd & id and
> i
> >> get connected
> >> >--Original Message--
> >> >maxzim
> >> >Can you show us your creation script?
> >> >This example works without creating a linked server
> >> >declare @.pid varchar(30)
> >> >set @.pid='2450'
> >> >
> >> >
> >> >select id from openrowset('sqloledb',
> >> >'server';'sa';'pass',database.dbo.table)
> >> >where id > @.pid
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> >> >> Hi ,
> >> >>
> >> >> I have tried to add a linked server(sql2000)
> residing
> >> >> at location B from Location A (a server
> computer ,using
> >> >> SQL 7.0) but i got the error "general network error ,
> >> pls
> >> >> check network documentation" I have gotten the
> correct
> >> >> login id & pwd from the user at the other end.
> >> >>
> >> >> have also configured the port#(under Client
> Utility)
> >> >> correctly for client connection and also triedthe
> >> >> sp_addlinkedservers & sp_addsvrlogin already
> >> >>
> >> >> Is there any dll file needed ? such as
> DBNET.dll ? to
> >> >> add linked server from sql ver 7.0 to sql2000 ?
> >> >>
> >> >> Does it need any specific version of the odbc
> driver
> >> >> for SQL Server ? current it's version is 3.70.10.63 i
> >> have
> >> >> tried to add and odbc link but still have the same
> error
> >> >>
> >> >> I have no problem when using Query Analyzer from my
> >> >> client computer AND the user from the other end is
> able
> >> to
> >> >> add my sqlserver successfully as a linked server. The
> >> odbc
> >> >> driver for SQL Server is 3.85.xx.xx
> >> >>
> >> >> Appreciate if somebody could help me out as i am
> doing
> >> >> some data migration which i have set up some DTS
> jobs to
> >> >> copy data daily over instead of one big chunks the
> day
> >> >> before migration
> >> >>
> >> >> many thks
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hi Uri ,
i'll have to check wif the administrator at the other
end then
thks for all the help given =)
>--Original Message--
>Hi
>Probably this is a permission issue. Make sure that you
have full
>permissions on destination server.
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:673f01c493dc$63f279e0$a601280a@.phx.gbl...
>> Hi Uri ,
>> i have used the openrowset but it says "server does not
>> exist or access denied" which i am using the
>> administrator's id & pwd
>> rdgs
>> >--Original Message--
>> >Hi
>> >Do you have specified Server Type in the first tab when
>> you use EM?
>> >
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
>> >> Hi Uri ,
>> >> thks will try out urs
>> >> but for my creation script
>> >>
>> >> i have used the Enterprise Manager and just
specify
>> the
>> >> remote user & pwd
>> >>
>> >> for the query analyzer on the sql server i used :
>> >>
>> >> sp_addlinkedserver 'serverA' -- it's using sql
server
>> so
>> >> i did not specify the rest of the connection
>> >>
>> >> sp_addsvrlogin 'serverA' ,
>> >> false , 'sa', 'userid' , 'userpassword'
>> >>
>> >> for the query analyzer from my local client
>> computer ,
>> >> i simply enter the server name , enter the pwd & id
and
>> i
>> >> get connected
>> >> >--Original Message--
>> >> >maxzim
>> >> >Can you show us your creation script?
>> >> >This example works without creating a linked server
>> >> >declare @.pid varchar(30)
>> >> >set @.pid='2450'
>> >> >
>> >> >
>> >> >select id from openrowset('sqloledb',
>> >> >'server';'sa';'pass',database.dbo.table)
>> >> >where id > @.pid
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >"maxzsim" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message
>> >> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> >> >> Hi ,
>> >> >>
>> >> >> I have tried to add a linked server(sql2000)
>> residing
>> >> >> at location B from Location A (a server
>> computer ,using
>> >> >> SQL 7.0) but i got the error "general network
error ,
>> >> pls
>> >> >> check network documentation" I have gotten the
>> correct
>> >> >> login id & pwd from the user at the other end.
>> >> >>
>> >> >> have also configured the port#(under Client
>> Utility)
>> >> >> correctly for client connection and also triedthe
>> >> >> sp_addlinkedservers & sp_addsvrlogin already
>> >> >>
>> >> >> Is there any dll file needed ? such as
>> DBNET.dll ? to
>> >> >> add linked server from sql ver 7.0 to sql2000 ?
>> >> >>
>> >> >> Does it need any specific version of the odbc
>> driver
>> >> >> for SQL Server ? current it's version is
3.70.10.63 i
>> >> have
>> >> >> tried to add and odbc link but still have the same
>> error
>> >> >>
>> >> >> I have no problem when using Query Analyzer
from my
>> >> >> client computer AND the user from the other end is
>> able
>> >> to
>> >> >> add my sqlserver successfully as a linked server.
The
>> >> odbc
>> >> >> driver for SQL Server is 3.85.xx.xx
>> >> >>
>> >> >> Appreciate if somebody could help me out as i am
>> doing
>> >> >> some data migration which i have set up some DTS
>> jobs to
>> >> >> copy data daily over instead of one big chunks the
>> day
>> >> >> before migration
>> >> >>
>> >> >> many thks
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>

Saturday, February 25, 2012

add extra path to new component fails

I have a derived column component which has an regular output and an error output.

This works fine.

I want to add an extra regular output path.

I create a new OLEDB destination component and name it X

I right click on the derived column component and select "add path"

I then select X as the recipient of the new path.

And it errors with this garbage below

How can all the inputs be used up, it is a new component! It doesn't have any inputs!

Anyone know why this is happening (apart from the fact that it is not possible for me to go more than about 4 hours without encoutering another cryptic SSIS error that has no corresponding help topic on the MS website)

error message: (once again the MS help topic returns nothing)

===================================

All the available inputs on the target component are connected to outputs. Edit this component to define new inputs before proceeding. (Microsoft Visual Studio)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=DtsComponentNoOutputsFree&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewPath(PipelineComponentMetadata sourceDtsComponentObject, PipelineComponentMetadata destinationDtsComponentObject)
at Microsoft.DataTransformationServices.Design.DataFlowDiagram.OnAddPath(MenuCommand sender, CommandHandlingArgs args)
at Microsoft.DataWarehouse.Design.CommandHandlingInfoMap.HandleCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.Design.ComponentDiagram.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataTransformationServices.Design.DataFlowDiagram.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.Design.ComponentDiagram.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)

Most transformations have only one output. If you would like for the same output information to go to more than one component you should use the multicast transform after the derived column and have that pass on the output to as many places as you would like.|||

how stupid that it is shown as a menu option, and that it even goes through the process of allowing you to select another component

|||

Adolf,

'Add Path' option is just the equivalent of joining DF components via red/green arrows; it is not for creating additional outputs in a component.

If you need an additional data stream, you would need to use the multicast transformation or perhaps an scritp component.

Add Extra Error Columns - From The Component?

I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.

I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.

Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.

This might be possible. Try the following (I'm assuming your custom component is synchronous):

1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.

2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).

3. If you got the handle, use it in ProcessInput to set the error description before calling RedirectErrorRow.

|||

Ted Lee - MSFT wrote:

This might be possible. Try the following (I'm assuming your custom component is synchronous):

1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.

That part worked. The extra column was added to the error output. Unfortunately:

Ted Lee - MSFT wrote:

2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).

The above caused an error during PreExecute:

The output "TranslateProvidersErrorOutput" (347) is a synchronous output and the buffer type cannot be retrieved for a synchronous output.

The code I tried is:

Code Snippet

public override void ProvideComponentProperties()

{

ComponentMetaData.UsesDispositions = true;

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "TranslateProvidersInput";

input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "TranslateProvidersOutput";

output.SynchronousInputID = input.ID;

output.ExclusionGroup = 1;

// The new code:

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();

errorOutput.Name = "TranslateProvidersErrorOutput";

errorOutput.SynchronousInputID = input.ID;

errorOutput.ExclusionGroup = 1;

errorOutput.IsErrorOut = true;

IDTSOutputColumn90 extraColumn = errorOutput.OutputColumnCollection.New();

extraColumn.Name = "ErrorDescription";

extraColumn.SetDataTypeProperties(DataType.DT_WSTR, 255, 0, 0, 0);

_extraColumnLineageID = extraColumn.LineageID;

// The original code:

//AddErrorOutput(

//"TranslateProvidersErrorOutput", input.ID, output.ExclusionGroup);

IDTSRuntimeConnection90 connection =

ComponentMetaData.RuntimeConnectionCollection.New();

connection.Name = "ADONET";

}

public override void PreExecute()

{

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

errorOutput.Buffer, _extraColumnLineageID);

}

The hightlighted code is what failed.

I don't yet understand how buffers work with multiple outputs, so I can't figure out any other way to get the column index. I'm stuck at that point, for now.

If anyone has more ideas, I'll be glad to try them. One workaround I thought of was to add the extra columns to the default output and see if they get replicated to the error output when the default error output is created. This would have the unfortunate side-effect of proliferating error-only columns throughout the data flow, but should work well enough if the extra columns are truly required.

It will be necessary for components in the flow to not add one of these columns if it is already present.

|||

John,

I've got this working in my destination component: adding error messages, row #, time, etc.

The difference I think is in this line:

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

errorOutput.Buffer, _extraColumnLineageID);

The equivalent in my code would be:

IDTSInput90 input = ComponentMetaData.InputCollection[0];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

input.Buffer, _extraColumnLineageID);

It is the input buffer that actually has the slots for the new error columns.

Martin

Add Extra Error Columns - From The Component?

Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.

This might be possible. Try the following (I'm assuming your custom component is synchronous):

3. If you got the handle, use it in ProcessInput to set the error description before calling RedirectErrorRow.

|||

Ted Lee - MSFT wrote:

This might be possible. Try the following (I'm assuming your custom component is synchronous):

1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.

That part worked. The extra column was added to the error output. Unfortunately:

Ted Lee - MSFT wrote:

2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).

The above caused an error during PreExecute:

The output "TranslateProvidersErrorOutput" (347) is a synchronous output and the buffer type cannot be retrieved for a synchronous output.

The code I tried is:

Code Snippet

public override void ProvideComponentProperties()

{

ComponentMetaData.UsesDispositions = true;

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "TranslateProvidersInput";

input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "TranslateProvidersOutput";

output.SynchronousInputID = input.ID;

output.ExclusionGroup = 1;

// The new code:

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();

errorOutput.Name = "TranslateProvidersErrorOutput";

errorOutput.SynchronousInputID = input.ID;

errorOutput.ExclusionGroup = 1;

errorOutput.IsErrorOut = true;

IDTSOutputColumn90 extraColumn = errorOutput.OutputColumnCollection.New();

extraColumn.Name = "ErrorDescription";

extraColumn.SetDataTypeProperties(DataType.DT_WSTR, 255, 0, 0, 0);

_extraColumnLineageID = extraColumn.LineageID;

// The original code:

//AddErrorOutput(

//"TranslateProvidersErrorOutput", input.ID, output.ExclusionGroup);

IDTSRuntimeConnection90 connection =

ComponentMetaData.RuntimeConnectionCollection.New();

connection.Name = "ADONET";

}

public override void PreExecute()

{

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

errorOutput.Buffer, _extraColumnLineageID);

}

The hightlighted code is what failed.

I don't yet understand how buffers work with multiple outputs, so I can't figure out any other way to get the column index. I'm stuck at that point, for now.

It will be necessary for components in the flow to not add one of these columns if it is already present.

|||

John,

I've got this working in my destination component: adding error messages, row #, time, etc.

The difference I think is in this line:

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

errorOutput.Buffer, _extraColumnLineageID);

The equivalent in my code would be:

IDTSInput90 input = ComponentMetaData.InputCollection[0];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

input.Buffer, _extraColumnLineageID);

It is the input buffer that actually has the slots for the new error columns.

Martin

Friday, February 24, 2012

Add connection error: 40

Hi i just reinstalled windows, and installed visual studio .net 2005 and the Sql server.

But when im trying to add connection, select my computer at Server

name, type a databasename then pressing OK i get this message:

An error has occured while estabilishing a connection to the server.

When connection to SQL server 2005, this faliure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections. (provider: Names pipes provider, error: 40 - Could not

open a connection to SQL Server)

Does anyone know what todo?

hermanTry this: open the SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, highlight protocols for MSSQLSERVER and enable TCIP/IP.

Mike

Add column with variable name

HI!

Im trying to use an stored procedure to add a new column.
The problem is that the column name is stored in a variable, and I get syntax error.

Here is my code:

CREATE PROCEDURE test AS
declare @.colname as varchar(10)

set @.colname = 'new_col'

alter table prueba add @.colname int

And here is the error I get:
Error 170: Line 6 Incorrect syntax near '@.colname'

Any idea?Originally posted by soy_lore_lore
HI!

Im trying to use an stored procedure to add a new column.
The problem is that the column name is stored in a variable, and I get syntax error.

Here is my code:

CREATE PROCEDURE test AS
declare @.colname as varchar(10)

set @.colname = 'new_col'

alter table prueba add @.colname int

And here is the error I get:
Error 170: Line 6 Incorrect syntax near '@.colname'

Any idea?

Try this:

exec('alter table prueba add '+@.colname+' int')|||Originally posted by snail
Try this:

exec('alter table prueba add '+@.colname+' int')

THANKS!

Sunday, February 12, 2012

Ad Hoc Distributed Queries Error

I am getting the following error when with SQL Express.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on...

I tried sp_configure'Ad Hoc Distributed Queries', 1 but got the following error

The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have...

Could someone help me out with this?

Thanks - Mark

As 'Ad Hoc Distributed Queries' is an advanced option, you need to turn on advanced options. Try the statements below:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

|||

FANTASTIC! Quick and easy!

That works - Thanks

Ad hoc access to OLE DB provider MSOLAP has been denied

Hi:

When I am trying to connect to OLAP I am getting this error...Ad hoc access to OLE DB provider MSOLAP has been denied.You must access this provider through a linked server.

I have configured the linked server.How do i fix this...?

Thanks,

Pramod

This is not an AS specific issue and out of my area of expertise, but it appears to be a security issue. Doing a quick search on the error message it appears by default that only SQL system admins are allowed to use OpenRowset/OpenDataSource queries. The information at http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.oledbprovidersettings.disallowadhocaccess.aspx may be usefull in resolving this.

ActveX Pull error

I have a publication created using SQLDMO on the server.
.PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull
+ _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us
I am trying to get a Pull to work from a laptop (in test mode connected
to the main SQL machine via ethernet, but in the end will be connected
via the internet).
The Pull is done via ActiveX:
With myMergeObj
.Distributor = "IIS-SQL"
.DistributorLogin = login
.DistributorPassword = password
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = "SAMPLE"
.Publication = "SAMPLE-RS"
.PublisherLogin = login
.PublisherPassword = password
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = "SAMPLE"
.SubscriberSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.PULL
.SubscriberLogin = login
.SubscriberPassword = password
.SubscriptionName = "SAMPLE-RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
This errors with:
Server 'IIS-LAPTOP' is not registered at server 'IIS-SQL'.
I thought for PULL all I had to do was create the subscription for
anonymous access. What am I missing?
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
You will need to use the PublisherNetwork and DistributorNetwork and set
them to TCPIP_SOCKETS (a value of 1), and set the PublisherAddress and
DistributorAddress to the IP address of the Publisher or its FQDN (i.e.
Publisher.Microsoft.com).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darin" <darin_nospam@.nospamever> wrote in message
news:OApUxNvgFHA.3220@.TK2MSFTNGP10.phx.gbl...
> I have a publication created using SQLDMO on the server.
> PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull
> + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us
> I am trying to get a Pull to work from a laptop (in test mode connected
> to the main SQL machine via ethernet, but in the end will be connected
> via the internet).
> The Pull is done via ActiveX:
> With myMergeObj
> .Distributor = "IIS-SQL"
> .DistributorLogin = login
> .DistributorPassword = password
> .DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .Publisher = inxRS.Globals.gServer
> .PublisherDatabase = "SAMPLE"
> .Publication = "SAMPLE-RS"
> .PublisherLogin = login
> .PublisherPassword = password
> .PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .Subscriber = Trim(Environment.MachineName)
> .SubscriberDatabase = "SAMPLE"
> .SubscriberSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .SubscriptionType = SUBSCRIPTION_TYPE.PULL
> .SubscriberLogin = login
> .SubscriberPassword = password
> .SubscriptionName = "SAMPLE-RS"
> .HostName = Trim(inxRS.Globals.gLogin)
> .ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
> End With
> myMergeObj.Initialize()
> myMergeObj.Run()
> myMergeObj.Terminate()
>
> This errors with:
> Server 'IIS-LAPTOP' is not registered at server 'IIS-SQL'.
> I thought for PULL all I had to do was create the subscription for
> anonymous access. What am I missing?
> Thanks.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||I got further, thanks. But, no I get the error:
The schema script
'c:\inware\data\unc\IIS-SQL_inware_inware-RS\20050707140259\UTCompany_1.
sch' could not be propagated to the subscriber.
The process could not read file 'same file as above' due to OS error 3.
The system cannot find the path specified.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
the problem would appear to be with the location of the working folder -
it's using the c drive of the distributor. If you change it to use a file
share, reinitialize, run the snapshot agent then synchronize it should be
fine.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tuesday, March 27, 2012

Adding a new report to VS 2005

Sunday, March 25, 2012

Adding A default value in a column in a declare statment

Tuesday, March 20, 2012

Added SP2 now have linked server error

Added SP2 now have linked server error

Monday, March 19, 2012

Add user to Local Group Error

Sunday, March 11, 2012

Add Reporting Services to Trusted Accounts

Thursday, March 8, 2012

Add NT users

Tuesday, March 6, 2012

Add New Database Error "object reference not set to an instance of an object"

Add member failed for DatabaseRole 'db_datareader'

add linked server

add linked server

Saturday, February 25, 2012

add extra path to new component fails

Add Extra Error Columns - From The Component?

Add Extra Error Columns - From The Component?

Friday, February 24, 2012

Add connection error: 40

Add column with variable name

Sunday, February 12, 2012

Ad Hoc Distributed Queries Error

Ad hoc access to OLE DB provider MSOLAP has been denied

ActveX Pull error

Accessing more than

Blog Archive

About Me