Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:
> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
[vbcol=seagreen]
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
>|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:
> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
>
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
[vbcol=seagreen]
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
>|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sampl
e
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:
> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
>
>
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Sunday, March 25, 2012
COLLATION in bulk insert
Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:
> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have a bulk insert statement:
> >
> > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > WITH (
> > DATAFILETYPE = 'WIDECHAR'
> > FIRSTROW = 1,
> > FIELDTERMINATOR = 'COL_SEP',
> > ROWTERMINATOR = 'ROW_SEP',
> > CODEPAGE = 'RAW')
> >
> > Some fields that are inserted are encrypted using special characters
> > beyond the normal ascii character set. I have a database that has a
> > collation set to Arabic. When the collation is set to arabic the import
> > does not transfer some data across properly.
> >
> > When I set the collation to latin on this database, the data comes
> > across correctly. Can someone help me to explain why this is the case?
> >
> >|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:
> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
> > Hi
> >
> > You don't say how the data file is created! Have you tried using code page
> > 1256?
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have a bulk insert statement:
> > >
> > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > WITH (
> > > DATAFILETYPE = 'WIDECHAR'
> > > FIRSTROW = 1,
> > > FIELDTERMINATOR = 'COL_SEP',
> > > ROWTERMINATOR = 'ROW_SEP',
> > > CODEPAGE = 'RAW')
> > >
> > > Some fields that are inserted are encrypted using special characters
> > > beyond the normal ascii character set. I have a database that has a
> > > collation set to Arabic. When the collation is set to arabic the import
> > > does not transfer some data across properly.
> > >
> > > When I set the collation to latin on this database, the data comes
> > > across correctly. Can someone help me to explain why this is the case?
> > >
> > >
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have tried using codepage 1256 but to no luck. The data file is
> > created in code just by running through some old DBF files and
> > extracting there values and putting them in to a csv file.
> >
> >
> > John Bell wrote:
> >
> > > Hi
> > >
> > > You don't say how the data file is created! Have you tried using code page
> > > 1256?
> > >
> > > John
> > >
> > > "Feldaspar" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a bulk insert statement:
> > > >
> > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > WITH (
> > > > DATAFILETYPE = 'WIDECHAR'
> > > > FIRSTROW = 1,
> > > > FIELDTERMINATOR = 'COL_SEP',
> > > > ROWTERMINATOR = 'ROW_SEP',
> > > > CODEPAGE = 'RAW')
> > > >
> > > > Some fields that are inserted are encrypted using special characters
> > > > beyond the normal ascii character set. I have a database that has a
> > > > collation set to Arabic. When the collation is set to arabic the import
> > > > does not transfer some data across properly.
> > > >
> > > > When I set the collation to latin on this database, the data comes
> > > > across correctly. Can someone help me to explain why this is the case?
> > > >
> > > >
> >
> >|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sample
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:
> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
> > Hi
> >
> > Is the file created unicode? If not then you may wish to change the
> > Datafiletype. Also make sure that contents of the file are as expected for
> > the Arabic characters.
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have tried using codepage 1256 but to no luck. The data file is
> > > created in code just by running through some old DBF files and
> > > extracting there values and putting them in to a csv file.
> > >
> > >
> > > John Bell wrote:
> > >
> > > > Hi
> > > >
> > > > You don't say how the data file is created! Have you tried using code page
> > > > 1256?
> > > >
> > > > John
> > > >
> > > > "Feldaspar" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a bulk insert statement:
> > > > >
> > > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > > WITH (
> > > > > DATAFILETYPE = 'WIDECHAR'
> > > > > FIRSTROW = 1,
> > > > > FIELDTERMINATOR = 'COL_SEP',
> > > > > ROWTERMINATOR = 'ROW_SEP',
> > > > > CODEPAGE = 'RAW')
> > > > >
> > > > > Some fields that are inserted are encrypted using special characters
> > > > > beyond the normal ascii character set. I have a database that has a
> > > > > collation set to Arabic. When the collation is set to arabic the import
> > > > > does not transfer some data across properly.
> > > > >
> > > > > When I set the collation to latin on this database, the data comes
> > > > > across correctly. Can someone help me to explain why this is the case?
> > > > >
> > > > >
> > >
> > >
>sqlsql
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:
> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have a bulk insert statement:
> >
> > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > WITH (
> > DATAFILETYPE = 'WIDECHAR'
> > FIRSTROW = 1,
> > FIELDTERMINATOR = 'COL_SEP',
> > ROWTERMINATOR = 'ROW_SEP',
> > CODEPAGE = 'RAW')
> >
> > Some fields that are inserted are encrypted using special characters
> > beyond the normal ascii character set. I have a database that has a
> > collation set to Arabic. When the collation is set to arabic the import
> > does not transfer some data across properly.
> >
> > When I set the collation to latin on this database, the data comes
> > across correctly. Can someone help me to explain why this is the case?
> >
> >|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:
> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
> > Hi
> >
> > You don't say how the data file is created! Have you tried using code page
> > 1256?
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have a bulk insert statement:
> > >
> > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > WITH (
> > > DATAFILETYPE = 'WIDECHAR'
> > > FIRSTROW = 1,
> > > FIELDTERMINATOR = 'COL_SEP',
> > > ROWTERMINATOR = 'ROW_SEP',
> > > CODEPAGE = 'RAW')
> > >
> > > Some fields that are inserted are encrypted using special characters
> > > beyond the normal ascii character set. I have a database that has a
> > > collation set to Arabic. When the collation is set to arabic the import
> > > does not transfer some data across properly.
> > >
> > > When I set the collation to latin on this database, the data comes
> > > across correctly. Can someone help me to explain why this is the case?
> > >
> > >
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have tried using codepage 1256 but to no luck. The data file is
> > created in code just by running through some old DBF files and
> > extracting there values and putting them in to a csv file.
> >
> >
> > John Bell wrote:
> >
> > > Hi
> > >
> > > You don't say how the data file is created! Have you tried using code page
> > > 1256?
> > >
> > > John
> > >
> > > "Feldaspar" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a bulk insert statement:
> > > >
> > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > WITH (
> > > > DATAFILETYPE = 'WIDECHAR'
> > > > FIRSTROW = 1,
> > > > FIELDTERMINATOR = 'COL_SEP',
> > > > ROWTERMINATOR = 'ROW_SEP',
> > > > CODEPAGE = 'RAW')
> > > >
> > > > Some fields that are inserted are encrypted using special characters
> > > > beyond the normal ascii character set. I have a database that has a
> > > > collation set to Arabic. When the collation is set to arabic the import
> > > > does not transfer some data across properly.
> > > >
> > > > When I set the collation to latin on this database, the data comes
> > > > across correctly. Can someone help me to explain why this is the case?
> > > >
> > > >
> >
> >|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sample
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:
> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
> > Hi
> >
> > Is the file created unicode? If not then you may wish to change the
> > Datafiletype. Also make sure that contents of the file are as expected for
> > the Arabic characters.
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have tried using codepage 1256 but to no luck. The data file is
> > > created in code just by running through some old DBF files and
> > > extracting there values and putting them in to a csv file.
> > >
> > >
> > > John Bell wrote:
> > >
> > > > Hi
> > > >
> > > > You don't say how the data file is created! Have you tried using code page
> > > > 1256?
> > > >
> > > > John
> > > >
> > > > "Feldaspar" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a bulk insert statement:
> > > > >
> > > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > > WITH (
> > > > > DATAFILETYPE = 'WIDECHAR'
> > > > > FIRSTROW = 1,
> > > > > FIELDTERMINATOR = 'COL_SEP',
> > > > > ROWTERMINATOR = 'ROW_SEP',
> > > > > CODEPAGE = 'RAW')
> > > > >
> > > > > Some fields that are inserted are encrypted using special characters
> > > > > beyond the normal ascii character set. I have a database that has a
> > > > > collation set to Arabic. When the collation is set to arabic the import
> > > > > does not transfer some data across properly.
> > > > >
> > > > > When I set the collation to latin on this database, the data comes
> > > > > across correctly. Can someone help me to explain why this is the case?
> > > > >
> > > > >
> > >
> > >
>sqlsql
Sunday, March 11, 2012
code Sample
Hello,
Can someone help me with a SQL code sample to insert a Picture / Documentin
image data field?
Regards
SathianSathian,
One or more of the following KB articles, should help you!
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
201785 (Q201785) HOWTO: Import FileSystem Data Using DTS and Index Server
http://support.microsoft.com/defaul...kb;en-us;201785
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/defaul...kb;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/defaul...kb;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cta232$23a$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can someone help me with a SQL code sample to insert a Picture /
Documentin
> image data field?
> Regards
> Sathian
>|||Hi
The following article and links to KB articles should give you everything
you need!
http://www.aspfaq.com/show.asp?id=2149
John
"Sathian T" wrote:
> Hello,
> Can someone help me with a SQL code sample to insert a Picture / Documenti
n
> image data field?
> Regards
> Sathian
>
>|||or this ;0)
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cta232$23a$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can someone help me with a SQL code sample to insert a Picture /
> Documentin
> image data field?
> Regards
> Sathian
>|||John,
The MAJOR thing wrong with the below link is that it makes NO reference to
the advantages of storing documents (MS Word, Excel, Adobe's PDF, etc.) and
only references the storing of images, such as *.jpg files (which can also
be FT Indexed with existing JPEG IFilters). While, I don't want to start a
flame war, and I've not reviewed all of the KB articles (I will at a later
time, as I have to go to work ;-), I strongly disagree with the premise of
this links recommendation as it makes NO reference to advantage of storing
files for the purpose of accessing the content of these files via Full text
Search that is fully support by Microsoft in SQL Server 7.0, SQL Server 2000
and vastly improved in SQL Server 2005.
Respectively,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:EFED2C44-641B-4AD8-87D2-4DFB9E45214C@.microsoft.com...
> Hi
> The following article and links to KB articles should give you everything
> you need!
> http://www.aspfaq.com/show.asp?id=2149
> John
>
> "Sathian T" wrote:
>
Documentin
Can someone help me with a SQL code sample to insert a Picture / Documentin
image data field?
Regards
SathianSathian,
One or more of the following KB articles, should help you!
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
201785 (Q201785) HOWTO: Import FileSystem Data Using DTS and Index Server
http://support.microsoft.com/defaul...kb;en-us;201785
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/defaul...kb;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/defaul...kb;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cta232$23a$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can someone help me with a SQL code sample to insert a Picture /
Documentin
> image data field?
> Regards
> Sathian
>|||Hi
The following article and links to KB articles should give you everything
you need!
http://www.aspfaq.com/show.asp?id=2149
John
"Sathian T" wrote:
> Hello,
> Can someone help me with a SQL code sample to insert a Picture / Documenti
n
> image data field?
> Regards
> Sathian
>
>|||or this ;0)
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cta232$23a$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can someone help me with a SQL code sample to insert a Picture /
> Documentin
> image data field?
> Regards
> Sathian
>|||John,
The MAJOR thing wrong with the below link is that it makes NO reference to
the advantages of storing documents (MS Word, Excel, Adobe's PDF, etc.) and
only references the storing of images, such as *.jpg files (which can also
be FT Indexed with existing JPEG IFilters). While, I don't want to start a
flame war, and I've not reviewed all of the KB articles (I will at a later
time, as I have to go to work ;-), I strongly disagree with the premise of
this links recommendation as it makes NO reference to advantage of storing
files for the purpose of accessing the content of these files via Full text
Search that is fully support by Microsoft in SQL Server 7.0, SQL Server 2000
and vastly improved in SQL Server 2005.
Respectively,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:EFED2C44-641B-4AD8-87D2-4DFB9E45214C@.microsoft.com...
> Hi
> The following article and links to KB articles should give you everything
> you need!
> http://www.aspfaq.com/show.asp?id=2149
> John
>
> "Sathian T" wrote:
>
Documentin
Thursday, March 8, 2012
Code page conversion shortcut?
Is there a simple way to avoid having to use a data conversion task to convert Unicode data (from DB2) to ANSI format (for insert to Oracle)?
I'm hoping that there is a way to set the code page property on the OLEDB driver so that it implicitly converts to the format I want instead of having to explicitly force the conversion in the package.
Thoughts? suggestions?
-Clayton
In the intervening month, the best method I've come up with is to use the SSIS wizard to build/rebuild the Data Flow. By default the wizard creates the Data Flow with the data conversion task in place, which saves a lot of time over performing the task manually.
To access the wizard, right click on the "SSIS Packages" node of the Solution Explorer and choose "SSIS Import and Export Wizard..." This will create a new package, but the data flow object can be cut from the new package and pasted into an existing one.
I did not find any way to tweak the code page settings on the source driver to cast to an ANSI code page.
|||
Try this: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx
Code page conversion shortcut?
Is there a simple way to avoid having to use a data conversion task to convert Unicode data (from DB2) to ANSI format (for insert to Oracle)?
I'm hoping that there is a way to set the code page property on the OLEDB driver so that it implicitly converts to the format I want instead of having to explicitly force the conversion in the package.
Thoughts? suggestions?
-Clayton
In the intervening month, the best method I've come up with is to use the SSIS wizard to build/rebuild the Data Flow. By default the wizard creates the Data Flow with the data conversion task in place, which saves a lot of time over performing the task manually.
To access the wizard, right click on the "SSIS Packages" node of the Solution Explorer and choose "SSIS Import and Export Wizard..." This will create a new package, but the data flow object can be cut from the new package and pasted into an existing one.
I did not find any way to tweak the code page settings on the source driver to cast to an ANSI code page.
|||Try this: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx
code needed for a trigger
Does anyone have code for a trigger that when the user deletes the record trigger it to insert into another table? Thanks in advance and appreciate your help!I cant "write" code for you but I can steer in you in the right direction
you can do a
|||I am sorry but i am really unfamiliar with the trigger functions. So if anyone can help i would appreciate it.|||Check out the DELETE TRIGGER portion of this article:Auditing Through Triggers.
DECLARE @.var1 ..., @.var2...
SELECT
@.var1=Col1
@.var2=Col2
...
FROM
DELETEDINSERT INTO <table> <cols> VALUES (@.var1, @.var2...)
Terri|||Exactly what i needed. Thank you very much!
Wednesday, March 7, 2012
CoCreate of DSO for BULKIMPORTSTREAM returned 0x8007045a
Hello,
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!
Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!
|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
>
|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it worked for me previously.
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>
|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
DSO
>
>.
>
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!
Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!
|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
>
|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it worked for me previously.
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>
|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
DSO
>
>.
>
CoCreate of DSO for BULKIMPORTSTREAM returned 0x8007045a
Hello,
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
>|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it wo
rked for me previously.
--
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
DSO[vbcol=seagreen]
>
>.
>
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
>|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it wo
rked for me previously.
--
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
DSO[vbcol=seagreen]
>
>.
>
Saturday, February 25, 2012
CmdExec in a Nightly Job
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.
The step being executed by the Job is "Ren C:\MyTestFile.csv C:\MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.
I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."
As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.
I would appreciate any assistance anyone could provide. Thanks in advance!
SQL Server Agent has its own service account -it doesn't use the 'sa' account.
In Enterprise Manager, right-click on SQL Agent, then Properties, and you can view (and/or change) the service account for the SQL Server Agent service.
Here are a couple of resources about setting the SQL Agent account:
Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557
Sunday, February 12, 2012
clustered index fragmentation
Hi
I am testing some clustered index on a table I was working on. I put a clustered index on an int column with identity and insert some several thousand records into the table. I check the index fragmentation and notice that it was about 20%. I thought since the identity field is incremental, there would be little or no fragmentation. Is this true or is there something wrong with my server
thanks
Paul
A clustered index is a table (heap), indexed.See:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EJD|||When was the last time the DBREINDEX job executed? Try to update statistics on the involved tables for a performance gain.|||
When you created the index what fill factor did you use? Did you select the padding option?
|||The fragmentation on your tables are due to updates of the variable length columns and not due to inserts. An int column with identity is the best candidate for clustered index, as there wont be any fragmentation during inserts. However, when you update your tables and increase the data size of columns having variable length, than it is very likely of your table getting fragmented.One suggestion is to use a fill factor depending on the frequency of updates of your variable length columns. Hope this helps :)
clustered index fragmentation
Hi
I am testing some clustered index on a table I was working on. I put a clustered index on an int column with identity and insert some several thousand records into the table. I check the index fragmentation and notice that it was about 20%. I thought since the identity field is incremental, there would be little or no fragmentation. Is this true or is there something wrong with my server
thanks
Paul
A clustered index is a table (heap), indexed.See:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EJD|||When was the last time the DBREINDEX job executed? Try to update statistics on the involved tables for a performance gain.|||
When you created the index what fill factor did you use? Did you select the padding option?
|||The fragmentation on your tables are due to updates of the variable length columns and not due to inserts. An int column with identity is the best candidate for clustered index, as there wont be any fragmentation during inserts. However, when you update your tables and increase the data size of columns having variable length, than it is very likely of your table getting fragmented.One suggestion is to use a fill factor depending on the frequency of updates of your variable length columns. Hope this helps :)
Subscribe to:
Posts (Atom)