Load images into Sql Server 2005

Hi There

I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005.

All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type.

I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script.

Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know.

Thanx

Hi,

U can use Activex Script. But it is better to use Script task.

|||

You should look at the Import Column component. http://msdn2.microsoft.com/en-us/library/ms141262.aspx

All you need is a source file or table, with a column containing the filenames you wish to insert. SSIS will then load each named file into the data flow, from whence you can insert it to the database as normal.

Choice of the best data type for storing images would be better discussed on the SQL Server engine forum.

Donald

|||

Hi Donald

Would a for each file loop not work better, as a source file or table is not realistic considering there are over 9 million flat files.

I am not sure i am following you, import column requires a data source, if i point a flat file source to a image file it gets very confused with randon columns and junk data. I am not sure how to feed the flat file image to the import column task?

I have figured out another way to do it. But i am also interested in your suggestion.

Thanx

|||

That is indeed a lot of files. Actually it is so many that you may want to contact me offline to discuss options. DonaldDotFarmerAtMicrosoftDotCom will get me.

One way to generate a suitable file as a source, may be to pipe a dir command to file and then parse the result using SSIS - either using a flat file source, or a script component. From there it would be relatively simple to implement your scenario.

Donald

|||

Thanx Donald

I am gonna try a couple of things and i will get back to you.

|||

The TextCopy utility is a great way to load binary data into BLOB fields. I am calling the util from a .bat file. Its another option anyway....

Check out this article

http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

|||

Dietz wrote:

Hi There

I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005.

All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type.

I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script.

Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know.

Thanx

the t-sql OPENROWSET BULK statement is designed to import files into sql server: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

this statement can then be used in an execute sql task.

i suggest that you consult with the t-sql forum if you need assistance with this statement.

Load images into Sql Server 2005

Hi There

I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005.

All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type.

I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script.

Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know.

Thanx

Hi,

U can use Activex Script. But it is better to use Script task.

|||

You should look at the Import Column component. http://msdn2.microsoft.com/en-us/library/ms141262.aspx

All you need is a source file or table, with a column containing the filenames you wish to insert. SSIS will then load each named file into the data flow, from whence you can insert it to the database as normal.

Choice of the best data type for storing images would be better discussed on the SQL Server engine forum.

Donald

|||

Hi Donald

Would a for each file loop not work better, as a source file or table is not realistic considering there are over 9 million flat files.

I am not sure i am following you, import column requires a data source, if i point a flat file source to a image file it gets very confused with randon columns and junk data. I am not sure how to feed the flat file image to the import column task?

I have figured out another way to do it. But i am also interested in your suggestion.

Thanx

|||

That is indeed a lot of files. Actually it is so many that you may want to contact me offline to discuss options. DonaldDotFarmerAtMicrosoftDotCom will get me.

One way to generate a suitable file as a source, may be to pipe a dir command to file and then parse the result using SSIS - either using a flat file source, or a script component. From there it would be relatively simple to implement your scenario.

Donald

|||

Thanx Donald

I am gonna try a couple of things and i will get back to you.

|||

The TextCopy utility is a great way to load binary data into BLOB fields. I am calling the util from a .bat file. Its another option anyway....

Check out this article

http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

|||

Dietz wrote:

Hi There

I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005.

All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type.

I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script.

Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know.

Thanx

the t-sql OPENROWSET BULK statement is designed to import files into sql server: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

this statement can then be used in an execute sql task.

i suggest that you consult with the t-sql forum if you need assistance with this statement.

Load entire catelog into memory and keep it there?

I want to do Full text search exclusively using SQL 2005. Is there a way to
focus the SQL server to load the catelog into the system memory and stay
there? Since I want to make the search fastest as possible, everything else
can be slow. Thanks
--Xin Chen
No, there is no way of doing this. In the past I have looked at storing my
catalogs on a RAM disk and you do get better performance, however some of
Microsoft's customers have had spectacular failures while doing this.
The catalog is cached in the file system cache which surprisingly does work
optimally. The file system cache will be cached in RAM, which will be
flushed to disk when memory pressures get significant.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:eGEYRBKJFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I want to do Full text search exclusively using SQL 2005. Is there a way
to
> focus the SQL server to load the catelog into the system memory and stay
> there? Since I want to make the search fastest as possible, everything
else
> can be slow. Thanks
> --Xin Chen
>

Load Balancing and Fail Over for SQL Server.

Hi!
Currently we only have one SQL Server database in our production system. I would like to add one more SQL server 2000 database. I would like to configure them so that both server share the load and Failover. I did some research and I found that I can do this by installing the OS (Windows 2000 Server Enterprise Edition) and SQL 2000 Enterprise Edition on both machines using the windows clustering. I want both servers to be active. They both have a copy of the production data. What I don't know is that, if it is possible to synchronize the data on both databases using SQL Server Replication utitlites. From what I know one SQL Server must be Publisher and one Subscriber. Can one sql server be both? Because I want both sql server to be identical. Can I set up replication between more than 2 servers? We just need to add one server for now but I would like it to be expandable. In the future we may need more.
So Please provide me some ideas and answers about the following.
1- Can two SQL Server cross replicate (both update each other in order to be identical)?
2 -Does replication work beyond 2 servers.?
3- If you were to set up a production database; what do you recommend considering the load balancing and Failover using Windows 2000 Server Enterprise Edition and SQL Sever 20000 Enterprise Edition?

Thanks,
MichaelTry this link for a tutorial but get his book a chapter will walk you through the SQL Server setup with the hardware. The only thing he did not cover was the Fiber Channels in detail. Hope this helps.
http://www.windowsnetworking.com/articles_tutorials/Deploy-Windows-Server-2003-Planning-Network-Bandwidth.html|||

I did not answer the second part to your question about replication it is SQL Server Agent dependent. So you must install SQL Server Agent with a service account, most of the service packs we have been getting are fixes to the replication system why I cannot tell you because I have not use it for a long time it uses a lot of system resources and you could do the same with DTS on scheduled Jobs. You can test it because in SQL Server 2000 you can setup replication within one server. Hope this helps.

|||

Thank you guys for your time. I did find an open source solution. It is cross platform and can do merge replication between different databases. The name of the product is daffodil.

Thanks again.

Programmer 420

Load a Dataset to SQL Mobile (Quickly)

I have five small tables that I need to insert to a SQL CE database.

I am using the 2.0 Compact Framework with the 2.0 System.Data.SqlServerCe.

My table definition is dynamic so I never know it's design.

1- If I go Row by Row using an this.ExecuteNonQuery(_global, par); it takes about 26 seconds to insert 5 tables of 330 rows.

2- If a use

StringBuilder sbColumns = new StringBuilder();

foreach (DataColumn dc in table.Columns)

{

if (sbColumns.ToString() != "")

sbColumns.Append(",");

sbColumns.Append(dc.ColumnName);

}

SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT " + sbColumns.ToString() + " FROM " + _tablename, m_con);

SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);

da.MissingMappingAction = MissingMappingAction.Passthrough;

da.InsertCommand = cb.GetInsertCommand();

da.Update(table);

da.Dispose();

it takes about 46 seconds.

How Can write it faster or is this fastest it can go?

Thanks

You can use SqlCeResultset, which will be the fastet option in .NET. see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.aspx

You can find details in this excellent article by Joao: http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11003&atb.perform(details)=&

Live Webcast tomorrow Essential Team System for Database Developers

Live Webcast tomorrow

Essential Team System for Database Developers

1 PM Central time 12/28

https://www.clicktoattend.com/invitation.aspx?code=112602

Do we know if there will be any SSIS topics covered in this Webcast?|||I don't know, but I thought it would be of general interest and on another thread someone was asking about test data generation and that will be covered.

listing non system databases

I know sp_databases can list all the databases, but how can i only list
non-system databases? I just want to get a list of user databasesYou can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Hi
If you profile SQL Management Studio in SQL 2005 to see how it determines a
system database, you will see that it queries sysdatabases and determines
that the are system database by their name.
John
"Brian Henry" <nospam@.nospam.com> wrote in message
news:u5cw%23c7CGHA.3064@.TK2MSFTNGP10.phx.gbl...
>I know sp_databases can list all the databases, but how can i only list
>non-system databases? I just want to get a list of user databases
>|||> You can query the INFORMATION_SCHEMA.SCHEMATA view
Note that this view has a different meaning in 2005, where it returns the sc
hemas in the current
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u734707CGHA.2704@.TK2MSFTNGP15.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases sys
tem table to the the
> list of all databases. You can avoid the master, model, msdb, mssqlweb, te
mpdb, Pubs & Northwind
> databases in the WHERE clause to get the list of non-system databases.
> --
> Anith
>|||Yes, thanks. In 2005, use sys.databases instead.
Anith

List the System names has sql Server installed

hi,
I need to display the system names which has sql Server installed. How it can be done in vb.net.
Help me plz
regards
Somu

All things System are in the Master Database, spend some time with there are almost 1000 stored procedures in it, those and the tables are Microsoft Property try not to use them. Hope this helps.

List of tables

Hi,
Is there any command to find all the tables(including system tables) in SQLserver(I do not want to use enterprise manager).
Regards,Originally posted by sursujmad
Hi,

Is there any command to find all the tables(including system tables) in SQLserver(I do not want to use enterprise manager).

Regards,

You can use a sql request like this

select * from sysobjects where type = 's' or type = 'u'|||--Try
exec sp_tables
select user_name(uid)+'.'+name from sysobjects where xtype in ('U','S')
select * from information_schema.tables

list of stored procedure changes

I noticed that there is an inbuilt report in SSMS for the "Schema Changes
History". My questions are simple - how is it achieved? Is there a system
view I can use to achieve the same? Is there a setting which controls
whether this info is logged? Also, is it possible to get a history of
changes to a particular stored procedure this way, or is it just the name of
the stored procedure?
TIAYou could write your own in SQL server 2005 by using
OBJECT_DEFINITION() and the INFORMATION_SCHEMA.ROUTINES view
Code is here
(http://sqlservercode.blogspot.com/2006/04/use-objectdefinition-to-track.html)
Basically you would schedule a job that runs every day (or whatever you
like) and store the proc code in a table
That way you can query the tables and go back to a certain date (if
needed) and you can also check if procs have changed
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Joe wrote:
> I noticed that there is an inbuilt report in SSMS for the "Schema Changes
> History". My questions are simple - how is it achieved? Is there a system
> view I can use to achieve the same? Is there a setting which controls
> whether this info is logged? Also, is it possible to get a history of
> changes to a particular stored procedure this way, or is it just the name of
> the stored procedure?
> TIA|||Looks like the Management Studio report is using the traces to look for
object changes, which you can achieve yourself by running a trace.
Alternatively, you could you DDL triggers to track object changes in SQL
Server 2005.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe" <Joe.Barrymore@.BT.Com> wrote in message
news:e$2LeF7iGHA.960@.TK2MSFTNGP05.phx.gbl...
I noticed that there is an inbuilt report in SSMS for the "Schema Changes
History". My questions are simple - how is it achieved? Is there a system
view I can use to achieve the same? Is there a setting which controls
whether this info is logged? Also, is it possible to get a history of
changes to a particular stored procedure this way, or is it just the name of
the stored procedure?
TIA|||SQL Server 2005 by default runs a 'lightweight trace' that captures
information about errors, security changes, configuration changes and object
creation, alter and drop. SSMS gets its data from this trace. The trace file
is stored in your \MSSQL\log directory and a new one is created every time
you restart your SQL Server.
A new feature of SQL 2005 that I like a lot is that you can open up a trace
file, or copy its contents to a table for analysis, even while the trace is
running.
I wrote about these traces in the March issue of SQL Server Magazine.
http://www.sqlmag.com/Article/ArticleID/48939/Searching_for_a_Trace.html
There is a configuration option called 'default trace enabled' that turns
this tracing on or off, and it is ON by default.
Hopefully, this can give you a start into doing your own analysis.
HTH
Kalen Delaney, SQL Server MVP
"Joe" <Joe.Barrymore@.BT.Com> wrote in message
news:e$2LeF7iGHA.960@.TK2MSFTNGP05.phx.gbl...
>I noticed that there is an inbuilt report in SSMS for the "Schema Changes
>History". My questions are simple - how is it achieved? Is there a system
>view I can use to achieve the same? Is there a setting which controls
>whether this info is logged? Also, is it possible to get a history of
>changes to a particular stored procedure this way, or is it just the name
>of the stored procedure?
> TIA
>

list of clients connected to a database

I remember some time ago, coincidenteally, by browsing around in system
tables, I saw a place where I could see which clients were connected to
which database, allowing me to select all client names connected to a
particular database.
But I forgot where I found this (in SQL7).
Can someone tell me?
Lisasysprocesses table in master database.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
I remember some time ago, coincidenteally, by browsing around in system
tables, I saw a place where I could see which clients were connected to
which database, allowing me to select all client names connected to a
particular database.
But I forgot where I found this (in SQL7).
Can someone tell me?
Lisa|||Try EXEC sp_who or EXEC sp_who2
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> I remember some time ago, coincidenteally, by browsing around in system
> tables, I saw a place where I could see which clients were connected to
> which database, allowing me to select all client names connected to a
> particular database.
> But I forgot where I found this (in SQL7).
> Can someone tell me?
> Lisa
>|||Thanks, strange enough, under client name, it lists the server name running
the sql server rather than the name of the client connecting to it.. this is
weird.
Lisa
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
> sysprocesses table in master database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> I remember some time ago, coincidenteally, by browsing around in system
> tables, I saw a place where I could see which clients were connected to
> which database, allowing me to select all client names connected to a
> particular database.
> But I forgot where I found this (in SQL7).
> Can someone tell me?
> Lisa
>
>|||Note that this can be controlled with the connection
string, and is therefore not totally reliable. The client
program can specify whatever name it fancies.
Linchi
>--Original Message--
>Thanks, strange enough, under client name, it lists the
server name running
>the sql server rather than the name of the client
connecting to it.. this is
>weird.
>Lisa
>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote
in message
>news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> sysprocesses table in master database.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> http://vyaskn.tripod.com/
>> Is .NET important for a database professional?
>> http://vyaskn.tripod.com/poll.htm
>>
>>
>> "Lisa Pearlson" <no@.spam.plz> wrote in message
>> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
>> I remember some time ago, coincidenteally, by browsing
around in system
>> tables, I saw a place where I could see which clients
were connected to
>> which database, allowing me to select all client names
connected to a
>> particular database.
>> But I forgot where I found this (in SQL7).
>> Can someone tell me?
>> Lisa
>>
>>
>
>.
>|||HOST_NAME() depens on the ODBC connection string?
How? Is it the WSID parameter?
See, I always wondered what the difference was between WSID and SERVER as
they seemed to be the same, but obviously that was because I was working on
the same machine running the server locally.
Where can I find a list of ODBC connection string parameters and their
meaning?
I just kind of figured these things out by GetConnect() from within C++.
Lisa
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:168a01c3df99$df95c270$a601280a@.phx.gbl...
> Note that this can be controlled with the connection
> string, and is therefore not totally reliable. The client
> program can specify whatever name it fancies.
> Linchi
> >--Original Message--
> >Thanks, strange enough, under client name, it lists the
> server name running
> >the sql server rather than the name of the client
> connecting to it.. this is
> >weird.
> >
> >Lisa
> >
> >"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote
> in message
> >news:utalOC43DHA.1428@.TK2MSFTNGP12.phx.gbl...
> >> sysprocesses table in master database.
> >> --
> >> HTH,
> >> Vyas, MVP (SQL Server)
> >> http://vyaskn.tripod.com/
> >> Is .NET important for a database professional?
> >> http://vyaskn.tripod.com/poll.htm
> >>
> >>
> >>
> >>
> >> "Lisa Pearlson" <no@.spam.plz> wrote in message
> >> news:O9fZxt33DHA.2000@.TK2MSFTNGP11.phx.gbl...
> >> I remember some time ago, coincidenteally, by browsing
> around in system
> >> tables, I saw a place where I could see which clients
> were connected to
> >> which database, allowing me to select all client names
> connected to a
> >> particular database.
> >>
> >> But I forgot where I found this (in SQL7).
> >> Can someone tell me?
> >>
> >> Lisa
> >>
> >>
> >>
> >>
> >
> >
> >.
> >

List of all database users

We had 40+ databases and like to see all of users in these databases. What
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:

> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
>
>

List of all database users

We had 40+ databases and like to see all of users in these databases. What
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:
> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> > We had 40+ databases and like to see all of users in these databases.
> > What
> > is the data dictionary, the system table, or the sys view can list all
> > database users?
>
>

list existing replication's settings

Hi experts,
I wish to document the replication settings on our
environment, is there a good way to list the settings on
the system ?
regards,
Diana.
Diana,
there are loads of sps to help with documentation:
sp_helpdistpublisher
sp_helpdistributor
sp_helppublication
sp_helpsubscription
to name but a few. Many of them take parameters, so you can't run them
without first configuring.
I personally do things in a less sophisticated way - I right-click the
replication folder and select "Generate SQL script..." which is then placed
in Sourcesafe.
You could also do this in SQLDMO, but IMO the simplest and quickest way
would be to have EM create the script as above.
HTH,
Paul Ibison
|||sp_helppublication provides much of the information you are looking for.
I also find it helpful to script out my replication jobs. Here is an example
of such a script.
set objServer=CreateObject("SQLDMO.SQLServer")
objServer.LoginSecure=True
objServer.Connect "Publisher"
objServer.Replication.Script 1024,"c:\sqlout.sql"
set objServer=Nothing
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Diana" <yuendiana@.sinaman.com> wrote in message
news:261301c47dfb$d68d8a40$a301280a@.phx.gbl...
> Hi experts,
> I wish to document the replication settings on our
> environment, is there a good way to list the settings on
> the system ?
> regards,
> Diana.