datalength( '' + space(5) + '' ) = 7 !??

Hello,
What configuration could cause a database to substitute a space character
for empty strings ?
Two databases on the same server. In one the statement select '' + '123' +
'' yields " 123 " and on the other the same statement yields "123".
Thanks for any insight
APSET ANSI_PADDING
--
HTH
Ryan Waight, MCDBA, MCSE
"Antoine Perret" <antoine@.bow.ch> wrote in message
news:eUi$EfYjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What configuration could cause a database to substitute a space character
> for empty strings ?
> Two databases on the same server. In one the statement select '' + '123' +
> '' yields " 123 " and on the other the same statement yields "123".
> Thanks for any insight
> AP
>|||Thank-you sir!|||Hi Antoine,
The database that yields " 123 " is probably on a compatibility level of 60
or 65.
Run
EXEC sp_dbcmptlevel [ [ @.dbname = ] name ]
to check.
--
Jacco Schalkwijk
SQL Server MVP
"Antoine Perret" <antoine@.bow.ch> wrote in message
news:eUi$EfYjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What configuration could cause a database to substitute a space character
> for empty strings ?
> Two databases on the same server. In one the statement select '' + '123' +
> '' yields " 123 " and on the other the same statement yields "123".
> Thanks for any insight
> AP
>|||Yes that is it.
Thanks
Funny I don't recall that feature on older versions...

Data-Driven Subscription - Empty Field List on Step 4

I am attempting to create a data-driven subscription. I step through the
wizard, paste in the query, validate and click next. When I get to the screen
to map the fields from the database table to the fields in the subscription
(to, cc, reply-to, etc).
There are no fields in the drop-down. I have tried numerous different
queries, that I know work when I run them in a report, but don't seem to want
to populate the field list. The source database is Teradata, if that makes a
difference.
There is no error, and I can't seem to find any log entries, so I am not
sure where to turn. Any Ideas?
SteveUPDATED: I have confirmed that, in fact the data-driven subscription will
pull fields from SQL Server, so the issue seems to be with the Teradata
connection.
Any help?
"Steve Muise" wrote:
> I am attempting to create a data-driven subscription. I step through the
> wizard, paste in the query, validate and click next. When I get to the screen
> to map the fields from the database table to the fields in the subscription
> (to, cc, reply-to, etc).
> There are no fields in the drop-down. I have tried numerous different
> queries, that I know work when I run them in a report, but don't seem to want
> to populate the field list. The source database is Teradata, if that makes a
> difference.
> There is no error, and I can't seem to find any log entries, so I am not
> sure where to turn. Any Ideas?
> Steve

DataDir empty

Hi,

The DataDir content has been removed on my production server and I lost all my cubes.

Any ideas to explain this issue ? Do i only need to recreate my cubes or this directory contains other files necessary to run SS2005 ?

Cheers, JL

If you restart the SSAS service with an empty DataDir it will add all the required system files and startup with no databases installed. Then you will either need to redeploy your project and reprocess, or restore a backup.|||

furmangg,

Thanks for your reply ; it was due to a disk issue but things are ok now.

Cheers, JL.

DataDir empty

Hi,

The DataDir content has been removed on my production server and I lost all my cubes.

Any ideas to explain this issue ? Do i only need to recreate my cubes or this directory contains other files necessary to run SS2005 ?

Cheers, JL

If you restart the SSAS service with an empty DataDir it will add all the required system files and startup with no databases installed. Then you will either need to redeploy your project and reprocess, or restore a backup.|||

furmangg,

Thanks for your reply ; it was due to a disk issue but things are ok now.

Cheers, JL.

Databases folder empty in Enterprise Manager

Hi all,
I have installed MSDE 2000 SP3a on a workstation, as well as SQL Server
client tools on the same machine. When I open Enterprise Manager, the
Databases folder is empty. Even the master database plus other default
databases (msdb, ...) are not shown. I have made sure the "show system
databases and objects" setting is checked. This happens whether I
connect to the MSDE server using the sa account or Windows
authentication. Also the Query Analyzer utility doesn't work as well.
It opens, but the command window has no place to type SQL commands, and
the Object Browser doesn't show any databases.
I also have a number of old databases (.mdf and .ldf files) that I want
to attach to this instance. These databases have been originally
copied from another machine, and have been working on this machine with
a previous installation of SQL Server Personal Edition. When I open
the attach databases window in Enterprise Manager, and select a mdf
file, and click OK, at first nothing happens (no feedback, and the
dialog even doesn't go away) but clicking OK a second time gives an
error message stating that a database with the same name already exists
(and yet the dialog remains open.) Any further actions will take
Enterprise Manager down by a crash.
Then I tried connecting to MSDE using osql.exe, and a "USE master;
SELECT * FROM sysdatabases;" shows the attached db as well, and I can
even switch to that db and select/insert/update/delete data in it. And
using the sp_attach_db stored procedure in osql.exe works without any
errors. But still none of the databases listed in sysdatabases is
shown in Enterprise Manager.
I could go on working in osql.exe but I'm not very knowledgable in
working with MSDE using SQL commands, and I'd rather do the same in
Enterprise Manager. I have the exact same setup on other workstations
and they work beautifully. Upgrding MSDE to SP4 and client tools to
SP3 didn't solve anything as well.
Can someone help me there please?
Thanks!
Ehsan
The MSDE edition of SQL Server was developed to answer the needs of some
people who don't want to spend money on buying SQL Server. MSDE is available
to anyone but has some restrictions, some of them are:
- 2 GB maximum capacity
- Doesn't work with Graphic Administration Tools like Enterprise Manager.
The administration must be made by code (T-SQL).
I don't know the kind of needs you have but try to build an Access project
and link the database tables to it. You can build views directly and have
access to table objects.
"ehsan.akhgari@.gmail.com" wrote:

> Hi all,
> I have installed MSDE 2000 SP3a on a workstation, as well as SQL Server
> client tools on the same machine. When I open Enterprise Manager, the
> Databases folder is empty. Even the master database plus other default
> databases (msdb, ...) are not shown. I have made sure the "show system
> databases and objects" setting is checked. This happens whether I
> connect to the MSDE server using the sa account or Windows
> authentication. Also the Query Analyzer utility doesn't work as well.
> It opens, but the command window has no place to type SQL commands, and
> the Object Browser doesn't show any databases.
> I also have a number of old databases (.mdf and .ldf files) that I want
> to attach to this instance. These databases have been originally
> copied from another machine, and have been working on this machine with
> a previous installation of SQL Server Personal Edition. When I open
> the attach databases window in Enterprise Manager, and select a mdf
> file, and click OK, at first nothing happens (no feedback, and the
> dialog even doesn't go away) but clicking OK a second time gives an
> error message stating that a database with the same name already exists
> (and yet the dialog remains open.) Any further actions will take
> Enterprise Manager down by a crash.
> Then I tried connecting to MSDE using osql.exe, and a "USE master;
> SELECT * FROM sysdatabases;" shows the attached db as well, and I can
> even switch to that db and select/insert/update/delete data in it. And
> using the sp_attach_db stored procedure in osql.exe works without any
> errors. But still none of the databases listed in sysdatabases is
> shown in Enterprise Manager.
> I could go on working in osql.exe but I'm not very knowledgable in
> working with MSDE using SQL commands, and I'd rather do the same in
> Enterprise Manager. I have the exact same setup on other workstations
> and they work beautifully. Upgrding MSDE to SP4 and client tools to
> SP3 didn't solve anything as well.
> Can someone help me there please?
> Thanks!
> Ehsan
>
|||Thanks for your reply,
I did not know about this restriction for MSDE. That explains
everything now.
Ehsan

Databases folder empty in Enterprise Manager

Hi all,
I have installed MSDE 2000 SP3a on a workstation, as well as SQL Server
client tools on the same machine. When I open Enterprise Manager, the
Databases folder is empty. Even the master database plus other default
databases (msdb, ...) are not shown. I have made sure the "show system
databases and objects" setting is checked. This happens whether I
connect to the MSDE server using the sa account or Windows
authentication. Also the Query Analyzer utility doesn't work as well.
It opens, but the command window has no place to type SQL commands, and
the Object Browser doesn't show any databases.
I also have a number of old databases (.mdf and .ldf files) that I want
to attach to this instance. These databases have been originally
copied from another machine, and have been working on this machine with
a previous installation of SQL Server Personal Edition. When I open
the attach databases window in Enterprise Manager, and select a mdf
file, and click OK, at first nothing happens (no feedback, and the
dialog even doesn't go away) but clicking OK a second time gives an
error message stating that a database with the same name already exists
(and yet the dialog remains open.) Any further actions will take
Enterprise Manager down by a crash.
Then I tried connecting to MSDE using osql.exe, and a "USE master;
SELECT * FROM sysdatabases;" shows the attached db as well, and I can
even switch to that db and select/insert/update/delete data in it. And
using the sp_attach_db stored procedure in osql.exe works without any
errors. But still none of the databases listed in sysdatabases is
shown in Enterprise Manager.
I could go on working in osql.exe but I'm not very knowledgable in
working with MSDE using SQL commands, and I'd rather do the same in
Enterprise Manager. I have the exact same setup on other workstations
and they work beautifully. Upgrding MSDE to SP4 and client tools to
SP3 didn't solve anything as well.
Can someone help me there please?
Thanks!
EhsanThe MSDE edition of SQL Server was developed to answer the needs of some
people who don't want to spend money on buying SQL Server. MSDE is available
to anyone but has some restrictions, some of them are:
- 2 GB maximum capacity
- Doesn't work with Graphic Administration Tools like Enterprise Manager.
The administration must be made by code (T-SQL).
I don't know the kind of needs you have but try to build an Access project
and link the database tables to it. You can build views directly and have
access to table objects.
"ehsan.akhgari@.gmail.com" wrote:
> Hi all,
> I have installed MSDE 2000 SP3a on a workstation, as well as SQL Server
> client tools on the same machine. When I open Enterprise Manager, the
> Databases folder is empty. Even the master database plus other default
> databases (msdb, ...) are not shown. I have made sure the "show system
> databases and objects" setting is checked. This happens whether I
> connect to the MSDE server using the sa account or Windows
> authentication. Also the Query Analyzer utility doesn't work as well.
> It opens, but the command window has no place to type SQL commands, and
> the Object Browser doesn't show any databases.
> I also have a number of old databases (.mdf and .ldf files) that I want
> to attach to this instance. These databases have been originally
> copied from another machine, and have been working on this machine with
> a previous installation of SQL Server Personal Edition. When I open
> the attach databases window in Enterprise Manager, and select a mdf
> file, and click OK, at first nothing happens (no feedback, and the
> dialog even doesn't go away) but clicking OK a second time gives an
> error message stating that a database with the same name already exists
> (and yet the dialog remains open.) Any further actions will take
> Enterprise Manager down by a crash.
> Then I tried connecting to MSDE using osql.exe, and a "USE master;
> SELECT * FROM sysdatabases;" shows the attached db as well, and I can
> even switch to that db and select/insert/update/delete data in it. And
> using the sp_attach_db stored procedure in osql.exe works without any
> errors. But still none of the databases listed in sysdatabases is
> shown in Enterprise Manager.
> I could go on working in osql.exe but I'm not very knowledgable in
> working with MSDE using SQL commands, and I'd rather do the same in
> Enterprise Manager. I have the exact same setup on other workstations
> and they work beautifully. Upgrding MSDE to SP4 and client tools to
> SP3 didn't solve anything as well.
> Can someone help me there please?
> Thanks!
> Ehsan
>|||Thanks for your reply,
I did not know about this restriction for MSDE. That explains
everything now.
Ehsan

Databases folder empty in Enterprise Manager

Hi all,
I have installed MSDE 2000 SP3a on a workstation, as well as SQL Server
client tools on the same machine. When I open Enterprise Manager, the
Databases folder is empty. Even the master database plus other default
databases (msdb, ...) are not shown. I have made sure the "show system
databases and objects" setting is checked. This happens whether I
connect to the MSDE server using the sa account or Windows
authentication. Also the Query Analyzer utility doesn't work as well.
It opens, but the command window has no place to type SQL commands, and
the Object Browser doesn't show any databases.
I also have a number of old databases (.mdf and .ldf files) that I want
to attach to this instance. These databases have been originally
copied from another machine, and have been working on this machine with
a previous installation of SQL Server Personal Edition. When I open
the attach databases window in Enterprise Manager, and select a mdf
file, and click OK, at first nothing happens (no feedback, and the
dialog even doesn't go away) but clicking OK a second time gives an
error message stating that a database with the same name already exists
(and yet the dialog remains open.) Any further actions will take
Enterprise Manager down by a crash.
Then I tried connecting to MSDE using osql.exe, and a "USE master;
SELECT * FROM sysdatabases;" shows the attached db as well, and I can
even switch to that db and select/insert/update/delete data in it. And
using the sp_attach_db stored procedure in osql.exe works without any
errors. But still none of the databases listed in sysdatabases is
shown in Enterprise Manager.
I could go on working in osql.exe but I'm not very knowledgable in
working with MSDE using SQL commands, and I'd rather do the same in
Enterprise Manager. I have the exact same setup on other workstations
and they work beautifully. Upgrding MSDE to SP4 and client tools to
SP3 didn't solve anything as well.
Can someone help me there please?
Thanks!
EhsanThe MSDE edition of SQL Server was developed to answer the needs of some
people who don't want to spend money on buying SQL Server. MSDE is available
to anyone but has some restrictions, some of them are:
- 2 GB maximum capacity
- Doesn't work with Graphic Administration Tools like Enterprise Manager.
The administration must be made by code (T-SQL).
I don't know the kind of needs you have but try to build an Access project
and link the database tables to it. You can build views directly and have
access to table objects.
"ehsan.akhgari@.gmail.com" wrote:

> Hi all,
> I have installed MSDE 2000 SP3a on a workstation, as well as SQL Server
> client tools on the same machine. When I open Enterprise Manager, the
> Databases folder is empty. Even the master database plus other default
> databases (msdb, ...) are not shown. I have made sure the "show system
> databases and objects" setting is checked. This happens whether I
> connect to the MSDE server using the sa account or Windows
> authentication. Also the Query Analyzer utility doesn't work as well.
> It opens, but the command window has no place to type SQL commands, and
> the Object Browser doesn't show any databases.
> I also have a number of old databases (.mdf and .ldf files) that I want
> to attach to this instance. These databases have been originally
> copied from another machine, and have been working on this machine with
> a previous installation of SQL Server Personal Edition. When I open
> the attach databases window in Enterprise Manager, and select a mdf
> file, and click OK, at first nothing happens (no feedback, and the
> dialog even doesn't go away) but clicking OK a second time gives an
> error message stating that a database with the same name already exists
> (and yet the dialog remains open.) Any further actions will take
> Enterprise Manager down by a crash.
> Then I tried connecting to MSDE using osql.exe, and a "USE master;
> SELECT * FROM sysdatabases;" shows the attached db as well, and I can
> even switch to that db and select/insert/update/delete data in it. And
> using the sp_attach_db stored procedure in osql.exe works without any
> errors. But still none of the databases listed in sysdatabases is
> shown in Enterprise Manager.
> I could go on working in osql.exe but I'm not very knowledgable in
> working with MSDE using SQL commands, and I'd rather do the same in
> Enterprise Manager. I have the exact same setup on other workstations
> and they work beautifully. Upgrding MSDE to SP4 and client tools to
> SP3 didn't solve anything as well.
> Can someone help me there please?
> Thanks!
> Ehsan
>|||Thanks for your reply,
I did not know about this restriction for MSDE. That explains
everything now.
Ehsan

Database with empty name

I somehow managed to get a database object with an empty name into one of my instances of SQL Server. I can't delete it or otherwise work with it (I've tried renaming it so I could delete it) without getting an error message because of the empty name. I've tried these things (as well as a "Drop Database" query with no name, which I didn't expect to work and it didn't) from both the SQL 2000 and 2005 environments. Has anyone come across this before? I don't suppose this database is hurting anything but I'd still like to get rid of it. The objects in it make it appear to be a copy of the Master database - it also doesn't show up where I would expect it to in the Data folder for this instance.

Thanks,

Dave

Dave,

I never come across this problem. Whenever I have to drop or delete a database, I relatively get that done with ease. In case the database name is empty, which is very weird case, I will suggest you to try deleting using Enterprise manager. It would have been more easy to address your issue if you could specify what error message did you get when you tried renaming or using "Drop Database" query. But I am pretty sure you can delete your database using Enterprise Manager. However, you have to make sure before deleting your database that the database is not currently running. Most easy way to do that is to stop the Sql Server Service Manager from your task bar.

I hope it works.

Ujjwal Kaji

|||

Thanks for the reply. I tried deleting it in Enterprise Manager and get the same Error Message:

Error 21776: [SQL-DMO]The name '' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

Thanks for any insight into this.

-Dave

|||

Hi,

before messing with the system tables, try to do a:

1. DROP DATABASE []

2. sp_renamedb '','MyDatabasetoDelete'

or (posted by Tom Moreau)

3. (can vary to SQL server 2k5, because sysdatabases is sys.databases now)

sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases
set
name = 'MyDB'
where
name = ''
go
sp_configure 'allow', 1
go
reconfigure with override
go

Stop and start SQL Server.

Check after each steps if the database is till existing, sometime SQl Server doesn′t know how to handle return code for this strange situation and gives back a weird error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the reply. I guess somehow it was a system database because your 3rd suggestion worked perfectly. Very strange...

Thanks for your help.

Dave

|||Thank you for the post, option 3 solved my empty database name as well.

Database with empty name

I somehow managed to get a database object with an empty name into one of my instances of SQL Server. I can't delete it or otherwise work with it (I've tried renaming it so I could delete it) without getting an error message because of the empty name. I've tried these things (as well as a "Drop Database" query with no name, which I didn't expect to work and it didn't) from both the SQL 2000 and 2005 environments. Has anyone come across this before? I don't suppose this database is hurting anything but I'd still like to get rid of it. The objects in it make it appear to be a copy of the Master database - it also doesn't show up where I would expect it to in the Data folder for this instance.

Thanks,

Dave

Dave,

I never come across this problem. Whenever I have to drop or delete a database, I relatively get that done with ease. In case the database name is empty, which is very weird case, I will suggest you to try deleting using Enterprise manager. It would have been more easy to address your issue if you could specify what error message did you get when you tried renaming or using "Drop Database" query. But I am pretty sure you can delete your database using Enterprise Manager. However, you have to make sure before deleting your database that the database is not currently running. Most easy way to do that is to stop the Sql Server Service Manager from your task bar.

I hope it works.

Ujjwal Kaji

|||

Thanks for the reply. I tried deleting it in Enterprise Manager and get the same Error Message:

Error 21776: [SQL-DMO]The name '' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

Thanks for any insight into this.

-Dave

|||

Hi,

before messing with the system tables, try to do a:

1. DROP DATABASE []

2. sp_renamedb '','MyDatabasetoDelete'

or (posted by Tom Moreau)

3. (can vary to SQL server 2k5, because sysdatabases is sys.databases now)

sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases
set
name = 'MyDB'
where
name = ''
go
sp_configure 'allow', 1
go
reconfigure with override
go

Stop and start SQL Server.

Check after each steps if the database is till existing, sometime SQl Server doesn′t know how to handle return code for this strange situation and gives back a weird error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the reply. I guess somehow it was a system database because your 3rd suggestion worked perfectly. Very strange...

Thanks for your help.

Dave

|||Thank you for the post, option 3 solved my empty database name as well.