Error accessing report: string

Showing posts with label string. Show all posts

Tuesday, March 27, 2012

Error encountered when creating a new datasource

Hello:
I am new with the Reporting services.
When I tried creating a data source with the following connection string
with the following options:
Conenction Type: SQLServer or OLE DB
Connect using: credentials supplied by the user running the report)
Connection String:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
Catalog=Northwind;Data Source=venkat-wxp2;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=VENKAT-WXP2;Use Encryption for
Data=False;Tag with column collation when possible=False
I get the following error:
The underlying connection was closed: Could not establish trust relationship
with remote server.
I am on a standalone Windows 2003 Server named venkat-wxp2.
Any help will be appreciated. Thanks in advance.
VenkiTry a simple connection string first and see if it works.
Initial Catalog=Northwind;Data Source=venkat-wxp2;
--
| Thread-Topic: Error encountered when creating a new datasource
| thread-index: AcVrkf5jtPegPMTmTqyEkHE51Kcf9A==| X-WBNR-Posting-Host: 24.56.142.17
| From: "=?Utf-8?B?dnZlbms=?=" <vvenk@.discussions.microsoft.com>
| Subject: Error encountered when creating a new datasource
| Date: Tue, 7 Jun 2005 11:52:03 -0700
| Lines: 28
| Message-ID: <577C1F19-4A5B-4E75-B15D-C81CFDD9BD9D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45474
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello:
|
| I am new with the Reporting services.
|
| When I tried creating a data source with the following connection string
| with the following options:
| Conenction Type: SQLServer or OLE DB
| Connect using: credentials supplied by the user running the report)
| Connection String:
|
| Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
| Catalog=Northwind;Data Source=venkat-wxp2;Use Procedure for
Prepare=1;Auto
| Translate=True;Packet Size=4096;Workstation ID=VENKAT-WXP2;Use Encryption
for
| Data=False;Tag with column collation when possible=False
|
| I get the following error:
|
| The underlying connection was closed: Could not establish trust
relationship
| with remote server.
|
| I am on a standalone Windows 2003 Server named venkat-wxp2.
|
| Any help will be appreciated. Thanks in advance.
|
| Venki
|
|
|
||||Brad:
Yes, I did try the simpler connection string. I encounter the same issue.
Thanks.
venki
""Brad Syputa - MS"" wrote:
> Try a simple connection string first and see if it works.
> Initial Catalog=Northwind;Data Source=venkat-wxp2;
> --
> | Thread-Topic: Error encountered when creating a new datasource
> | thread-index: AcVrkf5jtPegPMTmTqyEkHE51Kcf9A==> | X-WBNR-Posting-Host: 24.56.142.17
> | From: "=?Utf-8?B?dnZlbms=?=" <vvenk@.discussions.microsoft.com>
> | Subject: Error encountered when creating a new datasource
> | Date: Tue, 7 Jun 2005 11:52:03 -0700
> | Lines: 28
> | Message-ID: <577C1F19-4A5B-4E75-B15D-C81CFDD9BD9D@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45474
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hello:
> |
> | I am new with the Reporting services.
> |
> | When I tried creating a data source with the following connection string
> | with the following options:
> | Conenction Type: SQLServer or OLE DB
> | Connect using: credentials supplied by the user running the report)
> | Connection String:
> |
> | Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
> | Catalog=Northwind;Data Source=venkat-wxp2;Use Procedure for
> Prepare=1;Auto
> | Translate=True;Packet Size=4096;Workstation ID=VENKAT-WXP2;Use Encryption
> for
> | Data=False;Tag with column collation when possible=False
> |
> | I get the following error:
> |
> | The underlying connection was closed: Could not establish trust
> relationship
> | with remote server.
> |
> | I am on a standalone Windows 2003 Server named venkat-wxp2.
> |
> | Any help will be appreciated. Thanks in advance.
> |
> | Venki
> |
> |
> |
> |
>|||While I do not know the cause of your error, a good article to read is at :
http://www.awprofessional.com/articles/article.asp?p=357694&rl=1
--
| Thread-Topic: Error encountered when creating a new datasource
| thread-index: AcVrvSNw13tsTe+nTPeEUMl0Hpblcw==| X-WBNR-Posting-Host: 24.56.142.17
| From: "=?Utf-8?B?dnZlbms=?=" <vvenk@.discussions.microsoft.com>
| References: <577C1F19-4A5B-4E75-B15D-C81CFDD9BD9D@.microsoft.com>
<btNl$X6aFHA.2476@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Error encountered when creating a new datasource
| Date: Tue, 7 Jun 2005 17:00:53 -0700
| Lines: 71
| Message-ID: <12ECD0E6-E614-4932-96D5-1AEF2B80B125@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45502
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Brad:
|
| Yes, I did try the simpler connection string. I encounter the same issue.
|
| Thanks.
|
| venki
|
| ""Brad Syputa - MS"" wrote:
|
| > Try a simple connection string first and see if it works.
| >
| > Initial Catalog=Northwind;Data Source=venkat-wxp2;
| > --
| > | Thread-Topic: Error encountered when creating a new datasource
| > | thread-index: AcVrkf5jtPegPMTmTqyEkHE51Kcf9A==| > | X-WBNR-Posting-Host: 24.56.142.17
| > | From: "=?Utf-8?B?dnZlbms=?=" <vvenk@.discussions.microsoft.com>
| > | Subject: Error encountered when creating a new datasource
| > | Date: Tue, 7 Jun 2005 11:52:03 -0700
| > | Lines: 28
| > | Message-ID: <577C1F19-4A5B-4E75-B15D-C81CFDD9BD9D@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.reportingsvcs:45474
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Hello:
| > |
| > | I am new with the Reporting services.
| > |
| > | When I tried creating a data source with the following connection
string
| > | with the following options:
| > | Conenction Type: SQLServer or OLE DB
| > | Connect using: credentials supplied by the user running the report)
| > | Connection String:
| > |
| > | Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
| > | Catalog=Northwind;Data Source=venkat-wxp2;Use Procedure for
| > Prepare=1;Auto
| > | Translate=True;Packet Size=4096;Workstation ID=VENKAT-WXP2;Use
Encryption
| > for
| > | Data=False;Tag with column collation when possible=False
| > |
| > | I get the following error:
| > |
| > | The underlying connection was closed: Could not establish trust
| > relationship
| > | with remote server.
| > |
| > | I am on a standalone Windows 2003 Server named venkat-wxp2.
| > |
| > | Any help will be appreciated. Thanks in advance.
| > |
| > | Venki
| > |
| > |
| > |
| > |
| >
| >
||||Brad:
I just de-installed SQL Server 2000 and installed SQL Server 2005 instead
and I was able to create a datasource.
Thanks.
""Brad Syputa - MS"" wrote:
> While I do not know the cause of your error, a good article to read is at :
> http://www.awprofessional.com/articles/article.asp?p=357694&rl=1
> --
> | Thread-Topic: Error encountered when creating a new datasource
> | thread-index: AcVrvSNw13tsTe+nTPeEUMl0Hpblcw==> | X-WBNR-Posting-Host: 24.56.142.17
> | From: "=?Utf-8?B?dnZlbms=?=" <vvenk@.discussions.microsoft.com>
> | References: <577C1F19-4A5B-4E75-B15D-C81CFDD9BD9D@.microsoft.com>
> <btNl$X6aFHA.2476@.TK2MSFTNGXA01.phx.gbl>
> | Subject: RE: Error encountered when creating a new datasource
> | Date: Tue, 7 Jun 2005 17:00:53 -0700
> | Lines: 71
> | Message-ID: <12ECD0E6-E614-4932-96D5-1AEF2B80B125@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45502
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Brad:
> |
> | Yes, I did try the simpler connection string. I encounter the same issue.
> |
> | Thanks.
> |
> | venki
> |
> | ""Brad Syputa - MS"" wrote:
> |
> | > Try a simple connection string first and see if it works.
> | >
> | > Initial Catalog=Northwind;Data Source=venkat-wxp2;
> | > --
> | > | Thread-Topic: Error encountered when creating a new datasource
> | > | thread-index: AcVrkf5jtPegPMTmTqyEkHE51Kcf9A==> | > | X-WBNR-Posting-Host: 24.56.142.17
> | > | From: "=?Utf-8?B?dnZlbms=?=" <vvenk@.discussions.microsoft.com>
> | > | Subject: Error encountered when creating a new datasource
> | > | Date: Tue, 7 Jun 2005 11:52:03 -0700
> | > | Lines: 28
> | > | Message-ID: <577C1F19-4A5B-4E75-B15D-C81CFDD9BD9D@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:45474
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | Hello:
> | > |
> | > | I am new with the Reporting services.
> | > |
> | > | When I tried creating a data source with the following connection
> string
> | > | with the following options:
> | > | Conenction Type: SQLServer or OLE DB
> | > | Connect using: credentials supplied by the user running the report)
> | > | Connection String:
> | > |
> | > | Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
> | > | Catalog=Northwind;Data Source=venkat-wxp2;Use Procedure for
> | > Prepare=1;Auto
> | > | Translate=True;Packet Size=4096;Workstation ID=VENKAT-WXP2;Use
> Encryption
> | > for
> | > | Data=False;Tag with column collation when possible=False
> | > |
> | > | I get the following error:
> | > |
> | > | The underlying connection was closed: Could not establish trust
> | > relationship
> | > | with remote server.
> | > |
> | > | I am on a standalone Windows 2003 Server named venkat-wxp2.
> | > |
> | > | Any help will be appreciated. Thanks in advance.
> | > |
> | > | Venki
> | > |
> | > |
> | > |
> | > |
> | >
> | >
> |
>

Monday, March 19, 2012

Error Creating Function

I want to calculate a string, so I have created this Function, but its
giving error any idea please ?
Best Regards,
Luqman
Create function eval
(@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
declare @.myString varchar(50)
select @.mystring=exec('select '+@.Mycalcstring)
return(@.myString)
endHi
I am not sure why you don't use cast/convert directly, although this would
depend on the quality of what you are passing to the function.
Create function eval (@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
return(CAST(@.Mycalcstring AS decimal(12,2)))
end
SELECT dbo.eval('12.2')
John
"Luqman" wrote:

> I want to calculate a string, so I have created this Function, but its
> giving error any idea please ?
> Best Regards,
> Luqman
> Create function eval
> (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> declare @.myString varchar(50)
> select @.mystring=exec('select '+@.Mycalcstring)
> return(@.myString)
> end
>
>|||Say, I want to calculate this string : '(10-5)*7/2'
select cast('(10-5)*7/2' AS decimal(12,2))
will give error :
Error converting data type varchar to numeric.
But if I use:
Select (10-5)*7/2 ' It will return 17 which is Correct Result.
So, please advise whats wrong with my function.
Best Regards,
Luqman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:CD3C2647-4BAF-4499-A6B9-06D275405010@.microsoft.com...
> Hi
> I am not sure why you don't use cast/convert directly, although this would
> depend on the quality of what you are passing to the function.
> Create function eval (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> return(CAST(@.Mycalcstring AS decimal(12,2)))
> end
> SELECT dbo.eval('12.2')
> John
> "Luqman" wrote:
>|||Luqman wrote:
> Say, I want to calculate this string : '(10-5)*7/2'
> select cast('(10-5)*7/2' AS decimal(12,2))
> will give error :
> Error converting data type varchar to numeric.
> But if I use:
> Select (10-5)*7/2 ' It will return 17 which is Correct Result.
> So, please advise whats wrong with my function.
> Best Regards,
> Luqman
Dont use the ' because they indicate a character column.
SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||Such calculations are stored in a Table with Varchar Type, so I need to
calculate their values, and as such I need this function.
Following lines will work, but how can I put the select result in Variable.
declare @.myString as varchar(50)
declare @.myValue as Decimal(12,2)
set @.myString='(10-5)/2'
exec ('select ' + @.myvalue) 'This works
set @.myValue=exec ('select ' + @.myvalue) 'This does not work
Best Regards,
Luqman
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
> Luqman wrote:
> Dont use the ' because they indicate a character column.
> SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue
hth,
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Such calculations are stored in a Table with Varchar Type, so I need to
> calculate their values, and as such I need this function.
> Following lines will work, but how can I put the select result in
Variable.
> declare @.myString as varchar(50)
> declare @.myValue as Decimal(12,2)
> set @.myString='(10-5)/2'
> exec ('select ' + @.myvalue) 'This works
> set @.myValue=exec ('select ' + @.myvalue) 'This does not work
> Best Regards,
> Luqman
>
>
> "Jo Segers" <jo.segers@.alro.be> wrote in message
> news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
>|||avnrao wrote:
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
>
Or if you realy want your 2 decimals:
declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = 1.0*' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue|||Hi,
I have created following function in master database and when select it with
:
Select dbo.eval('10-5')
Error occured:
Server: Msg 557, Level 16, State 2, Procedure eval, Line 9
Only functions and extended stored procedures can be executed from within a
function.
Any idea please?
create function eval
(@.myString as varchar(50))
returns decimal(12,2)
as
begin
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
return(@.myValue)
end
"avnrao" <avn@.newsgroups.com> wrote in message
news:u8v6gEQDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Variable.
>|||You cannot run dynamic SQL from within a function. EXEC and
sp_executesql are not allowed. Either use an SP or, better still IMO,
do it in some external code. I don't understand why you would want to
do this in a database.
Also, I wouldn't recommend putting functions or other objects in Master
on a production system.
David Portas
SQL Server MVP
--|||Hi,
I just want to create a function which should calculate a String, and return
the result, someone recommended this way ?
If you have better solution, please adv.
Best Regards,
Luqman
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107775757.602219.248630@.o13g2000cwo.googlegroups.com...
> You cannot run dynamic SQL from within a function. EXEC and
> sp_executesql are not allowed. Either use an SP or, better still IMO,
> do it in some external code. I don't understand why you would want to
> do this in a database.
> Also, I wouldn't recommend putting functions or other objects in Master
> on a production system.
> --
> David Portas
> SQL Server MVP
> --
>

Friday, March 9, 2012

Error connectiong to database

Hi,
I am designing a webpage with the DbNetGrid control on it. I am folloing
its documentation and using the following connection string to connect the
grid control to a database:
'Provider=SQLOLEDB;Driver={SQL
Server};Server=(local);Database=pubs;Tru
sted_Connection=yes;'
But DbNetGrid keeps giving me the following error. I have tried different
variations of this connection string but nothing seems to work.
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified (-2147467259)
Thanks.helpful sql wrote:
> Hi,
> I am designing a webpage with the DbNetGrid control on it. I am folloin
g
> its documentation and using the following connection string to connect the
> grid control to a database:
> 'Provider=SQLOLEDB;Driver={SQL
> Server};Server=(local);Database=pubs;Tru
sted_Connection=yes;'
> But DbNetGrid keeps giving me the following error. I have tried different
> variations of this connection string but nothing seems to work.
> [Microsoft][ODBC Driver Manager] Data source name not found and no default
> driver specified (-2147467259)
You have to create ODBC Data Source object as the message states.
Look for Data Sources icon in the Control Panel/Administrative Tools.
Then use its name in the connection string.|||Not neccessarily.
http://www.connectionstrings.com
And no need really to cross-post so much!
Jeff
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:a2P_f.23982$NS6.16336@.newssvr30.news.prodigy.com...
> helpful sql wrote:
> You have to create ODBC Data Source object as the message states.
> Look for Data Sources icon in the Control Panel/Administrative Tools.
> Then use its name in the connection string.|||Jeff Dillon wrote:
> Not neccessarily.
> http://www.connectionstrings.com
Thanks, I did not know that.

> And no need really to cross-post so much!
I hope your intention was to say that to the original poster.

> Jeff
> "Sericinus hunter" <serhunt@.flash.net> wrote in message
> news:a2P_f.23982$NS6.16336@.newssvr30.news.prodigy.com...
>|||No, I had already tried this website and all their examples for Sql Server
2000 connection strings. They did work from my test application but they did
not work with DbNetGrid. I had to create an ODBC Data Source in
Administrative Tools. Then it worked fine.
Thanks all for help.
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:ACR_f.3328$mu2.302@.newssvr24.news.prodigy.net...
> Jeff Dillon wrote:
> Thanks, I did not know that.
>
> I hope your intention was to say that to the original poster.
>

Error connecting to SQL Server 2005 Express from Access

I am trying to connect to SQL Server 2005 Express from Access using a
DSN-less connection string, but I keep encountering errors. What happens is
that I have a login form with 2 text boxes for users to enter their username
and password. When they have entered them, they click OK and Access tries to
connect using the connection string below.
After waiting a while I get an error, however the SQL Server Login box
appears and I am able to login using my sa login. So, since I know I can
connect to SQL Server, I think the problem must be with my connection string:
stConnect = "ODBC;DRIVER={SQL Native Client}" _
& ";Trusted_Connection=no" _
& ";SERVER=CHRIS\SQLEXPRESS" _
& ";Address=10.0.0.17,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB 2005_11_11SQL" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"
These are the error messages I get when I try to connect:
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]TCP Provider: No connection could be made
because the target machine actively refused it.
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
I should mention that I was able to use a similar connection string to
connect to an MSDE instance with no problems, so if need be I can just use
MSDE. Still, I would prefer to use SQL Server Express since it doesn't have
a workload governor. Also, this is on my local machine, so there shouldn't
be any network issues.
I would appreciate any assistance that anyone can offer.
Thanks,
Chris
By default SQL Express does not accept remote connections over TCP or Named
Pipes, you need to enable that explicitly.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>I am trying to connect to SQL Server 2005 Express from Access using a
> DSN-less connection string, but I keep encountering errors. What happens
> is
> that I have a login form with 2 text boxes for users to enter their
> username
> and password. When they have entered them, they click OK and Access tries
> to
> connect using the connection string below.
> After waiting a while I get an error, however the SQL Server Login box
> appears and I am able to login using my sa login. So, since I know I can
> connect to SQL Server, I think the problem must be with my connection
> string:
> stConnect = "ODBC;DRIVER={SQL Native Client}" _
> & ";Trusted_Connection=no" _
> & ";SERVER=CHRIS\SQLEXPRESS" _
> & ";Address=10.0.0.17,1433" _
> & ";Network=DBMSSOCN" _
> & ";DATABASE=authorDB 2005_11_11SQL" _
> & ";UID=" & stUID _
> & ";PWD=" & stPWD & ";"
> These are the error messages I get when I try to connect:
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]TCP Provider: No connection could be made
> because the target machine actively refused it.
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]An error has occurred while establishing a
> connection to the server. When connecting to SQL Server 2005, this
> failure
> may be caused by the fact that under the default settings SQL Server does
> not
> allow remote connections.
> Connection failed:
> SQLState: 'S1T00'
> SQL Server Error: 0
> [Microsoft][SQL Native Client]Login timeout expired
>
> I should mention that I was able to use a similar connection string to
> connect to an MSDE instance with no problems, so if need be I can just use
> MSDE. Still, I would prefer to use SQL Server Express since it doesn't
> have
> a workload governor. Also, this is on my local machine, so there
> shouldn't
> be any network issues.
> I would appreciate any assistance that anyone can offer.
> Thanks,
> Chris
|||Gert, I know that. I started the Browser and enabled TCP/IP. Like I said, I
can connect using the sa login, and techinically it's not a remote connection
as it's on a local machine. Thanks for the response, but I already checked
and that isn't the problem.
-Chris
"Gert E.R. Drapers" wrote:

> By default SQL Express does not accept remote connections over TCP or Named
> Pipes, you need to enable that explicitly.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
> news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>
>
|||I figured it out. I just needed to change the default port for TCP/IP since
MSDE was using port 1433.
-Chris
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16-2A89D9672C1B@.microsoft.com...[vbcol=seagreen]
> Gert, I know that. I started the Browser and enabled TCP/IP. Like I
> said, I
> can connect using the sa login, and techinically it's not a remote
> connection
> as it's on a local machine. Thanks for the response, but I already
> checked
> and that isn't the problem.
> -Chris
> "Gert E.R. Drapers" wrote:

Error connecting to SQL Server 2005 Express from Access

I am trying to connect to SQL Server 2005 Express from Access using a
DSN-less connection string, but I keep encountering errors. What happens is
that I have a login form with 2 text boxes for users to enter their username
and password. When they have entered them, they click OK and Access tries t
o
connect using the connection string below.
After waiting a while I get an error, however the SQL Server Login box
appears and I am able to login using my sa login. So, since I know I can
connect to SQL Server, I think the problem must be with my connection string
:
stConnect = "ODBC;DRIVER={SQL Native Client}" _
& ";Trusted_Connection=no" _
& ";SERVER=CHRIS\SQLEXPRESS" _
& ";Address=10.0.0.17,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB 2005_11_11SQL" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"
These are the error messages I get when I try to connect:
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]TCP Provider: No connection could be m
ade
because the target machine actively refused it.
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]An error has occurred while establishi
ng a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does no
t
allow remote connections.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
I should mention that I was able to use a similar connection string to
connect to an MSDE instance with no problems, so if need be I can just use
MSDE. Still, I would prefer to use SQL Server Express since it doesn't have
a workload governor. Also, this is on my local machine, so there shouldn't
be any network issues.
I would appreciate any assistance that anyone can offer.
Thanks,
ChrisBy default SQL Express does not accept remote connections over TCP or Named
Pipes, you need to enable that explicitly.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>I am trying to connect to SQL Server 2005 Express from Access using a
> DSN-less connection string, but I keep encountering errors. What happens
> is
> that I have a login form with 2 text boxes for users to enter their
> username
> and password. When they have entered them, they click OK and Access tries
> to
> connect using the connection string below.
> After waiting a while I get an error, however the SQL Server Login box
> appears and I am able to login using my sa login. So, since I know I can
> connect to SQL Server, I think the problem must be with my connection
> string:
> stConnect = "ODBC;DRIVER={SQL Native Client}" _
> & ";Trusted_Connection=no" _
> & ";SERVER=CHRIS\SQLEXPRESS" _
> & ";Address=10.0.0.17,1433" _
> & ";Network=DBMSSOCN" _
> & ";DATABASE=authorDB 2005_11_11SQL" _
> & ";UID=" & stUID _
> & ";PWD=" & stPWD & ";"
> These are the error messages I get when I try to connect:
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]TCP Provider: No connection could be
made
> because the target machine actively refused it.
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]An error has occurred while establis
hing a
> connection to the server. When connecting to SQL Server 2005, this
> failure
> may be caused by the fact that under the default settings SQL Server does
> not
> allow remote connections.
> Connection failed:
> SQLState: 'S1T00'
> SQL Server Error: 0
> [Microsoft][SQL Native Client]Login timeout expired
>
> I should mention that I was able to use a similar connection string to
> connect to an MSDE instance with no problems, so if need be I can just use
> MSDE. Still, I would prefer to use SQL Server Express since it doesn't
> have
> a workload governor. Also, this is on my local machine, so there
> shouldn't
> be any network issues.
> I would appreciate any assistance that anyone can offer.
> Thanks,
> Chris|||Gert, I know that. I started the Browser and enabled TCP/IP. Like I said,
I
can connect using the sa login, and techinically it's not a remote connectio
n
as it's on a local machine. Thanks for the response, but I already checked
and that isn't the problem.
-Chris
"Gert E.R. Drapers" wrote:

> By default SQL Express does not accept remote connections over TCP or Name
d
> Pipes, you need to enable that explicitly.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in messag
e
> news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>
>|||I figured it out. I just needed to change the default port for TCP/IP since
MSDE was using port 1433.
-Chris
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16-2A89D9672C1B@.microsoft.com...[vbcol=seagreen]
> Gert, I know that. I started the Browser and enabled TCP/IP. Like I
> said, I
> can connect using the sa login, and techinically it's not a remote
> connection
> as it's on a local machine. Thanks for the response, but I already
> checked
> and that isn't the problem.
> -Chris
> "Gert E.R. Drapers" wrote:
>

Error connecting to SQL Server 2005 - ... does not allow remote connections?

I have an ASP.NET 2.0 Website that in the connection string does not connect
to the local SQL Server but tries to connect to another SQL Server 2005
Server in another State. When I run the application from Visual Studio 2005
it connects just fine. When I published the website to the Live Server I
receive the below error:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
Any ideas?
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgCheck this url for troubleshooting:
http://blogs.msdn.com/sql_protocols.../22/506607.aspx
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Marco Napoli" <marco@.avantitecnospam.com> wrote in message
news:#14p4JoaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> I have an ASP.NET 2.0 Website that in the connection string does not
connect
> to the local SQL Server but tries to connect to another SQL Server 2005
> Server in another State. When I run the application from Visual Studio
2005
> it connects just fine. When I published the website to the Live Server I
> receive the below error:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> Any ideas?
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>
>|||Thank you.
Marco
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:euqdnWInVNe2O8zZRVnyjg@.bt.com...
> Check this url for troubleshooting:
> http://blogs.msdn.com/sql_protocols.../22/506607.aspx
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Marco Napoli" <marco@.avantitecnospam.com> wrote in message
> news:#14p4JoaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> connect
> 2005
>

Wednesday, March 7, 2012

Error connecting to remote SQL 2000 Server

I'm experiencing a problem connecting to a SQL 2000 server through my ASP code. My connection string is as follows:

<addname="TheConnectionString"connectionString="driver={Sql Server};provider=MSDASQL;server=10.0.1.42;database=dbname;uid=*********;pwd=*********"providerName="System.Data.Odbc" />

The problem doesn't occur when I run my ASP code from my workstation using VS.NET's builtin webserver. It makes the connections and executes the CRUD commands successfully. However, when I publish my site to the webserver (which resides on 10.0.1.16) it fails out with the following error:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

I've verified that the webserver can talk to the DB server by connecting to the remote DB server through SQL Enterprise Manager running locally on the webserver. If I try to do this with a DSN I get the same results. I get the same error from any other webserver on the internal network. The difference between my workstation and the internal network is that I'm using a VPN to connect to our internal network while the webservers are physically connected to it. Firewalling isn't the issue in this case because the webservers and DB server are on a trusted network. I've seen other ways of connecting to the DB server including using Named Pipes (which I would rather not do because I don't want to setup a named pipe on the production db server).

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know. I've been racking my brains trying to get this to work outside of the devel env.

Try with this:

<add name="TheConnectionString" connectionString="Server=10.0.1.42;Database=dbname;User ID=*********;Password=*********;"/>

|||

johnladda:
System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

First, this error is a generic one so your actual problem may range from incorrect credentials to a network problem.

Second, I'm not too sure why you've used ODBC library to connect to SQL while you've the SQL library.

johnladda:
I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know.

Third, you can use "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" to connect to SQL Server. You can referwww.connectionstrings.com . This site provides a number of connection string examples for almost every database.

Hope this will help.

|||

This site is fantastic. It's exactly what I needed. I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

|||

johnladda:
I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

No, you've mistaken the Data Source property of the connection string. It is no way similar to any server control. In fact, you can user Data Source or Server property in your connection string. Below are some of the alternatives you can use in your connection string.

Data Source = Server
Initial Catalog = Database
User Id = uId
Password = pwd

The default port is 1433 for SQL Server TCP/IP connection. You can specify any other port in your connection string as below:

"Data Source=xxx,portNumber;Initial Catalog=xxx;User ID=xxx;Password=xxx". That is, after either the IP or the name of the server put a comma and then provide the port number.

Sunday, February 26, 2012

Error concatenating string

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@.@.SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep@.job_id = @.JobID,
@.step_id = 1,
@.step_name = N'Import OCC Series Data',
@.command = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME,
@.database_name = N'',
@.server = N'',
@.database_user_name = N'',
@.subsystem = N'CmdExec',
@.cmdexec_success_code = 0,
@.flags = 2,
@.retry_attempts = 0,
@.retry_interval = 1,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 3,
@.on_fail_step_id = 0,
@.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME, everything works fine. I even tried declaring a local
variable named @.command and setting it in the select statement, but no
dice."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0402051028.4aa7015e@.posting.google.c om...
> I am trying to create a job that, as one of its steps, will kick off a
> DTS package. As part of the command parameter, I need to concat a
> system variable (@.@.SERVERNAME) to a constant string. I am receiving an
> error about incorrect syntax near the +.
> Here is the code for the job step.
> -- Add the job steps
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
> @.step_id = 1,
> @.step_name = N'Import OCC Series Data',
> @.command = N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME,
> @.database_name = N'',
> @.server = N'',
> @.database_user_name = N'',
> @.subsystem = N'CmdExec',
> @.cmdexec_success_code = 0,
> @.flags = 2,
> @.retry_attempts = 0,
> @.retry_interval = 1,
> @.output_file_name = N'',
> @.on_success_step_id = 0,
> @.on_success_action = 3,
> @.on_fail_step_id = 0,
> @.on_fail_action = 3
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> If I just try SELECT N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME, everything works fine. I even tried declaring a local
> variable named @.command and setting it in the select statement, but no
> dice.

You can't build a parameter value 'dynamically' like that, but assigning the
entire string to a variable first should work:

declare @.mycommand nvarchar(1000)
set @.mycommand = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME

EXECUTE msdb.dbo.sp_add_jobstep
...
@.command = @.mycommand
...

What error did you get when you tried this?

Simon|||I ended up figuring that out and doing exactly what you suggested.
Something I did not know about passing parameters to stored
procedures...

The error I had gotten was
Incorrect syntax at '+'

Thanks for the help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Friday, February 24, 2012

Error Code

Error messages:
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\Servername_ADVENTUREWORKS_ADVWORKSPRODUCTTRANS\20070625161637\' is denied.
Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: mscorlib, Error number: 0)

I totally followed by tutorials of replication, repldata is new created folder

sharing for windowns accounts like shown below

repl_distribution read

repl_merge read

repl_snapshot Full control

the settting for these accounts same with security setting

any idea about this error?

many thanks

Can you confirm that xp_cmdshell is enabled? Also can you confirm that the account your snapshot agent is running under (either the windows impersonation account, of the SQL Server agent account on your publisher) has write, and list files and folders rights on the publisher?

Sunday, February 19, 2012

Error by converting string to decimal

Hi,

I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.

Try number1:

a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".

b) Running this package results in this error:

"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines m?glichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

You should try and find the offending data. Following what you did in your "try number 1", put an error output on the flat file source adapter. This error output will contain all values that failed the conversion. You can then look at the failed data either in a data viewer or a flat file destination and we can then work out why the conversion is failing.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

The "errorflatfile" contains all rows of my test inputfile which didn't had a value in column NXT_PUT_PX. I'm wondering about that, because I allowed this column to be NULL and cannot see why there could be potential data loss.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

What do you mean you allowed the column to be NULL? There is no concept of nullability in the SSIS pipeline (because NULLs are always allowed) so I assume you mean you specified columns to be NULLable on the destination but that is irrelevant because the erros occur before the data even gets to the destination.

It sounds as though the problem is happening during the parsing in the flat file source adapter. For that reason, I would import the data as a string and then parse it out yourself using a derived column expression, making sure that you check for NULLs (or empty strings) and handling them accordingly.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

My suspicion would be that the failure is occuring on the conversion to DT_DECIMAL in the non-null cases. It might be as simple as a value that cannot fit in scale 20, or maybe there are spaces you need to trim or replace? My suggestion would be to configure the error output and send the failing rows to it, and inspect the data to see if that gives you any clues.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

You might be able to optimize that a litte and get rid of the explicit string replacement of "." with "," by setting the locale ID on the conversion component to a locale that uses "." for decimal place. Or better yet, set the locale ID on the flat file source component, and set the column type to DT_R8 right there.

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

when I set the locale ID, I get a error message that says that this locale id is not installed on my computer. I will try this again in one of the next packages, due to time pressure on my actual work.

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

|||Hi Duane,
I used the following line:
ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The line you are complaining about was just used as "pseudo code" for explaining the algorithmus I wanted to use.
Regards,

Jan

Error by converting string to decimal

Hi,

Try number1:

b) Running this package results in this error:

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

Jan

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)
It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).
I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.
If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.