Absurd project: records

Showing posts with label records. Show all posts

Wednesday, March 28, 2012

Help for Newbie!

I have a table with 10 records: 10, 9, 8, 7 ... and so on. I want to SELECT the top 3 records starting with the second record on the list (which in this case is 9.) So the result should be 9, 8, and 7. What would be the correct SQL statement for this?

I tried "SELECT TOP 3 * FROM tableXXX WHERE (FieldID<>(SELECT TOP 1 * FROM tableXXX ORDER BY FieldID DESC) ORDER BY FieldID DESC" but sure as heck it didn't work. Can anyone help?drop the * on the 2nd select and only qry for the pkval :

SELECT Top 3 *
FROM Contacts
WHERE idCont <> (SELECT Top 1 idCont FROM Contacts)|||Thank you, Mike. It works beautifully! The complete statement is:

SELECT Top 3 *
FROM tbl_XXX
WHERE (idXXX <>(SELECT Top 1 idXXX FROM tbl_XXX ORDER BY idXXX DESC))
ORDER BY idXXX DESC

Again, thank you. You are great help.

Monday, March 26, 2012

Help Finding records with matching columns

I'm running MS SQL 7.0.
I've been trying to figure this out for a couple of days without success.
I need to change the values in column6 of a table to NULL where the value of
column6 is not NULL, and the value of column5 is 97, and the values of
column1, column2, column3, and column4 each match the corresponding columns
in the other row.
In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
match the same columns in row 2, and the value of column6 in row 1 is 97 and
in row2 is something else (it doesn't matter what the value is), and the
value of column 6 is not NULL.
Example:
row1 col1 col2 col3 col4 col5 col6
A B C D 97 2
row2 col1 col2 col3 col4 col5 col6
A B C D 1 3
In this example, I need to change the value in row1, column6 from 2 to NULL.
I need to do that to all rows with similar matching qualities, which I
figure to be around 1000 rows. But I DO NOT want rows returned if the value
of col6 in one of the rows is not 97.
Can anyone help?
Thanks,
John Steen
On Fri, 20 Aug 2004 08:43:01 -0700, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>I'm running MS SQL 7.0.
>I've been trying to figure this out for a couple of days without success.
>I need to change the values in column6 of a table to NULL where the value of
>column6 is not NULL, and the value of column5 is 97, and the values of
>column1, column2, column3, and column4 each match the corresponding columns
>in the other row.
>In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
>match the same columns in row 2, and the value of column6 in row 1 is 97 and
>in row2 is something else (it doesn't matter what the value is), and the
>value of column 6 is not NULL.
>Example:
>row1 col1 col2 col3 col4 col5 col6
> A B C D 97 2
>row2 col1 col2 col3 col4 col5 col6
> A B C D 1 3
>In this example, I need to change the value in row1, column6 from 2 to NULL.
> I need to do that to all rows with similar matching qualities, which I
>figure to be around 1000 rows. But I DO NOT want rows returned if the value
>of col6 in one of the rows is not 97.
>Can anyone help?
>Thanks,
>John Steen
Hi John,
Pity you didn't post the DDL (CREATE TABLE statements) and INSERTS for the
sample data that would have allowed me to test. Anyway, here's an untested
suggestion:
UPDATE MyTable
SET col6 = NULL
WHERE col6 IS NOT NULL
AND col5 = 97
AND EXISTS
(SELECT *
FROM MyTable AS x
WHERE x.col1 = MyTable.col1
AND x.col2 = MyTable.col2
AND x.col3 = MyTable.col3
AND x.col4 = MyTable.col4
AND ( x.col5 <> 97
OR x.col5 IS NULL))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||if i understand your requirement correctly you can try the query given in
following example.
--sample data
create table t(col1 char(1),col2 char(1),col3 char(1),col4 char(1),col5
int,col6 int)
go
insert into t
select 'A','B','C','e',97, 2 union all --row will be updated
select 'A','B','C','e',9, 3 union all
select 'A','B','C','e',95, 2 union all
select 'A','B','C','e',97, null union all
select 'A','B','C','e',97, 5 union all --row will be updated
select 'A','B','C','x',9, 3 union all
select 'A','B','C','x',95, 2
go
--query
update t set col6 = null
where exists
(select col1,col2,col3,col4
from t x
where t.col1 = x.col1 and t.col2 = x.col2 and t.col3=x.col3 and t.col4 =
x.col4
group by col1,col2,col3,col4
having count(*) > 1)
and t.col5 = 97 and t.col6 is not null
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks, Hugo and Vishal! Both solutions worked.

Friday, March 23, 2012

Help expanding a query .. group By

Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?

> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:

> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql

help deleting SIMILAR records (not duplicate)

i've read lots of usenet and and microsoft support articles about how
to remove duplicate rows from a table, but i am trying to modify that
logic to delete "similar" rows. for example, consider the following:
create table t1 (
col1 int,
col2 bit,
col3 bit)
insert into t1 values (1, 0, 0)
insert into t1 values (2, 0, 0)
insert into t1 values (3, 0, 1)
now, clearly there are no duplicate rows. but what if, for the sake of
logical consistency, i need to remove "similar" rows, defining similar
in this example as rows with duplicate col2 and col3 values? keep in
mind: i don't care which row gets deleted (deleting the col1 value of 1
OR 2 will be fine)
most of the examples i'm reading involve selecting distinct * into a
temp table, which won't work for me, because the whole row is not
duplicated.
the result i am looking for AFTER the similar row deletion is as
follows:
select * from t1
col1 col2 col3
-- -- --
1 0 0
3 0 1
thanks for any help!Delete Table
Where Col1 In(
Select Min(T1.Col1)
From Table1 As T1
Group By T1.Col2, T1.Col3
Having Count(*) > 1
)
Thomas
"jason" <iaesun@.yahoo.com> wrote in message
news:1114800493.591486.311530@.l41g2000cwc.googlegroups.com...
> i've read lots of usenet and and microsoft support articles about how
> to remove duplicate rows from a table, but i am trying to modify that
> logic to delete "similar" rows. for example, consider the following:
> create table t1 (
> col1 int,
> col2 bit,
> col3 bit)
> insert into t1 values (1, 0, 0)
> insert into t1 values (2, 0, 0)
> insert into t1 values (3, 0, 1)
> now, clearly there are no duplicate rows. but what if, for the sake of
> logical consistency, i need to remove "similar" rows, defining similar
> in this example as rows with duplicate col2 and col3 values? keep in
> mind: i don't care which row gets deleted (deleting the col1 value of 1
> OR 2 will be fine)
> most of the examples i'm reading involve selecting distinct * into a
> temp table, which won't work for me, because the whole row is not
> duplicated.
> the result i am looking for AFTER the similar row deletion is as
> follows:
> select * from t1
> col1 col2 col3
> -- -- --
> 1 0 0
> 3 0 1
> thanks for any help!
>|||this is exactly the kind of logic i need, even though that will only
delete 1 similar row, where as i would like to only KEEP 1 similar row.
your code satisfies the example completely, however i might actually
have dozens of "similar" rows, for which i would only want to keep the
rows identified by the select min(col1) statement.
thanks again!|||You could run the query several times in succession or put it in a loop :)
BTW, once you get this resolved, you might want to make sure that the
client-side logic doesn't allow inserts of "similar rows"; or you could
ensure this via trigger.
"jason" <iaesun@.yahoo.com> wrote in message
news:1114801761.830867.51880@.z14g2000cwz.googlegroups.com...
> this is exactly the kind of logic i need, even though that will only
> delete 1 similar row, where as i would like to only KEEP 1 similar row.
> your code satisfies the example completely, however i might actually
> have dozens of "similar" rows, for which i would only want to keep the
> rows identified by the select min(col1) statement.
> thanks again!
>|||Try,
delete t1
where exists(select * from t1 as t2 where t1col2 = t2.col2 and t1.col3 =
t2.col3 and t2.col1 > t1.col1)
AMB
"jason" wrote:

> this is exactly the kind of logic i need, even though that will only
> delete 1 similar row, where as i would like to only KEEP 1 similar row.
> your code satisfies the example completely, however i might actually
> have dozens of "similar" rows, for which i would only want to keep the
> rows identified by the select min(col1) statement.
> thanks again!
>|||DELETE FROM T1
WHERE EXISTS
(SELECT *
FROM T1 AS T2
WHERE T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col1 > T2.col1)
David Portas
SQL Server MVP
--

Help creating a DELETE trigger.

I need to create a trigger that will run whenever a record is deleted. I
want the trigger to delete records from table2 where the value in the ID
field matches the value in the ID field of the record being deleted from
table1.
I'm new to triggers and am not sure how to set this up.
Thanks,
JohnTry,
create trigger tr_table1_del on table1
for delete
as
set nocount on
if exists(select * from deleted as d inner join table2 as t on d.[id] = t[id])
begin
delete table2
where exists(select * from deleted ad d where d.[id] = table2.[id])
if @.@.error != 0
begin
rollback transaction
raiserror('Error deleting rows in table2.', 16, 1)
return
end
end
go
AMB
"John Piotrowski" wrote:

> I need to create a trigger that will run whenever a record is deleted. I
> want the trigger to delete records from table2 where the value in the ID
> field matches the value in the ID field of the record being deleted from
> table1.
> I'm new to triggers and am not sure how to set this up.
> Thanks,
>
> John

Monday, March 19, 2012

HELP : all records of one table have been deleted!

For a reason that we don't know, one table consisting of about 1200 rows in
SQL Server 2005 Express SP2 shows to be empty at once. Since all records
were addded within this day, there is no suitable back-up of a couple of
hours ago, I don't know a way to restore the records.
1) Is this a known bug of SQL Server 2005 SP2?
1) Is there a way to find out the reason why this happened?
2) Is there a way to restore the deleted rows?
3) How can I prevent this to happen again?"Oscar" <oku@.xs4all.nl> wrote in message
news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
> For a reason that we don't know, one table consisting of about 1200 rows
> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
> records were addded within this day, there is no suitable back-up of a
> couple of hours ago, I don't know a way to restore the records.
> 1) Is this a known bug of SQL Server 2005 SP2?
> 1) Is there a way to find out the reason why this happened?
> 2) Is there a way to restore the deleted rows?
> 3) How can I prevent this to happen again?
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
> For a reason that we don't know, one table consisting of about 1200 rows
> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
> records were addded within this day, there is no suitable back-up of a
> couple of hours ago, I don't know a way to restore the records.
> 1) Is this a known bug of SQL Server 2005 SP2?
Not that I've heard.
> 1) Is there a way to find out the reason why this happened?
Possibly, but may not be entirely worth the effort.
> 2) Is there a way to restore the deleted rows?
MAYBE!
If you are in full-logging and not simple logging, you may be able to do the
following:
I assume you have a full backup from say last night?
Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option so
that you don't truncate the log, just in case).
Anyway, do a RESTORE from the full backup to a different database name WITH
NORECOVERY.
Then a RESTORE LOG using the backup you just made and then WITH STOPATMARK =<time>, STANDBY=filename.
This will restore the log to whatever time you want. The STANDBY will allow
you to put the database into read-only mode while you then do a select on
the table.
If the data is there, you can then select it back into your live database.
If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
luck.
If you need more help, let me know via email or here.
> 3) How can I prevent this to happen again?
Security.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
I am not that familiar with these items.
a) how can I find whether the configuration is full-logging or simple
logging?
b) what do you mean with 'Take a Transaction Log backup now'? How can I find
this?
c) how can I do a RESTORE LOG?
thanks for your help.
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:%23qklnHLcHHA.4032@.TK2MSFTNGP02.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> For a reason that we don't know, one table consisting of about 1200 rows
>> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
>> records were addded within this day, there is no suitable back-up of a
>> couple of hours ago, I don't know a way to restore the records.
>> 1) Is this a known bug of SQL Server 2005 SP2?
> Not that I've heard.
>
>> 1) Is there a way to find out the reason why this happened?
> Possibly, but may not be entirely worth the effort.
>> 2) Is there a way to restore the deleted rows?
> MAYBE!
> If you are in full-logging and not simple logging, you may be able to do
> the following:
> I assume you have a full backup from say last night?
> Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option
> so that you don't truncate the log, just in case).
> Anyway, do a RESTORE from the full backup to a different database name
> WITH NORECOVERY.
> Then a RESTORE LOG using the backup you just made and then WITH STOPATMARK
> = <time>, STANDBY=filename.
> This will restore the log to whatever time you want. The STANDBY will
> allow you to put the database into read-only mode while you then do a
> select on the table.
> If the data is there, you can then select it back into your live database.
> If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
> HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
> luck.
> If you need more help, let me know via email or here.
>> 3) How can I prevent this to happen again?
> Security.
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi Greg,
> I am not that familiar with these items.
> a) how can I find whether the configuration is full-logging or simple
> logging?
> b) what do you mean with 'Take a Transaction Log backup now'? How can I
> find this?
> c) how can I do a RESTORE LOG?
> thanks for your help.
> Oscar
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:%23qklnHLcHHA.4032@.TK2MSFTNGP02.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> For a reason that we don't know, one table consisting of about 1200 rows
>> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
>> records were addded within this day, there is no suitable back-up of a
>> couple of hours ago, I don't know a way to restore the records.
>> 1) Is this a known bug of SQL Server 2005 SP2?
>> Not that I've heard.
>>
>> 1) Is there a way to find out the reason why this happened?
>> Possibly, but may not be entirely worth the effort.
>> 2) Is there a way to restore the deleted rows?
>> MAYBE!
>> If you are in full-logging and not simple logging, you may be able to do
>> the following:
>> I assume you have a full backup from say last night?
>> Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option
>> so that you don't truncate the log, just in case).
>> Anyway, do a RESTORE from the full backup to a different database name
>> WITH NORECOVERY.
>> Then a RESTORE LOG using the backup you just made and then WITH
>> STOPATMARK = <time>, STANDBY=filename.
>> This will restore the log to whatever time you want. The STANDBY will
>> allow you to put the database into read-only mode while you then do a
>> select on the table.
>> If the data is there, you can then select it back into your live
>> database.
>> If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
>> HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
>> luck.
>> If you need more help, let me know via email or here.
>> 3) How can I prevent this to happen again?
>> Security.
>>
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi Greg,
> I am not that familiar with these items.
>
Ok, that'll make this a bit tougher, but let's see what we can do.
> a) how can I find whether the configuration is full-logging or simple
> logging?
Hmm, since you're running Express, you'll have to use SQLCMD to connect to
the database.
So, connect to your database and then type:
SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
GO
Let me know if this returns a 0 or a 1.
(You want a 0).
If this is 1, then there's very little if anything that can be done.
If it's 0, we might be able to help.
> b) what do you mean with 'Take a Transaction Log backup now'? How can I
> find this?
> c) how can I do a RESTORE LOG?
> thanks for your help.
> Oscar
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Meanwhile I've already started the first step to do a transaction log
backup.
However it shows now '(Restoring)' after the database name instead of the
back up.
It has run for almost an hour now and I don't know how long this will last.
I've also looked into another SQL server where almost the same database
resides for testing purposes.
After typing your selection query it shows one record with two fields. In
the first field it shows a '1' and in the second field it shows a 'NULL'
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
> Ok, that'll make this a bit tougher, but let's see what we can do.
>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
> Hmm, since you're running Express, you'll have to use SQLCMD to connect to
> the database.
> So, connect to your database and then type:
> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
> GO
> Let me know if this returns a 0 or a 1.
> (You want a 0).
> If this is 1, then there's very little if anything that can be done.
> If it's 0, we might be able to help.
>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
>>
>> Ok, that'll make this a bit tougher, but let's see what we can do.
>>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
>> Hmm, since you're running Express, you'll have to use SQLCMD to connect
>> to the database.
>> So, connect to your database and then type:
>> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
>> GO
>> Let me know if this returns a 0 or a 1.
>> (You want a 0).
>> If this is 1, then there's very little if anything that can be done.
>> If it's 0, we might be able to help.
>>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
>>
>> Ok, that'll make this a bit tougher, but let's see what we can do.
>>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
>> Hmm, since you're running Express, you'll have to use SQLCMD to connect
>> to the database.
>> So, connect to your database and then type:
>> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
>> GO
>> Let me know if this returns a 0 or a 1.
>> (You want a 0).
>> If this is 1, then there's very little if anything that can be done.
>> If it's 0, we might be able to help.
>>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
If it's showing a 1, then it's doing SIMPLE RECOVERY mode and the log gets
truncated at each checkpoint so you're probably out of luck.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
Can you type exactly what commands you used.
A BACKUP should NOT in any way change a database name to "restoring" so I
suspect you're doing something else here.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||These are the steps :
Tasks -> Back-up
Configured : Backup type : Transaction Log
Options : Transaction Log : Back up the tail of the log and leave the
database in the restoring state (in order to deselect the option 'Trancate
the transaction log' as you advised me.
Then pressed OK after which is shows now 'Restoring'.
Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
think the process takes too much time (about three hours now), it could have
been crashed. Task Manager doesn't show any noticeable activity. Can/should
I stop this proces now?
with respect to the logging setting, the results of the query look like the
'1' responds to a record number and the second field is the value we're
looking for and it's 'null'.
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:eQ8B2gMcHHA.1508@.TK2MSFTNGP06.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Meanwhile I've already started the first step to do a transaction log
>> backup.
> If it's showing a 1, then it's doing SIMPLE RECOVERY mode and the log gets
> truncated at each checkpoint so you're probably out of luck.
>
>> However it shows now '(Restoring)' after the database name instead of the
>> back up.
> Can you type exactly what commands you used.
> A BACKUP should NOT in any way change a database name to "restoring" so I
> suspect you're doing something else here.
>
>> It has run for almost an hour now and I don't know how long this will
>> last.
>> I've also looked into another SQL server where almost the same database
>> resides for testing purposes.
>> After typing your selection query it shows one record with two fields. In
>> the first field it shows a '1' and in the second field it shows a 'NULL'
>> Oscar
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:OY2Xv6McHHA.4488@.TK2MSFTNGP03.phx.gbl...
> These are the steps :
> Tasks -> Back-up
> Configured : Backup type : Transaction Log
> Options : Transaction Log : Back up the tail of the log and leave the
> database in the restoring state (in order to deselect the option 'Trancate
> the transaction log' as you advised me.
> Then pressed OK after which is shows now 'Restoring'.
> Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
> think the process takes too much time (about three hours now), it could
> have been crashed. Task Manager doesn't show any noticeable activity.
> Can/should I stop this proces now?
Ok. Sounds like you're using the GUI. In cases like this I REALLY REALLY
recommend you don't and you execute the commands by hand. I'm not 100% sure
what GUI is doing under the covers. (Though I was able to replicate what you
did with the GUI and cause the same issue. And telling it to generate the
script, I can see what it was doing.)
Anyway, my guess is it completed the backup and you've covered the dialog
box with the OK button. It should take only a minute or two to do that
backup (or error out if it can't.)
> with respect to the logging setting, the results of the query look like
> the '1' responds to a record number and the second field is the value
> we're looking for and it's 'null'.
I'm not sure why you're getting two columns. You should be getting one.
Anyway, before we go further, did you have an existing full backup from last
night (or rather, what is your last full backup?)
> Oscar
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
my last full back up is of 2 days ago.
Any idea how I can stop or kill the (Restoring)?
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:OwwZnONcHHA.4216@.TK2MSFTNGP02.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:OY2Xv6McHHA.4488@.TK2MSFTNGP03.phx.gbl...
>> These are the steps :
>> Tasks -> Back-up
>> Configured : Backup type : Transaction Log
>> Options : Transaction Log : Back up the tail of the log and leave the
>> database in the restoring state (in order to deselect the option
>> 'Trancate the transaction log' as you advised me.
>> Then pressed OK after which is shows now 'Restoring'.
>> Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
>> think the process takes too much time (about three hours now), it could
>> have been crashed. Task Manager doesn't show any noticeable activity.
>> Can/should I stop this proces now?
> Ok. Sounds like you're using the GUI. In cases like this I REALLY REALLY
> recommend you don't and you execute the commands by hand. I'm not 100%
> sure what GUI is doing under the covers. (Though I was able to replicate
> what you did with the GUI and cause the same issue. And telling it to
> generate the script, I can see what it was doing.)
> Anyway, my guess is it completed the backup and you've covered the dialog
> box with the OK button. It should take only a minute or two to do that
> backup (or error out if it can't.)
>
>> with respect to the logging setting, the results of the query look like
>> the '1' responds to a record number and the second field is the value
>> we're looking for and it's 'null'.
> I'm not sure why you're getting two columns. You should be getting one.
> Anyway, before we go further, did you have an existing full backup from
> last night (or rather, what is your last full backup?)
>
>
>> Oscar
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:e1kYIhNcHHA.4656@.TK2MSFTNGP06.phx.gbl...
> Hi Greg,
> my last full back up is of 2 days ago.
> Any idea how I can stop or kill the (Restoring)?
> Oscar
>
Can you email me at mooregr (at) greenms.com We can probably do this faster
off line.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

HELP : all records of one table have been deleted!

Friday, March 9, 2012

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors :)
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>|||I juz think Uri is trying to make a point.. And what would that be'|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors :)
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>|||I juz think Uri is trying to make a point.. And what would that be'|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is

"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors

> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

you need to look at the result set as a whole when you are processing and
not a single record ar a time.
|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>
|||I juz think Uri is trying to make a point.. And what would that be?
|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is

> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> >I am running a T-SQL query and if there are one or more records returned
> >by it I need to run another query. How does a newbie like me determine
> >this or how do I get the value of the COUNT function into a variable I
> >can use elsewhere in my sproc?
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>> After I see that the first query has records I am going to use values
>> from it to run the second query and I assume I am going to use a cursor
>> to accomplish this. Thank you.
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?
> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors :)
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>> Code
>> BOL's example
>> USE pubs
>> DECLARE @.RowCount int
>> EXEC sp_executesql
>> N'SELECT @.RowCount = COUNT(*) FROM authors',
>> N'@.RowCount int OUTPUT',
>> @.RowCount OUTPUT
>> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
>|||I juz think Uri is trying to make a point.. And what would that be'|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors :)
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Wednesday, March 7, 2012

HELP - Running a Report with Dynamic Query from a web page

Here's the situation. I have a report that uses a dynamic query to select one
or all records from the dataset to display.The query looks like this
="SELECT abunch of fieds" &
"FROM a view" &
iif(Parameters!orgid.value = 0,"","WHERE organization_id = " &
Parameters!orgid.value)
Essentially if the report parameter (orgid) = 0 then get all records, if
it's something other than 0, get the specified record.
The report is called from an ASP web application which builds the required
URL depending on the organization selected. The URL looks like this:
"http://webserver/reporting/pages/report.aspx?ItemPath=pathtoreport/report&rc:Parameters=false&orgid=123
In the browser, the HTML (I guess) viewer opens, displays the Parameter bar,
prompts me for a orgid and sits there.
I don't want the Parameter bar to show, just run the report with the
parameter supplied in the url. What am I doing wrong' Or what am I not doing
right?You have set up the beginning of the url incorrectly.
It should look like this:
http://webserver/reportserver?/pathToReport/ReportName&rc:Parameters=false&orgid=123
See the following msdn article for details:
http://msdn2.microsoft.com/en-us/library/ms152835.aspx
Rowen McDermott|||Ah Rowen, if only it were so easy and straightforward. BTW, thanks for the
reply.
When I use the method described by MS and everybody else who wrote a book on
Reporting Services, this is what happens:
I'm re-directed to the reportserver home page with a URL of
http://<webserver>/<reportserver>/Pages/Folder.aspx. I can see the folder
that contains the report to run, I have an administrative Toolbar and that's
pretty much it. If I browse down to the report, I can click on it, the report
will start and prompt me for an orgid.
Have any other suggestions? I've got the sa who set up Reporting Services
checking on her end to see if she can get the report to run as advertised.
Thanks again.
The only way I've gotten the report to start is the method I first described.
"Rowen" wrote:
> You have set up the beginning of the url incorrectly.
> It should look like this:
> http://webserver/reportserver?/pathToReport/ReportName&rc:Parameters=false&orgid=123
> See the following msdn article for details:
> http://msdn2.microsoft.com/en-us/library/ms152835.aspx
> Rowen McDermott
>|||On Mar 20, 11:15 pm, JoeKoko <JoeK...@.discussions.microsoft.com>
wrote:
> Ah Rowen, if only it were so easy and straightforward. BTW, thanks for the
> reply.
> When I use the method described by MS and everybody else who wrote a book on
> Reporting Services, this is what happens:
> I'm re-directed to the reportserver home page with a URL of
> http://<webserver>/<reportserver>/Pages/Folder.aspx. I can see the folder
> that contains the report to run, I have an administrative Toolbar and that's
> pretty much it. If I browse down to the report, I can click on it, the report
> will start and prompt me for an orgid.
> Have any other suggestions? I've got the sa who set up Reporting Services
> checking on her end to see if she can get the report to run as advertised.
> Thanks again.
> The only way I've gotten the report to start is the method I first described.
>
In that case I'm afraid I cant help you. The method I described above
worked for me, so I can't say what is going on in your case.
I would suggest testing a very basic report with no parameters using
the microsoft documented method and see if it displays the report or
re-directs you to the report server home page.
Regards,
Rowen

Monday, February 27, 2012

Help - how to select from a comma delimited field ?

Dear SQL,

Lets say I have a field: User_Names

and it can have more than one name
example: "Yovav,John,Shon"

How can I select all the records that has "Yovav" in them ?

I try some like this:
SELECT * FROM User_Table WHERE User_Names IN ('Yovav')

but it only works if the field User_Names only has "Yovav" in it with no extras...

is there some SQL function to tell if string is found in a field ?

Any hope 4 me ?Try

SELECT * FROM User_Table WHERE User_Names LIKE '%Yovav%'|||Thank Q 4 lightning up my night in such times of overSQLing...

finally I can have some sleep :-)

and Thank GOD 4 this forum !!!

Help - deleting existing auto-generated primary key

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

Friday, February 24, 2012

help

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

Wednesday, March 28, 2012

Help for Newbie!

Monday, March 26, 2012

Help Finding records with matching columns

Friday, March 23, 2012

Help expanding a query .. group By

help deleting SIMILAR records (not duplicate)

Help creating a DELETE trigger.

Monday, March 19, 2012

HELP : all records of one table have been deleted!

HELP : all records of one table have been deleted!

HELP : all records of one table have been deleted!

Friday, March 9, 2012

Help - Simple Question

Help - Simple Question

Help - Simple Question

Help - Simple Question

Wednesday, March 7, 2012

HELP - Running a Report with Dynamic Query from a web page

Monday, February 27, 2012

Help - how to select from a comma delimited field ?

Help - deleting existing auto-generated primary key

Friday, February 24, 2012

help

Absurd project

Blog Archive

About Me