datatype problem

how can i transfer incoming data from flat file which would be a string to my sql table of column int...

i have a problem with datatype can i conver string to int how should i do it...new to it

please help!!

This is what the Data Conversion Task is for. U could also use the Derived Column Task. Place it between source and destination, convert your column there.

What more can I say?

Pipo1

|||Use a derived column transformation.

This is one example of an expression you could use: (DT_I4)[Your_Column]sql

datatype problem

Hi Everyone,

In C# file:

bool abc; (it is working)

But in sql file: (bool is not working. What datatype should use for bool in the sql file?)

CREATE TABLEsb_payment_history (

sb_dispensing bool NULL <================ not working because bool is not known.

)

GO

Thanks,

May

You should use the bit datatype in your SQL table.

DataType change

I recieve a text file with a field that has a text format of 20010910. I import this data into sql server 7.0 utilizing the import wizard. (The wizard will not convert the data to datetime format) I need to know how to convert this data into date format of 2001/09/10. How do I do this in sql.

Thank You,
David A. FullertonYou can add a string column to your table and CONVERT in a post-importing execution or use Visual Basic CDATE in Transformation tab in DTS just before click OK in usual IMPORT... dialog|||CDATE will not work in this example. However, I agree with Cesar's recommendations. In this case, since using vbscript will be more complicated than using cdate you can try the following after the data is loaded:

select convert(varchar(10), cast('20010920' as datetime), 111)|||Thank You That worked..Have a great day!|||Happy to help and good luck.

DataTable and autoincremental Database Fields

Hi,

I got a problem regarding autoincremental Database Fields and DataTables. Using the technique to map the DataBase to an *.xsd file and trying to insert a Row into the table always results in this kind of error:

Cannot insert explicit value for identity column in table 'Cars' when IDENTITY_INSERT is set to OFF.

This only happens when I set CarIDs properties AutoIncrement = true, AutoIncrementSeed = -1, AutoIncrementStep = -1. Having AutoIncrement = false, results in this error:

Column 'CarID' does not allow nulls.

Do I missunderstand something, or do I need to change some parts? I would be glad if someone could help me and figure out what I am doing wrong.

Thanks a lot have a nice weekend.

Regards Johannes

Hi,

i figured out something new. Regarding the line in which the error occurs it happens when I am trying to add a new row into the Datatable.

That's my code:

1[System.ComponentModel.DataObjectMethodAttribute2 (System.ComponentModel.DataObjectMethodType.Insert,true)]3public bool InsertCar(int CarType,string CarNumberPlate,string CarMileage,4string CarServiceRange,bool CarSummerTires,bool CarWinterTires,bool CarHitch,5int CarCarageID, Guid CarUserID, DateTime CarPurchased,bool CarSold)6 {7 Fleet.CarsDataTable cars =new Fleet.CarsDataTable();8 Fleet.CarsRow car = cars.NewCarsRow();91011 car.CarType = CarType;12 car.CarNumberPlate = CarNumberPlate;13 car.CarMileage = CarMileage;14 car.CarServiceRange = CarServiceRange;15 car.CarSummerTires = CarSummerTires;16 car.CarWinterTires = CarWinterTires;17 car.CarHitch = CarHitch;18 car.CarGarageID = CarCarageID;19 car.CarUserID = CarUserID;20 car.CarPurchased = CarPurchased;21 car.CarSold = CarSold;2223 cars.AddCarsRow(car);24int rowsAffected = Adapter.Update(cars);2526return rowsAffected == 1;27 }

The error occurs in line 23. Depeding on the settings in my Fleed.xsd file for CarID, as mentioned above the error changes.

Okay tried something else and fixed it. I reconfigured my TableAdapter and activatedRefresh the data table.No it works perfectly. Ig anyone could explain that to me I would be happy.

Thanks a lot.

Regards Johannes

DataSource uploads as text file type

Hi,
I have a new installation of SQLExpress ADV with RS installed on XP Pro.
When I try to upload an rds file (which has already been running
successfully on a w2k3 server), it uploads as a text file and NOT as a
Datasource.
The associated report does of course not work.
Anyone seen this before - or know how to fix it please '.
Thanks
Regards
GrahamHi Oxns,
Thank you for your posting!
From your description, my understanding of this issue is: you want to
upload the report data source file as a shared data source in the report
manager. If I misunderstood your concern, please feel free to let me know.
You cannot upload a report data source (.rds) file to create a shared data
source. An .rds file is used only in Report Designer. It cannot provide the
content for a shared data source item that you define and manage through
Report Manager or Management Studio. As an alternative to uploading, you
can write a script that creates a shared data source based on a .rds file.
Please refer to the sample script PublishSamplReports.rss file in the
Samples. By default, scripting samples are located in C:\Program
Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples.
Here are the articles for your reference:
Script Samples (Reporting Services)
http://msdn2.microsoft.com/en-us/library/ms160854(d=ide).aspx
Uploading Files to a Folder
http://msdn2.microsoft.com/en-us/library/ms156300(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Oxns,
How are you doing on this issue? Have you got any progress or does Wei's
last reply help you some on this? If the problem is not resolved or there
is anything else we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

DataSet to SQL script?

Hi,Does anyone have a way to generate a SQL 2000 database generation script from a DataSet? I'm looking to load an XML file into a DataSet and use it to create the db design.Matt.

You can load elements/attributes into fields of database tables, which contains information used to create a database. Then you can query out the information, and use it in dynamic sql statement. For example if we want to create such a database using DataFileName, LogFileName, DatabaseName, Size, FileGrowth frommyTable:

DECLARE @.DFN sysname,@.LFN sysname, @.DBN sysname
DECLARE @.Size varchar(4),@.FG varchar(4)
SELECT @.DFN=DataFileName, @.LFN=LogFileName, @.DBN=DatabaseName,
@.Size=Size, @.FG=FileGrowth
FROMmyTable

--select @.DFN='c:\saledat.mdf',@.LFN='c:\salelog.ldf',@.DBN='Sales',
--@.Size='10MB',@.FG='5MB'
EXEC('CREATE DATABASE'+@.DBN+'
ON
( NAME = Sales_dat,
FILENAME ='''+@.DFN+''',
SIZE ='+@.Size+',
MAXSIZE = 50,
FILEGROWTH ='+@.FG+' )
LOG ON
( NAME = Sales_log,
FILENAME ='''+@.LFN+''',
SIZE ='+@.Size+',
MAXSIZE = 25,
FILEGROWTH ='+@.FG+' )')

Note: the aboving create database statement recieves @.Size and @.FG as varchar datatype.

|||Thanks, but I was under the impression that the BulkInsert3 method (SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute() found in xblkld3.dll) would take an xml schema and xml file and actually create the tables, within a specified database, if they were not already present.Do you know if this is actually possible?Matt.|||Actually, to clarify, I would like an automated version of the article mentioned previously (http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/) in order to generate a db (well, the tables) script.This comes from seeing Visual Studio open an XML file, click on XML->Create Schema and View Designer. This shows what I would derive a db table design from.However, currently I am loading the XML into a DataSet and generating the xml schema from that. I may have to simply generate the database tables from looking at the DataSet and then use the bulk insert library.Obviously, my intention here is to do as much of the work with automation as possible (as any good/lazy coder would) but it just looks like some crowbarring is needed.Unless anyone knows how to generate a databases tables from the XML/XML schema or DataSet directly?Matt.

Dataset Manipulation

Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.

I loop through that data to load a check list which also works wonderfully.

two questions based on that.

1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.

2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)

sorry for the fairly basic question. i appreciate the help.

Justin

re #1: You can add columns to dataset tables at any time.

DataColumn _dc =newDataColumn("newcolumnname");

mydataset.Tables[0].Columns.Add(_dc);

re #2: You are asking more than just updating. You are asking how to find the row to update as well.

Create a DataView object to pass in QueryStatements to find the records you want.

Then edit the field value, something such as:

mydataset.Tables[0].Rows[4][

"newcolumnname"] ="newvalue";

mydataset.AcceptChanges();

|||

great, thanks. not really shore how i missed the column.add, i was even looking for that...

I found something else i might try for the locate and update. because it reads from an XML with no schema it doesnt create a PK. I was going to set a PK (MyDataTable.PrimaryKey = PKColumn) and then use the Find method to locate the row i want (myDataTable.Rows.Find(objValue))

not sure how it will workout, going to give it a try. if not i can do as you suggested.

I appreciate the help.

Thanks

Justin

Dataflow where sourcecolumns vary ?

Is it possible to set up at dataflow with for example an excel source that points to a sheet with varying number of colums.

In the file there is some information about number of columns. But the question is here how you use that information to define the columns in the dataflow ?

The columns in the data-flow need to be known at design-time so by and large you cannot chage things at runtime.

There is a workaround though - you can change a package that you are about to execute using the execute package task. Here's how courtesy of Kirk Haselden: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

DataFlow suggestion

I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.

Here is what i do.

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

please help...

Not sure without looking at your data, but look into using the conditional split transformation to perform your logic checks. Use a derived column transformation to add a column.|||

sureshv wrote:

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

sureshv wrote:

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

Use a derived column to add extra columns to data flow pipeline

sureshv wrote:

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

There are tons of threads discussing that issue; the most popular is to use Lookup transform against the destination table; if a match occurs means the row already exists; otherwise it does not.

|||

In the first case.

Data is just a text file with column names in the first row.

eg:

name address telno

xxx yyy zzz

xxx sdfdsf sdfsf

But i dont want the column names coming from text file. instead i want to have my own column names(which i can and know to create)

If i transter data as in the text file the column names would appear in the first row.just to get rid of it i want to remove column name from text file.

In second case:

I use derived column to add a column,and conditional split for bad data.

My point is in the new column how should i insert the status(insert) or )update.

what should i use.

|||

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

I dont want to use this i can have and want to have my own column names.WHich can be done and i know about it.

please look at my reply earlier

|||

sureshv wrote:

My point is in the new column how should i insert the status(insert) or )update.

see if this helps:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

you could add a derived column transform at after each lookup transform and add there a column with the expected value(insert/update)...

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 data
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks :)"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
--
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks :)|||Hi,
I have the same problem, but i´m not a dba expert. How is sintaxe to create
a new table and accurate the it´s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:
> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
> >I am having trouble moving BLOBs (text fields).
> >
> > I have a 200Gb database in a single data file and want to split it into 4
> > even sized files (I have my reasons). To achieve this I plan on adding 3
> > data
> > files to the Primary filegroup. From there I will issue a DBCC dbreindex
> > on
> > all my tables, which effectively recreates the index (or table if its a
> > clustered index) across all datafiles and I get my even distribution.
> >
> > My plan comes unstuck when I encounter text fields and other BLOBs (as the
> > clustered index just contains pointers doesnt it'). How can I move them?
> > Do
> > I have to do a select into or bcp? Can I just issue a shrink file on the
> > oringal large file or does that just push pages from the end of that data
> > file to the beginning and not to other datafiles'
> >
> > Any suggestions would be great, I don't really want to create another
> > filegroup just yet.
> >
> > Thanks :)
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...
> Hi,
> I have the same problem, but i´m not a dba expert. How is sintaxe to
> create
> a new table and accurate the it´s will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>> You will need to create a new filegroup so you can create a new table on
>> this filegroup. Copy the old data to the new table. Drop the old table.
>> Rename the new tb to the old name. And then shrink the db to reclaim the
>> space.
>> --
>> -oj
>>
>> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
>> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>> >I am having trouble moving BLOBs (text fields).
>> >
>> > I have a 200Gb database in a single data file and want to split it into
>> > 4
>> > even sized files (I have my reasons). To achieve this I plan on adding
>> > 3
>> > data
>> > files to the Primary filegroup. From there I will issue a DBCC
>> > dbreindex
>> > on
>> > all my tables, which effectively recreates the index (or table if its a
>> > clustered index) across all datafiles and I get my even distribution.
>> >
>> > My plan comes unstuck when I encounter text fields and other BLOBs (as
>> > the
>> > clustered index just contains pointers doesnt it'). How can I move
>> > them?
>> > Do
>> > I have to do a select into or bcp? Can I just issue a shrink file on
>> > the
>> > oringal large file or does that just push pages from the end of that
>> > data
>> > file to the beginning and not to other datafiles'
>> >
>> > Any suggestions would be great, I don't really want to create another
>> > filegroup just yet.
>> >
>> > Thanks :)
>>

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 dat
a
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks |||Hi,
I have the same problem, but i′m not a dba expert. How is sintaxe to create
a new table and accurate the it′s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:

> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I have the same problem, but im not a dba expert. How is sintaxe to
> create
> a new table and accurate the its will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>

Datafile Full

Hi NG,
We have a datafile on drive d: and the log file on drive e: and now the
problem is drive d: is running out of space. We are planning to buy a new
hardisk drive f: what should I do next after we install the new hardisk?
Should I create a new datafile and include that in the primary filegroup and
set the new datafile as default. Is that all I need to do?
Thank you in advance.
its 1 option, yes.
just disable the file growth option for the first file (the one on the d
you can also create a new file group and move some tables from the primary
file group to the new one.
"JackCool" <jackcool@.yahoo.com> wrote in message
news:upMbY3aKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> We have a datafile on drive d: and the log file on drive e: and now the
> problem is drive d: is running out of space. We are planning to buy a new
> hardisk drive f: what should I do next after we install the new hardisk?
> Should I create a new datafile and include that in the primary filegroup
> and set the new datafile as default. Is that all I need to do?
> Thank you in advance.
>

Datafile Full

Hi NG,
We have a datafile on drive d: and the log file on drive e: and now the
problem is drive d: is running out of space. We are planning to buy a new
hardisk drive f: what should I do next after we install the new hardisk?
Should I create a new datafile and include that in the primary filegroup and
set the new datafile as default. Is that all I need to do?
Thank you in advance.its 1 option, yes.
just disable the file growth option for the first file (the one on the d
you can also create a new file group and move some tables from the primary
file group to the new one.
"JackCool" <jackcool@.yahoo.com> wrote in message
news:upMbY3aKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> We have a datafile on drive d: and the log file on drive e: and now the
> problem is drive d: is running out of space. We are planning to buy a new
> hardisk drive f: what should I do next after we install the new hardisk?
> Should I create a new datafile and include that in the primary filegroup
> and set the new datafile as default. Is that all I need to do?
> Thank you in advance.
>

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
Jerry
On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:

>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT
|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
--
JerryOn Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>>Can SQL Server 2000 import a flat data file directly from a mainframe
>>or other server that is not a Windows server or does the data file
>>need to be moved to a Windows server first?
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
JerryOn Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:

>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.
4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

DataFile and Log File

I want to know the datafile and log file for the given input parameter. I am
basically creating function and the input parameter would be database name
and that will return me DataFile and the LogFile.
Can anybody tell me which query I have to used in the function. I have to
used to get the DataFile and LogFile
Have a look at the sys.database_files catalog view in BOL.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>I want to know the datafile and log file for the given input parameter. I
>am basically creating function and the input parameter would be database
>name and that will return me DataFile and the LogFile.
> Can anybody tell me which query I have to used in the function. I have to
> used to get the DataFile and LogFile
>
|||In SQL Server 2000?
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGIt1IRiHHA.392@.TK2MSFTNGP06.phx.gbl...
> Have a look at the sys.database_files catalog view in BOL.
> --
> Andrew J. Kelly SQL MVP
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>
|||On Sat, 28 Apr 2007 10:42:15 -0400, Roger wrote:

>In SQL Server 2000?
Hi Roger,
A more polite way to write this would have been; "Oh, I'm sorry I forgot
to include in my original post that I'm using SQL Server 2000. Is there
an alternative method for that version?"
The answer to that question would have been to check the sysfiles system
table in Books Online.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Data-Driven Subscription Error

I working through the 'Creating a Data-Driven Subscription' walkthrough
supplied as part of the BOL documentation, using Report Server File
Share as the delivery method. It cannot generate the report using the
three sets of dummy user details. The resulting status is: 'Done: 3
processed of 3 total: 3 errors'. How can I resolve this error?Sounds like possibly there was an error in writing out the files to the
file share (...since 3 of 3 procesed).
Open the ReportSever_ error log (C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\LogFiles), and do a Find on 'Success:
False' to locate errors in the log, using the timestamps to narrow
down the entries you look at. Errors could include illegal file names
or permissions on file share etc.
Matt A

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James Colbert
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>
|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James
|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James ColbertSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx
.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
---
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James