ActiveX safe for scripting

Can someone help

I create a activeX with c# .net 2005, and need that it be "safe for scripting", but I don't have examples for do it

help me please

thanks

What does it have to with SQL Server ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

ActiveDirectory Group Security

Hi all

What i want to do - Execute a Stored Procedure when a user log on that is in aActiveDirectory Group.

I want to create a storedprocedure that will be executed when a windows user log on that is part of a specific ADGroup.

I was able to create the ADGroup and add it to logins. I was able to create the procedure with the ADGroup as owner.

The problem is when the user log on, he is not seen as part of the group that has rights on the DB.

Please help

From your description, I am assuming that you are having trouble to access a database (different than master) where your SP resides, correct?

If this is the case, probably what happened is that the AD group in that DB doesn’t have access to it. This is typically the case when users are created implicitly. You can try the following:

USE [<db_name>]

go

GRANT CONNECT TO [<ADGroup_name>]

go

BTW. If you only want the AD group to be able to execute the SP, you don’t need to make them owners, granting EXECUTE on the SP should be sufficient.

I hope this information helps, let us know if this solved your problem.

- Raul Garcia

SDE/T

SQL Server ENgine

Active/Passive Production with Test Instance

I am trying to setup a SQL cluster with the an
active/passive cluster for production. On the passive
node, I have been told to create a separate disk and
install a developers edition of SQL to use as a test
environment. The developers edition will be a named
instance of SQL to help isolate it from production should
a failover occur.
Does anyone else out there have this type of
configuration in their environment? Have you had any
problems or does it work fine? My gut is not real
comfortable with this, even though we can make it work in
a test lab. I would appreciate any other feed back to
make me feel better, or help me build the case to do
otherwise.
Thanks
Dan,
Reading your description, it appears that you want a production clustered SQL Server instance on one node (that you call active) and another test clustered SQL Server instance on second node (that you called
passive). This will work but then it is not really active/passive, it will be active/active configruation. Basically you are planning to install two clustered SQL Server instances on a two node cluster and each instance
running on seperate nodes. There is no issue in doing this except that you will need make sure that at any time the resources on a single node is enough for both the SQL Server instances.
SQL Server 2000 Failover Clustering is a High Availablity solution. Hence, one would not want to have production and test instances on the same cluster. I would not recommend having prod and test on same cluster
and I have not seen this. I have seen many clusters with two instances of SQL (infact 16 instances are supported) on the same cluster but all the instances are for production and they are configured such that even if
one node fails the other node can run all the instances without any issues.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||
>--Original Message--
>Dan,
>Reading your description, it appears that you want a
production clustered SQL Server instance on one node
(that you call active) and another test clustered SQL
Server instance on second node (that you called
>passive).
Actually, the test instance of SQL would not be
clustered, but exist solely on the second box. We do not
plan any failover for the test instance.
This will work but then it is not really active/passive,
it will be active/active configruation. Basically you are
planning to install two clustered SQL Server instances on
a two node cluster and each instance
>running on seperate nodes. There is no issue in doing
this except that you will need make sure that at any time
the resources on a single node is enough for both the SQL
Server instances.
>SQL Server 2000 Failover Clustering is a High
Availablity solution. Hence, one would not want to have
production and test instances on the same cluster. I
would not recommend having prod and test on same cluster
>and I have not seen this. I have seen many clusters with
two instances of SQL (infact 16 instances are supported)
on the same cluster but all the instances are for
production and they are configured such that even if
>one node fails the other node can run all the instances
without any issues.
>HTH,
>Best Regards,
>Uttam Parui
>Microsoft Corporation
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
http://www.microsoft.com/security.
>Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites:
>http://www.microsoft.com/protect
>http://www.microsoft.com/security/guidance/default.mspx
>
>.
>
|||Even if the second instance of SQL Server is standalone and not clustered, I would not recommend installing it on a production cluster. The passive node is like
Sometimes there is the notion especially in a single-instance SQL Serve cluster (active/passove) that the unused node (passive node) is being wasted. However, the passive node serve as an insurance policy in
the event of a failure. If you start utilizing the wasted node for something else, what will happen in a failover? Will SQL Server have enough resources to allocate to it?
I will give an example. Few yrs ago, one of my customers who had a SQL Server 7.0 active/passive production cluster thought that the passive node was being wasted and they installed a TEST SQL Server 2000
standalone instance on the passive node. What they didn't realize is that MDAC 2.6 got installed with SQL 2K and MDAC 2.6 can break SQL 7.0 cluster. So, unknowingly they affected their Highly Available
solution.Thats the reason, you don't want to do any test/dev stuff on your prod cluster. Ideally, you should have an identical dev/test cluster where you do all your development and testing and after getting successful
results, do that on the prodcution cluster.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Active/Passive Failover or Database Mirror without a Shared disk?

This is not my expertise, so would appreciate some help.
Is it possible to create an active/passive failover setup or database
mirroring without a shared disk? Client has two sql servers that they
wanted to upgrade SQL 2000 to SQL 2005 and then use the second box for an
active/passive configuration. They do not have a shared disk which I am
being told is necessary for this.
Unfortunately, I do not have the expertise on this and am reaching out to
the gurus here.
Also if any of the SQL experts in this group want some consulting work in
NYC for this job, please contact me off the list.
Lawrence Abrams
MVP - Windows Security
http://www.bleepingcomputer.com
Thank you very much for the info. Starting to make a bit more sense? I
assume for the mirror and the witness they would need to be fully licensed?
Also does the witness need to be as powerful as the actual DB servers?
If you know of any SQL MVPs in the NY area, please have them contact me if
they are interested in possibly doing consulting for this setup.
-L
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efiHUMteHHA.5052@.TK2MSFTNGP06.phx.gbl...
> With no shared storage you rule out clustering but Database Mirroring will
> be fine.A couple of whitepapers to help get your head around DB mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx
>
|||The witness needs a license, but the mirror does not require a SQL license,
provided it is used for failover only. The witness can be a workstation
grade system since all it is doing is observing.
NOTE: Always verify licensing for your configuration with your Microsoft
rep.
While I am not based on the NY area, the company I work for does have a NY
office and would be happy to quote on this project. Email me directly and I
will put you in touch with one of our Business Directors. My posting
address is a valid email address.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Lawrence Abrams" <grinler-REMOVETHIS@.bleepingcomputer.com> wrote in message
news:%23l0LVXteHHA.4604@.TK2MSFTNGP06.phx.gbl...
> Thank you very much for the info. Starting to make a bit more sense? I
> assume for the mirror and the witness they would need to be fully
> licensed? Also does the witness need to be as powerful as the actual DB
> servers?
> If you know of any SQL MVPs in the NY area, please have them contact me if
> they are interested in possibly doing consulting for this setup.
> -L
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:efiHUMteHHA.5052@.TK2MSFTNGP06.phx.gbl...
>

Active X Error

I get this error msg when I run my ActiveX script in a DTS package.

Err number: 429
Err Message: ActiveX component can't create object

When I Set crApplication = CreateObject("CrystalRuntime.Application.9")

if Err.Number <>0 then
'I get the message here

ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative accessdo you have crystal object library installed on the box?|||Hi there,

I assume you have installed Crystal on your box! So, I can imagine two reasons:

1. No file type is associated with this application (had this kind of problem recently with Cognos Impromptu!) and the system doesn't "know" CrystalRuntime.Application.9.

2. Is CreateObject("CrystalRuntime.Application.9") the correct syntax? Is the ".9" correct there?

Greetings,
Carsten

Originally posted by vmlal
I get this error msg when I run my ActiveX script in a DTS package.

Err number: 429
Err Message: ActiveX component can't create object

When I Set crApplication = CreateObject("CrystalRuntime.Application.9")

if Err.Number <>0 then
'I get the message here

ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative access|||hm, i don't think file association is required if the object library is properly registerd with all dependent components (i've seen dll's that cannot be registered without ocx's being registered first, etc.)|||Originally posted by ms_sql_dba
hm, i don't think file association is required if the object library is properly registerd with all dependent components (i've seen dll's that cannot be registered without ocx's being registered first, etc.)

If thats not the case where could i start resovling this issue? Any starting points? thanks.|||you need to make sure that you know exactly what components have been loaded on the server and that you have the required dll (-s) present. if the installation was done through windows installer you will see some footprint of it in controll panel/add/remove programs.sql

Active Directory User's Member Groups from SQL Server 2000

I can create a Linked Server for ADSI sucessfully but cannot retrieve group
membership for an indivdual user.
I've seen some examples of syntax but can't get it to work.
Does anyone have an example of some code I could use
LindaOLE DB provider for Directory Services does not support multi-valued
attributes such as: members and memberOf. sorry...
"Linda Lou" wrote:

> I can create a Linked Server for ADSI sucessfully but cannot retrieve grou
p
> membership for an indivdual user.
> I've seen some examples of syntax but can't get it to work.
> Does anyone have an example of some code I could use
> Linda

Active directory query 1000 page size limitaion

Hi.

We need to create a view of our active directory users (we have 2500).

I found out that there is max page size of 1000, so we cannot get more
data.

Anyone found a solution to that problem?

ThanksI don't really understand what your question has to do with MSSQL - are
you talking about Reporting Services, perhaps? If so, you can try
microsoft.public.sqlserver.reportingsvcs to see if you get a better
response.

Simon

Active Directory as linked Server in SQL

I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallogin = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why?
Hi,
From your descriptions, I understood that you meet the error [Prepare
returned 0x80040e14] when you are using linked server to Active Directory.
Have I understood you? If there is anything I misunderstood, plesae feel
free to let me know
Based on my knowledge, there should be some authority issue with this kind
of error. Would you please have a try on following steps?
1.
try to create a new linked server as follows
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
Will you get the same error?
2.
try to add SQL Server to Active Directory (Right click Server in SQL Server
Enterprise Manager -> properties -> Active Directory)
Could you make it successfully? If not, what kind of error do you meet?
3.
Checking Start-up account of SQL Server.
Step One: typing "services.msc" (without quotation marks) in Start -> Run
Step Two: right click 'MSSQLServer' or 'MSSQLServer$InstanceName',
according to the SQL Server you are using -> Properties -> Log On. If you
are using a Local System Account, change it to Domain Account
try to see whether you could successfully add it and make the query
Hope this helps and please feel free to post in the group if this solves
your problem or if you would like further help. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I have a very similar problem. I have double checked that:
First ran from a Member Server running SQL 2000 sp3
SQL Service is running as a domain user (even a domain admin)
SQL Server is registered in Active Directory
Created the linked server as suggested, and also tried creating using
enterprise manager
Tried various uses of sp_addlinkedsrvlogin as well as the security
dialog of the linked server in EM to make sure I have proper credentials
Tried running from the Domain Controller itself on SQL 7.0
updated MDAC to 2.8
When I run an OpenQuery in QA, I get a message like:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject'
ICommandPrepare::Prepare returned 0x80040e14
When I try to browse around in EM under the linked server, I get a
message dialog like:
Could not obtain a required interface from OLE DB provider
'ADSDSOObject'. OLE DB error trace[OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002: IDBSchemaRowset].
Any ideas? Perhaps I need to change the AD configuration, or I
overlooked something with the authentication?
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Active Directory as linked Server in SQL

I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource
g
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallogin = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"'
I keep getting this error no matter what I try
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why?Hi,
From your descriptions, I understood that you meet the error [Prepare
returned 0x80040e14] when you are using linked server to Active Directory.
Have I understood you? If there is anything I misunderstood, plesae feel
free to let me know :)
Based on my knowledge, there should be some authority issue with this kind
of error. Would you please have a try on following steps?
1.
try to create a new linked server as follows
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
Will you get the same error?
2.
try to add SQL Server to Active Directory (Right click Server in SQL Server
Enterprise Manager -> properties -> Active Directory)
Could you make it successfully? If not, what kind of error do you meet?
3.
Checking Start-up account of SQL Server.
Step One: typing "services.msc" (without quotation marks) in Start -> Run
Step Two: right click 'MSSQLServer' or 'MSSQLServer$InstanceName',
according to the SQL Server you are using -> Properties -> Log On. If you
are using a Local System Account, change it to Domain Account
try to see whether you could successfully add it and make the query :)
Hope this helps and please feel free to post in the group if this solves
your problem or if you would like further help. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Active Directory as linked Server in SQL

I want to create a view in SQL populated with users from our Active Director
y. I have learnt that this can be done using linked server. I have tried usi
ng the following:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject',
'adsdatasource'
go
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallog
in = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiA
o6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provi
der 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are returned 0x80040e14].
Any ideas why'Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I have a very similar problem. I have double checked that:
First ran from a Member Server running SQL 2000 sp3
SQL Service is running as a domain user (even a domain admin)
SQL Server is registered in Active Directory
Created the linked server as suggested, and also tried creating using
enterprise manager
Tried various uses of sp_addlinkedsrvlogin as well as the security
dialog of the linked server in EM to make sure I have proper credentials
Tried running from the Domain Controller itself on SQL 7.0
updated MDAC to 2.8
When I run an OpenQuery in QA, I get a message like:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject'
ICommandPrepare::Prepare returned 0x80040e14
When I try to browse around in EM under the linked server, I get a
message dialog like:
Could not obtain a required interface from OLE DB provider
'ADSDSOObject'. OLE DB error trace[OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002: IDBSchemaRowset].
Any ideas? Perhaps I need to change the AD configuration, or I
overlooked something with the authentication?
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Active Directory and SSRS

Is it possible to create a report in SSRS that queries Active Directory data such as user's phone extension, email address etc

What would be a good way to do this?

Thanks,

Nisha

I haven't done this but probably the easiest way will be to use the OLE DB Provider for Microsoft Directory Services. When setting up the data source, choose the OLE DB option and then click the Edit button which will bring you to all OLE DB providers installed.|||

Thank You so much for pointing me in the right direction. I was able to do what I needed.

NB

Action Queries

Two questions.

1. How do I create action queries in SQL Server? From Enterprise Manager -> view I can create, run but NOT save the query.

2. How do I access an SQL server query from access like i would an Access query? Do i have to make a call to a stored procedure or something like that?If you like to reuse a query you can create it in Query Analyzer and then save it to a file. Using Query Analyzer you can then open the file and run the query. You can use any text editor to create a query and save it. Also by using a stored procedure you are saving the SQL to the database so it can be used at any time just by calling it. Depending on how you setup permission you can let other users call your stored procedure.|||I'm a little new at this so bear with me... OK.. I follow you up to the point of the stored procedure. Say i save that file as Test.. Is test.sql the stored procedure? I thought stored procedures were only available inside Enterprise manager. Lastly Once the file containing the sql statement is created, how do i reference the file from enterprise manager? i can figure out how to call a stored procedure in the enterprise manager from Access VBA code.|||Access_Dude,
Action Queries are a bit different in SQL Server to Access. In access, it all gets saved down in the database. In SQL Server, you've got a choice: Either save it down as a .sql file (in effect a text file - rename the extension to .txt, will open it in Notepad, no problems). This allows you to have a permanent copy which you can open from other apps without needing SQL Server - useful for saving down scripts which you want to run again but don't want to keep in the database.
Or, create a Stored Procedure in SQL Server. This will save the SQL into the database itself. As an example:

select Name, Postcode
from Customer
where CustomerID = 123

... can become a stored proc ('action query')...

create proc spGetCustomerNameAndPostcode
as
select Name, Postcode
from Customer
where CustomerID = 123

... it can then be simply called from an app, or from within SQL Server, using:
exec spGetCustomerNameAndPostcode

(Note that the exec isn't always needed, depends on what you're doing)

Hope this helps :)
Jon.|||Access_Dude,
Action Queries are a bit different in SQL Server to Access. In access, it all gets saved down in the database. In SQL Server, you've got a choice: Either save it down as a .sql file (in effect a text file - rename the extension to .txt, will open it in Notepad, no problems). This allows you to have a permanent copy which you can open from other apps without needing SQL Server - useful for saving down scripts which you want to run again but don't want to keep in the database.
Or, create a Stored Procedure in SQL Server. This will save the SQL into the database itself. As an example:

select Name, Postcode
from Customer
where CustomerID = 123

... can become a stored proc ('action query')...

create proc spGetCustomerNameAndPostcode
as
select Name, Postcode
from Customer
where CustomerID = 123

... it can then be simply called from an app, or from within SQL Server, using:
exec spGetCustomerNameAndPostcode

(Note that the exec isn't always needed, depends on what you're doing)

Hope this helps :)
Jon.

acess I/O File

Is it possible to access a file (for example, to write in a text file)
with transact-sql?.
I've create a stored procedure and I'd like to write in a log file
some traces depends on the result of the statements executed in this
stored procedure.
ThanksHere's one method:

ALTER PROC WriteToLog
@.Message varchar(100)
AS
DECLARE @.Command varchar(255)
BEGIN TRAN
-- ensure only one process writes to the log at a time
EXEC sp_getapplock
@.Resource = 'MyLog.txt',
@.lockmode = 'Exclusive'
SET @.Command = 'ECHO ' +
@.Message +
' >>"C:\MyLog.txt"'
EXEC master..xp_cmdshell @.Command, NO_OUTPUT
EXEC sp_releaseapplock @.Resource = 'MyLog.txt'
COMMIT
GO

EXEC WriteToLog 'Test message'

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Bego?a" <bego_colomer@.yahoo.es> wrote in message
news:c04c2a85.0311180409.4631ec98@.posting.google.c om...
> Is it possible to access a file (for example, to write in a text file)
> with transact-sql?.
> I've create a stored procedure and I'd like to write in a log file
> some traces depends on the result of the statements executed in this
> stored procedure.
> Thanks

Accumulating Rolling Total

I'm trying to create an accumulating field based on a set of records. I need to fill in daily amount balances that accumulates on a daily basis. But I can't seem to figure out how to create a total for the daily dates and have it add on additional amounts if needed.

Here's some sample data:

5 6 20 1 200.00 5/5/20000
5 6 20 1 -149.00 5/8/2000

5 6 20 1 100.00 5/10/2000

Now I already have a table with the dates created via a stored procedure. I have a set of dates from 5/5/2000 to 5/8/2000. So that results set should look like this:

5 6 20 1 200.00 5/5/2000

5 6 20 1 200.00 5/6/20000
5 6 20 1 200.00 5/7/2000

5 6 20 1 51.00 5/8/2000

5 6 20 1 51.00 5/9/2000

5 6 20 1 151.00 5/10/2000

....

I'm trying to creating a rolling sum that accumulates the amount field for each daily record and if a new amount is listed, then roll that amount into the total. If you have any suggestions about how to perform this rolling total via TSQL or SSIS, I would greatly appreciate it.

Thanks

Greg

Its complicated but (I think) achievable. You'll probably need a list of all contiguous dates to start with. Then join that list to your balances data as shown above. You will need to join on all days from the balances data that are less than or equal to the date in the list of dates. Then do a sum of all balances grouping by all the dates in the list of dates.

Its alot easier to achieve than it is to explain believe me

-Jamie

Oh P.S. I'm not sure you'll be able to do this in SSIS because MERGE JOIN doesn't support non-equi joins. Yet.

Account problem when creating a new publication

I've got SQL Server Developer installed on my laptop. I'm trying to get
merge replication to work, but when I try to create a new publisher, when I
select the option to make SQL Server on my laptop its own distributor, I
then get an error msg. saying I've chosen a local system account, and
replication will not work. It then sends me to a publications properties
form to select a new account. I have no other accounts, this is just run
from my laptop. Can some kind soul help me and tell me what I need to do
to get this working?
My end result is to be able to get merge replication set up so I can sync.
with a handheld device using SQL Server 2000 CE.
Thanks in advance for any assistance.
The account that SQL Server and the Agent need to run in something other
than a Local account.
"DaveM" <nosebop@.yahoo.com> wrote in message
news:OFNJHikLFHA.2824@.TK2MSFTNGP10.phx.gbl...
> I've got SQL Server Developer installed on my laptop. I'm trying to get
> merge replication to work, but when I try to create a new publisher, when
I
> select the option to make SQL Server on my laptop its own distributor, I
> then get an error msg. saying I've chosen a local system account, and
> replication will not work. It then sends me to a publications properties
> form to select a new account. I have no other accounts, this is just
run
> from my laptop. Can some kind soul help me and tell me what I need to do
> to get this working?
> My end result is to be able to get merge replication set up so I can sync.
> with a handheld device using SQL Server 2000 CE.
> Thanks in advance for any assistance.
>
>

Access-SQL Serv Autonumber

I have an Access database that was upsized. Access F/E, S2k back. I cannot
update tables that I had to create an index for the upsize. All my queries
come back with "cannot insert null" into index field. Access calls it
Autonumber and we never have to do anything else.
I'm gusssing there is a constraint problem ? Where should I start looking.
Its a primary key field, unique, indenty=1. I thought that defined it as
Autonumber.
Help !You need to set the datatype on the SQL Server to Int. Then check the
checkbox that says 'Identity' field in the Manage indexes dialog.
"Stuart" wrote:

> I have an Access database that was upsized. Access F/E, S2k back. I cannot
> update tables that I had to create an index for the upsize. All my queries
> come back with "cannot insert null" into index field. Access calls it
> Autonumber and we never have to do anything else.
> I'm gusssing there is a constraint problem ? Where should I start looking.
> Its a primary key field, unique, indenty=1. I thought that defined it as
> Autonumber.
> Help !

Access-SQL Serv Autonumber

I have an Access database that was upsized. Access F/E, S2k back. I cannot
update tables that I had to create an index for the upsize. All my queries
come back with "cannot insert null" into index field. Access calls it
Autonumber and we never have to do anything else.
I'm gusssing there is a constraint problem ? Where should I start looking.
Its a primary key field, unique, indenty=1. I thought that defined it as
Autonumber.
Help !
You need to set the datatype on the SQL Server to Int. Then check the
checkbox that says 'Identity' field in the Manage indexes dialog.
"Stuart" wrote:

> I have an Access database that was upsized. Access F/E, S2k back. I cannot
> update tables that I had to create an index for the upsize. All my queries
> come back with "cannot insert null" into index field. Access calls it
> Autonumber and we never have to do anything else.
> I'm gusssing there is a constraint problem ? Where should I start looking.
> Its a primary key field, unique, indenty=1. I thought that defined it as
> Autonumber.
> Help !

Accessing to another server

Hello there
I have two databases, on two diffrent servers, connected to the same
network.
In order to use them both I:
1. create registry to both servers on the same enterprize manater
2. Set them as remote server on two sides.
Now when i'm trying to access to one server when connecting to other server,
it being connected as Guest. And i would like to access to another server as
Admin, or other user.
How can i do that?Roy
Have you read about Linked Servers?
"roy goldhammer" <roy@.hotmail.com> wrote in message
news:eOinwRG%23FHA.740@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I have two databases, on two diffrent servers, connected to the same
> network.
> In order to use them both I:
> 1. create registry to both servers on the same enterprize manater
> 2. Set them as remote server on two sides.
> Now when i'm trying to access to one server when connecting to other
> server, it being connected as Guest. And i would like to access to another
> server as Admin, or other user.
> How can i do that?
>|||Is there diffrenct between linked server and remote server?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFMRt3J%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
> Roy
> Have you read about Linked Servers?
>
> "roy goldhammer" <roy@.hotmail.com> wrote in message
> news:eOinwRG%23FHA.740@.TK2MSFTNGP11.phx.gbl...
>|||Roy
By creating Linked Server you will be able to query this (remote)
server like SELECT <> FROM Servername.DataBase.dbo.Table WHERE...
By registering a remote server via EM you can olny view a data if you have
an appropriate permissions
One thing I'd like to mention is you can use OPENROWSET command to query
remote server without creating linked server on it.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eri9hfL%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Is there diffrenct between linked server and remote server?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OFMRt3J%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Accessing tables in another server!

hi this might seem simple to all the techies out there but pls bear with me cos i am a novice to SQL Server 2000.

Q: Is it possible to create a trigger on a table in a current server(For eg. Server1) that can select/insert/delete/update a table in ANOTHER SERVER(Server2)?

I was exploring options of using a distributed partitioned view but i am still very much lost...Use a linked server. See BOL for details.|||If i used a linked server is a distributed partitioned view still necessary? can i just use a four-part-name in my queries to modify data? eg. servername.dbname.dbo.tablename|||Can someone just list out the steps briefly for me? Or just tell me if i am right..

1. create linked server(eg.server2) (I have done that)

2. create distributed views on server1

-creating the distributed views i understand that i can use OPENDATASOURCE or OPENROWSET or just a four-partname right?

After creating the distributed views can i use a four-part name to make references to the remote databases?Can i also insert to those databases?

Accessing SQL Server data using Thread

Hi,

How do we set credentials or contex to a thread.

I create a new thread and within that thread if I am use a connetion string with Integrated Security=True" to talk to the SQL Server, however it seems that the new thread's context/identity is blank and hence failing the SQL Server connection.

Please help.

Thanks

Read this thread. hopefully it's the same problem you are having..

http://www.msdner.com/forum/thread597465.html

|||

Thanks, It helped.