Data File and Filegroup in DB Properties

Hi All,
Is it possible to change db file to single file from multiple files?
Thanks any help
MelihMelih wrote:
> Hi All,
> Is it possible to change db file to single file from multiple files?
> Thanks any help
> Melih
Look up DBCC SHRINKFILE, particularly the EMPTYFILE clause.|||Melih,
Sure, you can remove a file from a filegroup when it is empty, using "alter
database". To empty the file use "DBCC SHRINKFILE(file_name | fiel_id,
EMPTYFILE)", it move the data to others files in the filegroup and mark it so
no more data is added to it.
Example:
use northwind
go
alter database northwind
add file (name=northwind_2,filename='c:\temp\northwind_data_2.ndf',size=512KB)
go
-- not needed in this case because it is empty
dbcc shrinkfile(northwind_2, EMPTYFILE)
go
alter database northwind
remove file northwind_2
go
AMB
"Melih" wrote:
> Hi All,
> Is it possible to change db file to single file from multiple files?
> Thanks any help
> Melih

Data File and Filegroup in DB Properties

Melih wrote:
> Hi All,
> Is it possible to change db file to single file from multiple files?
> Thanks any help
> Melih
Look up DBCC SHRINKFILE, particularly the EMPTYFILE clause.Melih,
Sure, you can remove a file from a filegroup when it is empty, using "alter
database". To empty the file use "DBCC SHRINKFILE(file_name | fiel_id,
EMPTYFILE)", it move the data to others files in the filegroup and mark it s
o
no more data is added to it.
Example:
use northwind
go
alter database northwind
add file (name=northwind_2,filename='c:\temp\nort
hwind_data_2.ndf',size=512K
B)
go
-- not needed in this case because it is empty
dbcc shrinkfile(northwind_2, EMPTYFILE)
go
alter database northwind
remove file northwind_2
go
AMB
"Melih" wrote:

> Hi All,
> Is it possible to change db file to single file from multiple files?
> Thanks any help
> Melih|||Hi All,
Is it possible to change db file to single file from multiple files?
Thanks any help
Melih|||Melih wrote:
> Hi All,
> Is it possible to change db file to single file from multiple files?
> Thanks any help
> Melih
Look up DBCC SHRINKFILE, particularly the EMPTYFILE clause.|||Melih,
Sure, you can remove a file from a filegroup when it is empty, using "alter
database". To empty the file use "DBCC SHRINKFILE(file_name | fiel_id,
EMPTYFILE)", it move the data to others files in the filegroup and mark it s
o
no more data is added to it.
Example:
use northwind
go
alter database northwind
add file (name=northwind_2,filename='c:\temp\nort
hwind_data_2.ndf',size=512K
B)
go
-- not needed in this case because it is empty
dbcc shrinkfile(northwind_2, EMPTYFILE)
go
alter database northwind
remove file northwind_2
go
AMB
"Melih" wrote:

> Hi All,
> Is it possible to change db file to single file from multiple files?
> Thanks any help
> Melih

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I need
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!
Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
Good luck.
sql

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I nee
d
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/pr...y/dtssql2k.mspx
Good luck.

Data Driven Subscriptions

Hi !!
I want to create a scheduled delivery, with email & file share used together
with a single execution on a report. This means that i will sent the report
attachment thru an email to the recipient, and the same time copy that report
using the file share in a folder. Is this possible to implement?
This is because, I can only execute the report only once due to an updation
being done to the database.
Thanks - PeteSubscriptions only support sending through 1 delivery extension. Have you
looked into setting up the report to run on an Execution snapshot? This
would allow you to control when the report will run and all renderings of
the report will come off the snapshot and not the data. You could then
create two subscriptions running off either a shared schedule of when the
report execution snapshot is updated.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:36D77E8B-1768-44AA-955E-D459516C7D41@.microsoft.com...
> Hi !!
> I want to create a scheduled delivery, with email & file share used
together
> with a single execution on a report. This means that i will sent the
report
> attachment thru an email to the recipient, and the same time copy that
report
> using the file share in a folder. Is this possible to implement?
> This is because, I can only execute the report only once due to an
updation
> being done to the database.
> Thanks - Pete

Data Conversion

I have single letters in a Flat file and it is in the string format,but now i want to convert it in to int format.I have tried doing this by SSIS but it is not working.
I used data conversion and copy column transformation.

I got a error message.

it automatically correct the meta data mismatch. But when i run the package, it gives the following error message.

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

This is just one error message.

Please can you tell me the steps to do this correctly.

Also I need to know how to run a SSIS package through command line?

Thanks

Nishan

Did you say you were trying to convert letters to int? Can't do that. Is there any more info?|||

I have a flat file which has string types of data. I want to convert them into int type before I load them into destination table. I used data convertion and derived column but neither worked for me.

Ex;

Source file (String type);

column 1

B

B

C

C

Destination file format should look like this. B - > 1 , C - > 2

Destination File ( int Type);

Column 1

1

1

2

2

Can you explain the setps to do this conversion using SSIS

Thanks

|||The Derived Column task should work for you: Use the conditional operator|||Shamen,
Please keep your posts together in one thread.

This can be done in a lookup transformation. Use the following SQL for the lookup:

select "B",1
union all
select "C",2
union all
select "D",3"
....

Then hook up your source to the lookup component, selecting the second column as the return value. Coming out of the lookup component, you'll have the number associated with the letter and can then go into a flat file destination.|||

Thanks Pill and SQL Pro...I will try..at the same time I want to convert the data type too.

Yes I will keep all my posting together in one thread...

Also I have another question...Once I create the package can I run it on command line? How can I do that?

Thanks

|||Thanks I will try|||

shamen wrote:

Also I have another question...Once I create the package can I run it on command line? How can I do that?

DTEXEC runs packages from the command line.

|||

Thanks....Still I'm having a problem with data conversion...I tried using both derived column and data conversion transformation....but still no luck...

This is the error message I got when I tried with data conversion transformation.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [75]: Data conversion failed while converting column "ReceiptType" (67) to column "ReceiptType" (112). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (75) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has ended.

Information: 0x402090DF at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

The program '[4360] Package.dtsx: DTS' has exited with code 0 (0x0).

|||

You can use the CODEPOINT function to return a numeric value for a character. That means you can use an expression in a Derived Column transform to do the work:

Expression: CODEPOINT(UPPER(YourColumn))-64

(the 64 is there because CODEPOINT("A") = 65)

This will work nicely where you have a one-character value to transform.

You can craft a more elaborate expression for multiple characters if required.

Dylan.

|||

Thanks Dylan...But at the same time I want to convert data type from String to Int.....

I'm supposed to get specific value for specific letters. As a example for letter B I'm supposed to get 1.

B - > 1

C- > 2

But first of all I have to figure it out how to convert data from string to Int.

Thanks

|||

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

|||

OK, but as pointed out above, you are on a fruitless quest.

A letter is not a number, so it cannot be converted to an int.

What you are saying is like asking how mainy legs a daisy has, or how many teeth in an elephant's trunk. It just doesn't make sense to talk about converting a letter into an int. By definition an int is a number, which is not a letter.

That said, what you CAN talk about is removing the column with the letter and replacing it with a corresponding column containing an int.

Then it is just a matter of defining the rule by which you convert your letter into a number.

One such rule is to say there is a specific mapping. So B=2, F=61, R=12.

In this case the select statement above is a good way of performing that mapping. However you can't deal with unknown values. This is like saying "African elephants have 6 teeth in their trunk. Asian elephants have 9 teeth." Using this rule, you can answer the question "How many teeth in an African elephant's trunk?" but not "How many teeth in an Australian elephant's trunk?" because you have no information about Australian elephants.

The other approach is to say there is a formula, so B=2, C=3, D=4, E=5. In this case you can say that even if you have never used an "F", if one were to be used for some reason then it would be equal to 6. That is where functions like CODEPOINT can help.

This is like saying "All daisys have twice as many legs as petals." Now you can look at any daisy, and answer the question "How many legs on my daisy?"

Notice that what we have done is create two different methods for answering questions that do not make sense in the real world. And likewise, we have revealed two different methods for turning a letter into an int, even though doing so does not make sense.

You should stop thinking about turning your letter into an int, and focus instead on dropping the column and replacing it with a derived int column. You are not converting anything, you are transforming and replacing. If it looks like "converting" to your pointy-haired managers, then that's fine, but you will know better on the inside.

Good luck,

Dylan.

|||

jwelch wrote:

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

That would depend on context and maintainability requirements.

If the letter value can be derived by formula rather than by lookup, that would be more appropriate in my opinion. If you are hardcoding values in a lookup, then you are creating a potential maintenance problem.

In addition, creating a complex union query where there are only ever two values to look up would be overcomplicated when an IF expression would be simpler.

Without knowing more about the requirements and implementation specifics, it is risky to talk about which solution is simplest. Normally I wouldn't care, but I've taken issue in this case because I've had to maintain a fair amount of work recently that was done using this "simplest" approach. Two years later, it has turned out to be a major pain.

|||

OK...Well I will try...

Thanks all

Damaged database

I in a real catastrophic scenario in a SQL2000
installation:
Due to a hardware failure:
I HAVE A SINGLE MDF, NOTHING ELSE .
The database originally was formed with this single mdf
and a single ldf, and its recovery model was simple.
I tried sp_attach_single_file_db as documented, but it
returned errors.
I tried to out smart the engine and create a database
with same name, and BORROW its log, but when attaching
with my lonely mdf, the engine returned a logical error
massage telling me that these two files doesn't belong .
Please can anyone help ?If that didn't work then you're in a bit of trouble but you can try this
**Make sure you have a copy of your MDF first and a backup of master**
Replace the filenames with your filename !!!
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following (make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names */
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME = 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the log file
will be small so it will be worth increasing its size
Unfortunately your files will be called fake_Data.MDF and
fake_Log.LDF but you can get round this by detaching the
database properly and then renaming the files and reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ATS967" <anonymous@.discussions.microsoft.com> wrote in message
news:098d01c3a06f$83c18e20$a301280a@.phx.gbl...
I in a real catastrophic scenario in a SQL2000
installation:
Due to a hardware failure:
I HAVE A SINGLE MDF, NOTHING ELSE .
The database originally was formed with this single mdf
and a single ldf, and its recovery model was simple.
I tried sp_attach_single_file_db as documented, but it
returned errors.
I tried to out smart the engine and create a database
with same name, and BORROW its log, but when attaching
with my lonely mdf, the engine returned a logical error
massage telling me that these two files doesn't belong .
Please can anyone help ?

Customizing replication

Hi,
Is it possible to replicate some field from multiple tables (as a result of select query with joins) at source to a single table at target ?
For example i have two tables at source 'source_table1' and 'source_table2' and one table at target namely 'target_table'. Now i want Field1 from 'source_table1' and field1 from 'source_table2' to be replicated into 'target_table'.
pictorial depiction of behavious i need is as under:
Tables at Source :
Table 1 Table at Target:
\ _________ Table
/
Table 2
1) Is is possible using SQL Server replication ? If not is there any workaround ?
2) Is is a good practice to replicate GBs of data from source to target over internet with security being an issue ?
Thanks in advance,
Hatim Ali.
1) it sure is, you create a custom sync object. The problem is that the log
reader can generate sql statements or insert procs based on updates of a
single object.
So one your publication or article will have to key off one table, and
probably replicate the second table as well so your custom proc can read
this data and merge it to the target table.
The simple way of doing this is using an indexed view.
2) To be secure you should use a VPN. Replicating Gbs of data can be
difficult, but it can be done. Security is a matrix of risk and liability.
Risk is small using FTP, anonymous authentication, and only allowing a range
of IP addresses to download your snapshot, but the liability can be
signficant.
I have had a client who couldn't care less if you snag their data because it
has a very high time value (they were a news agency). Then I have financial
clients and if I mention the word FTP I loose all credibility - not that I
have much to begin with mind you
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
news:99A07D96-B98C-41CC-947D-05F35FB8E018@.microsoft.com...
> Hi,
> Is it possible to replicate some field from multiple tables (as a result
of select query with joins) at source to a single table at target ?
> For example i have two tables at source 'source_table1' and
'source_table2' and one table at target namely 'target_table'. Now i want
Field1 from 'source_table1' and field1 from 'source_table2' to be replicated
into 'target_table'.
> pictorial depiction of behavious i need is as under:
> Tables at Source :
> Table 1 Table at Target:
> \ _________ Table
> /
> Table 2
> 1) Is is possible using SQL Server replication ? If not is there any
workaround ?
> 2) Is is a good practice to replicate GBs of data from source to target
over internet with security being an issue ?
> Thanks in advance,
> Hatim Ali.
>
>