How to manage RTE text directly in the DB ?

Hi all,
I store my Rich-Text files in my SQL2000 database.
I want to add a line at the end... it's very easy in my VB application with
a Rich Text control... but how to do that directly in the database (via a
Trigger) ?
Does SQL2000 have Rich Text function or show I use an external (Windows)
component ?
Lilian.
SQL Server 2000 has no knowledge of the contents of your character strings.
Content could be RTF, HTML XML, etc.
It might be possible to build the desired RTF string in Transact-SQL but I
don't know enough about RTF formatting codes to say for sure. Maintaining
RTF content is a job best done in application code rather than Transact-SQL.
Another option is to write an ActiveX control or extended stored procedure
to manipulate RTF strings that could be invoked from Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> Hi all,
> I store my Rich-Text files in my SQL2000 database.
> I want to add a line at the end... it's very easy in my VB application
> with
> a Rich Text control... but how to do that directly in the database (via a
> Trigger) ?
> Does SQL2000 have Rich Text function or show I use an external (Windows)
> component ?
> Lilian.
>
|||Maybe using an OLE object like this example:
http://support.microsoft.com/kb/312839/en-us
... but which object ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> a crit dans le message
de news:OYknWxePGHA.3100@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000 has no knowledge of the contents of your character
strings.
> Content could be RTF, HTML XML, etc.
> It might be possible to build the desired RTF string in Transact-SQL but I
> don't know enough about RTF formatting codes to say for sure. Maintaining
> RTF content is a job best done in application code rather than
Transact-SQL.[vbcol=seagreen]
> Another option is to write an ActiveX control or extended stored procedure
> to manipulate RTF strings that could be invoked from Transact-SQL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
a
>

How to manage RTE text directly in the DB ?

Hi all,
I store my Rich-Text files in my SQL2000 database.
I want to add a line at the end... it's very easy in my VB application with
a Rich Text control... but how to do that directly in the database (via a
Trigger) '
Does SQL2000 have Rich Text function or show I use an external (Windows)
component ?
Lilian.SQL Server 2000 has no knowledge of the contents of your character strings.
Content could be RTF, HTML XML, etc.
It might be possible to build the desired RTF string in Transact-SQL but I
don't know enough about RTF formatting codes to say for sure. Maintaining
RTF content is a job best done in application code rather than Transact-SQL.
Another option is to write an ActiveX control or extended stored procedure
to manipulate RTF strings that could be invoked from Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> Hi all,
> I store my Rich-Text files in my SQL2000 database.
> I want to add a line at the end... it's very easy in my VB application
> with
> a Rich Text control... but how to do that directly in the database (via a
> Trigger) '
> Does SQL2000 have Rich Text function or show I use an external (Windows)
> component ?
> Lilian.
>|||Maybe using an OLE object like this example:
http://support.microsoft.com/kb/312839/en-us
... but which object ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> a crit dans le message
de news:OYknWxePGHA.3100@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000 has no knowledge of the contents of your character
strings.
> Content could be RTF, HTML XML, etc.
> It might be possible to build the desired RTF string in Transact-SQL but I
> don't know enough about RTF formatting codes to say for sure. Maintaining
> RTF content is a job best done in application code rather than
Transact-SQL.
> Another option is to write an ActiveX control or extended stored procedure
> to manipulate RTF strings that could be invoked from Transact-SQL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
>

How to manage RTE text directly in the DB ?

Hi all,
I store my Rich-Text files in my SQL2000 database.
I want to add a line at the end... it's very easy in my VB application with
a Rich Text control... but how to do that directly in the database (via a
Trigger) '
Does SQL2000 have Rich Text function or show I use an external (Windows)
component ?
Lilian.SQL Server 2000 has no knowledge of the contents of your character strings.
Content could be RTF, HTML XML, etc.
It might be possible to build the desired RTF string in Transact-SQL but I
don't know enough about RTF formatting codes to say for sure. Maintaining
RTF content is a job best done in application code rather than Transact-SQL.
Another option is to write an ActiveX control or extended stored procedure
to manipulate RTF strings that could be invoked from Transact-SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> Hi all,
> I store my Rich-Text files in my SQL2000 database.
> I want to add a line at the end... it's very easy in my VB application
> with
> a Rich Text control... but how to do that directly in the database (via a
> Trigger) '
> Does SQL2000 have Rich Text function or show I use an external (Windows)
> component ?
> Lilian.
>|||Maybe using an OLE object like this example:
http://support.microsoft.com/kb/312839/en-us
... but which object ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> a écrit dans le message
de news:OYknWxePGHA.3100@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000 has no knowledge of the contents of your character
strings.
> Content could be RTF, HTML XML, etc.
> It might be possible to build the desired RTF string in Transact-SQL but I
> don't know enough about RTF formatting codes to say for sure. Maintaining
> RTF content is a job best done in application code rather than
Transact-SQL.
> Another option is to write an ActiveX control or extended stored procedure
> to manipulate RTF strings that could be invoked from Transact-SQL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> > I store my Rich-Text files in my SQL2000 database.
> >
> > I want to add a line at the end... it's very easy in my VB application
> > with
> > a Rich Text control... but how to do that directly in the database (via
a
> > Trigger) '
> >
> > Does SQL2000 have Rich Text function or show I use an external (Windows)
> > component ?
> >
> > Lilian.
> >
> >
>sql

How to manage different input (Excel files) format

Hi all,

I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).

I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.

I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.

Any help will be appreciated.

Kind regards,

Sbastien.

Have you discovered the expressions on precedence constraints? They seems like ideal fit for your requirements.

Double click a precedence constraint line, select a condition and an expression.|||

Right! That's what I needed.

Thanks for your answer.

How to make an if in the dataflow ?

I have a dataflow where i import 2 files. The one is the file containing a couple of million records. The other file contains rows with summed values on a specific key.

The file with the millions of records is aggregated on the key, sorted, so that the 2 collums from the files can be compared. I then do a mergejoin on the key and now i have temptable with the (key,sum1,sum2). Now there must not be a difference between sum1 and sum2.
I can make a conditional split where i say ([sum1] - [sum2]) > 0.1 so that i get an output with rows where the diffence is more than 0.1.

My question is now, how do i make an action on that. If that task put out a row or more then do something (send mail task, stop further processing) ?

CgplJust add a "RecordCount" transform to your pipeline. So you count "Error Records". In the Control Flow you can change the "Link" between the task and change the "constraint options" to "Expression". There you can check if the value of the variable you used for the RecordCount is greater then 0. If so you can link to a send mail task or whatever you want...

HTH
Thomas|||Thanks but can you point that out in detail ?|||Ahhh Found out! Thanks|||This may help: http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1843.aspx

-Jamie
EDIT: Ahh, except that you already worked it out while I was posting this. Never mind :)

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

how to load a set of 10000 xml files to a sql table?

Hi all,
I have a folder with about 10.000 xml files that need to be transferred to a
SQL table.
the structure of the xml file is something like this:
<?xml version="1.0" encoding="UTF-8"?>
<customer>
<id>123</id>
<sex>M</sex>
<firstName>firstname</firstName>
<lastName>Lastname</lastName>
<phone>123123</phone>
<BillingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</BillingAddress>
<ShippingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</ShippingAddress>
</customer>
How can I do this?
Has anyone a sample or a brief description how to do this?
Thank you very much!
Regards, Martin
Martin,
I have just written code for an ACTIVEX controled DTS that does exactly
what you are looking for. Ive copied and pasted the code below. Im just a
jr. developer, but I hope this helps you. You will have to change the code
so that it can "read" your xml files. Also, ive added some links to sites
that helped me accomplish this.
Ben
Links (sorry, i dont know how to do HTML links) (the second link is really
good):
http://www.eggheadcafe.com/articles/20030627b.asp
http://www.perfectxml.com/articles/xml/importxmlsql.asp
my code:
'************************************************* ***********
' Visual Basic ActiveX Script
'************************************************* ***********
Option Explicit
Function Main()
' Declare FSO Related Variables
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
' Import Folder
'global variable "ImportFolder" has a value like: C:\logs\
sFolder = DTSGlobalvariables("ImportFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'reads each file in the folder and moves them to a second
folder
For Each fsoFile in fsoFolder.Files
sFileName = sFolder & fsoFile.Name
call ParseFile(sFilename)
call MoveFile(sFilename)
Next
Main = DTSTaskExecResult_Success
End Function
'************************************************* **********
' ParseFile
' This method takes as input the complete name of an xml file
' (including path) and retrieves specific information from the
' file, as needed for the Webshield SPAM reporting
'************************************************* ***********
Function ParseFile(importFile)
'Declare File Related Variables
Dim objXMLDOM
Dim objNodes
Dim objEventNode
Dim objADORS
Dim objADORS2
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load importFile
'setting the format of the xml files, the eventlog has nodes
called event
Set objNodes = objXMLDOM.selectNodes("/EventLog/Event")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
Set objADORS2 = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=(local);UID=sa;PWD=;DATA BASE=Northwind;"
objADORS.Open "SELECT * FROM Logs", objADOCnn, adOpenKeyset, adLockOptimistic
objADORS2.Open "SELECT * FROM Imported", objADOCnn, adOpenKeyset,
adLockOptimistic
'Record file that was imported
With objADORS2
.AddNew
.fields("FileName") = importFile
.Update
End With
'Import record(s)
For Each objEventNode In objNodes
With objADORS
.AddNew
'read an attribute of the
event
.fields("Date")=
objEventNode.selectSingleNode("@.local-time").nodeTypedValue
'read specific values of the
event
.fields("From Inside Total") =
objEventNode.selectSingleNode("Info[@.name='smtp.fr om_inside.messages']").nodeTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
ParseFile = DTSTaskExecResult_Success
End Function
'************************************************* **********
' MoveFile
' This method moves a file from one folder to another so that the
' file does not get imported into the SQL database twice
'************************************************* **********
Function MoveFile(importFile)
' Declare FSO Related Variables
Dim sFolder
Dim endFolder
Dim fso
Dim fsoFolder
' Import Folder
sFolder = DTSGlobalvariables("ImportFolder")
' Imported Files Folder
endFolder = DTSGlobalvariables("saveFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'move the file
fso.movefile importFile, endFolder
MoveFile = DTSTaskExecResult_Success
End Function
|||Hi Ben,
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
> Martin,
> I have just written code for an ACTIVEX controled DTS that does exactly
> what you are looking for. Ive copied and pasted the code below. Im just
> a
> jr. developer, but I hope this helps you. You will have to change the
> code
> so that it can "read" your xml files. Also, ive added some links to sites
> that helped me accomplish this.
> Ben
Thank you very much! I think this was exactly what I was searching for.
Just one more question:
Do you start the code with a ActiveX Control in a dts package as described
in
http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
Regards, Martin
|||Martin,
I'm not sure I understand your question. The following are the steps to
use to be able to use the code:
1. In enterprise manager, under the specific server you are using, click on
data transfer services and then right click in the right window and create a
new package
2. Right click -> add task -> activeX script task
3. Copy in the code and then modify for your xml files
To set the global variables i use,
close the tasks and make sure non are selected, click package -> properties
and then click the tab global variables.
i hope this answers your question. if not, could you please rephrase it?
thanks.
Ben
"Martin Bübl" wrote:

> Hi Ben,
> "Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
> news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
>
> Thank you very much! I think this was exactly what I was searching for.
> Just one more question:
> Do you start the code with a ActiveX Control in a dts package as described
> in
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
> Regards, Martin
>
>
|||Hi Ben,
thank you, again this was exactly the information I was asking you for.
Regards, Martin
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:C3B288ED-D170-4465-97B1-62EA7AAD0086@.microsoft.com...[vbcol=seagreen]
> Martin,
> I'm not sure I understand your question. The following are the steps
> to
> use to be able to use the code:
> 1. In enterprise manager, under the specific server you are using, click
> on
> data transfer services and then right click in the right window and create
> a
> new package
> 2. Right click -> add task -> activeX script task
> 3. Copy in the code and then modify for your xml files
> To set the global variables i use,
> close the tasks and make sure non are selected, click package ->
> properties
> and then click the tab global variables.
> i hope this answers your question. if not, could you please rephrase it?
> thanks.
> Ben
> "Martin Bbl" wrote:

How to load .dbf files in Sql Server 2005

hi

i have a dbf file which i need to import in sql server 2005. \

anyone having any idea to import these files either through integration services or any other tool ?

SSIS will do it. Please search this forum for "dbf" and you'll get plenty of results/examples.|||

Hi Salman,

To add to what Phil said, you will want to use the FoxPro and Visual FoxPro OLE DB data provider. The download link has changed in the past few months. You can find it at http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx.