renaming database

I tried to renaming database using query analyser of sql
server 2000 but I got this error msg.
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform
the operation.
My sysntax was
sp_renamedb 'old_dbname', 'new_dbname'
How to handle this? what could be the solution?
Thanx
Dave
hi dave,
make sure no-one else is connected to the database, including you.
you can try:
use master
go
exec sp_renamedb ..........
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
--if, still you get errors you can run following command,to disconnect other
users:
--note:it will rollback existing transaction
alter database <db_name> set single_user with rollback IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||still same proble, bro...
dave
>--Original Message--
>hi dave,
>make sure no-one else is connected to the database,
including you.
>you can try:
>use master
>go
>exec sp_renamedb ..........
>--or (if sql 2000) you can try
>alter database old_Database_name modify name =
new_database_name
>--if, still you get errors you can run following
command,to disconnect other
>users:
>--note:it will rollback existing transaction
>alter database <db_name> set single_user with rollback
IMMEDIATE
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Try this. Stop the SQL Server from SQL Server Service Manager and Start
again, and then try to rename it.
Rohtash Kapoor
http://www.sqlmantra.com
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28f5f01c4656d$493c0f50$a601280a@.phx.gbl...[vbcol=seagreen]
> still same proble, bro...
> dave
> including you.
> new_database_name
> command,to disconnect other
> IMMEDIATE
|||Hi,
Follow the below steps to rename a database in SQL server 2000.
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
3. Set to multi user mode
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28e1e01c4654c$a8962ab0$a601280a@.phx.gbl...
> I tried to renaming database using query analyser of sql
> server 2000 but I got this error msg.
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform
> the operation.
> My sysntax was
> sp_renamedb 'old_dbname', 'new_dbname'
> How to handle this? what could be the solution?
> Thanx
> Dave
|||all you can do is restart the sql server service, which will clear out all
the connection to the db, and run
use master
go
exec sp_renamedb --
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You could detach the database and then re-attach it with a different
name.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Renaming column

ALTER TABLE ISMPBI CHANGE `PBICCGRFNUM` `PBICCGRFNUM1` BIGINT NOT NULL;
this query to rename column name in my sql but not working in sql server
so what is exact syntax to rename columnSee the syntax for the ALTER TABLE command in the online manual (http://msdn2.microsoft.com/en-us/library/ms190273.aspx)

Btw: don't use the backticks. They are a MySQL "speciality" and do not work with any other DBMS (actually they are not needed in MySQL as well)|||MS SQL SERVER does not support RENAME syntax i cant find the syntax to rename column can any one give me exact syntax to rename column|||The ALTER TABLE documentation links to the sp_rename (http://msdn2.microsoft.com/en-us/library/ms188351.aspx) function:
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type
Example B: "renaming a column"|||thanks

here is exact syntax

EXEC sp_rename 'BFMENUDEFINITION.[GROUP]', 'MENUGROUP', 'COLUMN';

'BFMENUDEFINITION== tablename

GROUP == old column

'MENUGROUP'== new columnsql

Rename tables from query analyzer

I have SQL 2k and I would like to know if I can rename a table from SQL
query analyzer.
Thanks
DimitrisEXEC sp_rename 'customers', 'custs'
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>I have SQL 2k and I would like to know if I can rename a table from SQL
>query analyzer.
> Thanks
> Dimitris
>|||And how can this be done when in query analyzer the current DB is different
than the databe of table?
I have tried:
EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
'[FACOM].[dbo].[_FACAddressBook]'
but I get error:
Error: The qualified @.oldname references a database (FACOM) other than the
current database.
"Ryan" <Ryan_Waight@.nospam.hotmail.com>
news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> EXEC sp_rename 'customers', 'custs'
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>|||>From BOL
You can change the name of an object or data type in the current
database only.
Thanks
Ajay Rengunthwar
MCDBA|||sp_rename works with objects in the current database only
To switch database :-
USE dbname
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>|||To add to the other responses, you can also execute sp_rename (and other
system procs) in the context of another database by qualifying with the
target database name:
EXEC FACOM..sp_rename '[dbo].[AddressBook]', '_FACAddressBook'
Also, specify *only* the new object name as the @.newname parameter value.
Do not specify the owner and do not enclose the name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>

Rename tables from query analyzer

I have SQL 2k and I would like to know if I can rename a table from SQL
query analyzer.
Thanks
DimitrisEXEC sp_rename 'customers', 'custs'
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>I have SQL 2k and I would like to know if I can rename a table from SQL
>query analyzer.
> Thanks
> Dimitris
>|||And how can this be done when in query analyzer the current DB is different
than the databe of table?
I have tried:
EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
'[FACOM].[dbo].[_FACAddressBook]'
but I get error:
Error: The qualified @.oldname references a database (FACOM) other than the
current database.
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> EXEC sp_rename 'customers', 'custs'
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>>I have SQL 2k and I would like to know if I can rename a table from SQL
>>query analyzer.
>> Thanks
>> Dimitris
>|||>From BOL
You can change the name of an object or data type in the current
database only.
Thanks
Ajay Rengunthwar
MCDBA|||sp_rename works with objects in the current database only
To switch database :-
USE dbname
--
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> EXEC sp_rename 'customers', 'custs'
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
>> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>>I have SQL 2k and I would like to know if I can rename a table from SQL
>>query analyzer.
>> Thanks
>> Dimitris
>>
>|||To add to the other responses, you can also execute sp_rename (and other
system procs) in the context of another database by qualifying with the
target database name:
EXEC FACOM..sp_rename '[dbo].[AddressBook]', '_FACAddressBook'
Also, specify *only* the new object name as the @.newname parameter value.
Do not specify the owner and do not enclose the name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:eWDOXvMeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> And how can this be done when in query analyzer the current DB is
> different than the databe of table?
> I have tried:
>
> EXEC sp_rename '[FACOM].[dbo].[AddressBook]',
> '[FACOM].[dbo].[_FACAddressBook]'
> but I get error:
> Error: The qualified @.oldname references a database (FACOM) other than the
> current database.
>
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:%23mHbkqMeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> EXEC sp_rename 'customers', 'custs'
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
>> news:OnIXVmMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>>I have SQL 2k and I would like to know if I can rename a table from SQL
>>query analyzer.
>> Thanks
>> Dimitris
>>
>

Rename File Name Attachment

I Use Extended Store Procedure to Send Email.
I want to Rename Attachment File Name in result of Query.
What shold I do?Hi Arief,
Why don't you rename the file attachment using DOS command, before attaching
it using xp_sendmail?
- - - - - - - - -
Thanks
Yogishsql

Rename File Name Attachment

I Use Extended Store Procedure to Send Email.
I want to Rename Attachment File Name in result of Query.
What shold I do?
Hi Arief,
Why don't you rename the file attachment using DOS command, before attaching
it using xp_sendmail?
- - - - - - - - -
Thanks
Yogish

Rename File Name Attachment

I Use Extended Store Procedure to Send Email.
I want to Rename Attachment File Name in result of Query.
What shold I do?Hi Arief,
Why don't you rename the file attachment using DOS command, before attaching
it using xp_sendmail?
--
- - - - - - - - -
Thanks
Yogish

Rename Columns via Query

I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to use
a view to display the ID column,and only one other, which will vary depending
on my requirements. I have another table that contains only one row (and is
updated regularly) with the column name (ColID) I'm trying to retieve.
Can anyone tell me how I can pass the value of ColID into the column name in
my view?
I hope this is clear. Thanks in anticipation...
Take a look at this example:
USE Northwind
CREATE TABLE QueryColumn
(qc sysname)
GO
INSERT INTO QueryColumn VALUES(N'CompanyName')
DECLARE @.q varchar(8000)
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
UPDATE QueryColumn SET qc=N'Phone'
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
GO
DROP TABLE QueryColumn
GO
Do please also read the article at
http://www.sommarskog.se/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"MillionChads" <MillionChads@.discussions.microsoft.com> wrote in message
news:ABCE5205-361F-4BB8-8DAC-DCBEF6DACB4C@.microsoft.com...
> I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to
use
> a view to display the ID column,and only one other, which will vary
depending
> on my requirements. I have another table that contains only one row (and
is
> updated regularly) with the column name (ColID) I'm trying to retieve.
> Can anyone tell me how I can pass the value of ColID into the column name
in
> my view?
> I hope this is clear. Thanks in anticipation...

Rename a table in Microsoft SQL Server 2005

hi,
Can you give the query in MS SQL Server 2005 which can be used to rename a existing table in database.
Thanks in advance,SELECT *
INTO NewTable
FROM OldTable

DROP TABLE OldTable

Keep in mind that you may need to rebuild constraints depending on the table. You can also use sp_rename, which I believe does the same thing as the sql above.

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
> > I have a query which returns the following results. What i want to do
> > is remove any rows where col1 does not appear in col2. e.g. "107". As
> > well as this i want to remove any rows that are affected by this, e.g.
> > "109" as once 107 goes, 109 does not appear in col2.
> > Thanks in advance.
> > col1 col2
> > 1 108
> > 1 112
> > 1 115
> > 1 116
> > 1 117
> > 1 118
> > 107 109
> > 108 114
> > 109 110
> > 110 111
> > 112 113
> > 118 119
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1108
1112
1115
1116
1117
1118
107109
108114
109110
110111
112113
118119
On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp
|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select
1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing null values from a result set

Hi,

I have following query which is returing null values along with other values. Could you please tell me how I can restrict to not to return null values

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Thanks,

Prudhvi

Prud,

I'm assuming that you want to filter out TotalHours

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Where [Measures].[Total Hours] is not null

Ham

|||

Thanks Ham. I did tried this earlier but got the following error message

Executing the query ...
Query (5, 1) The IS function expects a member expression for the 2 argument. A string or numeric expression was used.
Execution complete

|||

Prud,

Are you using SQL 2000 or SQL 2005? The "is not null" is a valid for SQL in the Where clause.

Ham

|||

SSAS 2005 introduces a HAVING clause:

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]

HAVING [Measures].[Total Hours] <> 0 ON 1
FROM[Labor Metrics]

Or, you can use Filter().

|||

Thanks Teo, it worked.

Removing headers from sql output

I'm writing a query which creates a script that I then want to be able to run, but can't get rid of the headers.

eg. my query is similar to this:
USE master
go
set nocount on
go
select 'restore database @.dbname = N' + "'" + db_name() + "'" + ','
go
select '@.filename' +convert(varchar(5),fileid)+ ' = N'+convert(varchar(50),filename)+',' from sysfiles
go

The output is similar this:
------------------------------------------------
restore database @.dbname = N'master',

---------------------
@.filename1 = Nd:\sysdata\SQL2000\MSSQL${instancename}\data\mast er.mdf,
@.filename2 = Nd:\sysdata\SQL2000\MSSQL${instancename}\data\mast log.ld,

I want to get rid of all the '---' and lines between the output.

Can anyone help please?Hello,

which database do you use ? Looks like MSSQL ?

Regards

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||I'm writing the script for SQL2000 -

Removing delimiters

Hi

I have a database in which the
bankno is 29 and
acctno is 01-001-0000002

I need to write a query to get the data as "29010010000002" that is to concatenate both the fields and take out the delimiters in the second field. I know how to concatenate both
i.e.

select bankno + acctno as acctnos from table;

But that will give me 2901-001-0000002. I need to modify the query so that I can remove the delimiters in the second column.
Can anyone give me an idea.

Thanx in advance.select
replace(bankno + acctno,'-','')
as acctnos from table;

removing dash lines in query result

Is there any way to remove the dash line in a query result?
use pubs
select emp_id, fname, lname from employee
emp_id fname lname
-- -- --
A-C71970F Aria Cruz
A-R89858F Annette Roulet
AMD15433F Ann Devon
ARD36773F Anabela Domingues
Thanks,
DougTo copy and paste the results ?
Switch to grid format (CTRL + D)
http://sqlservercode.blogspot.com/
"Doug Hood" wrote:

> Is there any way to remove the dash line in a query result?
> use pubs
> select emp_id, fname, lname from employee
> emp_id fname lname
> -- -- --
> A-C71970F Aria Cruz
> A-R89858F Annette Roulet
> AMD15433F Ann Devon
> ARD36773F Anabela Domingues
> Thanks,
> Doug|||Are you copying and pasting the results somewhere else? Try results to grid
instead of results to text. Much more useful for pasting into Excel, at
least.
"Doug Hood" <Doug Hood@.discussions.microsoft.com> wrote in message
news:60C4473C-FF97-4E45-B8B6-D637EE0E12D5@.microsoft.com...
> Is there any way to remove the dash line in a query result?
> use pubs
> select emp_id, fname, lname from employee
> emp_id fname lname
> -- -- --
> A-C71970F Aria Cruz
> A-R89858F Annette Roulet
> AMD15433F Ann Devon
> ARD36773F Anabela Domingues
> Thanks,
> Doug|||No, This ends up as the body of an email. So I need to remove them in the
query.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you copying and pasting the results somewhere else? Try results to gr
id
> instead of results to text. Much more useful for pasting into Excel, at
> least.
>
>
> "Doug Hood" <Doug Hood@.discussions.microsoft.com> wrote in message
> news:60C4473C-FF97-4E45-B8B6-D637EE0E12D5@.microsoft.com...
>
>|||Doug,
Might consider using Reporting Services. Much more flexible and robust for
generating reports including via email.
HTH
Jerry
"Doug Hood" <DougHood@.discussions.microsoft.com> wrote in message
news:D544663C-FA83-4AF7-9BAC-24A6AAE1DB91@.microsoft.com...
> No, This ends up as the body of an email. So I need to remove them in the
> query.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||In query analyzer go to tools options results and un check print column head
ers
"Doug Hood" wrote:
> No, This ends up as the body of an email. So I need to remove them in the
> query.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Thanks but that turns off both the dashed line AND the column names. I want
to just remove the dashed line.
Doug
"Lontae Jones" wrote:
> In query analyzer go to tools options results and un check print column he
aders
> "Doug Hood" wrote:
>|||I think you're out of luck. Just delete the line after pasting the data into
your email.
That's assuming that you are using QA to run this query. It's not the query
that's inserting these blank lines: it's QA. You could alway write your own
query execution tool in your favorite programming language and have complete
control over the formatting of the query results.
Bob Barrows
Doug Hood wrote:
> Thanks but that turns off both the dashed line AND the column names.
> I want to just remove the dashed line.
> Doug
> "Lontae Jones" wrote:
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Well then I'll say it. You're stuck with them. :-(
You just don't have much control over how QA returns the data.
(and it looks crappy anyway)
The best way I can think of is Jerry's suggestion,
Reporting services or another reporting tool.
"Doug Hood" <DougHood@.discussions.microsoft.com> wrote in message
news:282F0632-3D60-4B31-A8BD-AF9318D986C2@.microsoft.com...
> Thanks but that turns off both the dashed line AND the column names. I
> want
> to just remove the dashed line.
> Doug
> "Lontae Jones" wrote:
>