Attach remote DB file: text

Wednesday, March 7, 2012

Audit Question

This is a multi-part message in MIME format.
--=_NextPart_000_0082_01C4C333.0A1F0510
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Is there a way I can monitor who is using EM to open tables?
Suppose I have a database with a table(s) where I keep the answers to = questions on a test, or answers to a game a company is running on an = intranet, how do I know if people with sa permissions are going in via = the back door to look/retrieve answers?
Thanx!
--=_NextPart_000_0082_01C4C333.0A1F0510
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Is there a way I can monitor who is = using EM to open tables?

Suppose I have a database with a = table(s) where I keep the answers to questions on a test, or answers to a game a company = is running on an intranet, how do I know if people with sa permissions are = going in via the back door to look/retrieve answers?
Thanx!
--=_NextPart_000_0082_01C4C333.0A1F0510--This is a multi-part message in MIME format.
--=_NextPart_000_0159_01C4C33B.94A514B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You could use the Profiler on the database and monitor queries, spid's =etc. This can all
be logged to a table.
"JLS" <jlshoop@.hotmail.com> wrote in message =news:eiWh$z1wEHA.1300@.TK2MSFTNGP14.phx.gbl...
Is there a way I can monitor who is using EM to open tables?
Suppose I have a database with a table(s) where I keep the answers to =questions on a test, or answers to a game a company is running on an =intranet, how do I know if people with sa permissions are going in via =the back door to look/retrieve answers?
Thanx!
--=_NextPart_000_0159_01C4C33B.94A514B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You could use the Profiler on the =database and monitor queries, spid's etc. This can all
be logged to a table.
"JLS" wrote in message news:eiWh$z1wEHA.1300=@.TK2MSFTNGP14.phx.gbl...
Is there a way I can monitor who is =using EM to open tables?

Suppose I have a database with a =table(s) where I keep the answers to questions on a test, or answers to a game a =company is running on an intranet, how do I know if people with sa permissions =are going in via the back door to look/retrieve answers?
Thanx!

--=_NextPart_000_0159_01C4C33B.94A514B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0179_01C4C33C.37B5EC60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I also forgot to add... the sa password should really be kept under =wraps and in
the hands of people on a need to know basis.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message =news:eUYK$V2wEHA.1524@.TK2MSFTNGP09.phx.gbl...
You could use the Profiler on the database and monitor queries, spid's =etc. This can all
be logged to a table.
"JLS" <jlshoop@.hotmail.com> wrote in message =news:eiWh$z1wEHA.1300@.TK2MSFTNGP14.phx.gbl...
Is there a way I can monitor who is using EM to open tables?
Suppose I have a database with a table(s) where I keep the answers =to questions on a test, or answers to a game a company is running on an =intranet, how do I know if people with sa permissions are going in via =the back door to look/retrieve answers?
Thanx!
--=_NextPart_000_0179_01C4C33C.37B5EC60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I also forgot to add... the sa =password should really be kept under wraps and in
the hands of people on a need to know basis.
"Armando Prato"
You could use the Profiler on the =database and monitor queries, spid's etc. This can all
be logged to a table.

"JLS" wrote =in message news:eiWh$z1wEHA.1300=@.TK2MSFTNGP14.phx.gbl...
Is there a way I can monitor who is =using EM to open tables?

Suppose I have a database with a =table(s) where I keep the answers to questions on a test, or answers to a game a =company is running on an intranet, how do I know if people with sa permissions =are going in via the back door to look/retrieve answers?
Thanx!

--=_NextPart_000_0179_01C4C33C.37B5EC60--|||The best solution would be to put that database on a secured installation
where you controlled who had sa access.
The problem with any sort of auditing solution is that the people you are
auditing are GOD within the system, which means they have the power to
subvert your tracking.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:
> You could use the Profiler on the database and monitor queries, spid's etc. This can all
> be logged to a table.
>
> "JLS" <jlshoop@.hotmail.com> wrote in message news:eiWh$z1wEHA.1300@.TK2MSFTNGP14.phx.gbl...
> Is there a way I can monitor who is using EM to open tables?
> Suppose I have a database with a table(s) where I keep the answers to questions on a test, or answers to a game a company is running on an intranet, how do I know if people with sa permissions are going in via the back door to look/retrieve answers?
> Thanx!|||Exactly!!! These are my peers, network guys in my department. But I want
the game to be fair, if they can just go into the tables via the back door
then that's really not fair to the rest of the people playing.
To address the comment about the sa password only being given on a need to
know, it is.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:006309CB-AB18-4C59-8B35-75903AA6ECC2@.microsoft.com...
> The best solution would be to put that database on a secured installation
> where you controlled who had sa access.
> The problem with any sort of auditing solution is that the people you are
> auditing are GOD within the system, which means they have the power to
> subvert your tracking.
> Sincerely,
>
> Anthony Thomas
>
> "Armando Prato" wrote:
> >
> > You could use the Profiler on the database and monitor queries, spid's
etc. This can all
> > be logged to a table.
> >
> >
> > "JLS" <jlshoop@.hotmail.com> wrote in message
news:eiWh$z1wEHA.1300@.TK2MSFTNGP14.phx.gbl...
> > Is there a way I can monitor who is using EM to open tables?
> >
> > Suppose I have a database with a table(s) where I keep the answers to
questions on a test, or answers to a game a company is running on an
intranet, how do I know if people with sa permissions are going in via the
back door to look/retrieve answers?
> >
> > Thanx!|||You could always change the 'sa' password yourself prior to the
game commencing. I am assuming that this isn't a production
server. At least I hope it's not ;)
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uPoodP3wEHA.3612@.tk2msftngp13.phx.gbl...
> Exactly!!! These are my peers, network guys in my department. But I want
> the game to be fair, if they can just go into the tables via the back door
> then that's really not fair to the rest of the people playing.
> To address the comment about the sa password only being given on a need to
> know, it is.
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:006309CB-AB18-4C59-8B35-75903AA6ECC2@.microsoft.com...
> > The best solution would be to put that database on a secured
installation
> > where you controlled who had sa access.
> >
> > The problem with any sort of auditing solution is that the people you
are
> > auditing are GOD within the system, which means they have the power to
> > subvert your tracking.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "Armando Prato" wrote:
> >
> > >
> > > You could use the Profiler on the database and monitor queries, spid's
> etc. This can all
> > > be logged to a table.
> > >
> > >
> > > "JLS" <jlshoop@.hotmail.com> wrote in message
> news:eiWh$z1wEHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > Is there a way I can monitor who is using EM to open tables?
> > >
> > > Suppose I have a database with a table(s) where I keep the answers
to
> questions on a test, or answers to a game a company is running on an
> intranet, how do I know if people with sa permissions are going in via the
> back door to look/retrieve answers?
> > >
> > > Thanx!
>

Thursday, February 16, 2012

Attaching files

I have just started using ASP.net.
I have to develop a system that attaches word and PDF files to form. I also need to be able to do a full text search on the documents attached.
I'm currently using SQL server 2000 as my backend DBMS, but I have never used it to attach files. My mates recommend using BLOGS but I'm not sure how to do this and I'm not sure if I can do a full text search using this solution.
Could some body please recommend a solution to get me started.
Thanks in advanced
PaddyYou have two requirements because Word and PDF files are better stored as IMAGE while FULL TEXT uses TEXT and NTEXT. FULL TEXT is an add on to SQL Server dependent on Microsoft Search and the Catalogs must be populate to get search results. Run a search for FULL TEXT, CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE in the BOL(books online). Hope this helps.

Sunday, February 12, 2012

Attachement generated by xp_sendmail

Hello folks, I am sending a email from sql server that consists of results of a query and that's working perfect.
When I open the text file generated by xp_sendmail in notepad it's OK, but when I open it in MS Word or WordPad, I see garbage characters in form of squares added to the content. Those sqare are unicode spaces equivalent to Nchar(140) or Nchar(0020) in SQL. I tried to replace them using the replace function :

declare @.foo varchar(8000)
set @.foo='select top 4 Replace(priMarykey,Nchar(0020),'') as primarykey,
Replace(Sitename,Nchar(0020),'') as Sitename,
Replace(REgNumber,Nchar(0020),'') as REgNumber,
Replace(ContactEmpFirstNames,Nchar(0020),'') as ContactEmp
from organisationsite'
EXEC master..xp_sendmail @.recipients = 'Bertrandk@.Holala.com',
@.subject = 'Test mail',
@.query =@.foo,@.no_header= 'TRUE',
@.message='some attachement',
@.attach_results = 'TRUE'
This does not work, the spaces are still in the file
Is there something I can do to correct this?
Thanks in anticipation for your help.

Hi,

what about converting your whole message to VARCHAR ? Will that work in your situation, or do you need additional stored information in UNICODE from your columns ?

set @.foo='select top 4 CONVERT(VARCHAR(4000),Replace(priMarykey,Nchar(0020),'')) (...)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||I tried that but it did not help. I would like to know if everyone just uses notepad to open sql attachements or if they do use other text editors, do they also get the same problem that I am getting?|||

No, never has that problem, although dealing with UNICODE data. Could you please post you table definition in here ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Thursday, February 9, 2012

Attach text files into sql server table

Is there a way i can attach multiple text files into a single sql server table. Or what are the options for attaching multiple text files into sql server?

Please advise.

Thanks

You can use

BULK INSERT

bcp

Import Export Wizard

See SQL Server 2005 Books Online topics:

BULK INSERT (Transact-SQL)

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

bcp Utility

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

SQL Server Import and Export Wizard F1 Help

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

|||

There is no way to 'attach' a text file to SQL Server. You can, however, import the data from a text file into a SQL Server table -recognizing that any modifications will NOT be available to that text file, they are only in the server table. You can also export data from a SQL Server table to a text file.

But these are not inconsequential activities.

|||I know how to import single text file data into sql server table, but i have multiple text files. So how can i import all those at once?|||

In 2005 you can use SSIS...It has a loop task inside of which you could put an import task.

You could write a script, vbscript, that looped through a directory and used the sqlcmd utility to import each file.