How to insert record to SQL server which is on the other machine from client computer?

I want to write simple sql database project that that works in the network. and i need to insert record from a client to a server that is on the other machine.

This is a pretty broad question. I suggest you do some additional research online or via any one of a number of excellent books, articles or whitepapers.

This will require that you (at least)

Install and configure the SQL Server on the remote system.

how to insert int variable...

hi,

I have two question

string a;

int i;

insert into sss (id, name) values(.....)

as id is integer and name is nvarchar in the database.

how can I write this variable within the values paranthesis

2) how to adjust a column in sql server 2005 to auto number

thanks

I may be mistaken, but these seem to me to be questions which are not specific to the ADO.Net vNext technology preview--but rather general SQL server questions. Given that assumption, I'm moving this thread to a more appropriate forum. If I'm mistaken and this does more directly apply to the ADO.Net technology preview, please post again back in that forum.|||Do you want to know how to exactly insert them in .NET or TSQL ? TheI Insert should be straight forward using something like

insert into sss (id, name) values(i,a)

You can′t cahnge the id column to a autonumber (in SQL Server it is called identity column), you will have to add a column and assign the identity value while the creation to it. Thats actually what the designer like SSMS does behind the scenes, creating a new table with the ID column, copying all the data from one to the other table, renaming the new one and dropping the old.
You don′t have to care about that if you are using the GUI, its just to keep in mind as the process will take a while.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How to insert a new record (and get it's identity), if a record is not found

What I'm trying to do is write a query (actually it will be a sub-query) that gets the ID of a record using a standard SELECT/WHERE, but if the record is not found then I want to insert a record and return the ID of the inserted record.

So far I've got this (which doesn't work!)

SELECT ISNULL((Select ContactID AS ID FROM [TileManager].[dbo].[Contact] WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs')), (INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs'); SELECT @.@.IDENTITY AS ID))

Any help would be greatly appreciated.

Rob:

Be careful using the @.@.identity function; try SCOPE_IDENTITY() instead. Look it up in the books.

Dave

|||

I don't believe you can embedded the insert statement like that - why aren't you trying something like this

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')
SELECT ID = @.@.IDENTITY

|||

SELECT ID = @.@.identity will almost always work; it can experience problems such as:

-- -
-- Illustrate the difference between @.@.identity and scopy_identity()
--
-- In this example, @.@.identity returns the value of the identity
-- that was inserted by the trigger
-- -
set nocount on

create table dbo.whatA ( what1 integer identity)
go
create table dbo.whatB ( what2 integer identity)
go

insert into whatA default values insert into WhatA default values
insert into whatB default values
insert into whatA default values insert into WhatA default values
insert into whatA default values insert into WhatA default values

--select * from whatA
--select * from whatB
go

create trigger dbo.trI_whatB on whatB
for insert
as
begin
insert into whatA default values
end

go

insert into whatB default values
select @.@.identity as [Incorrect @.@.Identity]
select scope_identity() as [Correct scope_identity()]
select max (what1) as [WhatA Identity] from whatA
select max (what2) as [WhatB Identity] from whatB
go

drop trigger dbo.trI_whatB
go
drop table dbo.whatA
go
drop table dbo.whatB
go

--
-- O U T P U T :
--

-- Incorrect @.@.Identity
-- -
-- 7

-- Correct scope_identity()
-- -
-- 2

-- WhatA Identity
-- --
-- 7

-- WhatB Identity
-- --
-- 2

|||

That's close to what I'm trying to get, but, what I want is the ID of the record if it is found or the ID of the added record if it is not found. The code you've given returns NULL if the record already exists.

Thanks

|||

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')

SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs'

is it acceptable for you to do it as two statements like this?

AWAL

|||

This will do the existance check first and only insert if no record is found...

DECLARE @.ID int

--Get value if existing

SELECT @.ID

FROM [TileManager].[dbo].[Contact]

WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs');

--No value found so insert

IF @.ID IS NULL

BEGIN

INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs');

SELECT @.ID = SCOPE_IDENTITY();

END

--Use ID for something

SELECT @.ID

sql

How to input?

Hi again was just wondering what the best way is too write a script too write a script to record a sale of a workofart? into Sales and WorkOfArt Tables?
Thx :confused:I'm not sure I understand what you want, so we'll start with:INSERT INTO Sales (column_list1) VALUES (column_values1)

INSERT INTO Workofart (column_list2) VALUES (column_values2)I must be missing something, this seems way too easy.

-PatP

How to include the date as part of the log file name...

Hello to all,
I'm using SQL Server agent to perform some schedule jobs.
I have it setup so that it will write to an result output
file (Jobs Steps -> Advanced -> Output file:
The problem is that I would like to name this output file
such as 'result%date%.log' where %date% is the current
date so that I can get a seperate log file each day.
Does anyone know how to do this ? %date% does not work.
Thanks in advance.
KinKin:
The way I have it up is that I do archiving of these output log files every
day as a separate job. In the archiving process I copy the files and add
date to the filename as follows:
declare @.cmd sysname
declare @.var sysname
set @.var = convert(varchar(11),getdate(),112)
--add date to file and archive
set @.cmd = 'copy e:\Logs\DBMaintenance\*.* e:\Logs\Archive_Logs\*' + @.var +
'.txt'
exec master..xp_cmdshell @.cmd
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||I'm not sure that there is a direct way to do that in SQL Agent. You could
always build you're own backup log command and execute that directly. Also,
you could simply let the job name it what it wants, and then add a step that
uses xp_cmdshell to rename the file.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||>--Original Message--
>Hello to all,
>I'm using SQL Server agent to perform some schedule
jobs.
>I have it setup so that it will write to an result output
>file (Jobs Steps -> Advanced -> Output file:
>The problem is that I would like to name this output file
>such as 'result%date%.log' where %date% is the current
>date so that I can get a seperate log file each day.
>Does anyone know how to do this ? %date% does not work.
>Thanks in advance.
>Kin
>.
>

How to implement OR clause in MDX Queries

Hi

How to implement OR clause in MDX Queries so that i can write a query with OR condition on two dimensions.
If possible please post some example.

Regards;
Rakesh

Sets containing either members or tuples are now supported in the WHERE clause of an MDX statement.

Here is an example:

SELECT { [Time].[Calendar].[Quarter].&[2005904] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { DESCENDANTS( [Customer].[Customer].[All Customer], [Customer].[Customer].[Customer] ) }, 10, ( [Time].[Calendar].[Quarter].&[2005904], [Measures].[Sales Amt] ) ) } ON ROWS

FROM [Sales]

WHERE (

{ ([Product].[Family].[Business PCs], [Geography].[City].[Albany] )

,([Product].[Family].[Business PCs], [Geography].[City].[New York] )

,([Product].[Family].[Home PCs], [Geography].[City].[Albany] ) },

[Measures].[Sales Amt] )

The result of this query will be the top 10 customers based on Q4 2005 sales for customers who:

Bought a "Business PC" and live in "Albany"

OR

Bought a "Business PC" and live in "New York"

OR

Bought a "Home PC" and live in "Albany"

HTH,

- Steve

|||

Hi Steve
Thanks for your reply. The solution you provided doesn't work at my end. Am i making any mistake in impletementing the same? Here is the my case:

Let us take an example to illustrate this. Let say we have four tables; Users, UserAccessBranch, Branch and FactLoan. User’s access to particular loan is based on his access to loan’s Branch (BranchID or OperatingBranchID). If user has access to at-least one of the two branches (BranchID and OperatingBranchID) then the loan is accessible to the user.

Users.UserIDà BranchAccess(UserID,BranchID)?Branch.BranchIDàFcatLoan.BranchID

àFactLoan.OperationalBranchID

In case of T-SQL it’s very easy to implement. In case of MDX it’s easy to implement if there is only one column of BranchID is there in FactLoan. I have implemented this case (having one BranchID) by having Many-to-Many relationship in the “Dimension Usage” of cube designer. How can we implement the logic for two Branch access (either Or) in MDX and cube design.

The MDX queries i am trying are:

This works for BranchID check

SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([BranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])

This works for OperatingBranchID check

SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([OperatingBranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])

This DOESN'T works for both

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {(([BranchUsers].[Users].&[1.]),([OperatingBranchUsers].[Users].&[1.]))}

Any suggestion is most welcome. Thanks a lot.

|||

Rakesh,

Try the following:

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].DefaultMember),

([BranchUsers].[Users].DefaultMember,[OperatingBranchUsers].[Users].&[1.])}

The tuples should have the same "signature". So what you are seeing is "BranchUsers = 1 AND OperatingBranchUsers = Any" OR "BranchUsers = Any AND OperatingBranchUsers = 1".

HTH,

Steve

|||

Steve

Thanks for you prompt reply. Sorry, but its giving me following error.

#Error Arbitrary shape is not allowed when its elements cross a reference dimension.

Am I missing something? Is it because i am using Many-to-Many relationship between Fact table and Users dimension?

Please suggest.

Rakesh

|||

Rakesh,

I have not tried this with a Many-to-Many dimension relationship involved and you may have found a bug. I don't think you are missing anything, so you should report this to support and hopefully it is already fixed in SP2.

HTH,

Steve

|||

Yes - this is fixed in SP2 - both for many-to-many and for reference dimensions, and not only for arbitrary shapes in WHERE clause but also for arbitrary shapes in subselects. I briefly mentioned it here: http://www.sql.ru/forum/actualthread.aspx?tid=360352#3400111

HTH,

Mosha (http://www.mosha.com/msolap)

|||

You might like to look at the Analysis Services Stored Procedure Project http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. I built a function called AsymmetricSet to facilitate the easy construction of sets like these. So your query would look like the following.

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE ASSP.AsymmectricSet([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].&[1.])

|||

Hi Mosha

Thanks for your post on the forum.

On SP1 the MDX script gives me the error “#Error Arbitrary shape is not allowed when its elements cross a reference dimension”. Whereas on SP2 it displays the result. So; I think they have made the required changes for referenced dimension or M2M relationships. But I still believe that there is some issue with this kind of query and cube design. The result of the query

SELECT [Measures].[Fact Loan Count] on 0

FROM [SANDBOX]

WHERE {

(([Users].[Users].&[2]),([Operating Users].[Operating Users].DefaultMember)),

(([Users].[Users].DefaultMember),([Operating Users].[Operating Users].&[2]))

}

is not as per expectations. Following is the SQL query:

Select Count(*)

From FactLoan r

Where

( CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.OperatingBranchID and UserID = 2 ) > 0 THEN

1

ELSE

CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.BranchID and UserID = 2 ) > 0 THEN

1

ELSE

0

END

END

)=1

The output of the MDX query doesn’t matches with the SQL query output.

Please help out in understanding the cause for this. Thanks a lot. ?

Thanks & Regards;

Rakesh

|||

Hi Darren

Thanks for your post on the forum.

I have successfully installed the ASSP DLL but the output of the query

SELECT [Measures].[Fact Loan Count] on 0

FROM [SANDBOX]

WHERE [ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.])

doesn’t give the correct result. Am I missing some thing?

Regards,

Rakesh

|||

It's hard to say without knowing what result you are getting and what you were expecting.

You can display the output of the function by running the following query. This should let you validate if the function is generating the same set as the one coded by hand.

WITH Member [Measures].[OrSetStr] AS SetToStr([ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.]))

SELECT [Measures].[OrSetStr] on 0

FROM [SANDBOX]

|||

Rakesh,

I just had a read of your reply to Mosha's post and have the following observations.

If you are using a many-to-many relationship this would imply that one branch can have many users, and that one user can belong to many branches.

Therefore there would have to be a many-to-many "bridge table" which would be set up in the UDM as a measure group which would be used in the many-to-many relationship. In your SQL query the dimension table links straight to the fact table and there does not appear to be any joins to the table (or view) that would be facilitating the many-to-many relationship in the UDM. This may be why you are seeing different results between the MDX and the SQL.

|||

Hey Darren

Thnaks for you time and reply to this question.

Actually I have the "bridge table" in my UDM and that "bridge table"is being used as "fact less" fact table. I have got the correct MDX (that meets my requirement) from Chris. The correct query that matches with my requirement is:

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
( [Operating Branch].[Operating Branch].defaultmember *
exists([Branch].[Branch].[Branch].members, [Users].[Users].&[2], "User Access Branch")
),
( exists([Operating Branch].[Operating Branch].[Operating Branch].members, [Operating Users].[Operating Users].&[2], "Operating Branch Access")
* [Branch].[Branch].defaultmember
)
}

Thanks again for all your support and advice. :)

Regards;
Rakesh

|||

Having seen Rakesh's data model offline this approach won't work: going through the two m2m dimensions he set up it only seemed possible to get an AND and not an OR. Doing an Exists through the two intermediate measure groups that each User dimension was using to join to the main measure group as in my query above, so you found all the Branches each user had a relationship with, was the only thing that worked.

Chris

How to implement OR clause in MDX Queries

Hi

How to implement OR clause in MDX Queries so that i can write a query with OR condition on two dimensions.
If possible please post some example.

Regards;
Rakesh

Sets containing either members or tuples are now supported in the WHERE clause of an MDX statement.

Here is an example:

SELECT { [Time].[Calendar].[Quarter].&[2005904] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { DESCENDANTS( [Customer].[Customer].[All Customer], [Customer].[Customer].[Customer] ) }, 10, ( [Time].[Calendar].[Quarter].&[2005904], [Measures].[Sales Amt] ) ) } ON ROWS

FROM [Sales]

WHERE (

{ ([Product].[Family].[Business PCs], [Geography].[City].[Albany] )

,([Product].[Family].[Business PCs], [Geography].[City].[New York] )

,([Product].[Family].[Home PCs], [Geography].[City].[Albany] ) },

[Measures].[Sales Amt] )

The result of this query will be the top 10 customers based on Q4 2005 sales for customers who:

Bought a "Business PC" and live in "Albany"

OR

Bought a "Business PC" and live in "New York"

OR

Bought a "Home PC" and live in "Albany"

HTH,

- Steve

|||

Hi Steve
Thanks for your reply. The solution you provided doesn't work at my end. Am i making any mistake in impletementing the same? Here is the my case:

Let us take an example to illustrate this. Let say we have four tables; Users, UserAccessBranch, Branch and FactLoan. User’s access to particular loan is based on his access to loan’s Branch (BranchID or OperatingBranchID). If user has access to at-least one of the two branches (BranchID and OperatingBranchID) then the loan is accessible to the user.

Users.UserIDà BranchAccess(UserID,BranchID)?Branch.BranchIDàFcatLoan.BranchID

àFactLoan.OperationalBranchID

In case of T-SQL it’s very easy to implement. In case of MDX it’s easy to implement if there is only one column of BranchID is there in FactLoan. I have implemented this case (having one BranchID) by having Many-to-Many relationship in the “Dimension Usage” of cube designer. How can we implement the logic for two Branch access (either Or) in MDX and cube design.

The MDX queries i am trying are:

This works for BranchID check

SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([BranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])

This works for OperatingBranchID check

SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([OperatingBranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])

This DOESN'T works for both

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {(([BranchUsers].[Users].&[1.]),([OperatingBranchUsers].[Users].&[1.]))}

Any suggestion is most welcome. Thanks a lot.

|||

Rakesh,

Try the following:

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].DefaultMember),

([BranchUsers].[Users].DefaultMember,[OperatingBranchUsers].[Users].&[1.])}

The tuples should have the same "signature". So what you are seeing is "BranchUsers = 1 AND OperatingBranchUsers = Any" OR "BranchUsers = Any AND OperatingBranchUsers = 1".

HTH,

Steve

|||

Steve

Thanks for you prompt reply. Sorry, but its giving me following error.

#Error Arbitrary shape is not allowed when its elements cross a reference dimension.

Am I missing something? Is it because i am using Many-to-Many relationship between Fact table and Users dimension?

Please suggest.

Rakesh

|||

Rakesh,

I have not tried this with a Many-to-Many dimension relationship involved and you may have found a bug. I don't think you are missing anything, so you should report this to support and hopefully it is already fixed in SP2.

HTH,

Steve

|||

Yes - this is fixed in SP2 - both for many-to-many and for reference dimensions, and not only for arbitrary shapes in WHERE clause but also for arbitrary shapes in subselects. I briefly mentioned it here: http://www.sql.ru/forum/actualthread.aspx?tid=360352#3400111

HTH,

Mosha (http://www.mosha.com/msolap)

|||

You might like to look at the Analysis Services Stored Procedure Project http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. I built a function called AsymmetricSet to facilitate the easy construction of sets like these. So your query would look like the following.

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE ASSP.AsymmectricSet([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].&[1.])

|||

Hi Mosha

Thanks for your post on the forum.

On SP1 the MDX script gives me the error “#Error Arbitrary shape is not allowed when its elements cross a reference dimension”. Whereas on SP2 it displays the result. So; I think they have made the required changes for referenced dimension or M2M relationships. But I still believe that there is some issue with this kind of query and cube design. The result of the query

SELECT [Measures].[Fact Loan Count] on 0

FROM [SANDBOX]

WHERE {

(([Users].[Users].&[2]),([Operating Users].[Operating Users].DefaultMember)),

(([Users].[Users].DefaultMember),([Operating Users].[Operating Users].&[2]))

}

is not as per expectations. Following is the SQL query:

Select Count(*)

From FactLoan r

Where

( CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.OperatingBranchID and UserID = 2 ) > 0 THEN

1

ELSE

CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.BranchID and UserID = 2 ) > 0 THEN

1

ELSE

0

END

END

)=1

The output of the MDX query doesn’t matches with the SQL query output.

Please help out in understanding the cause for this. Thanks a lot. ?

Thanks & Regards;

Rakesh

|||

Hi Darren

Thanks for your post on the forum.

I have successfully installed the ASSP DLL but the output of the query

SELECT [Measures].[Fact Loan Count] on 0

FROM [SANDBOX]

WHERE [ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.])

doesn’t give the correct result. Am I missing some thing?

Regards,

Rakesh

|||

It's hard to say without knowing what result you are getting and what you were expecting.

You can display the output of the function by running the following query. This should let you validate if the function is generating the same set as the one coded by hand.

WITH Member [Measures].[OrSetStr] AS SetToStr([ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.]))

SELECT [Measures].[OrSetStr] on 0

FROM [SANDBOX]

|||

Rakesh,

I just had a read of your reply to Mosha's post and have the following observations.

If you are using a many-to-many relationship this would imply that one branch can have many users, and that one user can belong to many branches.

Therefore there would have to be a many-to-many "bridge table" which would be set up in the UDM as a measure group which would be used in the many-to-many relationship. In your SQL query the dimension table links straight to the fact table and there does not appear to be any joins to the table (or view) that would be facilitating the many-to-many relationship in the UDM. This may be why you are seeing different results between the MDX and the SQL.

|||

Hey Darren

Thnaks for you time and reply to this question.

Actually I have the "bridge table" in my UDM and that "bridge table"is being used as "fact less" fact table. I have got the correct MDX (that meets my requirement) from Chris. The correct query that matches with my requirement is:

SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
( [Operating Branch].[Operating Branch].defaultmember *
exists([Branch].[Branch].[Branch].members, [Users].[Users].&[2], "User Access Branch")
),
( exists([Operating Branch].[Operating Branch].[Operating Branch].members, [Operating Users].[Operating Users].&[2], "Operating Branch Access")
* [Branch].[Branch].defaultmember
)
}

Thanks again for all your support and advice. :)

Regards;
Rakesh

|||

Having seen Rakesh's data model offline this approach won't work: going through the two m2m dimensions he set up it only seemed possible to get an AND and not an OR. Doing an Exists through the two intermediate measure groups that each User dimension was using to join to the main measure group as in my query above, so you found all the Branches each user had a relationship with, was the only thing that worked.

Chris

How to ignore sp_runwebtask errors?

Hi,
I have some tables in my database that, upon modification, write a file
to disk with data. I have create the triggers that handle this, using
the sp_makewebtask stored procedure.
For these triggers to work, I needed to login from my application with
integrated security. This is not a problem and works fine.
Sometimes I also need to do some maintenance on the tables, and for
this purpose I cannot use integrated security (because I connect
through vpn an am not on the actual machine). When i then change
something on the tables I get an error: "SQL Web Assistant: Could not
open the output file".
The fact that if I'm doing maintenance, the file is not created, is not
a problem, so I hope that one of the following solutions is possible:
- Build error handling into the trigger that simply ignores the error
- Detect in the trigger that the current user is not a windows user,
and then skip the creation of the file
- something else.
What could be a solution for my problem?
BTW We are using SQL 2000 on Windows 2003 Server.
Regards, FelixFelix,

> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
You can use SUSER_SNAME() to determine this.
IF SUSER_SNAME() = 'MySQLServerLogin' ...
Robert
"felix planjer" <fplanjer@.gmail.com> wrote in message
news:1143541278.508476.286150@.e56g2000cwe.googlegroups.com...
> Hi,
> I have some tables in my database that, upon modification, write a file
> to disk with data. I have create the triggers that handle this, using
> the sp_makewebtask stored procedure.
> For these triggers to work, I needed to login from my application with
> integrated security. This is not a problem and works fine.
> Sometimes I also need to do some maintenance on the tables, and for
> this purpose I cannot use integrated security (because I connect
> through vpn an am not on the actual machine). When i then change
> something on the tables I get an error: "SQL Web Assistant: Could not
> open the output file".
> The fact that if I'm doing maintenance, the file is not created, is not
> a problem, so I hope that one of the following solutions is possible:
> - Build error handling into the trigger that simply ignores the error
> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
> - something else.
> What could be a solution for my problem?
> BTW We are using SQL 2000 on Windows 2003 Server.
> Regards, Felix
>