Adding a ODBC link to many computers

Is there any way of adding an OBDC connection to multiple computers without
going to each system. I need to push out a new SQL Server connection, does
anyone know an easy way.
Thanks!
Use a DSN-less connection.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"HenryG" <HenryG@.discussions.microsoft.com> wrote in message
news:49B3EF04-D325-462C-9FFC-A79600D0CAD9@.microsoft.com...
> Is there any way of adding an OBDC connection to multiple computers
> without
> going to each system. I need to push out a new SQL Server connection,
> does
> anyone know an easy way.
> Thanks!
sql

Adding a full text search across multiple tables (with text fields)

Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:

Documents:
- DocumentID (int, PK)
- Title (varchar)
- Content (text)
- CategoryID (int, FK)

Categories:
- CategoryID (int, PK)
- CategoryName (varchar)

I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:

CREATE VIEW vw_Documents
WITH SCHEMABINDING AS
SELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID

GO
CREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndex
ON vw_Documents(DocumentID)

But this gave me the error:

Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.

I tried converting the Content to a varchar(max) within my view but it still didn't like.

Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

Hi jgd12345,

After talking with my colleagues i find a solution:

First you need to change Content datatype from text to varchar(max);

Then set the following options:

SET CONCAT_NULL_YIELDS_NULL ON

|||Cheers, i'll test this out when i get back to the office. Thanks again.

add sql logins from multiple domains

Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.If the domain in which your SQL Server exists and the other domains are
'trusted', then you could simply add those other domain's logins using
sp_grantlogin.
You could also create a windows group into which you add all the logins you
wanted, and grant access to this windows group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"German" <german.koninin@.crm-worldwideDOTnet> wrote in message
news:eGYi2lIaDHA.2464@.TK2MSFTNGP09.phx.gbl...
Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.

Add field to multiple tables

How can I add one or two fields to about 50 tables without having to go into
each one seperately?Write a Transact-SQL script.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:C6DDC84D-5D08-415A-8805-392465AA6041@.microsoft.com...
> How can I add one or two fields to about 50 tables without having to go
> into
> each one seperately?|||Brian,
Are you saying that is possible through a TSQL script to do this? If so, do
you have any examples that could assist me with this?
Thanks
Scuba79
"Brian Selzer" wrote:

> Write a Transact-SQL script.
> "scuba79" <scuba79@.discussions.microsoft.com> wrote in message
> news:C6DDC84D-5D08-415A-8805-392465AA6041@.microsoft.com...
>
>|||SELECT 'ALTER TABLE [' + LTRIM(RTRIM(TABLE_NAME)) + '] ADD Column1 INT NOT
NULL DEFAULT(0), Column2 CHAR(8) NOT NULL DEFAULT('''')' FROM
INFORMATION_SCHEMA.TABLES
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EA1FEFB2-62B2-4B4B-9E9F-51404BA4E560@.microsoft.com...
> Brian,
> Are you saying that is possible through a TSQL script to do this? If so,
> do
> you have any examples that could assist me with this?
> Thanks
> Scuba79
> "Brian Selzer" wrote:
>|||Brian,
Thanks for the assistance, this is exactly what I needed to know.
Scuba79
"Brian Selzer" wrote:

> SELECT 'ALTER TABLE [' + LTRIM(RTRIM(TABLE_NAME)) + '] ADD Column1 INT NOT
> NULL DEFAULT(0), Column2 CHAR(8) NOT NULL DEFAULT('''')' FROM
> INFORMATION_SCHEMA.TABLES
>
> "scuba79" <scuba79@.discussions.microsoft.com> wrote in message
> news:EA1FEFB2-62B2-4B4B-9E9F-51404BA4E560@.microsoft.com...
>
>

Add composite primary key

I'm a bit unclear on the syntax to add a composite (multiple columns)
primary key to a table. The columns already exist. Thanks.ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_ConstraintName PRIMARY KEY NONCLUSTERED
(
Col1,
Col2
) ON [PRIMARY]
When in doubt, you can always perform the action in Enterprise Manager
and then look at the change script to help clear up basic syntax
things.
HTH,
Stu|||Do:
ALTER TABLE tbl ADD CONSTRAINT pk PRIMARY KEY ( col1, col2 )
Anith|||Roughly this at table creation:
CONSTRAINT PK_NameIt PRIMARY KEY CLUSTERED (Column1, Column2, Column3)
Obviously if you need a NONCLUSTERED PK, use that syntax instead.
Mark
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc2cab9b0d541d8989902@.msnews.microsoft.com...
> I'm a bit unclear on the syntax to add a composite (multiple columns)
> primary key to a table. The columns already exist. Thanks.

Ad hoc reports multiple tables

HI,
i am a new to SSRS and i am facing a problem with Ad Hoc reporting.
actually i wanted to create a report with data from two different table. now its the relation between these two tables that is not allowing me to create a req. adhoc report.

i want the report to have data from table A and table B and the relationship between A and B is many-to-many. so in the database design i have an associate table C between A and B. so in .dsv file i have C related to A as one-to-many and C related to B as one to many.

Now here how do i get data from both table A and table B?
i will put some more info, put the question in a different way.

Say I have 3 tables related as shown in the diagram below

Say

1) plan organization

2) plan

3) plan opportunity

in the ad hoc report I wanted to have data from plan organization and plan opportunity.

How will I do that?

plan organization >-|- Plan -|--< plan opportunity

one-to-many relation between plan and plan organization
one-to-many relation betweeb plan and plan opportunity

Ad hoc reports multiple tables

HI,
i am a new to SSRS and i am facing a problem with Ad Hoc reporting.
actually i wanted to create a report with data from two different table. now its the relation between these two tables that is not allowing me to create a req. adhoc report.

i want the report to have data from table A and table B and the relationship between A and B is many-to-many. so in the database design i have an associate table C between A and B. so in .dsv file i have C related to A as one-to-many and C related to B as one to many.

Now here how do i get data from both table A and table B?
i will put some more info, put the question in a different way.

Say I have 3 tables related as shown in the diagram below

Say

1) plan organization

2) plan

3) plan opportunity

in the ad hoc report I wanted to have data from plan organization and plan opportunity.

How will I do that?

plan organization >-|- Plan -|--< plan opportunity

one-to-many relation between plan and plan organization
one-to-many relation betweeb plan and plan opportunity

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific record is in a table that has multiple varchar fields? I'm trying to do some statistics on bytes/record and can't seem to find an easy way to see what the actual storage space is.
Thanks!
SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific recor
d is in a table that has multiple varchar fields? I'm trying to do some sta
tistics on bytes/record and can't seem to find an easy way to see what the a
ctual storage space is.
Thanks!SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific record is in a table that has multiple varchar fields? I'm trying to do some statistics on bytes/record and can't seem to find an easy way to see what the actual storage space is
Thanks!SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Activity Monitoring for a Single Table

We started experiencing a strange problem with one of our database last week. After analyzing the data and comparing with multiple backup sets, we found that data was randomly being deleted from a single table.
Is there any way to monitor a single table for activity, add, delete, change, etc? We want to do this for a day or so to identify the offending process.
Thanks,
William (Bill) Corder
wscorder@.hotmail.com
Sure, look up CREATE TRIGGER in Books Online. You can also run a trace
using Profiler (or better yet, a server-side trace without the GUI)...
"William (Bill) Corder" <wscorder@.hotmail.com> wrote in message
news:07955481-D3E1-48A9-AB7A-16ACA438B7DA@.microsoft.com...
We started experiencing a strange problem with one of our database last
week. After analyzing the data and comparing with multiple backup sets, we
found that data was randomly being deleted from a single table.
Is there any way to monitor a single table for activity, add, delete,
change, etc? We want to do this for a day or so to identify the offending
process.
Thanks,
William (Bill) Corder
wscorder@.hotmail.com
|||Aaron,
Thanks, I am setting up a trace to monitor the activity.
Bill
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e5TEw8BOIHA.3852@.TK2MSFTNGP06.phx.gbl...
> Sure, look up CREATE TRIGGER in Books Online. You can also run a trace
> using Profiler (or better yet, a server-side trace without the GUI)...
>
> "William (Bill) Corder" <wscorder@.hotmail.com> wrote in message
> news:07955481-D3E1-48A9-AB7A-16ACA438B7DA@.microsoft.com...
> We started experiencing a strange problem with one of our database last
> week. After analyzing the data and comparing with multiple backup sets,
> we found that data was randomly being deleted from a single table.
> Is there any way to monitor a single table for activity, add, delete,
> change, etc? We want to do this for a day or so to identify the offending
> process.
> --
> Thanks,
> William (Bill) Corder
> wscorder@.hotmail.com
>
>
|||William (Bill) Corder (wscorder@.hotmail.com) writes:
> We started experiencing a strange problem with one of our database last
> week. After analyzing the data and comparing with multiple backup sets,
> we found that data was randomly being deleted from a single table.
> Is there any way to monitor a single table for activity, add, delete,
> change, etc? We want to do this for a day or so to identify the
> offending process.
A trigger that logs the deleted rows together with columns populated with
the output from host_name(), app_name(), SYSTEM_USER, getdate() etc is
definitely what you need.
A server-side trace as Aaron suggested gives you the offending statement
more directly, so that is also a good idea. The log table gives you what
was deleted and when, and from that you can look up the trace.
I had to do this recently. Except that I did not come very far. That is,
before I deployed my trigger to production, I tested it, and when I ran
a delete operation from the GUI, I found that the log table said that
two rows had been deleted. Oh-oh. The culprit was a DELETE statement in
a procedure where I had forgotten the WHERE clause, blush!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The just re-did this with a trigger and it is more direct. I get exactly
what I need. All I had to do was create a separate log table to to hold the
data.
Thanks for the help and I will take a look at LogManager.

Thanks,
William (Bill) Corder
wscorder@.hotmail.com
"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam_@.upscene.com> wrote in
message news:#fQucLOOIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
>
> You probably won't get the old/new field values with that. This is easily
> done with triggers, as Aaron has pointed out.
> There are tools out there, which help you to setup a trigger-based
> auditing solution. For instance, our MSSQL LogManager product is one of
> them.
>
> --
> Best Regards,
> Thomas Steinmaurer
> LogManager Series - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database, MS SQL Server and
> NexusDB V2
> Upscene Productions
> http://www.upscene.com