DSN goes to wrong database

Hello!

I set up a system DSN but it connects to the wrong database. I have set the default database correctly but it seems to ignore it.

If i switch to the correct BD like this: "USE myDB SELECT * FROM myTable" then it works fine.

Am i missing something?

this is my connection string [in my ASP page]:

"Driver={SQL Server};" &_
"Server=myServer;" &_
"DSN=myDSN;" &_
"UID=myUserName;" &_
"PWD=myPWD;"

Thanks!What database is it defaulting to ?|||Looks like you're mixing up the ODBC connect string data. Use one of these formats

To use the System DSN you created use
"Driver={SQL Server};" &_
"DSN=myDSN;" &_
"UID=myUserName;" &_
"PWD=myPWD;"
(may or maynot work)

To use a DSNless connection use
"Driver={SQL Server};" &_
"Database=myDB;" &_
"UID=myUserName;" &_
"PWD=myPWD;"
(Should work, but haven't tested)

I'm thinking it's probably trying to use the user's default database set up in SQL Server. Your alternative would be to go into use manager and change the default db (or tell your DBA to do it).

Originally posted by rnealejr
What database is it defaulting to ?|||yeah i think it has something to do with the particular user. a different username and password will change the default database.

i'll try that connection string. thank you!

the problem is i am the DBA at the moment. the ususal guy is away for awhile.

Drop column with default constraint

Hello
I have a table with column which have default constraint
(SQL server generated name of constraint).
Is there a way to drop this column without re-creating the table?
Example:
create table abc (xxx int, yyy int default 0);
alter table abc drop column yyy;
Thanks
TibXSee thread:
SQL Help with Drop Column
http://tinyurl.com/65nva
Bryce|||It's a good idea to give a default a meaningful name when you create
it. Query Analyzer will show you the name of the constraint in the
object browser (the name of a default begins with DF and includes the
table and column name). Then you can drop it in the usual way:
ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
ALTER TABLE abc DROP COLUMN yyy;
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
colA int,
colB varchar(25) default ('aaaaa')
)
go
declare @.sql nvarchar(4000)
declare @.cnstname sysname
select
@.cnstname = [name]
from
sysobjects as so
where
xtype = 'D'
and parent_obj = object_id('dbo.t')
and col_name(parent_obj, info) = 'colB'
if @.cnstname is not null
begin
set @.sql = N'alter table dbo.t drop constraint ' + @.cnstname
execute sp_executesql @.sql
end
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
alter table dbo.t
add constraint df_t_colB default ('aaaaa') for colB with values
go
alter table dbo.t
drop constraint df_t_colB
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
create default df_t_colB as 'aaaaa'
go
execute sp_bindefault df_t_colB, 't.colB'
go
execute sp_unbindefault 't.colB'
go
drop default df_t_colB
go
drop table dbo.t
go
AMB
"TibX" wrote:

> Hello
> I have a table with column which have default constraint
> (SQL server generated name of constraint).
> Is there a way to drop this column without re-creating the table?
>
> Example:
> create table abc (xxx int, yyy int default 0);
> alter table abc drop column yyy;
>
> Thanks
> TibX
>|||The problem is that I have several installations
with generated name of that default constraint
and I need drop column by a script.
TibX
David Portas wrote:
> It's a good idea to give a default a meaningful name when you create
> it. Query Analyzer will show you the name of the constraint in the
> object browser (the name of a default begins with DF and includes the
> table and column name). Then you can drop it in the usual way:
> ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
> ALTER TABLE abc DROP COLUMN yyy;
>|||Try this:
DECLARE @.df SYSNAME
SET @.df =
(SELECT OBJECT_NAME(cdefault)
FROM SYSCOLUMNS
WHERE id = OBJECT_ID('dbo.abc')
AND name = 'yyy')
IF @.df IS NOT NULL
BEGIN
EXEC sp_rename @.df, 'df_to_drop', 'OBJECT'
ALTER TABLE dbo.abc DROP CONSTRAINT df_to_drop
END
ALTER TABLE dbo.abc DROP COLUMN yyy
David Portas
SQL Server MVP
--|||Thanks for your solutions.
I use
select name from sysobjects ...
TibX

drilldown group expanded by default

Hello!!

I have a matrix in a report with groups and subgroups like this :

*group1

*subgroup1.1

item1

item2

etc.

I would like to show the group expanded by default so when I view the report I see:

*group1

subgroup1.1

*group2

subgroup 2.1

Right now I've played with InitialToggleState and visibility->Hidden properties but I don′t know how to do it....help me please

Thanks

You need to set the visibility of the inner group to true. Right click and edit the group in the matrix.|||

I published an article showing the steps for this (at least an example similar enough to be useful, hopefully):

http://www.databasejournal.com/features/mssql/article.php/3527321

This was for MSRS2000, but the logic should be good in SSRS2005.

Good Luck!

Bill

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office

wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17