Data Flow Task Problem: created

Showing posts with label created. Show all posts

Thursday, March 29, 2012

data file path in SQL 2005

After I created an instance in SQL 2005, can I still change the data file
path and how? Thanks.Do you mean the default location for databases? If so use the Management
Studio and go to the properties of the instance. One of the categories is
databases and yoiu can set the default location there.
-003KobeBrian wrote:
> After I created an instance in SQL 2005, can I still change the data file
> path and how? Thanks.
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||I found the database default location. I wonder how this works since I can
see the datafile path in individual database. So I specify the database
default location at top level, will that overwrite the datafile path in
individual database?
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> Do you mean the default location for databases? If so use the Management
> Studio and go to the properties of the instance. One of the categories is
> databases and yoiu can set the default location there.
> -003KobeBrian wrote:
>> After I created an instance in SQL 2005, can I still change the data file
>> path and how? Thanks.
> --
> Brett I. Holcomb
> brettholcomb@.R777bellsouth.net
> Remove R777 to email|||When you create a new database, you can specify a specific file location, or
use the default. If you do not specify a new location, the default will be
used. If you specify a location, it will override the default.
The default only applies to new databases. It will not 'overwrite' any
location for existing database files.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"-003KobeBrian" <kb@.yahoo.com> wrote in message
news:epJhkvrDHHA.348@.TK2MSFTNGP06.phx.gbl...
>I found the database default location. I wonder how this works since I can
>see the datafile path in individual database. So I specify the database
>default location at top level, will that overwrite the datafile path in
>individual database?
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>> Do you mean the default location for databases? If so use the Management
>> Studio and go to the properties of the instance. One of the categories
>> is
>> databases and yoiu can set the default location there.
>> -003KobeBrian wrote:
>> After I created an instance in SQL 2005, can I still change the data
>> file
>> path and how? Thanks.
>> --
>> Brett I. Holcomb
>> brettholcomb@.R777bellsouth.net
>> Remove R777 to email
>|||Hi
If you want to move the data and log files to a different location for
existing database then you can call sp_detach_db to detach the files, move
the file (at a command prompt!) and then call sp_attach_db to re-attach the
data and log files. Alternatively you can backup the database and restore it
specifying a different location for the files on the options table or use the
MOVE clause in the RESTORE command in T-SQL. You may want to read
http://support.microsoft.com/kb/224071/
HTH
John
"-003KobeBrian" wrote:
> I found the database default location. I wonder how this works since I can
> see the datafile path in individual database. So I specify the database
> default location at top level, will that overwrite the datafile path in
> individual database?
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> > Do you mean the default location for databases? If so use the Management
> > Studio and go to the properties of the instance. One of the categories is
> > databases and yoiu can set the default location there.
> >
> > -003KobeBrian wrote:
> >
> >> After I created an instance in SQL 2005, can I still change the data file
> >> path and how? Thanks.
> >
> > --
> > Brett I. Holcomb
> > brettholcomb@.R777bellsouth.net
> > Remove R777 to email
>
>|||To add on to other posts.. Use the below uRL to move system databases
http://www.sqlservercentral.com/columnists/vIacoboni/2605.asp
Thanks
Hari
"-003KobeBrian" <kb@.yahoo.com> wrote in message
news:OELu8ZqDHHA.4464@.TK2MSFTNGP06.phx.gbl...
> After I created an instance in SQL 2005, can I still change the data file
> path and how? Thanks.
>|||Very helpful. Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D9A36D72-A50F-464C-AC88-A3C5E8420123@.microsoft.com...
> Hi
> If you want to move the data and log files to a different location for
> existing database then you can call sp_detach_db to detach the files, move
> the file (at a command prompt!) and then call sp_attach_db to re-attach
> the
> data and log files. Alternatively you can backup the database and restore
> it
> specifying a different location for the files on the options table or use
> the
> MOVE clause in the RESTORE command in T-SQL. You may want to read
> http://support.microsoft.com/kb/224071/
> HTH
> John
> "-003KobeBrian" wrote:
>> I found the database default location. I wonder how this works since I
>> can
>> see the datafile path in individual database. So I specify the database
>> default location at top level, will that overwrite the datafile path in
>> individual database?
>> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
>> news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>> > Do you mean the default location for databases? If so use the
>> > Management
>> > Studio and go to the properties of the instance. One of the categories
>> > is
>> > databases and yoiu can set the default location there.
>> >
>> > -003KobeBrian wrote:
>> >
>> >> After I created an instance in SQL 2005, can I still change the data
>> >> file
>> >> path and how? Thanks.
>> >
>> > --
>> > Brett I. Holcomb
>> > brettholcomb@.R777bellsouth.net
>> > Remove R777 to email
>>

data file path in SQL 2005

Tuesday, March 27, 2012

Data feeding a report is cached between different calls for a session?

Microsoft SQL Server 2000 - 8.00.818 + Reporting Services SP2
We have created a report (for invoices) that is usually called to render pdf
files (for quality) but can also be rendered into tif files (lower quality).
The front end program for users is written in Access 2003 with a SQL Server
back end for storing the data (client-server approach). Our problem is
described as follows:
1. We create a new invoice (insert new record for invoices table and some
new records for invoicesdetails table)
2. User wants to preview the pdf file for the invoice and click on a button
(MS Access) that internally connects to reporting services (via https)
downloads the pdf file, stores it locally (deleting the original local file
if exists) and opens using acrobat reader.
3. User needs to modify something in the invoice or details and updates the
record (s). Updates the records from tables that are used in the report.
4. Click again on the button to generate the pdf again and to see the new
revision of the invoice but they still receive the old data!!!
Simplifying the scenario, if the data feeding a report is changed between 2
subsecuent calls from the same user (session), reporting services generates
the report with oudated data. It seems that reporting services sees the
request coming from the same user asking for the same data with same
parameters and within the same session and instead of asking SQL server for
the data again (that might have changed), serves the report with the same
old data that it has stored somehow.
To reproduce this behaviour:
1. Create a simple report MyTestReport accepting a parameter InvoiceId
(integer) based on a simple table TInvoices (InvoiceId int, Amount money).
The report just shows the InvoiceId and the Amount of it on a single page.
It uses a shared datasource, type: Microsoft SQL Server, Windows
authentication. Upload this report to Reporting Services.
2. Execute the following SQL command 'INSERT INTO TInvoices (InvoiceId,
Amount) VALUES (0,0)' to ensure that an invoice with Id=0 exists with a
amount of 0.
3. Copy this URL and paste it into your web browser:
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
You will see a reporting services page with InvoiceId field on top filled in
with a 0 and the contents of the report showing 0 0 on it.
4. From the dropdown list of 'Select a format' select 'Acrobat PDF File' and
click on 'export', then click 'open'. You see a pdf file with 0 0 on it.
This is correct. Close acrobat reader. Do not close IE window.
5. Go back to your SQL Server and execute 'UPDATE TInvoices SET Amount=1000
WHERE InvoiceId = 0'
6. Up to this poing, the 0 0 on IE window is outdated, but we have not
refreshed yet. If we click on IE Refresh button to retrieve the same
original URL that we had on our clipboard a 0 0 still appears!!!!! The data
is not fetched again from SQL server, reporting services is caching it
somehow and returning it to me again. To be sure that it is not a problem
with IE cache, go Tools | Internet options | General tab and click the
buttons 'Delete cookies' and 'Delete files'. Click on Accept button. Now
refresh the page again (F5). The page is still showing 0 0!!!!!
7. If we select 'Adobe PDF File' from 'Select a format' dropdownlist and
click again on 'export', then 'open', a new PDF file is downloaded and open,
but there is a 0 0 on it yet!!!!
Of course, if you click the button 'View report' on top right side of the
page, the report is shown (requeried) with updated data. The report reads 0
1000 which is correct. However in our scenario, our users are not browsing
through reporting services site interface and do not have that 'View report'
button at hand. When they need a report (Invoices or other) with whatever
the filter and parameters, a MS Access Form is shown to them to select from
dropdown lists, dates, codes, etc. Then our front end in MS Access
constructs the apropriate URL considering the parameters the user has just
entered in the form and opens (or downloads) the report acordingly.
The only way to retrieve the updated data without clicking that 'View
report' button is having the user asking for another unwanted InvoiceId
(InvoiceId=1 for instance) before asking again for what he/she needs:
UPDATE TInvoices (InvoiceId, Amount) VALUES (0,0)
INSERT TInvoices (InvoiceId, Amount) VALUES (1,10)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE
0 0 is shown
UPDATE TInvoices (InvoiceId, Amount) VALUES (0,1000)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE or click refresh
0 0 is shown wich is incorrect
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=1&rs:Command=Render&rs:Format=HTML4.0
in IE
1 10 is shown (unwanted, but needed for reporting services flushing its
internal outdated data)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE
0 1000 is shown which is correct.
It seems that unless the user asks reporting services to generate the report
with other parameters, it returns the old dataset without asking again to
SQL server, in this case generating a report with outdated data.
Note: We have also checked that 'execution properties' for the report has
'Show this report with the most recent data' and 'Do not save cache copies
of this report' enabled.
Is this a known issue? Is there a fix/workaround for it? Any registry tweaks
for forcing reporting services to always ask the datasource instead of
caching?David,
This is correct behavior. Reporting Services maintains report session,
which is needed for certain things like switching between pages or drilling
down.
For your situation, I think there are two easy things you can do. First,
you can add "rs:ClearSession=true" to your URL. This will treat the request
as a new session, and not reuse the data from the previous report view. You
can find more information if you search BOL or this newsgroup for
ClearSession.
I think you can also resolve this problem by deallocating your web request
object in Access after each report view. I haven't done this myself, but I
think that would cause each request to start with a new session.
(FYI, the caching option allows you to store/share dataset results across
users. This is a big performance boost for data that doesn't change, e.g.
last month's balance sheet. Your problem has to do with session for the same
user.)
HTH,
Ted
"David Lightman Robles" wrote:
> It seems that unless the user asks reporting services to generate the report
> with other parameters, it returns the old dataset without asking again to
> SQL server, in this case generating a report with outdated data.
> Note: We have also checked that 'execution properties' for the report has
> 'Show this report with the most recent data' and 'Do not save cache copies
> of this report' enabled.
> Is this a known issue? Is there a fix/workaround for it? Any registry tweaks
> for forcing reporting services to always ask the datasource instead of
> caching?|||Thanks for the information. That solved all our problems.
"Ted K" <tedk@.nospam.nospam> escribió en el mensaje
news:F5ABBB95-AE6C-482B-B1B6-1A26859B0C0B@.microsoft.com...
> David,
> This is correct behavior. Reporting Services maintains report session,
> which is needed for certain things like switching between pages or
> drilling
> down.
> For your situation, I think there are two easy things you can do. First,
> you can add "rs:ClearSession=true" to your URL. This will treat the
> request
> as a new session, and not reuse the data from the previous report view.
> You
> can find more information if you search BOL or this newsgroup for
> ClearSession.
> I think you can also resolve this problem by deallocating your web request
> object in Access after each report view. I haven't done this myself, but
> I
> think that would cause each request to start with a new session.
> (FYI, the caching option allows you to store/share dataset results across
> users. This is a big performance boost for data that doesn't change, e.g.
> last month's balance sheet. Your problem has to do with session for the
> same
> user.)
> HTH,
> Ted
> "David Lightman Robles" wrote:
>> It seems that unless the user asks reporting services to generate the
>> report
>> with other parameters, it returns the old dataset without asking again to
>> SQL server, in this case generating a report with outdated data.
>> Note: We have also checked that 'execution properties' for the report has
>> 'Show this report with the most recent data' and 'Do not save cache
>> copies
>> of this report' enabled.
>> Is this a known issue? Is there a fix/workaround for it? Any registry
>> tweaks
>> for forcing reporting services to always ask the datasource instead of
>> caching?
>sql

Data extension and ad hoc reporting

I've created a data processing extension that retrieves data from a
particular table based on the parameters given by the end-user. Of course
the tables do not have the same columns, so creating the report in the report
designer has been problematic. So far I've found one possible solution,
generating the rdl dynamically, similar to what is done in the "Ad hoc"
sample. I have been unable to successfully tie the dynamically generated rdl
to my data extension. I'm hoping that someone can point me in the right
direction.Sorry I forgot to include the following information.
RS 2000 Sp2
VB.NET
"Tamichan" wrote:
> I've created a data processing extension that retrieves data from a
> particular table based on the parameters given by the end-user. Of course
> the tables do not have the same columns, so creating the report in the report
> designer has been problematic. So far I've found one possible solution,
> generating the rdl dynamically, similar to what is done in the "Ad hoc"
> sample. I have been unable to successfully tie the dynamically generated rdl
> to my data extension. I'm hoping that someone can point me in the right
> direction.

Data Extension - Parameters ?

Hi,
I've just created a custom data extension for Reporting Services, which
works fine. Now, I want to support parameters. I correctly implemented
IDbParameter and IDbParameterCollection but they don't seem to be
called at anytime...
Any sample about implementing parameter support ?
Thanks in advance.
Best regards,
JulienNote that in order to support using parameters in your data extension (in
the report designer), you must also implement the IDbCommandAnalysis
interface, which parses the command text and returns the list of parameters
contained in the query.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julien Cheyssial" <julich@.gmail.com> wrote in message
news:1117728338.575235.15190@.g49g2000cwa.googlegroups.com...
> Hi,
> I've just created a custom data extension for Reporting Services, which
> works fine. Now, I want to support parameters. I correctly implemented
> IDbParameter and IDbParameterCollection but they don't seem to be
> called at anytime...
> Any sample about implementing parameter support ?
> Thanks in advance.
> Best regards,
> Julien
>|||Thanks, now I implemented IDbCommandAnalysis, parameters are now
correctly passed to my data extension. I thought IDbCommandAnalysis
wasn't mandatory, since it is called by the Reporting Services designer
to know which params are to be asked to the user.
Thanks,
Julien

Sunday, March 25, 2012

Data entry table not editable in Access ADP - what do I need to do

Hello,
I created a simple table for data entry where I can manually edit/add data
to the table in Enterprise Manager. But when I open the same table in an
Access it is locked. I cannot edit/add data to the table. Does anyone know
what I need to do to make the table editable in the Access ADP?
Thanks,
RichFigured it out. You need to add a key field to the table to make it manuall
y
editable outside of Enterprise Manager.
"Rich" wrote:

> Hello,
> I created a simple table for data entry where I can manually edit/add data
> to the table in Enterprise Manager. But when I open the same table in an
> Access it is locked. I cannot edit/add data to the table. Does anyone kn
ow
> what I need to do to make the table editable in the Access ADP?
> Thanks,
> Rich

Monday, March 19, 2012

Data Delivery

I've created a number of .html, pivot tables using Access' Data Page
tools. These work great and by pointing my browser to these files,
users throughout my firm can access these pivot tables. I had assumed
that I could simply publish these pivot tables on the web to outside
users who are configured as active directory users. The .htm pivot
table is linked to a query in Access that is built from a SQL database.
We've separately built a system where users can crawl through our file
server from outside of our network. I placed the .htm file in a folder
that the user has access to and the application shows the file.
However, when the user clicks on the file, s/he gets these messages:
Data Access Pages has detected that your IE security settings will not
allow you to access data from a site considered to be insecure.
[
In order to access the data contained within the Data Access Page, you
need to:
1. Start IE
2. Choose Internet Options from the Tools menu
3. Click on the "Security" Tab
4. Click on the "Trusted Sites" icon
5. Click on the "Sites..." button
6. Uncheck the "Require server verification (https) for all sites in
the zone" checkbox
]
Our technology consultant indicated that there's no way for the data to
"bind" to the browser and it's impossible to expose these .htm, pivot
tables to users outside of our network. Does anyone know a way to
accomplish what I'm trying to do? I feel like it should be possible
since Microsoft created that product to build .htm pages?
Ryan
Any suggestions would be very helpfulRyan.Chowdhury@.gmail.com wrote:
> I've created a number of .html, pivot tables using Access' Data Page
> tools. These work great and by pointing my browser to these files,
> users throughout my firm can access these pivot tables. I had assumed
> that I could simply publish these pivot tables on the web to outside
> users who are configured as active directory users. The .htm pivot
> table is linked to a query in Access that is built from a SQL database.
>
> We've separately built a system where users can crawl through our file
> server from outside of our network. I placed the .htm file in a folder
> that the user has access to and the application shows the file.
> However, when the user clicks on the file, s/he gets these messages:
> Data Access Pages has detected that your IE security settings will not
> allow you to access data from a site considered to be insecure.
> [
> In order to access the data contained within the Data Access Page, you
> need to:
> 1. Start IE
> 2. Choose Internet Options from the Tools menu
> 3. Click on the "Security" Tab
> 4. Click on the "Trusted Sites" icon
> 5. Click on the "Sites..." button
> 6. Uncheck the "Require server verification (https) for all sites in
> the zone" checkbox
> ]
> Our technology consultant indicated that there's no way for the data to
> "bind" to the browser and it's impossible to expose these .htm, pivot
> tables to users outside of our network. Does anyone know a way to
> accomplish what I'm trying to do? I feel like it should be possible
> since Microsoft created that product to build .htm pages?
> Ryan
> Any suggestions would be very helpful
>
First off, you might have better luck asking in an Access group instead
of SQL Server. Second, let me say, YIKES!!! You allow outside access
to your file servers?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Delivery

Data Conversion supported on Standard Edition?

I created an Integration Services Package that runs fine from my local computer using BIDS. However when I imported into our SQL Server and try to run it from there I get the following error:

DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion"

We are running SQL Server 2005 Standard Edition 64-bit.

We have integration services installed on the server. Is data conversion something that is not supported on Standard Edition?

Also have a similar message for "Send Mail Task."

Is there anywhere that outlines what features are supported on each version?

What version of SQL Server are you running on your developer's workstation?|||

I'm running BIDS on my workstation which is connecting to our SQL Server. The same SQL Server where the integration package will not run from when imported into. So I'm actually not running a full blown SQL Server database on my workstation, just using BIDS on it along with Studio.

The Management Studio on my workstation is 9.00.3042.

SQL Server Integration Services is 9.00.3042. (Found by going to About > Help in Visual Studio)

Our SQL Server version is 9.00.3050

|||So both your workstation and the server are running SQL Server Standard edition? Not developer/enterprise edition?|||

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

I guess that would explain why it works on my workstation but not on the server?

|||

Erikk Ross wrote:

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

Run this query on the server and again on your local version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Yeah, like I said I don't have the actual Database engine installed on my workstation. But our server version is SP2: 9.00.3050.

So I guess Data Conversion is not supported in Standard Edition? Or send mail? Is there a matrix somewhere that shows what features in Integration Services are supported in each version of SQL Server? It would seem to me that data conversion is something that is pretty common...I'm a little suprised it would require the Enterprise edition.

|||Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.|||

Phil Brammer wrote:
Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).
Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.

That was indeed the solution. I just did a quick test and creating the package from a Standard edition version did allow the data conversion to work correctly. Thank you!!

|||

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

|||

Erikk Ross wrote:

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.
At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

Any NEW packages created still don't work?|||

Correct, I created a brand new package. Well I first tried to rebuild my old package but that didn't work either, so I just created a new one. Same problems, runs fine on my local workstation, but when imported into SQL Server gives the same error.

I am currently installing standard edition workstation components on a new workstation with Visual Studio and will try to create a package from there and import it and see if that works.

|||Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Phil Brammer wrote:
Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

9.00.3050.00 SP2 Standard Edition (64-bit)

Well now I am completely lost. I just installed the standard edition workstation components on a brand new machine. A machine that has never had SQL Server installed on it. I created a new package in Visual Studio, imported it into our SQL Server, ran it, and still get the same ProductLevelToLow errors.

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion".

|||

After reading this I realized my problem: http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

I was running the package from my workstation using Management Studio, unaware that the package was actually running on my local workstation and not on the server. Since it was running on my workstation it required Integration Services to be installed which it wasn't, so that is why I got those error messages. As soon as I ran it directly from the server it worked fine.

*sigh* I wish this was more obvious. I thought that by running it in Management Studio it was just automatically running it on the server.

Data conversion inserting to DB2 on AS400 with SSIS

I created a SSIS package moving data from a SQL 2005 table to an existing DB2 table on AS400 using Microsoft OLE DB Provider for DB2.

When the package was run, it showed that rows were successfully inserted to DB2. However, the data didn't seem to be converted correctly. Most of the string values were inserted as unusual characters. Also any string values of digits were not inserted.

For example, 1.) a character field (char(1) or nchar(1) as I have tried both types) in SQL 2005 table with a simple value of 'H' was inserted into the DB2 table field of type "A" (alphanumeric) of length 1 as '?' and others letters were inserted as other unusual characters. 2.) A string value of '00100' in SQL Server is not inserted to DB2 table at all.

Later we found that the fields inserted with usual characters are difined as CSSID =65535. A few fields with correct data inserted have CSSID=00037.

Does anyone know why this happened and how to solve this to get the data inserted correctly in the DB2 table?

Thanks in advance for any help!

Try to set the appropriate LocaleID and DefaultCodePage on your destination component.

Thanks.

|||

The LocaleID and DefaultCodePage were set correctly.

What I found was in the Data Link Properties of connection manager, the "Host CCSID" was set to "OEM - United States [437]". I changed it to "EBCDIC - US/Canada [37]" and it started working perfectly.

Thank you for your suggestion though.

Sunday, March 11, 2012

Data Conversion Components & Code Page issue

I am using the SSIS wizard to pull data from DB2 z/os to sql server. The data flow task that is created converts the data to DT_STR Ansi 1252 before storing to sql server database. The package is blowing up on in the data conversion component...no match in found in target code page...for my city name field.

My old dts wizard didn't have this problem. The forums seem to indicate that SSIS is no longer doing some of the implicit conversions that DTS did and I may have to do more than one conversion.

What format type/code page do I use for the other conversion? The code page for my DB2 data source is 37.

I've tried several scenarios and none of them have worked. Any hints?

Quick and dirty work around was to change the destination column to nchar.|||

This one is a good workaround if you don't mind your strings being Unicode at the destination.

If you would rather keep your ANSI strings, then you should go to the package set the appropriate code page in the data conversion transform and set the appropriate collation in the CREATE TABLE statement.

Thanks.

|||

I tried that and couldn't get it to work.

My problem isn't a foreign language issue, but a special character issue. My old DTS packages accepted the special characters without a fuss but I'm told SSIS isn't doing the implicit conversion that the old DTS use to do.

Obviously, I can't seem to figure out which code page to use. And where do I set all of these code pages? My input data is coming from DB2 z/OS which is using code page 37. Do I set the code page of the source component to 37? The destination? The transform component? I tried setting the code page with multiple scenarios...none worked.

There is no where that I can find that explains how to do a code page transformation except in the most general terms.

PS. Wouldn't I only change the collation for a foreign language?

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know?

Thanks in advance,

Jerome Smith

The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database)? I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens? Runs as before. Which database is hit? The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database? No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know?

Thanks in advance,

Jerome Smith

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Thursday, March 8, 2012

Data Binding with ReportViewer Control to an Independent Data Table

Here is what I am trying to do. I am using ASP.NET 2.0. I have created a data set called Stocks data table called Data and put it in the App_Code folder. I am filling the data table from data in a csv file located on the web. I am populating the data table with the following code. This code works because I checked the record count and it shows 21 records for the Data data table.

publicstaticDataSet Convert() { WebClient Client =newWebClient(); Stream myStream = Client.OpenRead("http://mysite.com/table.csv"); StreamReader myStreamReader =newStreamReader(myStream); Stocks myStocks =newStocks(); string line = myStreamReader.ReadLine(); string allData = myStreamReader.ReadToEnd(); string[] rows = allData.Split("\r\n".ToCharArray()); foreach (string rin rows) { string[] items = r.Split(",".ToCharArray()); myStocks.Tables["Data"].Rows.Add(items); } return myStocks; }

I am trying to bind the data in this table to a report. I added a report to my project and selected the fields from the data table. I then added a ReportViewer control to the web form. I'm not sure how to configure the ObjectDataSource that is added to the web form after the ReportViewer control is added. It firsts asks to select business object. I have the following options. I'm not sure what to select.

Stocks

Stocks + DataTable

Stocks + DataRow

Stocks + DataRowChangeEvent

Stocks + DataRowChangeEventHandler

Next I need to define a data method. Not sure what option to select. I just want to bind the report to the data table and return the rows.

Any help would be appreciated. Thanks.

Weste

Once you drag and drop the report viewer in the aspx page, you will get a smart tag next to your report viewe. From that select the repot.rdlc.

Itr will automatically configure the object datasorce for you.

Did you design your report.rdlc using the dataset you created?

|||

Yes, that is what I already tried and it didn't work. It didn't configure the datasource correctly. I received the following error.

An error has occurred during report processing.
The type specified in the TypeName property of ObjectDataSource 'ObjectDataSource1' could not be found.

This is the code that was automatically added after I added the ReportViewer

<asp:ObjectDataSourceID="ObjectDataSource1"runat="server"SelectMethod="GetData"

TypeName="StcoksTableAdapters."></asp:ObjectDataSource>

I'm not sure how to configure the ObjectDataSource that is added to the web form after the ReportViewer control is added. It firsts asks to select business object. I have the following options. I'm not sure what to select.

Stocks

Stocks + DataTable

Stocks + DataRow

Stocks + DataRowChangeEvent

Stocks + DataRowChangeEventHandler

It then asks me to choose a method. Some of the available options are

Clone(), returns DataSet
Copy(), returns DataSet
CreateDataReader(), returns DataTableReader

I have done other reports using a SQL datasource and they work fine. It works somewhat different without a SQL datasource. I am having trouble finding any documentation to assist me. Thanks for your help.

Weste

|||

I resolved the issue. I added a class called StockData. I moved the code for the Convert procedure into this class. I then configured the ObjectDataSource as follows:

Business Object: StockData
Select Method: Convert(), returns DataSet

Saturday, February 25, 2012

Data added before publication created does not replicate - why?

Hello,

I have a small three server development environment where I am getting my feet wet with replication. I have set up peer-to-peer transactional replication and it works fine for data added to the publication's table after the publication was created. However, rows in the table that existed prior to the publication's creation have never replicated. If any of the "old" rows are edited they cause an error on the subscribing servers when the replicator attempts to apply updates to rows that do not exist.

How can I get the old rows that predate the publication to replicate?

Thanks,

BCB

I believe to setup peer-to-peer replication, you'll need to initialize the data at each node through backup and restore. You can find more info here.

http://msdn2.microsoft.com/en-us/library/ms146914.aspx

Gary

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
Regards
You may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.mic rosoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Thursday, March 29, 2012

data file path in SQL 2005

data file path in SQL 2005

data file path in SQL 2005

Tuesday, March 27, 2012

Data feeding a report is cached between different calls for a session?

Data extension and ad hoc reporting

Data Extension - Parameters ?

Sunday, March 25, 2012

Data entry table not editable in Access ADP - what do I need to do

Monday, March 19, 2012

Data Delivery

Data Delivery

Data Conversion supported on Standard Edition?

Data conversion inserting to DB2 on AS400 with SSIS

Sunday, March 11, 2012

Data Conversion Components & Code Page issue

Data Connections: Where the hell are they stored?!!

Data Connections: Where the hell are they stored?!!

Thursday, March 8, 2012

Data Binding with ReportViewer Control to an Independent Data Table

Saturday, February 25, 2012

Data added before publication created does not replicate - why?

Data access advise

Data access advise

Friday, February 24, 2012

Data access advise

Data access advise

Data Flow Task Problem

Blog Archive

About Me