Do SQL Analysis service needs a separate database ?

As Adventure Works have Adventure works DW as a separate database, Do we also needs to create new database such as while working with "pubs" or any other do we need to create pubs DW separately or pubs will be sufficient for our working for sql analysis ?

Hi,

you should to create a new DW database (it′s a best pratice), but it′s not mandatory.

If you have a small production database, you will not percept the performance degradation...

So to create a DW (Dataware house) can be good to isolate OLAP from OLTP.

Regards

Do I need to start the service..

Hi,
I am providing a UI in my application (which uses MSDE) for the user to back
up/restore the application database. Today I tried to test it and it didn't
work complaining that sql server does not exist. I checked and saw that I
had not started the MSSQL$M
yApp service.. when I started it, the backup and restore started working fin
e. So does that mean that I have to programatically start the service in my
app before backup/restore. Generally my application does not need to start
the service while talking
to the database.
ThanksIf you set the service to automatic, it will start when the computer
starts. There is always a possibility that it will get stopped manually. So
you could check to see if it is started in your app and if not start it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Do I need to start the service..

Hi,
I am providing a UI in my application (which uses MSDE) for the user to backup/restore the application database. Today I tried to test it and it didn't work complaining that sql server does not exist. I checked and saw that I had not started the MSSQL$M
yApp service.. when I started it, the backup and restore started working fine. So does that mean that I have to programatically start the service in my app before backup/restore. Generally my application does not need to start the service while talking
to the database.
Thanks
If you set the service to automatic, it will start when the computer
starts. There is always a possibility that it will get stopped manually. So
you could check to see if it is started in your app and if not start it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Do Analysis service needs a seprate Database ?

As Adventure Works have Adventure works DW, Do we also needs to create new database such as while working with pubs do we need to create pubs DW separately or pubs will be sufficient for our working ?

for sure not.

But it is a good design aproach.

You could do a seperate BI database, or

create your own tables in the exiting database, or

create views in the existing database or

use the Analysis Services Datasource View with Named querys

to do the data transformation

HANNES

dll & Web Service in VB.Net 2005 Stored Procedure

Hi,
I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProject Template.
Now, I have two questions for this stored procedure.
1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?
---
Partial Public Class StoredProcedures1
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetTable()
Dim strSQL As String = "SELECT * FROM Table1"
Using conn As New SqlConnection("context connection=true")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Text
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
.Connection.Close()
End With
End Using
End Using
End Sub
End Class
---
Thanks!"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am writing a vb.net2005 program that needs to create a stored procedure
> with SqlServerProject Template.
> Now, I have two questions for this stored procedure.
> 1) How can I import and execute the .dll in this Stored Procedures?
You need to deploy the assembly into your database (CREATE ASSEMBLY) and
then add a stored procedure referencing the method in the assembly (CREATE
PROCEDURE). Visual Studio will do this for you if you right-click on the
project and select Deploy.
Here's how you would do it by hand:
CREATE ASSEMBLY [SqlServerProject2]
FROM 'c:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE [GetTable]
AS
EXTERNAL NAME
[SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]

> 2) How can I connect the Web Service and get the result in this Stored
> Procedures?
>
From the web service just connect to the database and execute it with a
SqlCommand.
David|||Hi David,
sorry, i think that u were misunderstood my problem.
My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
.dll & web service to get some value.
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:O3xF9H5iGHA.45
04@.TK2MSFTNGP05.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> You need to deploy the assembly into your database (CREATE ASSEMBLY) and
> then add a stored procedure referencing the method in the assembly (CREATE
> PROCEDURE). Visual Studio will do this for you if you right-click on the
> project and select Deploy.
> Here's how you would do it by hand:
> CREATE ASSEMBLY [SqlServerProject2]
> FROM 'c:\SqlServerProject2.dll'
> WITH PERMISSION_SET = SAFE
> GO
> CREATE PROCEDURE [GetTable]
> AS
> EXTERNAL NAME
> [SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]
>
> From the web service just connect to the database and execute it with a
> SqlCommand.
> David
>|||"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> Hi David,
> sorry, i think that u were misunderstood my problem.
> My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
> .dll & web service to get some value.
>
To use another .dll add a reference to your database project. To use a web
service add a web reference.
David|||Hi David,
1) For DLL, VS.Net 2005 is not allow me to import a new References in
SqlServerProject Template.
2) For Web Service, it will occur error when running.
----
--
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SPWithWebService()
Dim WebService As New SqlServerProject1.localhost.Service
Dim msg As String = CStr(WebService.HelloWorld())
SqlContext.Pipe.Send(msg)
End Sub
End Class
----
--
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:OqYxDh5iGHA.34
96@.TK2MSFTNGP04.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> To use another .dll add a reference to your database project. To use a
> web service add a web reference.
> David
>|||Hello James,
When you are using the VS SQL Server Project you are restricted to what
assemblies you can reference. This behavior is by design. However, you
could create a normal class library project, add references as per usual
and then manually create your assembly through CREATE ASSEMBLY. Make sure
your referenced
assemblies are in the same directory as your user assembly.
If you want to use the VS SQL Server Project you can create the assembly
you want to reference in the database before you try to reference it. You
need do a manual CREATE ASSEMBLY against the DLL you want.
As for Web service , you will also need to use the CLR SDK tool SGEN.EXE to
create and register a helper assembly for the assembly generated by WSDL,
as described in:
http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

distribution cleanup cannot clean up snapshot folder

We are using SQL Server 2005 SP2 to do transactional replication.

We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.

I have been getting an error message when we run the distribution clean up job.

Executed as user: PROD\sqladmin. Could not remove directory '\\Tes01box\Repldata\unc\qabox01_DB01_TO_ORACLE\20070905104896\'. Check the security context of xp_cmdshell

I have dropped the publication and recreated which is what appears to have caused the error.

From

http://technet.microsoft.com/en-us/library/ms151151.aspx

Note:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.

The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.

Finally my questions:

Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?

If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?

open up your distribution clean up task job, and in the job step properties do the following:

setuser 'sqlrepadmin'
GO
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0, @.max_distretention = 72
GO|||

I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains.

What an amazingly simple and elegant solution. I feel silly that I did not think of this.

Thank you Hilary.

distribution cleanup cannot clean up snapshot folder

We are using SQL Server 2005 SP2 to do transactional replication.

We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.

I have been getting an error message when we run the distribution clean up job.

Executed as user: PROD\sqladmin. Could not remove directory '\\Tes01box\Repldata\unc\qabox01_DB01_TO_ORACLE\20070905104896\'. Check the security context of xp_cmdshell

I have dropped the publication and recreated which is what appears to have caused the error.

From

http://technet.microsoft.com/en-us/library/ms151151.aspx

Note:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.

The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.

Finally my questions:

Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?

If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?

open up your distribution clean up task job, and in the job step properties do the following:

setuser 'sqlrepadmin'
GO
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0, @.max_distretention = 72
GO|||

I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains.

What an amazingly simple and elegant solution. I feel silly that I did not think of this.

Thank you Hilary.

Distribution Agent basics?

Hey everyone,

sorry for the lame questions, but:

1) I have a transactional replication service set up. However, whenever we select a new table or column within a table to replication to the subscribers, the changes never seem to get picked up unless we make an actual change within some of the other data. Any idea why this is? Even after I've reinitialized snapshots, the new changes don't want to proprogate to teh subscribers until data is actually changed.

2) where do I need to go to manually stop and restart a a distribution agent to force these changes to be proprogated without having to change any data within the tables?

Thanks

Replication is very complex and complicated I have not used it a for a while, get to know the Agent and MSDB and the link below will take you in the right directions. Hope this helps.

http://www.informit.com/articles/article.asp?p=170807&seqNum=2&rl=1

Distributed transaction

I tri do start distribute transaction from a Server sql 2005, and other 2
server SQL 2000. I starto MSDTC service, the 2 server 2000 are connetcetd
true Linked Server from the SQL 2005 with SQLNCLI provider.
When i start the transaction and update a table on my first remote server
SQL server 2000 i get this error:
Server: messgae 7391, level 16, state 2, row 6
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "SQL-SERVER2" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI" for linked server "SQL-SERVER2" returned message
"The transaction coordinator has disabled the support for remote/network
transaction".
(Sorry but i translate the message error from italian)
If i update the remote server qithout transaction i've got no problem. Works
perfectly.
Someone can help me? How can i solve this problem?
ThanksIf you're sure MSDTC is enabled and running on all your computers then I
would guess there is a firewall blocking the connection in one of the
machines.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:53CAD34E-4F66-4D30-825B-FB59FACB6D22@.microsoft.com...
>I tri do start distribute transaction from a Server sql 2005, and other 2
> server SQL 2000. I starto MSDTC service, the 2 server 2000 are connetcetd
> true Linked Server from the SQL 2005 with SQLNCLI provider.
> When i start the transaction and update a table on my first remote server
> SQL server 2000 i get this error:
> Server: messgae 7391, level 16, state 2, row 6
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "SQL-SERVER2" was unable to begin a distributed transaction.
> OLE DB provider "SQLNCLI" for linked server "SQL-SERVER2" returned message
> "The transaction coordinator has disabled the support for remote/network
> transaction".
> (Sorry but i translate the message error from italian)
> If i update the remote server qithout transaction i've got no problem.
> Works
> perfectly.
> Someone can help me? How can i solve this problem?
> Thanks|||Could be a user's permission problem? And how i should configure the DTC
service and MSSQLSERVER service? What kinf of users these services sholud us
e
to run?
My configuration:
My local pc with WINXP SP2 (firewall disabled) and SQL Server 2005 with
MYLOCALDB and one linked server on a Server 2000 sp4 and SQL SERVER 200 sp2.
On both is running and enabled MSDTC, MSSQLSERVER and SQLSERVERAGENT and bot
h
the machine on the same domain.If i try to update a table on the remote
linked server the updata success. If i start a transaction and update the
remote table i've got this error:
OLE DB provider "SQLNCLI" for linked server "SQL-SERVER2" returned message
"The transaction coordinator has disabled its support for remote
transaction/network ".
Thanks
"Roger Wolter[MSFT]" wrote:

> If you're sure MSDTC is enabled and running on all your computers then I
> would guess there is a firewall blocking the connection in one of the
> machines.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:53CAD34E-4F66-4D30-825B-FB59FACB6D22@.microsoft.com...
>
>

DISTRIBUTED TRANSACTION

Hi,
I have two sql servers with SQL2000 service pack 3 which are linked by the "
Link Server". When i use the "begin tran" (distributed transaction) in store
d procedure, i am getting the following error.
"Server: Msg 8525, Level 16, State 1, Line 1
Distributed transaction completed. Either enlist this session in a new trans
action or the NULL transaction. "
The following article talks about this problem.
http://support.microsoft.com/?kbid=834849
But, both servers are SQL 2000 in my case. Any help?
Thanks,
VijayHi, i'm going this trouble but the versions are different. Server A has Sql
server 2000 enterprise edition and the server B has Sql Server 7.0, both
servers are linked properly and running msdtc. No changes were made to their
configurations. If I run the sentence with Begin distributed tran and commin
distributed tran it end right but using that statement it fails with the
error commented is this post.
Any ideas?
"Vijay" wrote:

> Hi,
>
> I have two sql servers with SQL2000 service pack 3 which are linked by the
"Link Server". When i use the "begin tran" (distributed transaction) in sto
red procedure, i am getting the following error.
>
> "Server: Msg 8525, Level 16, State 1, Line 1
> Distributed transaction completed. Either enlist this session in a new tra
nsaction or the NULL transaction. "
>
> The following article talks about this problem.
> http://support.microsoft.com/?kbid=834849
> But, both servers are SQL 2000 in my case. Any help?
>
> Thanks,
> Vijay
>
>

Distributed SQL VS. Service Broker

Hello SQL Programmers.
What is the big difference between distributed SQL and Service Broker? Am I
just confusing things here?
I am starting a project in SQL 2005 and basically I have regional server
and a data warehouse, customer might request reports which have data coming
from both regional servers and data warehouse. Should I use service broker o
r
distributed SQL to get the data from the regional servers? Basically I just
to run TSQL command on the regional server, there could be up to 50 regional
servers.
Please let me know.
Thank you,
Spike S.service broker can be within the same db, between dbs in a server or between
dbs in different servers. And its for asyncronous messaging.
Distirbuted transactions are synchronous.
Check out this link. Hope this helps.
http://www.c-sharpcorner.com/Longho...iceBroker02.asp|||Thank you Omnibuzz