Is there option available to dynamically change the connection string
properties of a published report?
--
Thanks
SaranYou could do that through the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
Other approaches for dynamic database connections in RS 2000 have also been
discussed on this newsgroup. E.g.:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=b1a2f8b8-457c-4424-9cfd-2c8269734898&sloc=en-us
FYI: RS 2005 will allow expression-based connection strings.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PTS" <PTS@.discussions.microsoft.com> wrote in message
news:765E3C18-186B-44AB-AA83-A5BA1EC1BEEE@.microsoft.com...
> Is there option available to dynamically change the connection string
> properties of a published report?
> --
> Thanks
> Saran
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Tuesday, March 27, 2012
datasource connection error
have developed a report on a test windows server machine in SSRS 2005 which
I can access from i./e. http://localhost/reports
DataSourcce is accessed using a sql login
My colleague on his machine can not access. His error is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'MarketAnalytics'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errorsCheck the SRS error logs they should have a better error for you. They can be
found in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting
Services\LogFiles.
--
SQL Server Developer Support Engineer
"arkiboys2" wrote:
> have developed a report on a test windows server machine in SSRS 2005 which
> I can access from i./e. http://localhost/reports
> DataSourcce is accessed using a sql login
> My colleague on his machine can not access. His error is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'MarketAnalytics'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>
I can access from i./e. http://localhost/reports
DataSourcce is accessed using a sql login
My colleague on his machine can not access. His error is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'MarketAnalytics'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errorsCheck the SRS error logs they should have a better error for you. They can be
found in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting
Services\LogFiles.
--
SQL Server Developer Support Engineer
"arkiboys2" wrote:
> have developed a report on a test windows server machine in SSRS 2005 which
> I can access from i./e. http://localhost/reports
> DataSourcce is accessed using a sql login
> My colleague on his machine can not access. His error is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'MarketAnalytics'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>
Sunday, March 25, 2012
datasource "on-the-fly"
Hi,
Is it possible to change a datasource (or its connection string) in the
moment of report running?
Thanks.On SQL 2000, the only way is to create an SP which uses linked servers based
on the parameter you pass ie
create proc getdata @.source varchar(10)
as
if @.source = 'a'
select ... from mylinkedserver1.db.dbo.table
else
if @.source
With SQL 2005 you can use a dynamic data source based on a parameter ie. In
the connection string
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vladimir Evdokimov" <evesq@.uk2.net> wrote in message
news:OCvqf0kzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to change a datasource (or its connection string) in the
> moment of report running?
> Thanks.
>|||Hello
In SQL 2000 is it possible to supply the datasource (database name etc) as
part of the arguments supplied in the URL?
My application runs off multiple databases corresponding to different
customers and I need to switch the datasource based on the customer user
calling the report.
Any ideas on the best way to accomplish this task in SQL 2000?
Thank you|||Although you can do this with RS 2000 it will be easier in 2005.
In 2000 you have to use dynamic SQL. Use generic query designer (button to
switch to this is to the right of the ...)
declare @.SQL varchar(255)
select @.SQL = 'select table_name from ' + @.Database +
'.information_schema.tables order by table_name'
exec (@.SQL)
Note that @.Database should cause a report parameter called this to be
created. Anyway, this is the concept.
In 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F7640227-35C6-44CB-89BD-ED283ED242A4@.microsoft.com...
> Hello
> In SQL 2000 is it possible to supply the datasource (database name etc) as
> part of the arguments supplied in the URL?
> My application runs off multiple databases corresponding to different
> customers and I need to switch the datasource based on the customer user
> calling the report.
> Any ideas on the best way to accomplish this task in SQL 2000?
> Thank you
>|||Thanks Bruce, unfortunately I'm stuck with SQL 2000 for the moment...
Just a further enquiry based on the above. To allow for the switching of
databases on the fly for my dataset based on an entered parameter, I created
the following sql:
USE master
declare @.SQL varchar(255), @.mydb varchar(255)
set @.mydb= (SELECT dbID FROM co WHERE (coID = @.co))
select @.SQL='USE ' + @.mydb + ' SELECT accid, accno, accname FROM acc'
exec (@.SQL)
This reads a user defined 'co' table in master that contains a reference to
my target databases, When running a report I then supply a 'coID' to retrieve
the reference to the correct database and this is then used as a variable in
report's underlying dataset to ensure that the correct database is used. I
think this is along the lines of what you suggested above.
My question is whether the select statement needs to be built up as a string
for execution, this will obviously require rewriting all my existing plain
sql statements and inserting concatenation operators around the parameters
etc.
Is there some way that I can declare a parameter for the database I want to
run the report off, and include this at the start of existing sql statements
in the 'use' statement without explicitly declaring a sql str variable?
Is it possible to change a datasource (or its connection string) in the
moment of report running?
Thanks.On SQL 2000, the only way is to create an SP which uses linked servers based
on the parameter you pass ie
create proc getdata @.source varchar(10)
as
if @.source = 'a'
select ... from mylinkedserver1.db.dbo.table
else
if @.source
With SQL 2005 you can use a dynamic data source based on a parameter ie. In
the connection string
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vladimir Evdokimov" <evesq@.uk2.net> wrote in message
news:OCvqf0kzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to change a datasource (or its connection string) in the
> moment of report running?
> Thanks.
>|||Hello
In SQL 2000 is it possible to supply the datasource (database name etc) as
part of the arguments supplied in the URL?
My application runs off multiple databases corresponding to different
customers and I need to switch the datasource based on the customer user
calling the report.
Any ideas on the best way to accomplish this task in SQL 2000?
Thank you|||Although you can do this with RS 2000 it will be easier in 2005.
In 2000 you have to use dynamic SQL. Use generic query designer (button to
switch to this is to the right of the ...)
declare @.SQL varchar(255)
select @.SQL = 'select table_name from ' + @.Database +
'.information_schema.tables order by table_name'
exec (@.SQL)
Note that @.Database should cause a report parameter called this to be
created. Anyway, this is the concept.
In 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F7640227-35C6-44CB-89BD-ED283ED242A4@.microsoft.com...
> Hello
> In SQL 2000 is it possible to supply the datasource (database name etc) as
> part of the arguments supplied in the URL?
> My application runs off multiple databases corresponding to different
> customers and I need to switch the datasource based on the customer user
> calling the report.
> Any ideas on the best way to accomplish this task in SQL 2000?
> Thank you
>|||Thanks Bruce, unfortunately I'm stuck with SQL 2000 for the moment...
Just a further enquiry based on the above. To allow for the switching of
databases on the fly for my dataset based on an entered parameter, I created
the following sql:
USE master
declare @.SQL varchar(255), @.mydb varchar(255)
set @.mydb= (SELECT dbID FROM co WHERE (coID = @.co))
select @.SQL='USE ' + @.mydb + ' SELECT accid, accno, accname FROM acc'
exec (@.SQL)
This reads a user defined 'co' table in master that contains a reference to
my target databases, When running a report I then supply a 'coID' to retrieve
the reference to the correct database and this is then used as a variable in
report's underlying dataset to ensure that the correct database is used. I
think this is along the lines of what you suggested above.
My question is whether the select statement needs to be built up as a string
for execution, this will obviously require rewriting all my existing plain
sql statements and inserting concatenation operators around the parameters
etc.
Is there some way that I can declare a parameter for the database I want to
run the report off, and include this at the start of existing sql statements
in the 'use' statement without explicitly declaring a sql str variable?
Thursday, March 22, 2012
Dataset Parameters and Expressions in Report Connection String
I am working with RS 2005 and have run into a problem with passing a server and database name as parameters into a server report.
Here's what the connection string looks like in these reports:
="Data Source = " & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!DBName.Value
I have default values set for both parameters, so testing usually works fine also. If I run a report with "normal" parameters or no parameters, the report runs fine with the supplied connection string.
The problem occurs when I try to add a parameter to the report that uses a dataset to populate a list of choices. I get the following error when trying to run a report in this situation:
"Error during processing of the ConnectString expression of datasource 'dbConnection'"
I don't get any build errors, just the message above in the report canvas.
Any direction/assistance anyone can provide would be greatly appreciated.. thanks in advance.
Very simple solution, it turns out... just make sure the ServerName and DBName parameters appear above all other parameters in the report definition.
Wednesday, March 21, 2012
dataset connection string defined by a parameter?
Is there any way to dynamically set the connection string for a dataset
based on the value of a parameter?
Would I have to write a custom data extension to accomplish this?
thanks, AndrewHello,
Datasource connection string is not part of your report. It is provided by
ReportServer.
So, datasource is not aware about parameter's reports.
Jerome BERTHAUD MCSD, MCT
http://www.winsight.fr
"Andrew" <nospam@.nospam.com> wrote in message
news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>|||Hi Jerome,
Thanks for the info. So basically, it would be impossible to set the
connection string from within the designer based on a parameter.
However, wouldn't it still be possible to get hold of the parameters
collection within a custom data extension and use that to determine the
connection string prior to actually querying the datasource?
Thanks, Andrew
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uigOlEfWEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Datasource connection string is not part of your report. It is provided by
> ReportServer.
> So, datasource is not aware about parameter's reports.
> Jerome BERTHAUD MCSD, MCT
> http://www.winsight.fr
> "Andrew" <nospam@.nospam.com> wrote in message
> news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Is there any way to dynamically set the connection string for a dataset
> > based on the value of a parameter?
> >
> > Would I have to write a custom data extension to accomplish this?
> >
> > thanks, Andrew
> >
> >
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Andrew" <nospam@.nospam.com> wrote in message
news:%23m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>
based on the value of a parameter?
Would I have to write a custom data extension to accomplish this?
thanks, AndrewHello,
Datasource connection string is not part of your report. It is provided by
ReportServer.
So, datasource is not aware about parameter's reports.
Jerome BERTHAUD MCSD, MCT
http://www.winsight.fr
"Andrew" <nospam@.nospam.com> wrote in message
news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>|||Hi Jerome,
Thanks for the info. So basically, it would be impossible to set the
connection string from within the designer based on a parameter.
However, wouldn't it still be possible to get hold of the parameters
collection within a custom data extension and use that to determine the
connection string prior to actually querying the datasource?
Thanks, Andrew
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uigOlEfWEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Datasource connection string is not part of your report. It is provided by
> ReportServer.
> So, datasource is not aware about parameter's reports.
> Jerome BERTHAUD MCSD, MCT
> http://www.winsight.fr
> "Andrew" <nospam@.nospam.com> wrote in message
> news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Is there any way to dynamically set the connection string for a dataset
> > based on the value of a parameter?
> >
> > Would I have to write a custom data extension to accomplish this?
> >
> > thanks, Andrew
> >
> >
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Andrew" <nospam@.nospam.com> wrote in message
news:%23m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>
Monday, March 19, 2012
DataReader Source can not configurate
when I configurate the datareader source using the ODBC connection manager. it show the follow error message:
"Error at Data Flow Task[DataReader Source [562]]: Cannot acquire a managed connection from the run-time connection manager"
this ODBC is connect to IBM DB2.
Can anyone help on this?
Frank,
see this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1
Thanks.
DataReader Source and ODBC connection to PostgresSQL
Hi,
I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:
Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)
Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.
Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.
Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.
Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.
Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End
Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?
Best,
Michael Sh
I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.
Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -
Have you made any progress?
-robert
|||After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.
Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.
Best,
Michael Sh
|||My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.
Do you have file write permissions where this package is running?
What is the enivironment TEMP or TMP variable value set to?
Thanks
Mark
|||You can tell that the temp directory is located at:
C:\Documents and Settings\<username>\Local Settings\Temp
Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"
It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.
I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -
I did a test and I created a similar package in DTS - it ran quickly and effortlessly.
|||Thanks for the extra information.
Could you share exactly how many files are in the temp directory at the point of failure?
Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?
Thanks
Mark
|||I re-viewed the data files- and I found that they are formatted as such:
DTS####.tmp - where # is a hexadecimal character -
I noticed the files were as such:
DTSAAA0.TMP
DTSAAA1.TMP
DTSAAA2.TMP
DTSAAA3.TMP
.....
DTSAAAA.TMP
DTSAAAB.TMP
...
DTSAAAF.TMP
DTSAAB0.TMP
...
and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.
In my dataset, all I have are about 8 columns, only one of which is a TEXT field.
I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)
-rob
|||Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.
At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.
Thanks
Mark
A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.
Mark
|||Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...
But next time if it ever happens to me again, I will give it a try!
|||Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error
Package Package
DataReader Source and ODBC connection to PostgresSQL
Hi,
I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:
Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)
Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.
Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.
Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.
Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.
Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End
Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?
Best,
Michael Sh
I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.
Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -
Have you made any progress?
-robert
|||After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.
Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.
Best,
Michael Sh
|||My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.
Do you have file write permissions where this package is running?
What is the enivironment TEMP or TMP variable value set to?
Thanks
Mark
|||You can tell that the temp directory is located at:
C:\Documents and Settings\<username>\Local Settings\Temp
Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"
It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.
I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -
I did a test and I created a similar package in DTS - it ran quickly and effortlessly.
|||Thanks for the extra information.
Could you share exactly how many files are in the temp directory at the point of failure?
Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?
Thanks
Mark
|||I re-viewed the data files- and I found that they are formatted as such:
DTS####.tmp - where # is a hexadecimal character -
I noticed the files were as such:
DTSAAA0.TMP
DTSAAA1.TMP
DTSAAA2.TMP
DTSAAA3.TMP
.....
DTSAAAA.TMP
DTSAAAB.TMP
...
DTSAAAF.TMP
DTSAAB0.TMP
...
and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.
In my dataset, all I have are about 8 columns, only one of which is a TEXT field.
I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)
-rob
|||Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.
At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.
Thanks
Mark
A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.
Mark
|||Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...
But next time if it ever happens to me again, I will give it a try!
|||Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error
Package Package
DataReader Source and ODBC connection to PostgresSQL
Hi,
I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:
Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)
Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.
Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:\Documents and Settings\michaelsh\Local Settings\Temp". The path will not be considered for temporary storage again.
Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.
Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.
Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End
Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?
Best,
Michael Sh
I am definitely receiving the exact same error, with the exact same specifications- everything is working fine until I hit 19,932 - it sounds like it is some kind of error (I looked in the folder it was referencing) where there are too many temporary files- basically the directory gets mega huge with a ton of temporary filenames - and i guess it runs out of temporary file names to use -I think that is what stems the "The File Exists" error.
Right now, I am trying to see if I can just do a data conversion from a text string into a varchar(8000) field - i'm just going to truncate the column if needed -
Have you made any progress?
-robert
|||After exploring many differenet options, the only way I could get it to work on the original machine (desktop with 2 processor and 2 GB RAM) was to create multiple dataflow tasks, limit each to about 5,000 records then connect them to each other. Not a pretty solution but once working, I could eliminate data in records as culprit.
Ironically once I've installed SQL 2005 on my laptop, the same exact package worked without a hitch on first run going through 80,000 + records. Both machines run on XP Pro, the only difference is my laptop is single processor using slightly older version of Postgres ODBC driver, which I can't locate any more to see if that is the culprit or not.
Best,
Michael Sh
|||My first guess is that the pipeline is trying to create a file to spool the long columns due to memory limits, and it is unable to create that either because of permissions, or because it does not know where to put it.
Do you have file write permissions where this package is running?
What is the enivironment TEMP or TMP variable value set to?
Thanks
Mark
|||You can tell that the temp directory is located at:
C:\Documents and Settings\<username>\Local Settings\Temp
Basically what I did, was I emptied out this folder, and then ran my package- as soon as it started running- it started adding thousands of files named "DTS####.tmp"
It seems that after some point in time it stopped and said it could not find another filename to use because it was already created.
I'm sure we have write permissions to this folder - it just gets filled. To me it is a flaw of SSIS -
I did a test and I created a similar package in DTS - it ran quickly and effortlessly.
|||Thanks for the extra information.
Could you share exactly how many files are in the temp directory at the point of failure?
Aslo, approximately how many columns do you have in the table, and how many of those are LOB columns?
Thanks
Mark
|||I re-viewed the data files- and I found that they are formatted as such:
DTS####.tmp - where # is a hexadecimal character -
I noticed the files were as such:
DTSAAA0.TMP
DTSAAA1.TMP
DTSAAA2.TMP
DTSAAA3.TMP
.....
DTSAAAA.TMP
DTSAAAB.TMP
...
DTSAAAF.TMP
DTSAAB0.TMP
...
and so on- an so on- so you can see that there would definitely be a limit to this numbering scheme.
In my dataset, all I have are about 8 columns, only one of which is a TEXT field.
I have about 77,000 records - but the data in the TEXT column is quite large sometimes. (> 8000 characters)
-rob
|||Yes, it does sound like you are running out of temp files. Now we need to figure out why so many are created.
At this point, i recommend that you go here:http://msdn.microsoft.com/sql/bi/integration/ and choose the MSDN Product Feedback link under Support, and select Report a Bug. This is likely something that will need to be reproduced and investigated by the development team.
Thanks
Mark
A teammate suggested something that might help you work around this. You can have additional temp paths by setting the BLOBTempStoragePath to a semi-colon separated list of paths. This way, you will be able to create more unique temporary files.
Mark
|||Great find - I actually took a different route in creating a temporary fix and created a DTS package (*gasp!*) to just jam the data into my table ...
But next time if it ever happens to me again, I will give it a try!
|||Even after creating the semi colon delimited set in Temporrayblobtsorage it gives me the same error
Package Package
DataReader output column length
Hello,
I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.
This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :
Datatype : Unicode string [DT_WSTR]
Length : 10
Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?
tanks in advance ...
What are you talking about? How can you have a column declared with a width of 10 and have data that exceeds that length? You might want to double check your source metadata.|||renyx wrote:
In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.
That is a physical impossibility.
Can you expand more on what you mean?
-Jamie
|||
In Progress a column can be declared as Char(10) while creating the table, but while inserting in the table, a value larger than 10 can be inserted without causing an error.
So I am looking for a way to make the DataReader output column 15 in length.
|||renyx wrote:
In Progress a column can be declared as Char(10) while creating the table, but while inserting in the table, a value larger than 10 can be inserted without causing an error.
So I am looking for a way to make the DataReader output column 15 in length.
Really? OK, I take it back. Sorry. That's just....bizarre....for want of a better word.
I think you'll be able to go into the Advanced Editor of the Datareader Source and manually edit the column lengths.
|||
Really? OK, I take it back. Sorry. That's just....bizarre....for want of a better word.
I think you'll be able to go into the Advanced Editor of the Datareader Source and manually edit the column lengths.
I just looked into this some... Progress 4GL does not use the width definition for the storage of data. Character data types in Progress 4GL can be up to 2,000 characters, I believe. The char(10) definition that the OP mentioned is for query results, I believe, and not for storage.
So, how to get around this? Well, do as Jamie suggested and edit the Datareader Source manually using the advanced editor.|||
Thanks for the suggestion, but that did not work.
I also tried a query on the column in management studio (linked server) and that also did not work.
OLE DB provider "MSDASQL" for linked server "LISA" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Column KM4-CODE in table PUB.ARTIKEL has value exceeding its max length or precision.".
Looks like I will have to convince the Progress people to change the datatype.
|||This is a well known Progress problem. Check out http://www.progresstalk.com/archive/index.php?t-76301.html|||By the way, I'm just going on record to say that the Progress leaders should be sent back to logic school for designing a product that enforces the data length on a one-way basis. "Um yeah, we let you store data greater than what's defined, but we won't let you select it back out."That's the most ridiculous thing I have heard in a long time.|||Same issue happens to me. We import data from a Thoroughbred basic database. The field can be 12 chars long with 20 chars of data. In the Thoroughbred world, this is just an "integrity" problem....Their db still works somehow, but causes fits on our end.|||
If anyone out there wants a humorous outlook on this (and promises not to take offence to SQL zealots), go here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=263&messageid=340643
-Jamie
|||
Thoroughbred still lives? Aaargh! The only implementation of a computer language I ever saw that allowed indefinite GOTOs (I'm not kidding). I had not heard about this "integrity" issue with their DB, but am not surprised.
|||
Thanks for the suggestion, but that did not work.
I also tried a query on the column in management studio (linked server) and that also did not work.OLE DB provider "MSDASQL" for linked server "LISA" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Column KM4-CODE in table PUB.ARTIKEL has value exceeding its max length or precision.".
Looks like I will have to convince the Progress people to change the datatype.
Renyx, did you try changing the SqlCommand select statement to cast the column to something longer?
As you noted, you cannot change the column length in the advanced editor, but doing so in the select statement might work for you.
DataReader ODBC Query Timeout
Hello !
I get in my SSIS Package a Query Timeout in the Datareader!
I Use the ADO.Net OBC Connection with the Connection String:
Dsn=xxx;uid=xxx;connection timeout=0;command timeout=0;query timeout=0
Is there any Option to set the Query Timeout ?
Thanks !
Pseudo
Set your time out values to something other than 0, like 999.Some ODBC drivers interpet 0 as "use default". Since you didn't say what ODBC connection you are using, I cannot tell if that is your problem or not.|||
I have had this problem with queries on very large datasets coming from an AS 400 where indexes weren't present (that I could find out about anyway) when I was using multiple where clause conditions that resulted in an extremely long running query. What I found was that using a simpler query that initially imported way more data than I actually needed to import was more efficient (from the simplistic viewpoint of how long my processing step took to execute) than did changing the timeout to a value large enough to accomodate the query.
Changing the timeout is a good idea but realize that with data sources that you have little to no control over or lack adequate access to info about, it can be more efficient to simply import a lot of data into a holding table in SQL Server that you subsequently prune in an Execute SQL task before you convert it, etc. At lleast once you get it into SQL Server, you can control it however necessary.
Hope this helps.
|||
Thanks for the answer !!
I have not seen that the Datareader in the SSIS Designer have a Command Timeout property ?
I set this property to 0 and now it works...
But exist a difference between the Datareader Command Timeout property and the ODBC Connection Command Timeout property ?
Datareader not referencing connection object
Please see following code :
SqlConnection conn=new SqlConnection(@."something...;");
SqlCommand comm=new SqlCommand("Select TOP 10 * FROM TableReaderTest WITH (HOLDLOCK) ",conn);
conn.Open();
SqlDataReader rd;
conn=null;
try
{
rd = comm.ExecuteReader();
rd.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
This Code works fine. I have set conn=null, still datareader is able to read the data. Why?
Thank you.
You set conn to null, you are setting thereference to null. Since your SqlCommand has it's own reference to the connection, the connection is still around. You should use a Finally block for the .Close() to ensure it is called.|||Thank you for answering.
Yes it is bad code that I have set it null.
Please look following code:
rd=comm.ExecuteReader(<<behaviour close connection>>);
do someting.. donot close rd
comm.CommandText="another query"
rd=comm.ExecuteReader(<<behaviour close connection>>);
rd.close()
Is there First SQL Connection is still open?
Thank you.
The only provider that will even allow such a thing is SQL Server 2005, otherwise you will get an error when you attempt to execute the second ExecuteReader.
Regardless of what it actually does, I wouldn't recommend assuming anything. It's ambiguous on what it SHOULD do, so don't rely on what it really does, and don't code that.
|||Hi Motley,
Yes, we do not code like this way. But this is already done by other developers, and we are reviewing it.
Please have a look to following code (Assume try/catch is properly placed) :
privatevoid Form1_Load(object sender, System.EventArgs e)
{
SqlDataReader dr1=GetDataReader("Select 1"); //Line1
SqlDataReader dr2=GetDataReader("Select 2"); //Line2
dr1=GetDataReader("Select 3"); //Line3
dr1.Close(); //Line4
dr2.Close(); //Line5
}
private SqlDataReader GetDataReader(string qry)
{
SqlConnection conn=new SqlConnection(@."Server=localhost;uid=user;pwd=password;database=northwind;;");
SqlCommand comm=new SqlCommand(qry,conn);
conn.Open();
SqlDataReader rd;
rd = comm.ExecuteReader(CommandBehavior.CloseConnection);
comm=null;
conn=null;
return rd;
}
This works withSQL2000 also. Each time new instance of sqlDatareader is created and reference is returned.
At Line3, we are assigning new datareader to dr1. Will it close the dr1 previously opened (for "Select 1") automatically?
If 2 datareaders are not allowed open at the same time, how it is working?
At Line4 we are closing the dr1, but I think there are 2 different connections opened for dr1 and this will lose the last one. Right?
Thank you,
|||In this example, you are using a new connection for each data reader, and so you can get an additional datareader and assign it to dr1. However, in this case, dr1 will be closed (and in this example, the underlying connection) whenever the data reader object is garbage collected, which is a terrible idea.|||What doug said is correct.
DataReader not reading
Why won't this dataReader read?
Dim objCon2 As New SqlConnection()
objCon2.ConnectionString = "a standard connection string"
objCon2.Open()
Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objCon2)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()
Label1.Text = objReader("email")
strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).
So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)
System.InvalidOperationException: Invalid attempt to read when no data is present.You have to move the "read head" to the start of a record by calling objReader.Read(). You do this each time you want to move to the next record. Since in your case there is presumably only one record you only need to call it once.
objReader = objCommand.ExecuteReader()
if objReader.Read()
Label1.Text = objReader("email")
End If
' be sure to close and the data reader afterwards
objReader.Close()
Datareader can not open connection to my database for login "myusername"
This is my page_loads event code and iam getting the Exception pasted below the code.
---------------------------------------------
Protected
Sub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)IfNot Page.IsPostBackThenDim myconnectionAsNew SqlConnection("Data Source=localhost\SQLEXPRESS;initial catalog = safetydata.mdf;Integrated Security=True;User Instance=True")Dim strSQLAsString ="SELECT Incident_Id,Emp_No From Report_Incident"Dim mycommandAsNew SqlCommand(strSQL, myconnection)myconnection.Open()
Dim readerAs SqlDataReader = mycommand.ExecuteReader()Dim chartAsNew PieChart
chart.DataSource = reader
chart.DataXValueField =
"Incident_id"chart.DataYValueField =
"Emp_No"chart.DataBind()
chart.DataLabels.Visible =
True
ChartControl1.Charts.Add(chart)
ChartControl1.RedrawChart()
myconnection.Open()
EndIfEndSub
--------------------------------------
EXCEPTION IS BELOW
Cannot open database "mydatabase.mdf" requested by the login. The login failed.
Login failed for user 'myusername'.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Cannot open database "safetydata.mdf" requested by the login. The login failed.
Login failed for user 'myusername'.
Source Error:
Line 18: Dim strSQL As String = "SELECT Incident_Id,Emp_No From Report_Incident"Line 19: Dim mycommand As New SqlCommand(strSQL, myconnection)Line 20: myconnection.Open()Line 21: Dim reader As SqlDataReader = mycommand.ExecuteReader()Line 22:
Source File:C:\Incident Reporting System--Trial Version\WebChart.aspx Line:20
Stack Trace:
[SqlException (0x80131904): Cannot open database "safetydata.mdf" requested by the login. The login failed.Login failed for user 'myusername'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89 System.Data.SqlClient.SqlConnection.Open() +160 ASP.webchart_aspx.Page_Load(Object sender, EventArgs e) in C:\Incident Reporting System--Trial Version\WebChart.aspx:20 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.
Hi Nick,
Based on the connection string, we can see that you're currently using the the Windows integrated authentication to connection to the .mdf file. And the exception message reveals your windows identity is not a valid user to this database.
In this case, you can try to check if you have added your identity to the database login list. Also, please check if you have granted enough permission to this username.
HTH.
DataReader and DataAdapter
Hi,
What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.
Regards
Vijay.
Not much to compare about between these two. They are too different from each other.
A SqlDataReader is a data provider that allows a forward-only and read-only access to the data/result set. Meaning that you can't modify any data in the SqlDataReader object and you can only read data sequentially and forward-only. Meaning once you're on the second set of data you can't go back and read the first set. A SqlDataAdapter basically acts like a bridge between a data provider and a dataset. It's what connects your data in its native storage format in the database and your data in a more suitable representation designed for manipulation in your application. Think of it like an interpreter between two people speaking in different languages.
Thursday, March 8, 2012
Dataflow Tab:There is no ODBC Source option in the Toolbox
I need to extract data from tables in a database that I can only access via ODBC.
I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.
However I’m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.
This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process
How do I extract data from tables in a database via ODBC?
Thanks in advance
Dave
There is too, though it's not marked as such. Use the Data Reader Source.|||You can use the script component as source.
|||Thanks for that the data reader souce can actually use a ODBC source. I have it working fine.
I do think there is a peformance overhead and its slower than the ODBC connector in DTS.
|||well that is interesting How can it connect to a foxpro file .dbf and matching .fpt file the .fpt files are used for memo text fieldsthe foxprpro ODBC driver does this for you behind the scenes
|||Vijay Thirugnanam wrote:
You can use the script component as source.
True, though it shouldn't be faster than using the prepackaged source connectors.
DataEnviornment Connection to SQL Server 200
I have a VB 6.0 application that uses MS DataEnviornment in order to connection to an MSQ SQL 7.0 database.
This database was just converted into an SQL Server 2000 database.
After the conversion, that VB application is no longer able to connect to the DB.
Are there any permission or user issues that might be affecting the connection?
(The problem occurs when I try to open a recordset of a command of the connection to the DB).
What error message do you get?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
This database was just converted into an SQL Server 2000 database.
After the conversion, that VB application is no longer able to connect to the DB.
Are there any permission or user issues that might be affecting the connection?
(The problem occurs when I try to open a recordset of a command of the connection to the DB).
What error message do you get?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
DataEnviornment Connection to SQL Server 200
I have a VB 6.0 application that uses MS DataEnviornment in order to connect
ion to an MSQ SQL 7.0 database.
This database was just converted into an SQL Server 2000 database.
After the conversion, that VB application is no longer able to connect to th
e DB.
Are there any permission or user issues that might be affecting the connecti
on?
(The problem occurs when I try to open a recordset of a command of the conne
ction to the DB).What error message do you get?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
ion to an MSQ SQL 7.0 database.
This database was just converted into an SQL Server 2000 database.
After the conversion, that VB application is no longer able to connect to th
e DB.
Are there any permission or user issues that might be affecting the connecti
on?
(The problem occurs when I try to open a recordset of a command of the conne
ction to the DB).What error message do you get?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Friday, February 24, 2012
DATABASEPROPERYEX is not a recognised function name
I am trying to write a report in reporting services 2000 which access a sql server 7 database. It all workes fine with the connection and creating a dataset but I cannot seem to get the results displayed in the preview section. I get the following error:
"An error occured during report processing query execution failed for dataset XXXX DATABASEPROPERYEX is not a recognised function name "
Any help greatly appreciated
Your are running RS 2000 RTM - you have two options:
* install RS 2000 SP1 or SP2 on both the report server and the report designer machines. The service packs contain the fix.
* Alternatively, in report designer go to the "Data Options" tab of the "Dataset"
dialog. On the Data Options tab you will see that all settings contain "Auto" (and report server would therefore try to auto-detect the collation settings from the database server). Replace the Auto-settings with the following settings (e.g. if your SQL 7.0
database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
-- Robert
Friday, February 17, 2012
Database vs Schema
We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.
Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).
Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?
Thad
You are right that having a large number of databases will increase your fail-over time in a fail-over cluster.
Since you mentioned schemas, you must be on SQL Server 2005. For fail-over time, database mirroring is much faster than fail-over clustering. Database mirroring does require twice the storage space, and the licensing situation is not good compared to an Active/Passive cluster. You would have to mirror each database individually.
Are you failing-over for maintenance, rolling upgrades, etc.? Maintaining and adding dozens of schema in a database would be cumbersome and prone to error. It also has some performance drawbacks, since if you don't properly schema qualify a SP name (from code), or a table name (in an SP), you will have name resolution problems and cache misses.
Depending on the numbers of clients you are talking about, I would think about getting another cluster to split the load.
|||Currently the failover system is done for disaster recovery scenarios. As for performance. All our inline sql from the client class generate sql similar to
Code Snippet
SELECT [Schema].[Table].[Field] FROM [Schema].[Table]While a stored procedure is set is setup so it exists in each schema, but the schema name is not used in the sproc body. This is the same method we would use with Views and Functions.
Code Snippet
CREATE PROCEDURE [Schema].[ProcedureName](@.Param INT) ASBEGIN
SELECT * FROM [Table]
END
Would setting up the schema in this way cause these performance drawbacks. I am thinking that maintaining multiple schema will be as error prone as setting up the single db for each client.
Currently we are talking about approx 1000+ clients.
|||If your client code can pick up the schema name dynamically from a config file or something, then having a schema for each client as opposed to a database for each client will probably be not much different from a maintenance and configuration perspective.
There may be some negative effects on your buffer cache and procedure cache from this approach. Instead of having one large, normalized table that holds data for all your clients in a single database, you will have one table for each client. You will have multiple copies of the same SP (for different schemas) in your procedure cache.
|||Currently the client already selects the Database so we would just need to add a schema selector to the data selection. So all that is straight forward. And yes we are duplicating the entire database inside a schema.As for the procedure and data cache, which I am not completely familar with, would it not be the same as multiple database?
I can that this is not the best solution, but I think it may be the best solution given the time and other hardware constraints we have. So, it appears that there are little differences in performance and maintenance that we already deal with.
Subscribe to:
Posts (Atom)