Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
Nimesh
Upgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegr oups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>
|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegr oups.com...
>
>
> - Show quoted text -

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
NimeshUpgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
>
> > Hi,
> > I have an requirement for doing somecustomaction in my
> >applicationwhen anySQLservertable is modified. One of the ways,
> > which I think it is achievable is to write ancustomapplicationwhich
> > listens toSQLqueries fired on the particular Database. This will
> > enable me to do thecustomaction, when any DML statement is trapped
> > through mycustomSQLprofiler. So, more or less, it boils down to
> > writing/using something similar to theSQLProfilertool. Any help
> > would be appreciated.
> > Thanks in Advance,
> > Nimesh- Hide quoted text -
> - Show quoted text -

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
NimeshUpgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
>
>
>
> - Show quoted text -sql

Creating a vb6 Custom Install for MSDE 2000

Hello,
I'm trying to add MSDE to my installation program, and so far have been
unsuccessful. Right now, I'm shelling out the setup.exe program with the
parameters I want, and the install seems to go successfully. However, then I
cannot get that instance of MSDE to start, whether by "net start
instancename" or running sqlservr.exe from the instance's directory, or
running scm.exe from the Tools\Binn directory. Can someone show me the code
they have used to install MSDE along with their apps that works? Thank you
very much in advance for your help.
hi Jason,
"JasonSCC" <JasonSCC@.discussions.microsoft.com> ha scritto nel
messaggio news:AF3F3CDE-FEA0-47E0-B04A-44DE11592A93@.microsoft.com
> Hello,
> I'm trying to add MSDE to my installation program, and so far have
> been unsuccessful. Right now, I'm shelling out the setup.exe program
> with the parameters I want, and the install seems to go successfully.
> However, then I cannot get that instance of MSDE to start, whether by
> "net start instancename" or running sqlservr.exe from the instance's
> directory, or running scm.exe from the Tools\Binn directory. Can
> someone show me the code they have used to install MSDE along with
> their apps that works? Thank you very much in advance for your help.
I install MSDE the very same way you do, but actually I never tried to start
the service my self as I always require a reboot, in order to properly
register all COM components and services registration
after rebooting, the services are available for all my needs..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Fri, 7 Jan 2005 06:29:03 -0800, "JasonSCC"
<JasonSCC@.discussions.microsoft.com> wrote:

>Hello,
>I'm trying to add MSDE to my installation program, and so far have been
>unsuccessful. Right now, I'm shelling out the setup.exe program with the
>parameters I want, and the install seems to go successfully. However, then I
>cannot get that instance of MSDE to start, whether by "net start
>instancename" or running sqlservr.exe from the instance's directory, or
>running scm.exe from the Tools\Binn directory. Can someone show me the code
>they have used to install MSDE along with their apps that works? Thank you
>very much in advance for your help.
Have a look at the MSDE Installer example at
http://www.mvps.org/vbvision/
HTH,
Bryan
__________________________________________________ __________
New Vision Software "When the going gets weird,"
Bryan Stafford "the weird turn pro."
alpine_don'tsendspam@.mvps.org Hunter S. Thompson -
Microsoft MVP-Visual Basic Fear and Loathing in LasVegas
sql

Creating a generic package to import a variable number of columns

Hi,

We are building an application with

a database that contains Jobs. These Jobs have properties like Name, Code etc.

and some custom properties, definable by the application admin. For bulk import

of Jobs, we want to allow the import of an Excel sheet with the columns Name,

Code and a variable amount of columns. If the header names of these columns in

the Excel sheet match the name of a custom property in the system we want to add

the value of that cell into the database as property

value.

In our Data Flow of our Import

Package in SSIS we added an Excel Source that points to a test excel sheet with

the Name and Code columns and – for this example - 3 custom property columns

(Area, Department, Job Family). When we configure the Excel Source in the Excel

Source Editor, we have the option to select the Columns from the Available

External Columns table. But here lays the problem, we do not know at design

time, what custom property columns to expect. We DO expect the Name and Code

columns, but the rest is uncertain at design-time.

That raises the question: Is there

some way to select all of any incoming columns (something like a SELECT * in

T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is

being generated at design-time would need to be updated at run-time to reflect

the variability of the columns that might be encountered while reading the excel

sheet.

Then, we thought, we could add a Script

Component to our data flow that passes some kind of DataSet (or DataReader) in

which we can walk through the columns ourselves? But then still, we miss the

option to include ANY of the columns while reading an Excel sheet (or any other

datasource by the looks of it)

We are aware of the option of

optional columns in combination with the RaggedRight option, but it seems that

we would have to put all of the columns of a row in just one column and then

extract all the columns later with Derived Columns. But then, since the source

import file is being prepared by an application admin, we want don’t want to

burden him with this horrendous task of putting everything in one

column.

We would like to have some way of

iterating through all the columns, either in a Script Component or maybe with a

Pivot/Unpivot mechanism.

Does anyone have any suggestions? Are there other options we should have considered?

The metadata of the pipeline is fixed at design-time. You cannot change the columns at runtime.

-Jamie

|||

Since you're importing from Excel, you may be able to define a dataflow that reads the maximum amount of columns you anticipate ever having in one Excel file. The Excel files with less columns would return empty strings for the non-existent columns.

Haven't tried this, but it might work.

K

Creating a custom word-breaker and stemmer

Does anyone know of any links to articles or information on creating a
custom word-breaker and stemmer for full-text search on SQL 2005?
In the meantime I'll keep Googling.
Thanks
Microsoft.com has some articles.
http://msdn2.microsoft.com/en-gb/library/ms691035.aspx
http://msdn2.microsoft.com/en-us/library/ms916793.aspx
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:e5e4OywPHHA.3344@.TK2MSFTNGP02.phx.gbl...
> Does anyone know of any links to articles or information on creating a
> custom word-breaker and stemmer for full-text search on SQL 2005?
> In the meantime I'll keep Googling.
> Thanks
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23IUb7byPHHA.856@.TK2MSFTNGP05.phx.gbl...
> Microsoft.com has some articles.
> http://msdn2.microsoft.com/en-gb/library/ms691035.aspx
> http://msdn2.microsoft.com/en-us/library/ms916793.aspx
Thanks Hilary. BTW the folks here at work want to start doing some
full-text searching on certain items, and I've pointed them to your articles
for answers to a lot of their questions. Do you have a book on it, and if
so where can I get a copy?
Thanks again
|||The book is as yet unpublished.
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:OmbJ0czPHHA.2312@.TK2MSFTNGP04.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23IUb7byPHHA.856@.TK2MSFTNGP05.phx.gbl...
> Thanks Hilary. BTW the folks here at work want to start doing some
> full-text searching on certain items, and I've pointed them to your
> articles for answers to a lot of their questions. Do you have a book on
> it, and if so where can I get a copy?
> Thanks again
>
|||Please post when it's out, we could definitely use it.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubTWVadQHHA.4276@.TK2MSFTNGP02.phx.gbl...
> The book is as yet unpublished.
> --
> 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
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:OmbJ0czPHHA.2312@.TK2MSFTNGP04.phx.gbl...
>
|||Mike I had a contract with Apress to put one out several years ago - it
focused on Indexing Services. I wrote 912 pages and then .Net happened. I
then got more interested in replication and never updated it for .Net. Nor
did I complete a chapter on SharePoint. I just don't think the demand is
there for a book on search at this time. NWSU did express an interest in it
as well, but I really have moved on to other things.
If you want to read the 912 pages just ask. I wrote a chapter on SQL FTS
2005 for SQL Server 2005 Bible, but it was based on the late betas/CTPs, and
SP 1 brought a few changes.
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
"Mike C#" <xyz@.xyz.com> wrote in message
news:u8$Z40vQHHA.496@.TK2MSFTNGP06.phx.gbl...
> Please post when it's out, we could definitely use it.
> Thanks
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubTWVadQHHA.4276@.TK2MSFTNGP02.phx.gbl...
>
|||I would LOVE to read the 912 pages. Can you email it to me?
jsheehan@.groceryshopping.net
"Hilary Cotter" wrote:

> Mike I had a contract with Apress to put one out several years ago - it
> focused on Indexing Services. I wrote 912 pages and then .Net happened. I
> then got more interested in replication and never updated it for .Net. Nor
> did I complete a chapter on SharePoint. I just don't think the demand is
> there for a book on search at this time. NWSU did express an interest in it
> as well, but I really have moved on to other things.
> If you want to read the 912 pages just ask. I wrote a chapter on SQL FTS
> 2005 for SQL Server 2005 Bible, but it was based on the late betas/CTPs, and
> SP 1 brought a few changes.
> --
> 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
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:u8$Z40vQHHA.496@.TK2MSFTNGP06.phx.gbl...
>
>
|||Me too, sent you an email Hilary. Thanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ehwe9w0QHHA.4476@.TK2MSFTNGP05.phx.gbl...
> Mike I had a contract with Apress to put one out several years ago - it
> focused on Indexing Services. I wrote 912 pages and then .Net happened. I
> then got more interested in replication and never updated it for .Net. Nor
> did I complete a chapter on SharePoint. I just don't think the demand is
> there for a book on search at this time. NWSU did express an interest in
> it as well, but I really have moved on to other things.
> If you want to read the 912 pages just ask. I wrote a chapter on SQL FTS
> 2005 for SQL Server 2005 Bible, but it was based on the late betas/CTPs,
> and SP 1 brought a few changes.
> --
> 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
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:u8$Z40vQHHA.496@.TK2MSFTNGP06.phx.gbl...
>

Creating a custom transformation component Walkthrough

Microsoft published a "Creating a custom transformation component Walkthrough" published on

http://www.microsoft.com/downloads/details.aspx?FamilyID=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&DisplayLang=en

Does anyone know where to get the Hands-On Lab Files mentioned?

Thanks

Alex

I could not see any mention of files over and above the word document. There are plenty of samples available separately on MS Downloads, just search for SSIS.|||

On page 15, Task 1, Step 1 a path "c:\ssis hands-on-lab\scratch\.... " is mentioned. I just wanted to take a look at the regcomponent shown in setp 3.

Thanks anyway

|||

You create that yourself by going through pages 1 to 14!

-Jamie

|||http://www.microsoft.com/downloads/details.aspx?FamilyID=fc4de21d-9c5b-4b1d-aeef-ce43aadab4e4&DisplayLang=en

Creating a custom transformation component Walkthrough

Microsoft published a "Creating a custom transformation component Walkthrough" published on

http://www.microsoft.com/downloads/details.aspx?FamilyID=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&DisplayLang=en

Does anyone know where to get the Hands-On Lab Files mentioned?

Thanks

Alex

I could not see any mention of files over and above the word document. There are plenty of samples available separately on MS Downloads, just search for SSIS.|||

On page 15, Task 1, Step 1 a path "c:\ssis hands-on-lab\scratch\.... " is mentioned. I just wanted to take a look at the regcomponent shown in setp 3.

Thanks anyway

|||

You create that yourself by going through pages 1 to 14!

-Jamie

|||http://www.microsoft.com/downloads/details.aspx?FamilyID=fc4de21d-9c5b-4b1d-aeef-ce43aadab4e4&DisplayLang=en

Creating a custom resolver with VB.NET

Someone posted the question "Can anyone point me towards a source code
listing for a working replication custom resolver written in .Net?" Here is
an example that I have written.
Custom resolvers are created by adding a reference to the Microsoft SQL
Replication Conflict Resolver Library, replrec.dll. Unfortunately, the .NET
type library importer incorrectly defines the buffer parameter of methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to be the
address of an Object. For COM interfaces, which is what replrec.dll is
supposed to be, that implies a COM-VARIANT parameter passing mechanism, but
this is not what Replrec passes back. Replrec is simply passing back the
address of a buffer containing the column value which you have to decode.
So the solution involves correcting the method definitions so that a buffer
address can be passed and using the .NET Marshal routines to create the
buffer and move bytes out of the buffer.
Here are the steps involved, followed by a code example that resolves
conflicts by using the column values from the row with the latest date in
user defined column ModifyDate.
1. Open a Visual Studio .NET 2003 Command Prompt
2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
/OUT:SQLResolver_import.dll
3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
int32 pvBuffer"
5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
6. Create a new .NET Windows Control Library project
7. Remove the wizard generated control
8. Use "Add New Item..." to add a new COM class
9. Add reference to SQLResolver.dll created in step #5
Imports System.Text
Imports SQLResolver
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.MarshalAsAttribute
Imports SQLResolver.REPOLE_CHANGE_TYPE
Imports SQLResolver.REPOLE_COLSTATUS_TYPE
<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1
Implements SQLResolver.IVBCustomResolver
Private Const MAX_BUFFER_SIZE As Integer = 1048576
Private Const MAX_NAME_LENGTH As Integer = 128
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
Public Const InterfaceId As String =
"CECFBB8F-584F-4733-9373-B69AFA6F117F"
Public Const EventsId As String = "5D55BA40-A438-4FD4-BA8B-05095DC89948"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
IVBCustomResolver.GetHandledStates
ResolverBm = REPOLEUpdateConflicts
End Sub
Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
Integer, ByVal pvReserved As IReplRowChange) Implements
IVBCustomResolver.Reconcile
Dim cntColumns As Integer
Dim intColumn As Integer
Dim strColumnName As String
Dim strLogMessage As String
Dim WinningData As Object
Dim blnSourceIsWinner As Boolean
Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
Dim intBufferLenActual As Integer
Dim intBufferLen As Integer
Dim strDestinationDateTime As String
Dim strSourceDateTime As String
Dim strDestinationUser As String
Dim strSourceUser As String
Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
Dim strMsg As String
'If Not Debugger.IsAttached Then
' Debugger.Launch()
'Else
' Debugger.Break()
'End If
Call pRowChange.GetNumColumns(cntColumns)
For intColumn = 1 To cntColumns
strColumnName = " ".PadRight(MAX_NAME_LENGTH)
' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
Call pRowChange.GetColumnName(intColumn, strColumnName, MAX_NAME_LENGTH)
' strColumnName.TrimEnd(Chr(vbNull))
strColumnName = strColumnName.TrimEnd()
If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationDateTime = ConvertBufferToDateString(myBuffer)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceDateTime = ConvertBufferToDateString(myBuffer)
If strSourceDateTime > strDestinationDateTime Then
blnSourceIsWinner = True
Else
blnSourceIsWinner = False
End If
End If
If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
End If
Next intColumn
For intColumn = 1 To cntColumns
'Get the column status of each column
pRowChange.GetColumnStatus(intColumn, ColStatus)
' If the column has been updated at both the Publisher and Subscriber
If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
If blnSourceIsWinner Then
pRowChange.CopyColumnFromSource(intColumn)
End If
' For columns that have been updated without any changes, copy column
values from source
ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
pRowChange.CopyColumnFromSource(intColumn)
' For columns that have not been updated - do nothing.
ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
End If
Next intColumn
' Log conflict and call the UpdateRow method to commit all the column value
changes.
'
If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0 Then
If blnSourceIsWinner Then
strMsg = "Losing update(s) made by " & strDestinationUser
Else
strMsg = "Losing update(s) made by " & strSourceUser
End If
End If
pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
Call pRowChange.UpdateRow()
Marshal.FreeHGlobal(myBuffer)
End Sub
Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
Dim s As String = String.Empty
Dim i(7) As Short
Dim j As Integer
Marshal.Copy(p, i, 0, i.Length)
s = i(0).ToString '4 digit Year
For j = 1 To i.GetUpperBound(0)
s &= i(j).ToString.PadLeft(2, "0"c)
Next
ConvertBufferToDateString = s
End Function
Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
intBufferLenActual As Integer) As String
Dim i As Integer
Dim s As String = String.Empty
For i = 0 To intBufferLenActual - 1
s &= Chr(Marshal.ReadByte(p, i))
Next
ConvertBufferToString = s
End Function
Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
String, ByVal inMsg As String)
s = s & ";" & inMsg
If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
End Sub
End Class
u rock man!
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
"Douglas Arterburn" <darterburn@.precisdev.com> wrote in message
news:uzhwyd7iFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Someone posted the question "Can anyone point me towards a source code
> listing for a working replication custom resolver written in .Net?" Here
> is an example that I have written.
> Custom resolvers are created by adding a reference to the Microsoft SQL
> Replication Conflict Resolver Library, replrec.dll. Unfortunately, the
> .NET type library importer incorrectly defines the buffer parameter of
> methods GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to
> be the address of an Object. For COM interfaces, which is what
> replrec.dll is supposed to be, that implies a COM-VARIANT parameter
> passing mechanism, but this is not what Replrec passes back. Replrec is
> simply passing back the address of a buffer containing the column value
> which you have to decode.
> So the solution involves correcting the method definitions so that a
> buffer address can be passed and using the .NET Marshal routines to create
> the buffer and move bytes out of the buffer.
> Here are the steps involved, followed by a code example that resolves
> conflicts by using the column values from the row with the latest date in
> user defined column ModifyDate.
> 1. Open a Visual Studio .NET 2003 Command Prompt
> 2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
> /OUT:SQLResolver_import.dll
> 3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
> 4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
> GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
> int32 pvBuffer"
> 5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
> 6. Create a new .NET Windows Control Library project
> 7. Remove the wizard generated control
> 8. Use "Add New Item..." to add a new COM class
> 9. Add reference to SQLResolver.dll created in step #5
> Imports System.Text
> Imports SQLResolver
> Imports System.Runtime.InteropServices
> Imports System.Runtime.InteropServices.MarshalAsAttribute
> Imports SQLResolver.REPOLE_CHANGE_TYPE
> Imports SQLResolver.REPOLE_COLSTATUS_TYPE
> <ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
> Public Class ComClass1
> Implements SQLResolver.IVBCustomResolver
> Private Const MAX_BUFFER_SIZE As Integer = 1048576
> Private Const MAX_NAME_LENGTH As Integer = 128
> #Region "COM GUIDs"
> ' These GUIDs provide the COM identity for this class
> ' and its COM interfaces. If you change them, existing
> ' clients will no longer be able to access the class.
> Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
> Public Const InterfaceId As String =
> "CECFBB8F-584F-4733-9373-B69AFA6F117F"
> Public Const EventsId As String =
> "5D55BA40-A438-4FD4-BA8B-05095DC89948"
> #End Region
> ' A creatable COM class must have a Public Sub New()
> ' with no parameters, otherwise, the class will not be
> ' registered in the COM registry and cannot be created
> ' via CreateObject.
> Public Sub New()
> MyBase.New()
> End Sub
> Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
> IVBCustomResolver.GetHandledStates
> ResolverBm = REPOLEUpdateConflicts
> End Sub
> Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
> Integer, ByVal pvReserved As IReplRowChange) Implements
> IVBCustomResolver.Reconcile
> Dim cntColumns As Integer
> Dim intColumn As Integer
> Dim strColumnName As String
> Dim strLogMessage As String
> Dim WinningData As Object
> Dim blnSourceIsWinner As Boolean
> Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
> Dim intBufferLenActual As Integer
> Dim intBufferLen As Integer
> Dim strDestinationDateTime As String
> Dim strSourceDateTime As String
> Dim strDestinationUser As String
> Dim strSourceUser As String
> Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
> Dim strMsg As String
> 'If Not Debugger.IsAttached Then
> ' Debugger.Launch()
> 'Else
> ' Debugger.Break()
> 'End If
> Call pRowChange.GetNumColumns(cntColumns)
> For intColumn = 1 To cntColumns
> strColumnName = " ".PadRight(MAX_NAME_LENGTH)
> ' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
> Call pRowChange.GetColumnName(intColumn, strColumnName,
> MAX_NAME_LENGTH)
> ' strColumnName.TrimEnd(Chr(vbNull))
> strColumnName = strColumnName.TrimEnd()
> If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationDateTime = ConvertBufferToDateString(myBuffer)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceDateTime = ConvertBufferToDateString(myBuffer)
> If strSourceDateTime > strDestinationDateTime Then
> blnSourceIsWinner = True
> Else
> blnSourceIsWinner = False
> End If
> End If
> If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> End If
> Next intColumn
> For intColumn = 1 To cntColumns
> 'Get the column status of each column
> pRowChange.GetColumnStatus(intColumn, ColStatus)
> ' If the column has been updated at both the Publisher and Subscriber
> If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
> If blnSourceIsWinner Then
> pRowChange.CopyColumnFromSource(intColumn)
> End If
> ' For columns that have been updated without any changes, copy column
> values from source
> ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
> pRowChange.CopyColumnFromSource(intColumn)
> ' For columns that have not been updated - do nothing.
> ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
> End If
> Next intColumn
> ' Log conflict and call the UpdateRow method to commit all the column
> value changes.
> '
> If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0
> Then
> If blnSourceIsWinner Then
> strMsg = "Losing update(s) made by " & strDestinationUser
> Else
> strMsg = "Losing update(s) made by " & strSourceUser
> End If
> End If
> pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
> REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
> REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
> Call pRowChange.UpdateRow()
> Marshal.FreeHGlobal(myBuffer)
>
> End Sub
> Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
> Dim s As String = String.Empty
> Dim i(7) As Short
> Dim j As Integer
> Marshal.Copy(p, i, 0, i.Length)
> s = i(0).ToString '4 digit Year
> For j = 1 To i.GetUpperBound(0)
> s &= i(j).ToString.PadLeft(2, "0"c)
> Next
> ConvertBufferToDateString = s
> End Function
> Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
> intBufferLenActual As Integer) As String
> Dim i As Integer
> Dim s As String = String.Empty
> For i = 0 To intBufferLenActual - 1
> s &= Chr(Marshal.ReadByte(p, i))
> Next
> ConvertBufferToString = s
> End Function
> Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
> String, ByVal inMsg As String)
> s = s & ";" & inMsg
> If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
> End Sub
> End Class
>

Creating a custom Delivery Extension (File Share)

I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.

I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.

Are there any "Dummies" type of tutorials out there to get me started down this road?

Thanks for reading

hi , you can use this code .

public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}

Creating a custom Delivery Extension (File Share)

I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.

I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.

Are there any "Dummies" type of tutorials out there to get me started down this road?

Thanks for reading

hi , you can use this code .

public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}

Creating a Custom Connection Manager Sample

http://msdn2.microsoft.com/en-us/library/ms345276.aspx

does anyone know where this can be obtained? it did not come with the RTM. it does not seem to be available via download either?

a little help here!

If you will provide an email address, I can email you this sample and a 2nd custom connection manager sample that will be included in the next refresh of BOL, scheduled to coincide with SP1.

-Doug

|||

andy.d.loechler@.wellsfargo.com

thanks

|||Can I get it too. (cipracunari at sezampro dot yu)
|||

As Books Online has been updated, we also have some updated samples to match-

Download details: SQL Server 2005 Samples and Sample Databases (April 2006)
(http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en)

It is in the SqlServerSamples.msi.

Creating a Custom Connection Manager Sample

http://msdn2.microsoft.com/en-us/library/ms345276.aspx

does anyone know where this can be obtained? it did not come with the RTM. it does not seem to be available via download either?

a little help here!

If you will provide an email address, I can email you this sample and a 2nd custom connection manager sample that will be included in the next refresh of BOL, scheduled to coincide with SP1.

-Doug

|||

andy.d.loechler@.wellsfargo.com

thanks

|||Can I get it too. (cipracunari at sezampro dot yu)|||

As Books Online has been updated, we also have some updated samples to match-

Download details: SQL Server 2005 Samples and Sample Databases (April 2006)
(http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en)

It is in the SqlServerSamples.msi.

CreateSubscription service creates all the subscriptions as ASPNET user

I have a custom form which users can use to create subscriptions for specific
reprots. I am able to create subscriptions just fine but my issue is that all
the subscriptions are created as ASPNET user. Am I missing something in the
setup?
Thanks for your help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1Hi,
Do you use Windows NT Authentication or anonymous access is enabled?
"akhan via SQLMonster.com" wrote:
> I have a custom form which users can use to create subscriptions for specific
> reprots. I am able to create subscriptions just fine but my issue is that all
> the subscriptions are created as ASPNET user. Am I missing something in the
> setup?
> Thanks for your help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||I am using Windows Authentication.
eralper wrote:
>Hi,
>Do you use Windows NT Authentication or anonymous access is enabled?
>> I have a custom form which users can use to create subscriptions for specific
>> reprots. I am able to create subscriptions just fine but my issue is that all
>> the subscriptions are created as ASPNET user. Am I missing something in the
>> setup?
>> Thanks for your help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1