Database Discussion Boards

Re: How to audit user activities in ms access

Mekong River8-Dec-05 4:28

8-Dec-05 4:28

Thank you very much for your comment. I also prepare to record user activities in a table as the second solution. Now already develop half of this task and this is my first time to design an application with auditing user activities.

Using SQLXML, Bulk Insert XML with IDENTITY Column ...

cmacgowan7-Dec-05 8:53

cmacgowan

7-Dec-05 8:53

Using SQLXML, Bulk Insert XML with IDENTITY Column ...

I have a program that will insert xml data into a table using the SQLXML Bulk Load COM Object. The bulk load is successful when I supply the RecordId in the xml. When I add an IDENTITY column to the table then the bulk load fails with the following error:
[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]

From the following article, XML Bulk Load ignores elements and attributes that are not mapped (either because they are not described in the schema, or because they are annotated in the XSD schema with sql:mapped="false"). All unmapped data goes into the overflow column, if such a column is specified by using sql:overflow-field.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_9w9w.asp

I have attempted using sql:mapped="false" in the schema file and not using it .. both fail with the “nulls not allowed” error.

I have also set the KeepIdentity(true) to my pISQLXMLBulkLoad object.

Below is the table, xml, xsd and code ...
Any comments or answers are appreciated.

Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

///////////////////////////////////////////////////
// xml data (successful) RecordId is included
<ROOT>
<Customers>
<RecordId>1</RecordId>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<RecordId>2</RecordId>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<RecordId>3</RecordId>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

///////////////////////////////////////////////////
// xsd schema file (successful) RecordId is included
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="RecordId" type="xsd:integer" sql:field="RecordId" />
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

///////////////////////////////////////////////////
// xml data (fails) attempting to use identity column
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

///////////////////////////////////////////////////
// xsd schema file (fails) attempting to use identity column
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

///////////////////////////////////////////////////
// error message
<?xml version="1.0"?><Error><Record><HResult>0x80004005</HResult><SQLState>01000</SQLState><NativeError></NativeError><ErrorState>1</ErrorState><Severity>0</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[The statement has been terminated.]]></Description></Record><Record><HResult>0x80004005</HResult><SQLState>23000</SQLState><NativeError></NativeError><ErrorState>2</ErrorState><Severity>16</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]]></Description></Record></Error>

trace the computer name

Goodway7-Dec-05 4:14

Goodway

7-Dec-05 4:14

hi everyone,
In my company, 4 users are accessing the sql server with a username and a password, they are developers, they have rights to create tables, fields, insert rows, deleting rows,...
how can I trace which computer make an insert or delete.
for example if a user make an insert into a table from SQL Server enterprise manager by writing an insert query, how can i know which computer make this action?
is that possible?
thanks.

Re: trace the computer name

miah alom7-Dec-05 6:53

miah alom

7-Dec-05 6:53

I think you are looking for HOST_NAME()

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ha-hz_05r9.asp[^]

Re: trace the computer name

Goodway9-Dec-05 5:39

Goodway

9-Dec-05 5:39

Thanks, but this give me the computer name when a row is added, when a row is updated or deleted nothing is logged.
I can't know who update a specific record.
any other idea.
thanks again.

Re: trace the computer name

miah alom9-Dec-05 5:55

miah alom

9-Dec-05 5:55

I have solved this problem in a different way. When the user logs in to the application, I insert the kerberos user id and the hostname in a Activity table. But there is a limitation. One user cannot start two instances of the application from the same host.

So when there is a update or delete or insert, I pick up the value from this table.

Everytime the user logs in, I clear the Activity for that user in a host machine.

CREATE PROCEDURE ValidateUser (
@userid varchar(10) )
AS
DECLARE @cnt int
BEGIN

DELETE FROM Activity
WHERE Host = host_name()
INSERT INTO Activity VALUES(host_name(),@userid)

SELECT @cnt = count(*)
FROM Users
WHERE ID = @userid

IF @cnt = 1
SELECT 1
ELSE
SELECT 0

END

In the trigger I do something like

IF @@rowcount = 0
RETURN

SELECT @user = CurrentUser
FROM Activity
WHERE Host = host_name()

IF UPDATE(Column1)
INSERT INTO AuditTrail
SELECT @user,getdate(),'Table1','Column1' --blah blah
FROM deleted,inserted
WHERE -------------------

Does this help.

dataset getchanges() question

NikoTanghe7-Dec-05 0:58

NikoTanghe

7-Dec-05 0:58

GetChanges() returns nothing here,
right after a row is deleted.
and there is actually 1 row removed from the dataset,
how is this possible?
dsCustomers.GetChanges() gives the same results.

dsCustomers.Tables(0).Rows.Remove(dsCustomers.Tables(0).Rows(cm.Position))<br />
<br />
Dim DataSetDeleted As DataSet = dsCustomers.GetChanges(DataRowState.Deleted)<br />
If Not DataSetDeleted Is Nothing Then<br />
     daCustomers.Update(DataSetDeleted)<br />
     dsCustomers.AcceptChanges()<br />
End If

Re: dataset getchanges() question

jonathan157-Dec-05 3:55

jonathan15

7-Dec-05 3:55

From what i understand you need to call the delete method of the datarow.

Calling the remove method removes the row entirely (it is gone and the rowcount is reduced). When you call the getchanges method with the deleted parameter it looks for rows marked as deleted. as you have used remove there are no rows that fit this description. If you call the delete method it simply marks the row as deleted. GetChanges with then report that row as deleted and you can call an update.

Jonathan

Re: dataset getchanges() question

miah alom7-Dec-05 4:54

miah alom

7-Dec-05 4:54

Agreed.

Please call DataRow.Delete() instead.

ms sql server biggest data type

kasko_6-Dec-05 15:05

kasko_

6-Dec-05 15:05

hi,
how can i hold text of veeery big size? varchar max is 8000 bytes, and text type by default is text(16) in my mssql server.. maybe there are some algorithms or something else..

Re: ms sql server biggest data type

toxcct6-Dec-05 22:43

toxcct

6-Dec-05 22:43

see for Binary...

TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20] | soon : [VisualCalc 3.0]

Re: ms sql server biggest data type

Daniel Santillanes7-Dec-05 13:39

Daniel Santillanes

7-Dec-05 13:39

Actually... text(16) doesn't mean it only holds 16 characters... look it up, this type is REALLY big.

daniero

How to create a code parser?

Lord Kixdemp6-Dec-05 12:35

Lord Kixdemp

6-Dec-05 12:35

Hello everyone! Big Grin | :-D

OK, I would like to know that... I don't want to use a pre-made library, I want to make my own, does anyone know of a good tutorial on this? Thanks! Wink | ;-)

Lord Kixdemp
www.SulfurMidis.com
www.SulfurSoft.tk
[ftp://][http://][hotline://]tsfc.ath.cx

Re: How to create a code parser?

toxcct6-Dec-05 22:31

toxcct

6-Dec-05 22:31

code parser for what ?

you could have a look at my VisualCalc, even if it is mathematics oriented, but the general idea for writing a parser is still preserved. if you have any question on it, you can ask on the forum at the bottom of the article...

cheers,

TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20] | soon : [VisualCalc 3.0]

Re: How to create a code parser?

Lord Kixdemp10-Dec-05 9:25

Lord Kixdemp

10-Dec-05 9:25

Yeah, I looked at your code... seems complicated, but I'll try it! Thanks! Wink | ;-)

Lord Kixdemp
www.SulfurMidis.com
www.SulfurSoft.tk
[ftp://][http://][hotline://]tsfc.ath.cx

Export public keys from certificate

Russell Jones6-Dec-05 4:13

Russell Jones

6-Dec-05 4:13

I'm trying to use RSA to protect incoming packets of data containing changed passwords.

My first idea was to use c# and the RSACryptoServiceProvider to generate the key information and to save the privatekeyinfo in a db table. The keys would then be replenished every x minutes. This seemed slightly wrong, as the private key info could be intercepted and used to impersonate a user changing the password.

I then discovered that there is a CREATE ASSYMETRIC KEY x_y_z WITH ALGORITHM RSA_x feature in SQL server. I have created the master keys and generated the key-pair, but i am at a loss as to how to retrieve the public key info so i can send it to the clients.

Any pointers would really helpful,

Russ

ToWords() Method within crystal Report

achrafus6-Dec-05 2:07

achrafus

6-Dec-05 2:07

hi everybody,
I have a small question concerning the use of ToWords() method that is embeded within Crystal Report.This function translates numeric values into their coresponding words value ex:150 --> One handred and fifty.
The problem I got is that I got an automatic english conversion of my numeric values where I need french corresponing string value. Is there any way I can swish it to french? Or how can I add my function, that does the french translation, to the crystal report embeded functions?
If anyone can help pls do.

Learning is never ending

Re: ToWords() Method within crystal Report

Russell Jones6-Dec-05 4:25

Russell Jones

6-Dec-05 4:25

to add your own function create a COM dll whose name starts with CRUFL and place it in the same directory as u2lcom.dll on your machine. Functions declared in the dll will now be available to crystal reports.

I've used this before to translate report labels.

HTH

Russ

Re: ToWords() Method within crystal Report

Russell Jones6-Dec-05 5:23

Russell Jones

6-Dec-05 5:23

Just to clarify about com dlls.

I have done this in the past using VB6 which created the dlls for me. Now that vb and c# 2005 express editors are available from MS free of charge i guess you could use these and generate interop assemblies to use with crystal.

i found this link on codeproject with a .net example

http://www.codeproject.com/dotnet/DecryptForCrystal.asp?print=true

HTH

Russ

Re: ToWords() Method within crystal Report

achrafus7-Dec-05 7:40

achrafus

7-Dec-05 7:40

hi, and sorry again.
I followed the steps that are in the link and got some problems I couldn"t deal with. The first is that my DLL couldn't be registered within Windows. It says "No DLLRegisterServer Access point found"? I don't know what it means. The other problem is that within my project the "register for Com Interop is set to false but does not allow changes to true.
What I did is I created a new projet containing only the class needed to the translation an builded a new DLL named CRUFLctw.dll. Bt I 'm having the problem of Access point ??So the function still does not appear within formla staff in crystal report...
Please, I m really blocked and need your help

Thanks in advance

-- modified at 14:01 Wednesday 7th December, 2005

Re: ToWords() Method within crystal Report

Russell Jones7-Dec-05 22:35

Russell Jones

7-Dec-05 22:35

My guess here, and it's only a guess is that you have specified a windows application as the project output type. This is in the General section of the common properties tab. If you change the output type to Class library and then close and reopen the properties window (apply is not enough for some reason) You should be able to change the value of register for com interop.

BTW do you know that you will have to have the .net framework installed on any client that you want to run this function?

Re: ToWords() Method within crystal Report

achrafus7-Dec-05 23:59

achrafus

7-Dec-05 23:59

thanks for your answer,
I checked the project output it's set to class library. I think the problem comes from the fact that when trying to register the dll using regsvr32 it does not find DLLRegisterServer entry point. the msg I got is mydll.dll was correctly loaded But the DLLRegisterServer entry point was not found. Unable to register within windows. (I got the msg in french , I think this is the eng version.)
I didn't know that I'll need to have the .net framework installed on my clients

What Isolation Level should I use

The Liquidian6-Dec-05 1:30

The Liquidian

6-Dec-05 1:30

In my apllication, I read from 3 tables, I then make updates on the tables and then I read from them again, then I commit or roleback based on the information.

I want to make sure that from when I do the first read, that the database (or at least the 3 tables) is/are locked to that specific transaction. I do not want other clients to even read from the 3 table while the transaction is in progress.

I would like to know what Isolation Level is best for this?

Thank you.... Roll eyes | :rolleyes:

Re: What Isolation Level should I use

Farhan Noor Qureshi6-Dec-05 9:55

Farhan Noor Qureshi

6-Dec-05 9:55

The default one i.e. "Read committed" is fine in this case.

Farhan Noor Qureshi

^{if (this == this) thow this;}

SQL server 2000 backup

tusar1235-Dec-05 23:55

tusar123

5-Dec-05 23:55

Hi,
I want to take backup my datbase with backup filename as string + system date .can You suggest me how it is possible in SQL server 2000 Using Scheduling.

Thanks in Advance

Manoj

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.