Attach db without transaction log file

Hi!
Is it possible attach db without transaction log file?
With store procedure like sp_attach_db and sp_attach_single_file_db it isn't go. Server still looking for transaction log file.
Thanks your helpHi,
You can use sp_attach_single_file_db only on databases that have a single
log file.
Instead try to update the database status to emergency mode and create a new
database and use DTS to transafer all objects to new database.
Steps:
1. Update the database to Emergency mode
update sysdatabases set status=32768 where name=<name of the database>
2. Craete a new database
3. Use DTS to transfer all objects
Thanks
Hari
MCDBA
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||Try this:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly
SQL Server MVP
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||You must first: sp_detach_db
then: delete the ..._log.sfd
then: sp_attach_single_file_db
Bye
Uwe

Assigning a variable a table value

Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.

So, I declare a variable:
DECLARE @.variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.

I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.

Thanks in advance for any help

You need to use Select. You can assign multiple variables at the same time, but you cannot mix assignments with result sets. You can use either Set or Select to assign literal or constant values to a variable. Set only allows one variable at a time.

You can also use variables for values in a select statement.

Here is a full example:

Declare @.variable int,

@.pkey int

Set @.pkey = 42

Select @.variable = col1

From myTable

Where pkCol = @.pkey|||Thanks for your time and help it worked perfect.

assign count value

Hi all,

How I can assign a COUNT result to a variable in store procedure like this:

Code Snippet

SELECT @.NumCalc=COUNT(*) AS NC
FROM (SELECT COUNT(*) AS N
FROM Trace
GROUP BY Ora) TraceTmp

thanks a lot

by hid

Hi all sorry but

SELECT @.NumCalc=COUNT(*) /*AS NC */
FROM (SELECT COUNT(*) AS N
FROM Trace
GROUP BY Ora) TraceTmp

Run !

Bye Bye Hid

|||

If you want save into @.NumCalc result of SELECT COUNT(*) AS N FROM Trace GROUP BY Ora, you could use following code:

Code Snippet

SELECT @.NumCalc= COUNT(*)

FROM #Trace

GROUP BY Ora

But if SELECT COUNT(*) AS N FROM Trace GROUP BY Ora returns more than one line, only last result will be saved in @.NumCalc.

If you want save into @.NumCalc number of lines returned by SELECT COUNT(*) AS N FROM Trace GROUP BY Ora, you could use following:

Code Snippet

select @.NumCalc = count(distinct ora) from #trace

|||

Are you getting an error message returned? There's actually nothing wrong with your query - as long as you declare @.NumCalc then it should work fine.

Incidentally, your query is equivalent to this:

SELECT @.NumCalc = COUNT(DISTINCT Ora)

FROM Trace

...which, personally, I find easier to read.

Chris

|||

You code will not execute BECAUSE you cannot BOTH assign a aggregrate to a variable AND give it an ALIAS.

@.NumCalc=COUNT(*) AS NC

Remove the [ AS NC ] and it will execute just fine.

However, it would be easier to read if written as:

Code Snippet

SELECT @.NumCalc = count(DISTINCT Ora)
FROM Trace

|||

Arnie,

I see that this solution have been recommended several times. It will work just if column [Ora] does not accept NULL.

Code Snippet

select

count(distinct c1)

from

(

select null as c1

union all

select 1

) as t

select

count(*)

from

(

select

count(*) as cnt

from

(

select null as c1

union all

select 1

) as t1

group by

c1

) as t2

AMB

|||

'Hunchback'

I guess I didn't get your point. The OP asked for help with [ SELECT count(*) ]. In giving him/her the 'benefit of the doubt', I assume that if NULL is not to be included in the count, the OP will later clarify.

And yes, you are correct, there were previous variations of the same suggestion. I was not offering anything new EXCEPT for an explanition about why the OP's code excerpt would not work. (Actually, one post told the OP that there was "nothing wrong with your query", when in fact, it just will not execute as presented.

|||

Arnie,

> I assume that if NULL is not to be included in the count, the OP will later clarify.

The OP is counting the number of rows produced by a grouping by [Ora], so changing it to count(distinct [Ora]) could not produce the same result y [Ora] allows NULL. That was my point and was reproduced with the attached script.

AMB

|||Thanks for the clarification -I overlooked that point. I appreciate that you corrected my mistake.|||

Arnie said:

'Actually, one post told the OP that there was "nothing wrong with your query", when in fact, it just will not execute as presented.'

Yes, that was me. It seems that the OP had found a solution and subsequently corrected their query in a follow-up post, to which I was replying. The second query has the alias commented out so it will work as posted and, therefore, my statement is correct.

Chris

|||

Yes, I agree. Yet, it did seem confusing since due to forum latency it first appeared to me that you were responding to the original post. Even after the thread caught up, it was difficult to determine which post you responded to.

I should not have included that comment -it didn't add any value to the discussion. My regrets.

Assign a string to a variable

Hi
I'm using a varchar variable (@.Cadena) to store a select statement.
--
declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
set @.quincena = 24
set @.anio = 2004
set @.numeroempleado = 589
SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
WHEN 1 THEN ImpQuin01
WHEN 2 THEN ImpQuin02
WHEN 3 THEN ImpQuin03
WHEN 4 THEN ImpQuin04
WHEN 5 THEN ImpQuin05
WHEN 6 THEN ImpQuin06
WHEN 7 THEN ImpQuin07
WHEN 8 THEN ImpQuin08
WHEN 9 THEN ImpQuin09
WHEN 10 THEN ImpQuin10
WHEN 11 THEN ImpQuin11
WHEN 12 THEN ImpQuin12
WHEN 13 THEN ImpQuin13
WHEN 14 THEN ImpQuin14
WHEN 15 THEN ImpQuin15
WHEN 16 THEN ImpQuin16
WHEN 17 THEN ImpQuin17
WHEN 18 THEN ImpQuin18
WHEN 19 THEN ImpQuin19
WHEN 20 THEN ImpQuin20
WHEN 21 THEN ImpQuin21
WHEN 22 THEN ImpQuin22
WHEN 23 THEN ImpQuin23
WHEN 24 THEN ImpQuin24
END As SumaSDOS
FROM HisMovimientosQnal
WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
CAST(@.NumeroEmpleado AS varchar)
+ ' AND TipoIncidencia = ''SDOS'')'
EXEC (@.Cadena) --Execute the select statement
--
Till this point, the result is shown correctly.
I would like to store that result into another variable.
How can I do it?Gonzalo Torres wrote:
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = '
+
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
Exec @.variable =(@.Cadena)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Look up the topic sp_ExecuteSQL in SQL Server Books Online. It has some
examples which shows how to assign the results to an output variable.
Also, just by glancing through the code, it seems like you might have a
better/easier way of retrieving the results without using Dynamic SQL. But
then without further knowledge of your table structures & business rules, it
is hard to tell.
Anith|||I don't know your table structure, but this thing looks suspiciously
de-normalized...
Anyways, you might look into sp_executesql instead of EXEC. Safer and
shouldn't require all the CASTs, plus you can get a little performance boost
out of it since it compiles the query plan for parameterized queries. Check
it out in BOL.
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:O4nRqYCJFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
>