ActiveX and SQL Server question?

Apologies for posting this in the wrong section, now pointed to here.

I have the following

AciveX script set up as a DTS package which I would like to alter so

that the output is sent to another server. :-

Function Main()
dim oCmd, sSql, oDom
set oDom = CreateObject("Msxml2.DOMDocument")
set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=MyServer; Initial Catalog=MyDB; Integrated Security=sspi"

sSql = "<ROOT

xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT

* FROM VW_Projs1 FOR XML AUTO</sql:query></ROOT>"
oCmd.CommandText = sSql
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute, , 1024
oDom.Save "C:\Inetpub\wwwroot\test.xml"
Main = DTSTaskExecResult_Success
End Function

I want to schedule the package so it runs overnight, so
1.

Do I have to change the 'Integrated Security=sspi' part of the script

to reflect a user who has suitable rights to create/write a file on the

other server? If so How?
2. Would I redirect to the another

server/directory just by changing the path in oDom.Save to

"ServerName\DirectoryName".? or would there be a different format?

Thanks

I don't have much exp. in ActiveScript but I can say that if you are creating a flat file on a different server apart from the local server then you need to define the path correctly \\servername\path in order to create the file. Also ensure to check the SQLAgent permissions on the specified path as you're going to schedule this as a job.

Also you might try creating the file locally on the server and then copy the same using copy command in SQL scheduled job.

accuracy vs generalization

Hi!

So sorry for posting yet another one of my silly posts about the correct way of doing things!

I am using Stored Procedures to do various things in my database (inserting/deleting/updating) and using SQL Server 2000 SP4

The current situation is that I have a stored procedure which checks certain user details and returns either -1 or 1 as a success indicator to the caller.

Now, obviously if we executed several queries in this stored procedure, performance will be an issue.

However, I have no idea how to balance up the whole "more useful information" vs "general information". What I mean is this:

if we are checking 3 items in a table, I want to return the success value back to the caller - in this case, we could do an IF statement to see if the record exists, if it does, set the return value to 1, else, -1.

But then you may want to be more informative to the user, specifically stating what part of the information they entered is incorrect/invalid. So having this in mind, we would then need to execute, say, 3 queries to return a more specific "error" value.

What should I do in this case?

I want to check the username, password and if the account is activated.

Currently I have this query going on:

IF EXISTS (SELECT [ID] FROM Users WHERE username = @.un AND [password] = @.pw AND activate = 1)

SET @.theResult = 1

ELSE

SET @.theResult = -1

firstly, is that the good way of doing things? if not - then what is the better way?

secondly, if we decide that we want a specific detailed return value/error message, it would mean I have to check the results I want using a couple more queries, such as one query to check username, one query to check password and another to check if the account is active or not and then return the appropriate value back to the caller.

is this a good way of doing things? I am confused and stuck!

Many thanks for your valuable response :)

>>Now, obviously if we executed several queries in this stored procedure, performance will be an issue.<<

This isn't necessarily true, but it is always better to minimize the number of queries.

Take this query:

IF EXISTS (SELECT [ID] FROM Users WHERE username = @.un AND [password] = @.pw AND activate = 1)

SET @.theResult = 1

ELSE

SET @.theResult = -1

A better way to write this might be:

select ID, case when activate = 0 then 'inactive' else 'active' end as activeStatus,
case when password = @.pw then 'correct' else 'incorrect' end as pwCheck
from users
where username = @.un

Then, the caller can interpret the details as they see fit:

No result set - invalid user name, the other two possibilities are obvious.

|||Many thanks!

Accessing SQL Server database from Crystal Reports - being prompted to log on every time?

Hello, this is my first time posting here.
I've never really worked with SQL Server much, but I'm in the process of learning. Please forgive my ignorance.
Anyways, here's my dilemma.
A user at the office uses Crystal Reports (I'm not too familiar with this program either). Crystal Reports pulls data from SQL Server. However, every single time it does so, it prompts her to log into the database.
While there is no serious problem, this has gotten very annoying. She wants to be able to work without being prompted to log on to the database every single time Crystal Reports retrieves data.
How would I go about doing this? The office has no real security issues, so I'm open to "less-than-perfectly-secure" options as well.

I assume that by the log on prompt, you mean a request to enter a password. If that's the case, then your database application is using SQL Authentication. If Crystal Reports does not provide a "remember the password" option, you have to re-enter it each time. If you could use Windows Authentication, the need for entering a password would go away, but this would require changing the application.

Thanks
Laurentiu

Accessing reports through URL

Hello, All !!

I'm now getting problems with access reports through URL.
The situation is:
I'm posting the form to the frame, action attribute's URL includes
querystring.
Also I have some inputs in the form.
I'm using method=post.

Problem is that Report Server ignores parameters from query of the action.

I think it should work with them but it doesn't.

If anyone knows what's the matter here, please !

Thank you,
Alexander Yaremchuk

What version of the SQL RS are you using. The latest SQL 2005 CTP drop had this bug, which has been fixed since then.
Thanks
Tudor