Help for a real newbie

A problem so simple I'm a little embarrassed to post it... I am setting up a trigger in which I want to, among other things, record the network ID of the user who performs the action that triggers the trigger. I know there is a variable or function out there to pull that information, but I can't find it, and have tried every variation of "login logname usr_id network_id....." I can find.

So first.... could somebody please tell me the variable or function that will return that piece of information. And second, if you also could point me to a good place, either in print or on line, to look up that kind of information, I would be most grateful.

Thanks.

Hello Leslie. The first thing to remember, there is no stupid question (well almost never )

The second thing I will point you to is the Downloadable version of the "BOL" (Books On Line).

It can be found here. http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=SQL+BOL If you are using SQL 2005 then it will be the 4th link down. You should also be able to access this by hitting F1 in your Enterprise Manager or inside of SQL Studio. Then switch over to the Index.

Then remmeber that most variables in SQL that are global either scoped to the whole server or the current connection will usually begin with a @.@.. Typing the @.@. in the index will get you in the near vicinity.

I personally found the @.@.PROCID which

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @.@.PROCID cannot be specified in CLR modules or the in-process data access provider.

and @.@.SPID

Returns the session ID of the current user process.

Try both of those and see if one of those does what you are looking for.

Hope that helps. If you have further questions feel free to ask.

|||

Hi Leslie,

I think you're after the SYSTEM_USER function, which will return the windows login name in the form of ADomain\AUser (if the current user has used a trusted win connection of course).

Cheers,

Rob

HELP - Storing Full Text Catalog to Different Location

Hi,
I am building a database install aplication that among all other
stuff, creates and populates full-text catalogs for my database.
All this is done in VB.net and SQL Server 2003
My application works fine, here is the come sample:
oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.connect(strServer, strUserID, strPassword)
' enable full-text indexing for provided database
oSQLServer.Databases(strDBName).EnableFullTextCata logs()
' create full text catalog and add it to the collection
Dim strFullTextCatalogName As String
strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
oFullTextCatalog.Name = strFullTextCatalogName 'name
oFullTextCatalog.RootPath = strCatalogPath 'location
' create full-text catalog on the server and add it to
' the collection of full-text catalogs
oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
However, I get an System.Runtime.InteropServices.COMException
exception with additional information as:
Additional information:[Microsoft][ODBC SQL Server Driver][SQL
Server]Access is denied to 'C:\Documents and
Settings\username\MyDocuments', or the path is invalid. Full-text
search was not installed properly.
The path to which access is denied is the value of my variable
strCatalogPath that I assign to my full-text catalog RootPath
property. (see code sample above)
How can I fix this error. Please help.
Any help will be appreciated,
_dino_
Dino,
Well, the error message is clear "Access is denied to 'C:\Documents and
Settings\username\MyDocuments', or the path is invalid."
Did you try using sp_fulltext_catalog and create a FT Catalog using the same
path? or perhaps specifically reference 'C:\Program Files\Microsoft SQL
Server\Mssql\Ftdata' enclosed in double quotes? Do the folders "username"
or "MyDocuments" exist?
SQL Server BOL title "sp_fulltext_catalog" for the path states
"[@.path =] 'root_directory'
Is the root directory (not the complete physical path) for a create action.
root_directory is nvarchar(100) and has a default value of NULL, which
indicates the use of the default location specified at setup. This is the
Ftdata subdirectory in the Mssql directory; for example, C:\Program
Files\Microsoft SQL Server\Mssql\Ftdata. The specified root directory must
reside on a drive on the same computer, consist of more than just the drive
letter, and cannot be a relative path. Network drives, removable drives,
floppy disks, and UNC paths are not supported. Full-text catalogs must be
created on a local hard drive associated with an instance of Microsoft SQL
ServerT.
@.path is valid only when action is create. For actions other than create
(stop, rebuild, and so on), @.path must be NULL or omitted."
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:fs05f1lapksvfr4i0lgot578hubm1te3gj@.4ax.com...
> Hi,
> I am building a database install aplication that among all other
> stuff, creates and populates full-text catalogs for my database.
> All this is done in VB.net and SQL Server 2003
> My application works fine, here is the come sample:
> oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.connect(strServer, strUserID, strPassword)
> ' enable full-text indexing for provided database
> oSQLServer.Databases(strDBName).EnableFullTextCata logs()
> ' create full text catalog and add it to the collection
> Dim strFullTextCatalogName As String
> strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
> oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
> oFullTextCatalog.Name = strFullTextCatalogName 'name
> oFullTextCatalog.RootPath = strCatalogPath 'location
>
> ' create full-text catalog on the server and add it to
> ' the collection of full-text catalogs
> oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
> However, I get an System.Runtime.InteropServices.COMException
> exception with additional information as:
> Additional information:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Access is denied to 'C:\Documents and
> Settings\username\MyDocuments', or the path is invalid. Full-text
> search was not installed properly.
> The path to which access is denied is the value of my variable
> strCatalogPath that I assign to my full-text catalog RootPath
> property. (see code sample above)
> How can I fix this error. Please help.
> Any help will be appreciated,
> _dino_
|||Are you utilizing MSDE or SQL Server Express? Full-Text is not installed or
supported on either.
"Dino Buljubasic" wrote:

> Hi,
> I am building a database install aplication that among all other
> stuff, creates and populates full-text catalogs for my database.
> All this is done in VB.net and SQL Server 2003
> My application works fine, here is the come sample:
> oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.connect(strServer, strUserID, strPassword)
> ' enable full-text indexing for provided database
> oSQLServer.Databases(strDBName).EnableFullTextCata logs()
> ' create full text catalog and add it to the collection
> Dim strFullTextCatalogName As String
> strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
> oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
> oFullTextCatalog.Name = strFullTextCatalogName 'name
> oFullTextCatalog.RootPath = strCatalogPath 'location
>
> ' create full-text catalog on the server and add it to
> ' the collection of full-text catalogs
> oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
> However, I get an System.Runtime.InteropServices.COMException
> exception with additional information as:
> Additional information:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Access is denied to 'C:\Documents and
> Settings\username\MyDocuments', or the path is invalid. Full-text
> search was not installed properly.
> The path to which access is denied is the value of my variable
> strCatalogPath that I assign to my full-text catalog RootPath
> property. (see code sample above)
> How can I fix this error. Please help.
> Any help will be appreciated,
> _dino_
>

Help - Contiguous Date Range Query

I have a table that contains (among other things) start and end range datetimes. The ranges can overlap. View these datetimes as segments of coverage on a number line. Rather than returning each line segment individually, I'd like the query to return the start and the end of the contiguous segments. For example, the desired output of the query on the below table would be:

/* Start SQL */

/* Desired output: range_start_datetime range_end_datetime
01/01/2003 01/06/2003
01/08/2003 01/12/2003
*/

create table #ranges (range_start_datetime datetime, range_end_datetime datetime)

/* Range 1: 01/01/2003 to 01/06/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/01/2003', '01/02/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/03/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/06/2003')

/* Gap - no data at 01/07/2003 */

/* Range 2: 01/08/2003 to 01/12/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/08/2003', '01/09/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/09/2003', '01/10/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/10/2003', '01/12/2003')

select * from #ranges
drop table #ranges

/* End SQL */This version will work in Oracle at least:

SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );

RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||This works in Sybase as well. I had to change the first select to distinct when I added a few more test cases, but it works! I'm impressed with your skills.

If you get a moment, could you explain what your query is doing? I didn't even consider a 4-join solution.

Gump

Originally posted by andrewst
This version will work in Oracle at least:

SQL> select range_start_datetime,
2 (
3 select min(range_end_datetime)
4 from ranges r3
5 where not exists
6 ( select range_start_datetime from ranges r4
7 where r4.range_start_datetime <= r3.range_end_datetime
8 and r4.range_end_datetime > r3.range_end_datetime
9 )
10 and r3.range_end_datetime >= r1.range_start_datetime
11 )
12 from ranges r1
13 where not exists
14 ( select range_end_datetime from ranges r2
15 where r2.range_end_datetime >= r1.range_start_datetime
16 and r2.range_start_datetime < r1.range_start_datetime
17 );

RANGE_STAR (SELECTMIN
---- ----
01/01/2003 01/06/2003
01/08/2003 01/12/2003|||Probably easiest to start from here:

select range_start_datetime,
from ranges r1
where not exists
( select range_end_datetime from ranges r2
where r2.range_end_datetime >= r1.range_start_datetime
and r2.range_start_datetime < r1.range_start_datetime
);

All I have done is remove the second column from the main select (which was a "scalar subquery").

This query gets the start of each contiguous range, by finding all the start dates for which there does not exist a preceding record that meets or overlaps it, i.e.:

RANGE_STAR
----
01/01/2003
01/08/2003

Now let's look at the scalar subquery:

select min(range_end_datetime)
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)
and r3.range_end_datetime >= r1.range_start_datetime

This sort of does the opposite of the query above: it finds the end of each contiguous range, i.e. all the end dates for which there does not exist a following record that meets or overlaps it, i.e.

select range_end_datetime
from ranges r3
where not exists
( select range_start_datetime from ranges r4
where r4.range_start_datetime <= r3.range_end_datetime
and r4.range_end_datetime > r3.range_end_datetime
)

RANGE_END_
----
01/06/2003
01/12/2003

It then finds the MIN of those end dates where the end_date is on or after the start date of each record in the main query (i.e. corellated): for start date 01/01/2003 that will be 01/06/2003, and for start date 01/08/2003 that will be 01/12/2003.

Does that make sense?