How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx
.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some oth
er filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by cre
ate a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you ne
ed to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the
DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear
. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports
Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
H
Hi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H
|||Thanks Hari
I'll give it a go!
Regards
H
|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H
|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
--
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

how do i look through the log to see updates to tables in sql sever 2005?

hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?I posted a reply in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=655927&SiteID=1|||

hi winston,

if you want a cheaper alternative to know what has changed

you can use a database snapshot solution

as a baseline for comparison.

there's no easy way of looking at the logs

you have to use a 3rd party tools such as lumigent or apex sql

joey

|||thanks lads. i ended up getting ApexSQL Log.

How do I load SQL Samples?

Hello,

I am a total beginner to SQL and I'm unable to load the samples. I downloaded the .msi file and installed it but I don't know where to go from here.

Am I importing one of the samples in SQL or do I open them in Visual Basic?

The samples folder has several sub-folders with no executable so I don't know where to begin.

Can someone point me in the right direction?

thanks in advance.

Chris

Try this reference:

http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/GettingStartedWithSQLSamples.htm

|||

I read that reference and I'm still not sure how to load the samples. I don't know if I'm supposed to open them in VB or SQL and if so, which file?

I navigated to the "samples" folder on my local drive and there are eight folders "analysis services", "engine", "Integrated samples"......"Integration services", "notification services", "replication" and "reporting services.

Not really sure where to start.

Any and all help is appreciated.

Chris

|||

You need to read in Books Online (the SQL Reference) about using the ATTACH keyword.

How do I link two different field lengths with the same data

Hi,
In Crystal Reports 8.5, I am linking one file to another but do not get report data. The reason is the fields I am using to link one string is 4 characters while the other is 6. Is there a way to get two fields containing the same data in different files but wth different field lengths to link. thank youBump. This post is old, but I'm needing to do basically the same thing. I've got Crystal Reports 11.

Thanks

How do I link tables to another database?

With Access, I could link to tables in a foreign db, even a csv file, and
query them as if they were local tables. How do I do that in SQL Server?Check out OPENROWSET and OPENQUERY in BOLsql

How do I launch an agent job from command line?

What is the easiest way to execute a SQL Agent (2005) job from the command line? I need to make a batch file to execute a reporting service shared schedule job.

Thanks in advance.

Refer to Books Online, Topic: 'DTExec utility'

DTExec.exe is the replacement for SQL 2000's DTSRun.exe.

|||Correct me if i'm wrong, but I think DTExec is just used for SSIS packages? I need to run the entire job. The job was generated by Reporting Services and I need to call it manually. Thanks a lot.|||

You're correct. I mis-interpreted your question.

You can use SQLCmd.exe, combined with a call to sp_startJob.

(If you are using SQL 2000, then use OSQL.exe instead of SQLCmd.)

Refer to Books Online for the complete syntax for either.

|||

Thanks a lot, I couldn't get the stored procedure to work but i just used the code that it mentioned in the Job properties window. The exact code for sqlcmd was: sqlcmd -Q "exec ReportServer.dbo.AddEvent @.EventType ='SharedSchedule', @.EventData='0dd48b88-6c35-4f3c-913e-b5299de948db'"

Thanks much.

How do I know which record is being updated in Update trigger?

Hi All,
Thanks in advance!
I want to generate an XML file in a Update trigger script.
Only the updated record needs to be exported to the XML file.
How do I know which record is being updated in the Update trigger?
Thank you so much!
Regards,
SeanSean,
Could be more than one record. Use the DELETED and INSERTED pseudo/logical
tables available within the firing of the trigger.
HTH
Jerry
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Define being updated. If you mean the rows that were touched, even if all
values remain the same:
select *
from inserted
If you mean those that have actually changed:
select *
from inserted
join deleted
on <some unchangable value, like a surrogate (identity)
key>
where inserted.col1 <> deleted.col2 --not nullable
or (inserted.col2 <> deleted.col2
or (inserted.col2 is null and deleted.col2 is not null)
or (inserted.col2 is not null and deleted.col2 is null) )
If you mean something else, then please provide more info.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Dear Louis,
I need all the records that have changed and inserted.
That means a modified record and new record.
For example:
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
I have a problem detecting the @.@.identity. I could not get the information
of which records have been modified and inserted.
Thank you very much!
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Define being updated. If you mean the rows that were touched, even if all
> values remain the same:
> select *
> from inserted
> If you mean those that have actually changed:
> select *
> from inserted
> join deleted
> on <some unchangable value, like a surrogate (identity)
> key>
> where inserted.col1 <> deleted.col2 --not nullable
> or (inserted.col2 <> deleted.col2
> or (inserted.col2 is null and deleted.col2 is not null)
> or (inserted.col2 is not null and deleted.col2 is null) )
> If you mean something else, then please provide more info.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
>|||Yeah, you cannot rely on identities to identify a row like this. You really
need to have another key that you can expect not to change. Since you
insert the values, you know them and can trust them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
> Dear Louis,
> I need all the records that have changed and inserted.
> That means a modified record and new record.
> For example:
> CREATE TRIGGER trgUpdate
> ON User_Master
> FOR UPDATE
> AS
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
> REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
> SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
> CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
> EXEC master..xp_cmdshell @.bcpCommand
> I have a problem detecting the @.@.identity. I could not get the information
> of which records have been modified and inserted.
> Thank you very much!
>
> Regards,
> Sean
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Dear Louis,
Thanks for you information.
It helps a lot!
I am trying to copy the modified or new data from User_Master into a
User_MasterTemp table using an Update trigger.
CREATE TRIGGER trgUpdate ON User_Master
FOR UPDATE
AS
Delete From User_MasterTemp
Insert into User_MasterTemp Select * From Inserted
GO
Then do a exporting to XML file from the temp table using an Insert trigger.
CREATE TRIGGER User_MasterInsert ON [dbo].[User_MasterTemp]
FOR INSERT
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_MasterTemp for xml raw"
queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
Hopefully, the code can work. I am doing the troubleshooting cause the
script in the Insert trigger hangs the SQL Server.
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O04yriG0FHA.916@.TK2MSFTNGP10.phx.gbl...
> Yeah, you cannot rely on identities to identify a row like this. You
> really need to have another key that you can expect not to change. Since
> you insert the values, you know them and can trust them.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
>

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

May I suggest posting this question on sql server programming news group?

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming

Thanks

|||

You might get extra help in sql server programming news group for these kind of questions.

thanks

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming

|||Thanks. I'll do that.

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."

How do I insert data from a flat file into an existing SQL databas

How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all together.
.
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC"
)
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKSYou can use the command line utility - bcp.exe to load from a flat file.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bermychild" <Bermychild@.discussions.microsoft.com> wrote in message
news:7BDDC48D-4D33-42E5-B69B-11976FE75CC4@.microsoft.com...
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all
together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS|||Try to use BCP or BULK INSERT.
BCP can be done from the command line
BULK INSERT would be done from inside Query Analyzer.
For example: Store the following in a .bat file and run...
----
--
bcp [database].[owner].[tablename]
IN "D:\DataFilePath\DataFileName.ext"
-f D:\FormatFilePath\FormatFileName.fmt
-o D:\OutputFilePath\OutputFileName.fmt
-m 10000 -F 1 -c -S [ServerName] -T
----
--
Since this will be the first time you are runnning, remove the line
with
" -f D:\FormatFilePath\FormatFileName.fmt"
The bcp utility will prompt you when u run it and create the Format
File automatically.
The BULK INSERT Format is easier
BULK INSERT
database_name.schema_name.table_name
FROM 'data_file'
WITH
(
, FIELDTERMINATOR = ','
, FIRSTROW = 1
, KEEPNULLS
, MAXERRORS = 10000
, ROWTERMINATOR = '\n'
, TABLOCK
, ERRORFILE = 'file_name'
)
Also, if the flat file data is not in the same format as the
destination table, I recommend creating a TEMP TABLE...
CREATE TABLE ##bulkinsert() to match the specification of the datafile,
BULK INSERT or BCP into that file, and then use a normal SQL Query to
transport data from the temp table to the final table. This also allows
you the opportunity to clean the data in the temp table by removing
quotation marks, and filtering out invalid data prior to moving data to
your production table.s|||If you're using .Net 2.0, you can look at my component I sell,
http://www.csvreader.com , that gives syntax like below that I took off
my code samples page. It basically inserts at DTS speed and doesn't
have some of the issues as BCP and BULK INSERT.
Using loader As CsvDataReader = New CsvDataReader("somefile.csv")
loader.HasHeaders = True
loader.Columns.Add("varchar") ' First
loader.Columns.Add("varchar") ' Last
loader.Columns.Add("datetime") ' First Sale
loader.Columns.Add("money") ' Amount
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy("Data
Source=myServer;Initial Catalog=Test;User ID=sa;Password=")
bulkCopy.DestinationTableName = "Customer"
bulkCopy.WriteToServer(loader)
End Using
End Using
Bruce Dunwiddie
dizzler wrote:
> Try to use BCP or BULK INSERT.
> BCP can be done from the command line
> BULK INSERT would be done from inside Query Analyzer.
> For example: Store the following in a .bat file and run...
> ----
--
> bcp [database].[owner].[tablename]
> IN "D:\DataFilePath\DataFileName.ext"
> -f D:\FormatFilePath\FormatFileName.fmt
> -o D:\OutputFilePath\OutputFileName.fmt
> -m 10000 -F 1 -c -S [ServerName] -T
> ----
--
> Since this will be the first time you are runnning, remove the line
> with
> " -f D:\FormatFilePath\FormatFileName.fmt"
> The bcp utility will prompt you when u run it and create the Format
> File automatically.
>
> The BULK INSERT Format is easier
>
> BULK INSERT
> database_name.schema_name.table_name
> FROM 'data_file'
> WITH
> (
> , FIELDTERMINATOR = ','
> , FIRSTROW = 1
> , KEEPNULLS
> , MAXERRORS = 10000
> , ROWTERMINATOR = '\n'
> , TABLOCK
> , ERRORFILE = 'file_name'
> )
>
> Also, if the flat file data is not in the same format as the
> destination table, I recommend creating a TEMP TABLE...
> CREATE TABLE ##bulkinsert() to match the specification of the datafile,
> BULK INSERT or BCP into that file, and then use a normal SQL Query to
> transport data from the temp table to the final table. This also allows
> you the opportunity to clean the data in the temp table by removing
> quotation marks, and filtering out invalid data prior to moving data to
> your production table.ssql

How Do I Increase File Upload Size?

Does anyone know what the max upload size is? I want to save some documents
with my reports, but I get the following errors:
"Maximum request length exceeded."
or
"There was an exception running the extensions specified in the config file.
--> Maximum request length exceeded."
Thanks in advance,
-RGJIs the report server running on Windows 2003 with IIS 6? IIS 6 has a
security restriction of a default 4 MB file upload/download limit. This is
due to a buffering restriction implemented in IIS 6.0
(AspMaxRequestEntityAllowed setting in MetaBase.xml).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> Does anyone know what the max upload size is? I want to save some
documents
> with my reports, but I get the following errors:
> "Maximum request length exceeded."
> or
> "There was an exception running the extensions specified in the config
file.
> --> Maximum request length exceeded."
> Thanks in advance,
> -RGJ|||You got my hopes up :-)
Yes, It is running on Windows 2003 with IIS 6. I have tried changing that
value, but no luck.
Here is the documentation I found on that setting:
he AspMaxRequestEntityAllowed property specifies the maximum number of bytes
allowed in the entity body of an ASP request. If a Content-Length header is
present and specifies an amount of data greater than the value of
AspMaxRequestEntityAllowed, IIS returns a 403 error response. This property
is related in function to MaxRequestEntityAllowed, but is specific to ASP
request. Whereas you might set the MaxRequestEntityAllowed property to 1 MB
at the general World Wide Web Publishing Service (WWW Service) level, you may
choose to set AspMaxRequestEntityAllowed to a lower value, if you know that
your specific ASP applications handle a smaller amount of data.
I also tried increasing the AspBufferingLimit which has a 4Mb limit to 40Mb
- no good
The AspBufferingLimit property sets the maximum size of the ASP buffer. If
response buffering is turned on, this property controls the maximum number of
bytes that an ASP page can write to the response buffer before a flush occurs.
Do you know if there is a setting in web.config that might override this?
Thank you for your assistance,
-RGJ
"Robert Bruckner [MSFT]" wrote:
> Is the report server running on Windows 2003 with IIS 6? IIS 6 has a
> security restriction of a default 4 MB file upload/download limit. This is
> due to a buffering restriction implemented in IIS 6.0
> (AspMaxRequestEntityAllowed setting in MetaBase.xml).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "RGJ" <RGJ@.discussions.microsoft.com> wrote in message
> news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> > Does anyone know what the max upload size is? I want to save some
> documents
> > with my reports, but I get the following errors:
> >
> > "Maximum request length exceeded."
> > or
> > "There was an exception running the extensions specified in the config
> file.
> > --> Maximum request length exceeded."
> >
> > Thanks in advance,
> >
> > -RGJ
>
>|||Please verify that in IIS Manager, you have turned on "Enable direct
metabase edit / all­ow changes to MetaBase configuration while IIS is
running" before changing metabase.xml. Otherwise the changes will be
ignored! Also did you try to run iisreset?
You should also be able to do this from the command-line:
1. cscript adsutil.vbs get w3svc/aspmaxrequestentityallowed
2. replace 'get' to 'set' to configure the new value
3. then get again
Is it new or the old value?
-Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:5947747C-0259-4424-BA3D-272FECD7D33C@.microsoft.com...
> You got my hopes up :-)
> Yes, It is running on Windows 2003 with IIS 6. I have tried changing that
> value, but no luck.
> Here is the documentation I found on that setting:
> he AspMaxRequestEntityAllowed property specifies the maximum number of
bytes
> allowed in the entity body of an ASP request. If a Content-Length header
is
> present and specifies an amount of data greater than the value of
> AspMaxRequestEntityAllowed, IIS returns a 403 error response. This
property
> is related in function to MaxRequestEntityAllowed, but is specific to ASP
> request. Whereas you might set the MaxRequestEntityAllowed property to 1
MB
> at the general World Wide Web Publishing Service (WWW Service) level, you
may
> choose to set AspMaxRequestEntityAllowed to a lower value, if you know
that
> your specific ASP applications handle a smaller amount of data.
> I also tried increasing the AspBufferingLimit which has a 4Mb limit to
40Mb
> - no good
> The AspBufferingLimit property sets the maximum size of the ASP buffer. If
> response buffering is turned on, this property controls the maximum number
of
> bytes that an ASP page can write to the response buffer before a flush
occurs.
> Do you know if there is a setting in web.config that might override this?
> Thank you for your assistance,
> -RGJ
> "Robert Bruckner [MSFT]" wrote:
> > Is the report server running on Windows 2003 with IIS 6? IIS 6 has a
> > security restriction of a default 4 MB file upload/download limit. This
is
> > due to a buffering restriction implemented in IIS 6.0
> > (AspMaxRequestEntityAllowed setting in MetaBase.xml).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "RGJ" <RGJ@.discussions.microsoft.com> wrote in message
> > news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> > > Does anyone know what the max upload size is? I want to save some
> > documents
> > > with my reports, but I get the following errors:
> > >
> > > "Maximum request length exceeded."
> > > or
> > > "There was an exception running the extensions specified in the config
> > file.
> > > --> Maximum request length exceeded."
> > >
> > > Thanks in advance,
> > >
> > > -RGJ
> >
> >
> >|||Robert,
You were close. Thanks for putting me on the right track.
I was able to increase the File Upload by following this article ..
====================================================
Upload Larger Files
By default, ASP.NET only permits files that are 4,096 kilobytes (KB) (or 4
MB) or less to be uploaded to the Web server. To upload larger files, you
must change the maxRequestLength parameter of the <httpRuntime> section in
the Web.config file.
Note When the maxRequestLength attribute is set in the Machine.config file
and then a request is posted (for example, a file upload) that exceeds the
value of maxRequestLength, a custom error page cannot be displayed. Instead,
Microsoft Internet Explorer will display a "Cannot find server or DNS" error
message.
If you want to change this setting for all of the computer and not just this
ASP.NET application, you must modify the Machine.config file.
By default, the <httpRuntime> element is set to the following parameters in
the Machine.config file:
<httpRuntime executionTimeout="90" maxRequestLength="4096"
useFullyQualifiedRedirectUrl="false" minFreeThreads="8"
minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
The Machine.config file is located in the \System
Root\Microsoft.NET\Framework\Version Number\CONFIG directory.
====================================================
I modifiedthe machine.config file and reboot - and it worked !!!
Thanks for your help,
-RGJ|||Thanks for getting back and sharing how you resolved it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:609C3712-46CF-451A-AC98-B09864097228@.microsoft.com...
> Robert,
> You were close. Thanks for putting me on the right track.
> I was able to increase the File Upload by following this article ..
> ====================================================> Upload Larger Files
> By default, ASP.NET only permits files that are 4,096 kilobytes (KB) (or 4
> MB) or less to be uploaded to the Web server. To upload larger files, you
> must change the maxRequestLength parameter of the <httpRuntime> section in
> the Web.config file.
> Note When the maxRequestLength attribute is set in the Machine.config file
> and then a request is posted (for example, a file upload) that exceeds the
> value of maxRequestLength, a custom error page cannot be displayed.
Instead,
> Microsoft Internet Explorer will display a "Cannot find server or DNS"
error
> message.
> If you want to change this setting for all of the computer and not just
this
> ASP.NET application, you must modify the Machine.config file.
> By default, the <httpRuntime> element is set to the following parameters
in
> the Machine.config file:
> <httpRuntime executionTimeout="90" maxRequestLength="4096"
> useFullyQualifiedRedirectUrl="false" minFreeThreads="8"
> minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
> The Machine.config file is located in the \System
> Root\Microsoft.NET\Framework\Version Number\CONFIG directory.
> ====================================================> I modifiedthe machine.config file and reboot - and it worked !!!
> Thanks for your help,
> -RGJ
>

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

How do I import Excel file into SQL Express??

Hello,

I am total beginner to SQL and want to import an Excel file into SQL but I'm not sure where to start. The Excel file is a small company directory, names, addresses etc., not too big.

Any help would be greatly appreciated!!!!hi..
my adivice is dont use excel, create a MS-Access data base and import that into sql..|||thanks for the post.

Assuming I am able to create an MS Access database, what are the steps to importing this file into SQL?

thanks|||1.right click on any database
2.select TASKS and then select IMPORT DATA after clicking
this one dialog box will appear,click NEXT
3.Choose MICROSOFT ACCESS as DATA SOURCE,then BROWSE ur access file then click NEXT
4.Chosse the DESTINATION[SQL native client]
5.Select the SERVERNAME
6.There are two AUTHENTICATION, select the SQL SERVER AUTHENTICATION and give the USERNAME & PASSWORE
7.Select the DATABASE and then click next
8.select "COPY DATA FROM ONE OR MORE TABLES OR VIEWS" then click next
9.the tables what you created in access will be showed here
10.select the tables which you want to insert and then click next
11.select the "EXECUTE IMMEDIATELY" check box and then click next
12.click finish
13.it takes some time to process and finallY, if there are no mistakes,it executes
successsfuly else it shows the error message|||You can't Import / Export using the Express editions.|||

Quote:

Originally Posted by Tros

You can't Import / Export using the Express editions.

Seems it may be round about, Excel -> Access -> SQL Express.|||When you download the SQL Express software, I believe it comes with a 'wizard' called DTS Wizard. It allows you to transfer among all the previously mentioned types.sql

How do I import a dts storage file into SQL?

Hi All
I have a DTS storage file (*.dts) with no network
connectivity to another SQL server (I have to mail the
storage file there).
How do I import the *.dts file into the msdb database so
that I can edit it from the DTS package designer?
Thanks
JonoSorry to bother all...found where to do it.
Thanks anyway
J

How do I get it to a precisions scale of .00?

I have set the output columns to decimal and data scale of 2. And have also set the field to be 0.00, and in the csv desination file it always puts .000000, How can I get it to be 0.00?

Thanks you for the help

Try creating a derived column cast to DT_NUMERIC and scale of 2 between your data source and destination. That should allow you to get 2 decimal places for any numeric input type. You can add all the columns you want to do this to in the one derived column task.

How do I get AdventureWorks to show in sql2005.

How do you make the AdventureWorks sample show up (June ctp)? I already installed it using the msi file:
"C:\Program Files\Microsoft SQL Server\90\Tools\Samples\SQLServerSamples.msi"

But following these tutorial directions doesn't get me anywhere, since AdventureWorks won't show up on the database list (last step). What's missing?

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens. In the Server name text box, type the name of your SQL Server instance. In the Registered server name box, type AdventureWorks. On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.The adventureworks sample databases are not part of the MSI they are installed separately, did you select them at install time? If not then go back into Add/Remove programs and select them

-Euan|||

Yes, I selected everything in install time. I also ran: "C:\Program Files\Microsoft SQL Server\90Tools\Samples\SQLServerSamples.msi"

I see some adventureworks scripts and files in the sql server program directory, but I don't see how to make the sample show up as a database in sql management studio, or see the data.

|||The databases are not installed via that MSI, that contains the code and projects.

When you say you installed everything, did you go into the advanced tree from the component selection dialog, then drill down to sample databases and select both sample databases? They are not selected by default.

As a double check can you do a dir adv*.mdf /s from the root of the drive where you installed everything to please?

-Euan|||Yes AdventureWorks_Data.mdf and AdventureWorksDW_Data.mdf show up in my directory listing. (I installed everything by selecting "install ALL FEATURES to hard drive" on every option, exactly to avoid this kind of issue.)|||Right click on the databases folder and select All Tasks, Attach Database, point the dialog at the .mdf files and all shold be well.

-Euan|||Thanks, that works. The directions in the docs should get corrected, at least the tutorial-type, for those who don't know their way around yet.|||Fair point, actually the databases should have been auto attached and thats a bug.|||

I tried attaching Adventureworks and I get an error:

could not find row in sysindexes for database id 21, object id 1, index id 1...

could not open new database 'Adventureworks'. Create database is aborted. SQL Server Error: 602). Please advise. Thanks.

|||

This might be helpful?

The adventureWorks database installed at setup is only a starting point for creating

all the stored procedures used in the StoreFront. You must run the SQL install script

located in the C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP directory.

This procedure will create a new AdventureWorks.mdf that can be attached

in the App_Data Directory of your VS2005 project or site.

See the following

http://msdn2.microsoft.com/en-us/library/ms160715(en-US,SQL.90).aspx

|||Right click the DATABASE of the instance of your SQL server ,choose the "Attach to" ,in a new opened window click the "Add" button to find your the AdventureWorks.mdf which often is in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data " directiory. Additionally ,the .mis is AdventureWorksdb.mis.