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 replication for a non-Administrators

Can I do replication to copy tables in one database which was not create by
me to another database?
possibly, you need to be system administrator or dbo to create publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"M" <mxchen@.hotvoice.com> wrote in message
news:%23Lwd3w38EHA.1188@.tk2msftngp13.phx.gbl...
> Can I do replication to copy tables in one database which was not create
by
> me to another database?
>

Do I need to verify that NewID() returns a unique GUID?

I'm migrating a web based system to SQL server. I'm planning on using the SQL server function NewID() to create unique keys for many of my records in many different tables. I'm just wondering if NewID() is guaranteed to return a value that does not already exist in my database. I mean obviously once you have a certain number of records (a hell of a lot) you'd be breaking the odds to never come up with a duplicate.

Do I need to make sure the result of NEWID() doesn't already exist?

Thanks

No this algorithm is guaranteed to always return a unique value. At least for a really, really long time,

Hope this helps,

Do I need more integrity on my DB?

I am not sure I need more integrity on my DB. My DDL are down below.
thanks

************************************************** *****

create table person(
personId int identity(1,1) primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null
);

create table student(
studentId char(4) not null primary key,
personId int not null
);

alter table student
add constraint fk_person_student
foreign key (personId)
references person (personId)
;

create table instructor(
instructorId char(4) not null primary key,
instructorQual varchar(100) not null,
personId int not null
);

alter table instructor
add constraint fk_person_instructor
foreign key (personId)
references person (personId)
;

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

create table contact(
contactId int not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
street varchar(50) not null,
city varchar(25) not null,
state char(2) not null,
zip char(5) not null,
relationship varchar(25) not null,
phNum char(12) not null,
emailAdd varchar(50) null,
);

create table class(
classNum char(4) not null primary key,
className varchar(25) not null,
classDay char(3) not null,
classTime char(8) not null,
testNum char(5) not null
);

alter table class
add constraint fk_class_testnum
foreign key (testNum)
references test (testNum)
;

create table discount(
discountNum char(3) primary key,
discountDesc varchar(100) not null,
discountPer decimal(3,2) not null
);

create table test(
testNum char(5) primary key,
testName varchar(50) not null,
testDate smalldatetime not null,
testFee money not null,
);

create table studentClass(
studentId char(4) not null,
classNum char(4) not null,
pass char(1) not null
);

alter table studentClass
add constraint pk_studentclass primary key clustered (studentId, classNum)
;

alter table studentClass
add constraint fk_studentclass_studenttid
foreign key (studentId)
references student(studentId)
;

alter table studentClass
add constraint fk_studentclass_classnum
foreign key (classNum)
references class(classNum)
;

create table contractDiscount(
contractNum int not null,
discountNum char(3) not null
);

alter table contractDiscount
add constraint pk_contractdiscount primary key clustered (contractNum, discountNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_contractnum
foreign key (contractNum)
references contract(contractNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_discountnum
foreign key (discountNum)
references discount(discountNum)
;

create table instructorClass(
instructorId char(4) not null,
classNum char(4) not null,
);

alter table instructorClass
add constraint pk_instructorclass primary key clustered (instructorId, classNum)
;

alter table instructorClass
add constraint fk_instructorclass_instructorid
foreign key (instructorId)
references instructor(instructorId)
;

alter table instructorClass
add constraint fk_instructorclass_classnum
foreign key (classnum)
references class(classnum)
;Without knowing more about your business and the applications using the database, it's very hard to say if you need more. It's possible. This looks good though just from looking at it.|||as a specific example.

I have a contract table which is connected to student, contact and contractDiscount table with studentId and contactId as a FK. contractDiscount table is a intersection table.

IF I try to delete a contract record it won't let me, unlease I delete the discount relation on contractDiscount table. when I delete the relation with that certain row in discount on contractDiscount table then I am able to delete the contract.
However in that case, my student table and contact table record will be there without the existing contract.

My first question is do i even need to delete those records in student and contact table for intefrity of DB, when I delete the contract table?

second question is If I need to delete the record how I do that because without deleting contractDiscout record, I am not even able to delete the contract record.

Do foreign keys generate implicit indexes?

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bobbobdurie@.gmail.com (bobdurie@.gmail.com) writes:

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID?

In SQL Server, no.

Quote:

Originally Posted by

I've been told this is done in some databases, but i need to know for
sure if SQL Server does it. Has anyone heard of this before, on any
other databses perhaps?

I seem to recall having heard this about Sybase Anywhere.

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Indeed, it is often a good idea to add indexes on foreign keys, as it
can speed up deletions considerably. And it is not uncommon to search
for data in a table on a foreign key. However, as always, you should
think twice, and not add indexes blindly. For instance, if you have a
country-code column in a address table, there is little reason to add
an index on that column, since you don't delete countries very often.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I've been told this is done in some databases, .. <<

Yes, but better. Sybase SQL Anywhere (nee Watcom SQL) builds links
from all the FK references to the single PRIMARY KET/UNIQUE occurence
in the referenced table. Saves space, pre-joins tables for speed and
makes DRI actions both easy and fast.

SQL Server is still thinking in terms of "table = file" instead of
"table is part of a whole schema" and that "record =row" instead of
"row is made up of columns". Stonebreaker had a recent blog on column-
oriented design over contigous storage model.|||On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.

Quote:

Originally Posted by

>If i create a simple table with a foreign key constraint, does it
>create an implicit index on that given ID? I've been told this is
>done in some databases, but i need to know for sure if SQL Server does
>it. Has anyone heard of this before, on any other databses perhaps?
>
>Heres an example of how the foreign key constraint is being added:
>
>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
>My initial testing seems to indicate adding an index on the foreign
>key column helps, but i need to know for sure. Any insight would be
>greatly appreciated!
>
>Bob

|||Check with sysindexes to see if SQL Server does this too.

As Erland mentioned, SQL Server does not automatically index foreign key
columns. That task is left to the discretion of the DBA, who might choose
not to index the foreign column(s) due to low cardinality and static data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tom van Stiphout" <no.spam.tom7744@.cox.netwrote in message
news:8qqbf3190hk4cis52502s9th2ebsjjfpd5@.4ax.com...

Quote:

Originally Posted by

On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:
>
Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.
>
-Tom.
>
>

Quote:

Originally Posted by

>>If i create a simple table with a foreign key constraint, does it
>>create an implicit index on that given ID? I've been told this is
>>done in some databases, but i need to know for sure if SQL Server does
>>it. Has anyone heard of this before, on any other databses perhaps?
>>
>>Heres an example of how the foreign key constraint is being added:
>>
>>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>>
>>My initial testing seems to indicate adding an index on the foreign
>>key column helps, but i need to know for sure. Any insight would be
>>greatly appreciated!
>>
>>Bob

|||Also, analyse your query requirements then apply an Indexing Strategy

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
<bobdurie@.gmail.comwrote in message
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?
>
Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
>
Bob
>

|||On Sep 24, 3:25 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Also, analyse your query requirements then apply an Indexing Strategy
>
--
>
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
>
<bobdu...@.gmail.comwrote in message
>
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...
>

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

>

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:

>

Quote:

Originally Posted by

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

>

Quote:

Originally Posted by

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

>

Quote:

Originally Posted by

Bob

Thanks for all the responses on this, its much appreciated!!! I also
found this article which makes me realize other people have had the
same misconceptions as me :)
http://www.sqlskills.com/blogs/kimb...yColu mns.aspx

Do drag and drop controls create DataSets

When I drag a GridView from the toolbox onto a Webform, and then configure its DataSource is a true DataSet created that I can access in the code behind? When my results return I want to be able to access individual rows and cells, taking their values, assiging them to variables and then using the newly equated variable to perform calculations.

Thank you,

Your GridView is bound to dataset It is simple to access values in dataset: your_DataTable.Rows[rowindex][colunmindex]

Or use GridView1.Rows[rowid].Cells[cellindex] to access the control (or findControl(id) if there are many).

Do backups need to be manually deleted?

If I create a backup plan with weekly full, daily differential, and hourly
log backups to local disk, will the backup sets continue to grow over time
or do they get overwritten? IOW, after six months will I have THOUSANDS of
backup files that will need to be deleted manually?
New backups are created each time. You can configure the maintenance plan
to delete old backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bill Fuller" <someone@.nospam.com> wrote in message
news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
If I create a backup plan with weekly full, daily differential, and hourly
log backups to local disk, will the backup sets continue to grow over time
or do they get overwritten? IOW, after six months will I have THOUSANDS of
backup files that will need to be deleted manually?
|||Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
this?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> New backups are created each time. You can configure the maintenance plan
> to delete old backups.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
> If I create a backup plan with weekly full, daily differential, and hourly
> log backups to local disk, will the backup sets continue to grow over time
> or do they get overwritten? IOW, after six months will I have THOUSANDS of
> backup files that will need to be deleted manually?
>
|||When you specify a backup directory, there is also a checkbox to Remove
files older than a certain period.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bill Fuller" <someone@.nospam.com> wrote in message
news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
this?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> New backups are created each time. You can configure the maintenance plan
> to delete old backups.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
> If I create a backup plan with weekly full, daily differential, and hourly
> log backups to local disk, will the backup sets continue to grow over time
> or do they get overwritten? IOW, after six months will I have THOUSANDS of
> backup files that will need to be deleted manually?
>
|||Bill,
And to add to Tom's comment, it sounds like you are running SQL Server 2000,
but if you are running SQL Server 2005 there is also a Maintenance Cleanup
Task that deletes old files.
RLF
"Bill Fuller" <someone@.nospam.com> wrote in message
news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
> this?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
>
|||I am running SQL Server 2005 and think I found that task... it defaults to 4
weeks, which I kept.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:um2NmS7UIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Bill,
> And to add to Tom's comment, it sounds like you are running SQL Server
> 2000, but if you are running SQL Server 2005 there is also a Maintenance
> Cleanup Task that deletes old files.
> RLF
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
>
sql

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

dmx query probablity ?

CREATE MINING MODEL mortgage
(
[id] long key,
Edu_Status long DISCRETE,
Work_Status long DISCRETE,
age long CONTINUOUS,
asset_value long CONTINUOUS,
Net_income long CONTINUOUS,
paid_Status long DISCRETE PREDICT
)USING MICROSOFT_DECISION_TREES

i have a mining model above and i am designing a web cross ablication to see probablity of the a costumers' paid status.can you write for me dmx command for selecting paid status that have same criteria(ex;age=30 net income=2000,Edu_status=college) and when i write dxm window(select * from [paid_Status] ) it only shows "4" one row one column why ? as you see i dont know

dmx and datamining well..:)

string dxmcommand = "";

private AdomdCommand ascommand = new AdomdCommand();

private AdomdConnection asconnection=new AdomdConnection();

private AdomdDataReader asreader=new AdomdDataReader();

ascommand.CommandText = dmxcommand;

if I understand your question correctly, then the query would be something like:

SELECT Paid_Status, PredictProbability(Paid_Status)

FROM mortgage NATURAL PREDICTION JOIN

(SELECT 30 AS Age, 2000 as Net_Income ) AS T

This query will return the predicted value for Paid_Status as well as the proability for that prediction. Note that I did not include "College" as Edu_status. The reason is that the Edu_status column of the mining model is defined as "LONG DISCRETE" and "College" is a string, so it cannot be mapped to a long. You will have to convert "College" to the numeric code which I suppose it is used in describing this string, then add "<Numeric_Code_For_College> AS Edu_Status" to the query

Also, in C#, the sequence of operations should be along this line:

AdomdConnection cn = new AdomdConnection();

cn.ConnectionString = "Data Source=localhost; Initial Catalog=<Your Database>";

cn.Open(); // open the connection

AdomdCommand cmd = new AdomdCommand();

cmd.Connection = cn; // associate the command with the current connection

cmd.CommandText = dmxcommand;

AdomdDataReader rdr = cmd.ExecuteReader(); // obtain the reader from the command execution instead of creating it with new

Hope this helps

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.

Divide by number of days in the year.

How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advanceHere's how to get the number of days in the current year:
select
case
when year (getdate()) / 100 % 4 = 0 then 365
when year (getdate()) % 4 = 0 then 366
else 365
end
However, in a UDF, you cannot have a non-deterministic function within it.
Thus, you cannot use getdate() directly. However, you could feed a date to
the function.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:19165261-4C92-45C9-97B5-00AC910B17FE@.microsoft.com...
How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advance|||For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>|||scuba
Here is a stright but bit complicated one. This is useful evenif calender
changes(yuck!) provided years starts from jan1 and ends with 31 dec(kidding)
SELECT DATEDIFF(DAY, CAST('01-01-' + cast(YEAR(GETDATE()) as varchar(4)) AS
DATETIME),CAST('12-31-' + cast(YEAR(GETDATE()) as varchar(4)) AS DATETIME))+
1
Regards
R.D
"scuba79" wrote:

> How can I create a function that will divide a parameter passed into the
> stored procedure by the number of days in the current year? I know that I
> can not just use 365 since it will not take into account leap years.
> Thanks in advance|||:-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e%23m2LE3rFHA.1032@.TK2MSFTNGP12.phx.gbl...
For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>

divide by infinity

help... I have a field called PercentVar_P4 which really is budget - actuals... I need to create an expression that will that the

PercentVar_P4/budget.. when I do I get Infinity and nan... I want to see the negative number.. I have read through all the blogs and nothing seems to fit..

Please HELP!!

Did you look at the following blog article? http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

-- Robert

Distributor

Hi,
I have set a transactional replication by Interprise
Manager, but when I create a subscriber using "Pull
Subscription to ..." it creates it succesfuly and even
gives me this message that "you have created the
subscription successfully", but it doesn't start
replication and gives this message in "Last Action" column:
" 'XServer' is not Configured as a Distributor. The step
failed".
Thank you very much.
Mathew,
this could be a naming issue.
Please try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'XServer'
GO
Use Master
go
Sp_Addserver 'XServer', 'local'
GO
Stop and Start SQL Services
HTH,
Paul Ibison
|||can the subscriber ping Xserver? Is XServer a Publisher/Distributor or
Distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mathew" <anonymous@.discussions.microsoft.com> wrote in message
news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
> Hi,
> I have set a transactional replication by Interprise
> Manager, but when I create a subscriber using "Pull
> Subscription to ..." it creates it succesfuly and even
> gives me this message that "you have created the
> subscription successfully", but it doesn't start
> replication and gives this message in "Last Action" column:
> " 'XServer' is not Configured as a Distributor. The step
> failed".
> Thank you very much.
>
|||Paul,
Thank you so much. I have checked it, and it's correct.
Actually we have 2 servers in 2 different locations, and
both of them are called 'HAKIM-SERVER', so when I
run "Select @.@.ServerName" they answer 'HAKIM-SERVER'. I
have registered both of them in my "Enterprise
Manager",one by it's own name which is 'HAKIM-SERVER', and
the other one by it's IP address, as we have a VPN. It was
working until 2 weeks ago that our modem and router in the
other building were burned and we had to change them with
a brand new one. Our VPN is fine and there is nothing odd,
but when I go to to "Pull subscription
to 'xxx.xxx.xxx.xxx'... " on the server, which is in our
other location and I registered it with the IP address, to
create the subscribor, it gives me that error message
>--Original Message--
>Mathew,
>this could be a naming issue.
>Please try:
>Use Master
>go
>Select @.@.Servername
>This should return your current server name but if it
>returns NULL then try:
>Use Master
>go
>Sp_DropServer 'XServer'
>GO
>Use Master
>go
>Sp_Addserver 'XServer', 'local'
>GO
>Stop and Start SQL Services
>HTH,
>Paul Ibison
>
>.
>
|||Hi,
It's a Publisher/Distributor. I wrote a complete
explanation for the first respond from Paul. Whould you
please take a look at it?
Thnks again
>--Original Message--
>can the subscriber ping Xserver? Is XServer a
Publisher/Distributor or
>Distributor?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mathew" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
column:
>
>.
>
|||Mathew,
can you try using an alias rather than an IP address.
Regards,
Paul Ibison
|||Dear Paul,
How can I assign an alias to a remote server when it has
the same name as the local server.
Thanks,

>--Original Message--
>Mathew,
>can you try using an alias rather than an IP address.
>Regards,
>Paul Ibison
>
>.
>
|||Matthew,
in the client network utility, you can add an alias with the TCP/IP network
library. The server alias is any name you choose, and the server name can be
the IP address you have been using.
HTH,
Paul Ibison

Distribution of Application with SQL Server DB

If I create a Window's application that uses a MS Sql Server DB created with the express edition, do I need to get permission or submit royalties for distribution of my application? If so, where can I find the required info? I have been Googling without success and want to understand what is involved.

My projects are mostly for fun and my own amazement at this point, but I thinking about creating something that may actually become a product and need some guidance before I get much further along with it.

You may freely distribute SQL Server Express Edition.

See this link to register and for more details.

SQL Server 2005 Express Redistribution
http://www.microsoft.com/sql/editions/express/redistregister.mspx

The following may help you customize your own installer for SQL Server.

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

|||Thanks Arnie. Very encouraging info.

Distribution db doesn't show up after enabling transactional repl

We are using SQL 2k with SP4. After I used the 'Create and Manage
publication wizard..' to create my transaction replication (I took the
default settings), it finished successfully. When I check the database
listing in EM, the 'distribution' database doesn't show up in the list but
the physical .mdf and .ldf files do exist. I thought this is strange, I
went ahead to disable the publication and thought I could start over again
but it gave me an error message " Eror 945: Database 'distribution' cannot be
opened dur to inaccessible files or insufficient memory or disk space'. We
are out out of space or memory. I stop by restart SQL service but it didn't
help. Can anyone help?
Wingman
Do you have the show system databases enabled? This could account for the
invisibility of it. I would clear up space on your machine and then try to
disable publishing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:8A3D4193-03DC-4D43-A3D5-A7FE0D2E97CB@.microsoft.com...
> We are using SQL 2k with SP4. After I used the 'Create and Manage
> publication wizard..' to create my transaction replication (I took the
> default settings), it finished successfully. When I check the database
> listing in EM, the 'distribution' database doesn't show up in the list but
> the physical .mdf and .ldf files do exist. I thought this is strange, I
> went ahead to disable the publication and thought I could start over again
> but it gave me an error message " Eror 945: Database 'distribution' cannot
> be
> opened dur to inaccessible files or insufficient memory or disk space'.
> We
> are out out of space or memory. I stop by restart SQL service but it
> didn't
> help. Can anyone help?
> Wingman

distribution agent

I'm developing a setup package for an application that involves some
replications.
I create the new replication database, transactional publication, merge
pulication and a pull named subscription on the rtansactional
publication.
For checking that the snapshots have been generated successfully I do
the following:
get the job_name from the mssnapshot_agents table on distribution
database.
update job_state to run agent.
This worked well on both snapshots (trans, merge)
The problem is that I try to do the same steps for running the
distribution agent to initialize the subscription:
get the job_name from msdistribution_agents table on distribution
database.
BUT the job name is always "dose not exist"
I use sp_update_job to update the current step to start
and sp_start_job to start the job.
Is there a problem with those SPs or another way to do the same with
other SPs.
Thanks.
Have you looked at the replication ActiveX controls?
I think this is probably the simplest way to carry out what you are trying
to accomplish.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"taghreed" <tssamak@.gmail.com> wrote in message
news:1103290693.352210.19720@.z14g2000cwz.googlegro ups.com...
> I'm developing a setup package for an application that involves some
> replications.
> I create the new replication database, transactional publication, merge
> pulication and a pull named subscription on the rtansactional
> publication.
> For checking that the snapshots have been generated successfully I do
> the following:
> get the job_name from the mssnapshot_agents table on distribution
> database.
> update job_state to run agent.
> This worked well on both snapshots (trans, merge)
> The problem is that I try to do the same steps for running the
> distribution agent to initialize the subscription:
> get the job_name from msdistribution_agents table on distribution
> database.
> BUT the job name is always "dose not exist"
> I use sp_update_job to update the current step to start
> and sp_start_job to start the job.
> Is there a problem with those SPs or another way to do the same with
> other SPs.
> Thanks.
>
|||Thanks Hilary,
but I don't have time to reconstruct the application using ActiveX
controls.
Do you have other ideas?

Distributing/importing semantic models

Hi,
i'm using SQL Server 2005 Reporting Services to create ad-hoc reports.
Using the BI Dev Studio i'm defining a DSV and adding queries to it
before refining in the semantic model. i need to be able to distribute
these to our customers, who use an SS DB with known schema.
In a simulated customer environment i've successfully imported the DSV
file (using SMSS) to RS. When i subsequently try to import the SMDL
file i get an error complaining that the DataSourceView element is
missing from the SemanticModel. Quite correct. But how do i specify
the DSV element or otherwise make the association?
It is quite acceptable to me to make a simple text edit to the SMDL
file.
TIA for any help.OK, slight correction - i haven't successfully imported the DSV.
Import appeared to succeed but there's actually no data visible for it
in SMSS.|||Since there are no replies i'm either very stupid or nobody knows. If
it's the latter then for the benefit of all here's how i did it in the
end.
DataSourceView is a valid element of the SemanticModel element. So
manually edit your SMDL file and paste the entire content of your DSV
file (which is a single DataSourceView element) just above the
SemanticModel closure tag ("</SemanticModel>").
The Management Studio will now happily import the modified SMDL file.
The customer just needs to manually change the Data Source to their own
database using Management Studio (or Report Manager).
Cheers all.

distributing subscriber database on multiple machine - install

Hi,

Is it possible to create a 'master' subscriber, back-up the database with the replication triggers and subscription intact, then restore this subscriber database on multiple subscribers during an install? The subscription is for an anonymous web-synced publication, up to 40 subscribers, and I am trying to avoid the initial snapshot download.

Thanks,

Darrell Young
Hi Darrell,

Yes, it is possible to avoid the initial snapshot downlad on your subscribers. An alternative maybe, to initialize snapshot from backup. More information can be found in book online.

Initializing a Merge Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/ee16af24-d7e2-4b65-a25f-dc89caba2ea2.htm

Initializing a Transactional Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Regards,

Gary Chen|||Gary,

Thanks for the reply. The issue is have is that the subscribers will only have a subset of the data and the schema of the published database, so wholly backing up and restoring the published database is not practical. I had tried taking a subscriber database, then removing the subscription and creating a backup from that - the issue is that the rowguids are removed from the tables. I suppose the choice I have left is to create a backup of the published database, restore on a subscriber, manually remove the data and schema I am not including in the publication, then create a backup of that.
Thanks,

Darrell Young
|||

Hi Darrell,

Try the following,

- Create a snapshot at the publisher

- Download the initial snapshot to one subscriber S1

- Back up the subscriber database at S1

- Before you restore the back up database to other subscriber S2, drop the subscription that you have created before at subscriber S2

- Restore the S1 backup to S2 with KEEP_REPLICATION off (I believe by default it is off)

- Re-create the subcription with no-sync option.

You probably want to make sure that no data get updated to your publisher or subscriber database while you perform this backup-restore.

Try it on one or two machines to see if this work before you restore to all 40 machines.

Regards,

Gary Chen

Distributing Data with an App

I have developed an application which will be distributed. I know how to
create a SQL script which will create the table structure for the DB. Is
there an easy/recommended way to export the default data from my tables in a
way which will be easy and/or automatic for the installer to load into their
DB after the tables are created?
I could write C# into my app which will notice empty tables and populate
them but this seems tedious. Is there a better way?
thanks
charles
You could BCP or BULK COPY your data or you could create insert statements
with a tool like one of these:
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
Largo SQL Tools -- http://www.largosqltools.com/ (seems to be under
construction at the moment)
Keith
"charles" <spam@.synthigence.com> wrote in message
news:eoeNK4AnEHA.556@.tk2msftngp13.phx.gbl...
> I have developed an application which will be distributed. I know how to
> create a SQL script which will create the table structure for the DB. Is
> there an easy/recommended way to export the default data from my tables in
a
> way which will be easy and/or automatic for the installer to load into
their
> DB after the tables are created?
> I could write C# into my app which will notice empty tables and populate
> them but this seems tedious. Is there a better way?
> thanks
> charles
>
|||Thank you so much... QALite looks like it does exactly what I need,
creating a SQL INSERTs script from my existing data
charles
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:epWH5BBnEHA.3472@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You could BCP or BULK COPY your data or you could create insert statements
> with a tool like one of these:
> ObjectScripter -- http://www.rac4sql.net/
> QALite -- http://www.rac4sql.net/
> Lockwood Tech -- http://www.lockwoodtech.com/
> Largo SQL Tools -- http://www.largosqltools.com/ (seems to be under
> construction at the moment)
> --
> Keith
>
> "charles" <spam@.synthigence.com> wrote in message
> news:eoeNK4AnEHA.556@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
Is[vbcol=seagreen]
in
> a
> their
>

Distributing Data with an App

I have developed an application which will be distributed. I know how to
create a SQL script which will create the table structure for the DB. Is
there an easy/recommended way to export the default data from my tables in a
way which will be easy and/or automatic for the installer to load into their
DB after the tables are created?
I could write C# into my app which will notice empty tables and populate
them but this seems tedious. Is there a better way?
thanks
charlesYou could BCP or BULK COPY your data or you could create insert statements
with a tool like one of these:
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
Largo SQL Tools -- http://www.largosqltools.com/ (seems to be under
construction at the moment)
--
Keith
"charles" <spam@.synthigence.com> wrote in message
news:eoeNK4AnEHA.556@.tk2msftngp13.phx.gbl...
> I have developed an application which will be distributed. I know how to
> create a SQL script which will create the table structure for the DB. Is
> there an easy/recommended way to export the default data from my tables in
a
> way which will be easy and/or automatic for the installer to load into
their
> DB after the tables are created?
> I could write C# into my app which will notice empty tables and populate
> them but this seems tedious. Is there a better way?
> thanks
> charles
>|||Thank you so much... QALite looks like it does exactly what I need,
creating a SQL INSERTs script from my existing data
charles
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:epWH5BBnEHA.3472@.TK2MSFTNGP09.phx.gbl...
> You could BCP or BULK COPY your data or you could create insert statements
> with a tool like one of these:
> ObjectScripter -- http://www.rac4sql.net/
> QALite -- http://www.rac4sql.net/
> Lockwood Tech -- http://www.lockwoodtech.com/
> Largo SQL Tools -- http://www.largosqltools.com/ (seems to be under
> construction at the moment)
> --
> Keith
>
> "charles" <spam@.synthigence.com> wrote in message
> news:eoeNK4AnEHA.556@.tk2msftngp13.phx.gbl...
> > I have developed an application which will be distributed. I know how
to
> > create a SQL script which will create the table structure for the DB.
Is
> > there an easy/recommended way to export the default data from my tables
in
> a
> > way which will be easy and/or automatic for the installer to load into
> their
> > DB after the tables are created?
> >
> > I could write C# into my app which will notice empty tables and populate
> > them but this seems tedious. Is there a better way?
> >
> > thanks
> > charles
> >
> >
>