DPE method error; passing DS persisted As XML

Hello,
I am using Teo Lachev's DPE and have done the following so far.
Added the datasource with 'Nothing' in query. Also the @.datasource parameter
is taking a path to the xsd file that i created off the dataset that i will
use. The designer has the fields setup on the report.
Through code, based on parameters I am forming a dataset that i write to an
xml (this contains the same elements and schema as the xsd).
Then..
DataSet dsDataSet = new DataSet();
dsDataSet.ReadXml(@."C:\FSChartSampleDS.xml", System.Data.XmlReadMode.Auto);
parameters[0] = new ParameterValue();
parameters[0].Name = "DataSource";
parameters[0].Value = dsDataSet.GetXml();
followed by a call to the render function. Here the call fails with this
error:
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
System.NullReferenceException: Object reference not set to an instance of an
object.\n at
AWC.RS.Extensions.DataExtensions.DsDataReader.GetOrdinal(String name)\n at
Microsoft.ReportingServices.DataExtensions.MappingDataReader..ctor(String
dataSetName, IDataReader sourceReader, String[] aliases, String[]
fieldNames)\n at
Microsoft.ReportingServices.ReportProcessing.g..ctor(String A_0, IDataReader
A_1, String[] A_2, String[] A_3)\n at
Microsoft.ReportingServices.ReportProcessing.a0.v()\n at
Microsoft.ReportingServices.ReportProcessing.aw.c()\n at
Microsoft.ReportingServices.ReportProcessing.a0.a(Boolean& A_0)\n at
Microsoft.ReportingServices.ReportProcessing.aw.b()\n at
Microsoft.ReportingServices.ReportProcessing.a0.a(Object A_0)\n -- End of
inner exceptionsolved this myself
"google" wrote:
> Hello,
> I am using Teo Lachev's DPE and have done the following so far.
> Added the datasource with 'Nothing' in query. Also the @.datasource parameter
> is taking a path to the xsd file that i created off the dataset that i will
> use. The designer has the fields setup on the report.
> Through code, based on parameters I am forming a dataset that i write to an
> xml (this contains the same elements and schema as the xsd).
> Then..
> DataSet dsDataSet = new DataSet();
> dsDataSet.ReadXml(@."C:\FSChartSampleDS.xml", System.Data.XmlReadMode.Auto);
> parameters[0] = new ParameterValue();
> parameters[0].Name = "DataSource";
> parameters[0].Value = dsDataSet.GetXml();
> followed by a call to the render function. Here the call fails with this
> error:
> Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
> occurred during report processing. -->
> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
> error has occurred during report processing. -->
> System.NullReferenceException: Object reference not set to an instance of an
> object.\n at
> AWC.RS.Extensions.DataExtensions.DsDataReader.GetOrdinal(String name)\n at
> Microsoft.ReportingServices.DataExtensions.MappingDataReader..ctor(String
> dataSetName, IDataReader sourceReader, String[] aliases, String[]
> fieldNames)\n at
> Microsoft.ReportingServices.ReportProcessing.g..ctor(String A_0, IDataReader
> A_1, String[] A_2, String[] A_3)\n at
> Microsoft.ReportingServices.ReportProcessing.a0.v()\n at
> Microsoft.ReportingServices.ReportProcessing.aw.c()\n at
> Microsoft.ReportingServices.ReportProcessing.a0.a(Boolean& A_0)\n at
> Microsoft.ReportingServices.ReportProcessing.aw.b()\n at
> Microsoft.ReportingServices.ReportProcessing.a0.a(Object A_0)\n -- End of
> inner exception

Downgrade SQL 2005 Entreprise to Workgroup Edition ?

Hi

I have the following problem :

I have licensed a german version of sbs 2003 premium. With the upgrade to sbs 2003 r2, I will buy the rights to use the sql 2005 workgroup edition. But the german sbs 2003 r2 is not available by now.

I could go in production with the sql 2005 trial, this is an entreprise edition.

My question is : can I downgrade the sql server 2005 from this trial edition (entreprise) to the workgroup edition ? Is it possible ? easy ? any recommendations ?

Many thanks !

Ferrarirosso

You can upgrade Trial SQL Server 2005 to Workgroup edition but it must happen before the trial edition expires.

Double Space Character

Hi,
I have table having a column whose value is the following expression.
="' " & Fields!AgencyNumber.Value & " ' ' " & Fields!MessageNumber.Value
I want to keep the double " " space characters but when the report is
rendered all is converted to single spaces.
Am I missing anything?
Thanks...You may be running into this IE limitation:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=25f13795-932d-4eaa-adf0-464c07398865&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:07ED8D5D-C98F-4C47-9016-135B161D5A72@.microsoft.com...
> Hi,
> I have table having a column whose value is the following expression.
> ="' " & Fields!AgencyNumber.Value & " ' ' " &
Fields!MessageNumber.Value
> I want to keep the double " " space characters but when the report is
> rendered all is converted to single spaces.
> Am I missing anything?
> Thanks...

dont understand the following locking behavior

can't figure out why the following locking scenario works the way it
does:

spid 1:
start transaction
select a row from table T1 with updlock
result: see an Update lock for the row and index key

spid 2:
query for the same row as in session 1
result: the query succeeds

spid 3:
do same as spid 1
result: blocks trying to get Update lock on the index key for row

Now I do the query again in spid 2 and it blocks trying to get a Shared
lock on the index key, and it's waiting for spid3. why was it able to
get a shared lock on a index key that had an Update lock at first but
then can't get the same shared lock when somebody else is also trying
to get an update lock?"hendershot" <eyusim@.yahoo.com> wrote in message
news:1108174034.874699.16400@.o13g2000cwo.googlegro ups.com...
> can't figure out why the following locking scenario works the way it
> does:
> spid 1:
> start transaction
> select a row from table T1 with updlock
> result: see an Update lock for the row and index key
> spid 2:
> query for the same row as in session 1
> result: the query succeeds
> spid 3:
> do same as spid 1
> result: blocks trying to get Update lock on the index key for row
> Now I do the query again in spid 2 and it blocks trying to get a Shared
> lock on the index key, and it's waiting for spid3. why was it able to
> get a shared lock on a index key that had an Update lock at first but
> then can't get the same shared lock when somebody else is also trying
> to get an update lock?

I tried this quickly, and I had no problems running a query for the row from
spid 2, even with multiple other spids all blocked and waiting for a lock on
the same index key. Perhaps you can post a script which reproduces what
you're seeing? And what version/servicepack are you using (I used Enterprise
Edition 2000 SP3a)?

Simon|||I was using Standard Edition, it says version 8.00.194 (not 100% sure
about the last 3 digits). does SQL Server 8 = SQL Server 2000?

I'll try to post a script on monday.
thanks.|||hendershot (eyusim@.yahoo.com) writes:
> I was using Standard Edition, it says version 8.00.194 (not 100% sure
> about the last 3 digits). does SQL Server 8 = SQL Server 2000?

8.00.194 = The original version of SQL 2000 with no service packs. You
can download the current service pack SP3a from
http://www.microsoft.com/sql/downloads/2000/sp3.asp. I stronly recommend
you download and install this service pack, since it includes a fix for
the Slammer worm.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||installed SP3a and now I am at version 8.00.760, still seeing the same
locking behavior.

this is what I am doing in Query Analyzer w/the Northwind database:

Window #1:

begin transaction
select description from categories
with (updlock) where categoryid = 1

Window #2:

select description from categories
where categoryid = 1

Window #3:

begin transaction
select description from categories
with (updlock) where categoryid = 1

I run the sql in window #1 and that gets the Update locks on the row
and key, run the sql in window #2 and it runs just fine, run the sql in
window #3 and it 's waiting to get update lock on the key which is what
you'd expect. now when I run the query in window #2 again it hangs
waiting on a Shared lock for the key.

If I change description to "*" it behaves the same way, if I change the
query in window 2 to get another field (categoryid) it's just fine.

Any ideas of why it can't get the shared lock when somebody else is
waiting for update lock?

Erland Sommarskog wrote:
> hendershot (eyusim@.yahoo.com) writes:
> > I was using Standard Edition, it says version 8.00.194 (not 100%
sure
> > about the last 3 digits). does SQL Server 8 = SQL Server 2000?
> 8.00.194 = The original version of SQL 2000 with no service packs.
You
> can download the current service pack SP3a from
> http://www.microsoft.com/sql/downloads/2000/sp3.asp. I stronly
recommend
> you download and install this service pack, since it includes a fix
for
> the Slammer worm.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||hendershot (eyusim@.yahoo.com) writes:
> installed SP3a and now I am at version 8.00.760, still seeing the same
> locking behavior.
> this is what I am doing in Query Analyzer w/the Northwind database:
> Window #1:
> begin transaction
> select description from categories
> with (updlock) where categoryid = 1
> ...
> If I change description to "*" it behaves the same way, if I change the
> query in window 2 to get another field (categoryid) it's just fine.
> Any ideas of why it can't get the shared lock when somebody else is
> waiting for update lock?

I don't really have a good answer, but I note that only happens if the
query in window #2 attempts to access a text column. (Or ntext or image.)

It may be a bug. I can't reproduce the problem in SP4 beta. However, when
I test in the latest drop of SQL 2005, the query does not get blocked.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes, I see that it doesn't occur with certain types. I guess I'll
just have to wait for SP4.

thanks|||hendershot (eyusim@.yahoo.com) writes:
> yes, I see that it doesn't occur with certain types. I guess I'll
> just have to wait for SP4.

It does not seem to be fixed in SP4. In SQL 2005, it is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Don't think this can be done?

I have a table (which I didn't design ) which has the following columns
Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
each of which are flagged by either an'X' to indicate there is something on
that day or an'-' to indicate nothing on that day.
Is it possible to write a statement to select the last day which has
something on?
i.e.
Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
Sunday='-'
is it possible have a statement that returns Thursday is the last day with
something on?
Thanks in advance,
Geoselect CASE Sunday WHEN 'x' THEN 'Sunday' ELSE CASE Saturday WHEN 'x' THEN
'Saturday' ELSE ...
I'm lazy so I won't write the entire script but you can see where I'm going.
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:OdDpXAT6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table (which I didn't design ) which has the following columns
> Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
> each of which are flagged by either an'X' to indicate there is something
> on that day or an'-' to indicate nothing on that day.
> Is it possible to write a statement to select the last day which has
> something on?
> i.e.
> Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
> Sunday='-'
> is it possible have a statement that returns Thursday is the last day with
> something on?
> Thanks in advance,
> Geo
>|||Obviously this is a totally amateurish table design and personally I
wouldn't want to do anything that would prolong its unfortunate life,
but here goes:
SELECT ... ,
CASE
WHEN sunday = 'x' THEN 'sunday'
WHEN saturday = 'x' THEN 'saturday'
WHEN friday = 'x' THEN 'friday'
..
WHEN monday = 'x' THEN 'monday'
ELSE 'nothing'
END
FROM your_table ;
David Portas
SQL Server MVP
--|||Hi Geo,
SELECT
CASE
WHEN Sunday = 'x' THEN 'Sunday'
WHEN Saturday = 'x' THEN 'Saturday'
WHEN Friday = 'x' THEN 'Friday'
WHEN Thursday = 'x' THEN 'Thursday'
WHEN Wednesday = 'x' THEN 'Wednesday'
WHEN Tuesday = 'x' THEN 'Tuesday'
WHEN Monday = 'x' THEN 'Monday'
ELSE 'Noday'
END
>From SomeTable
HTH, Jens Suessmeyer.|||Forget this...
Mondays :-(
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJUdmFT6FHA.3636@.TK2MSFTNGP12.phx.gbl...
> select CASE Sunday WHEN 'x' THEN 'Sunday' ELSE CASE Saturday WHEN 'x' THEN
> 'Saturday' ELSE ...
> I'm lazy so I won't write the entire script but you can see where I'm
> going.
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:OdDpXAT6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||Hi Geo,
When does your w start? Sunday or Monday? David and Jens solution
both assume from the listing of column names that Sunday is the last
day of the w (not the first). CASE will return the first result
that matches the conditional, so if Sunday is the first day of the
w, then you'll need to modify the statement accordingly.
Stu|||On Mon, 14 Nov 2005 15:28:37 -0000, "Geo" <noSpamgbarr@.ibigroup.com>
wrote:
>I have a table (which I didn't design ) which has the following columns
>Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
>each of which are flagged by either an'X' to indicate there is something on
>that day or an'-' to indicate nothing on that day.
>Is it possible to write a statement to select the last day which has
>something on?
>i.e.
>Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
>Sunday='-'
>is it possible have a statement that returns Thursday is the last day with
>something on?
>Thanks in advance,
>Geo
Easy - write a seven-clause pivot select, create a reference table
with the names and ordering, and there you are!
Or use the case statement, it's no worse than the table.
J.|||Guys thanks for the input it's much appreciated. David I couldn't agree more
about the table design you should see some of the others I've inherited.
Geo
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:OdDpXAT6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table (which I didn't design ) which has the following columns
> Monday,Tuesday, Wednesday,Thursday, Friday,Saturday ,Sunday
> each of which are flagged by either an'X' to indicate there is something
> on that day or an'-' to indicate nothing on that day.
> Is it possible to write a statement to select the last day which has
> something on?
> i.e.
> Monday='x' Tuesday='-' Wednesday='-' Thursday='x' Friday='-' Saturday='-'
> Sunday='-'
> is it possible have a statement that returns Thursday is the last day with
> something on?
> Thanks in advance,
> Geo
>

Dont know which database to use for the following site

Hi All,

Just wondered if i could ask you for some advice and help.

I'm looking to write a site, and one of the functions of the site is to remind the user of a certain event at a certain time. I was wondering how i would do this? or if anyone has a working example i could use?

Also I dont quite know what database to use..... would MySQL do the function i am after?? If so then no more problem lol If not what database would you recommend i use?

Thanks

Dannyi recommend mysql

which language are you planning to use? perl? coldfusion? asp?|||do the whole thing in clipper or ada as the front end code.|||Well im looking to do it in perl. But thats not really my skill, but its a good enough time as any to start learning it. I know more asp than anything.

The only issue i have is, the site is hosted on a NAMESCO server that has MYSQL. And as i have never used MYSQL i dont know how to configure it...... not only that, i could probably know how to set it all up if i had some sort of interface to it. But theres nothing.

Probably me having a noob moment, but anyone know what i mean?

Thanks

Dan|||well, you can use asp with mysql as well

your interface to mysql could be one of the mysql utilities available on the mysql site

or you could use Heidi SQL, which is what i use, it's really great

Don't get Service Broker to Work

Hi,

I'm not able to get Service Broker to work. I've created the following sample and would excpect to get some data from "PreisanfrageQueue" or "PreisanfrageRequestorQueue". But both they are emtpy.

What do I do wrong?

Regards,

Manfred

create message type Preisanfrage
validation = well_formed_xml;

create message type PreisanfrageAntwort
validation = well_formed_xml;

create contract PreisanfrageContract
(
Preisanfrage sent by initiator,
PreisanfrageAntwort sent by target
);

create queue PreisanfrageRequestorQueue with
status=on;

create queue PreisanfrageQueue;

create service PreisanfrageRequestorService
on queue PreisanfrageRequestorQueue ( PreisanfrageContract );

create service PreisanfrageService
on queue PreisanfrageQueue (PreisanfrageContract );

create table debug_table;
create table debug_table (id int primary key identity(1,1), msg varchar(100));

create procedure PreisanfrageAction
as
declare @.conversation uniqueidentifier
declare @.msg nvarchar(max)
declare @.msgType nvarchar(256)
declare @.answer xml;

insert into debug_table(msg) values('1');

;receive top(1)
@.conversation = conversation_handle,
@.msg = message_body,
@.msgType = message_type_name
from PreisanfrageQueue;

insert into debug_table(msg) values('2');

-- Preisanfrage bearbeiten

set @.answer = '<preis>1</preis>';
;send on conversation @.conversation
message type PreisanfrageAntwort (@.answer);

end conversation @.conversation;

insert into debug_table(msg) values('3');

alter queue PreisanfrageQueue
with
status=on,
activation (
status=on,
PROCEDURE_NAME = PreisanfrageAction,
max_queue_readers = 100,
EXECUTE AS OWNER
);

-- Dialog starten

declare @.conversation uniqueidentifier;

begin dialog conversation @.conversation
from service [PreisanfrageRequestorService]
to service 'PreisanfrageService'
on contract [PreisanfrageContract];

declare @.request xml;

set @.request = '<?xml version="1.0" encoding="UTF-8"?><Preisanfrage xmlns="4711101'">http://www.xyz.at/samples/Preisanfrage"><KundenId>4711</KundenId><ProduktId>10</ProduktId><Anzahl>1</Anzahl></Preisanfrage>';

;send on conversation @.conversation
message type Preisanfrage ( @.request );

receive * from PreisanfrageQueue;

receive * from PreisanfrageRequestorQueue;

select * from debug_table

1. Please double check you have created the master key in this database.

create master key

encryption by password='asdfasdf'

2. query the system table to check the message’s transmission status.

select * from sys.transmission_queue

3. Don’t use

receive * from PreisanfrageQueue;

receive * from PreisanfrageRequestorQueue;

To check the queue's messenger, because this will remove the message from

the queue and but the conversation open

|||

Hi,

thanks - it was the missing master key.

Did I get it right, that thouse activation procedures are polling the queue ?
If yes - is there a way to use some kind of notification so that the activation procedure is just called every time when a message is enqueued ?

Best whishes,
Manfred

|||You did it correctly, but your messages are getting taken off as soon as you put them on there, through your receive * statements. You should try to receive one at a time and do something with those records.

Domain group server access?

Hi,
If a user needs access to a databases and I carry out the following steps:
1) Create a domain group
2) Put the users login details in this group
3) Give the domain group access to SQL Server via Enterprise Manager
4) Give the domain group database access via Enterprise Manager
Do I also need to give this domain group direct server access by adding it
to a group in:
Computer Management\Local Users and Groups\Groups ?
Ta
wendyalso, we are using active directory.
wendy
"Woo" wrote:

> Hi,
> If a user needs access to a databases and I carry out the following steps:
> 1) Create a domain group
> 2) Put the users login details in this group
> 3) Give the domain group access to SQL Server via Enterprise Manager
> 4) Give the domain group database access via Enterprise Manager
> Do I also need to give this domain group direct server access by adding it
> to a group in:
> Computer Management\Local Users and Groups\Groups ?
> Ta
> wendy|||No you don't need to give OS level permissions on the server
that is running SQL Server for the group to access SQL
Server. You only need to add the windows group to the SQL
Server logins (and whatever databases).
-Sue
On Fri, 3 Nov 2006 04:14:02 -0800, Woo
<Woo@.discussions.microsoft.com> wrote:

>Hi,
>If a user needs access to a databases and I carry out the following steps:
>1) Create a domain group
>2) Put the users login details in this group
>3) Give the domain group access to SQL Server via Enterprise Manager
>4) Give the domain group database access via Enterprise Manager
>Do I also need to give this domain group direct server access by adding it
>to a group in:
>Computer Management\Local Users and Groups\Groups ?
>Ta
>wendy|||And you will have to give the windows group permissions to access tables,
views, stored procedures, etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bcjmk2h4eoundvep74genb5d43ldjveog2@.
4ax.com...
> No you don't need to give OS level permissions on the server
> that is running SQL Server for the group to access SQL
> Server. You only need to add the windows group to the SQL
> Server logins (and whatever databases).
> -Sue
> On Fri, 3 Nov 2006 04:14:02 -0800, Woo
> <Woo@.discussions.microsoft.com> wrote:
>
>

Does this Stored Procedure Syntax look okay?

Does this Stored Procedure Syntax look okay?

I'm trying to call it using classic asp with the following code but get an error on calling the stored procedure:

set objComm = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = sstrDsnString 'You can also just specify a connection string here
objComm.CommandText = "editorTAmonthlyReport" ' stored procedure name
objComm.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
'Add Input Parameters to date and from date variables - dtFrom and dtTo
objComm.Parameters.Append.CreateParameter("@.dtFrom", adDate, adParamInput, 9)
objComm.Parameters.Append.CreateParameter("@.dtTo", adDate, adParamInput, 9)

CREATE PROCEDURE editorTAmonthlyReport
@.FromDate DateTime,
@.ToDate DateTime
As

Select
U.UserID,
U.Title,
U.FirstName,
U.Surname,
TAU.UserPosition,
U.Email,
A.AgencyName,
A.AddressLine1,
A.AddressLine2,
A.Town,
A.County,
A.Postcode,
C.Country,
Coalesce(U.Phone,A.Phone) As Phone,
TAU.DateOfCreation
From
[user] U LEFT OUTER JOIN MyTrafalgarUser MTU
ON MTU.userID=U.UserID
INNER JOIN TravelAgencyUser TAU
ON U.UserID=TAU.UserID
INNER JOIN Agency A
ON TAU.AgencyID=A.AgencyID
INNER JOIN Country C
ON A.CountryID=C.CountryID
Where ( u.userid > 34657 AND DateDiff(d,TAU.DateOfCreation, @.FromDate) < 0 )
and datediff(d,TAU.DateOfCreation, @.ToDate) > 0
ORDER BY TAU.DateOfCreation
GO

Hi

Your stored procedure looks good to me .

You can test you stored procedure in SQL Analyser to see if it returns correct records.

|||

It's okay, I got it working.

Thanks.

Does this log backup overwrite?

The following command is run every 5 minutes:
BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I think it is overwriting the TRN file every time.Yes -- the INIT option makes it overwrite.
--
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Jay" <nospam@.nospam.org> wrote in message
news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
> The following command is run every 5 minutes:
> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME
> = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
> NO_TRUNCATE
> I think it is overwriting the TRN file every time.
>|||Psssst! Wrong answer Hans!
I was really hoping to be told I'm a idiot and didn't RTFM right.
Thank you sir.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
> Yes -- the INIT option makes it overwrite.
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>|||You should really think about generating backups with a unique filename each
time. Appending to a single file can get pretty ugly since you can't delete
individual backups in the file. It's all or nothing.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Psssst! Wrong answer Hans!
> I was really hoping to be told I'm a idiot and didn't RTFM right.
> Thank you sir.
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>|||Actually, I've been here a month now and had initially only verified that
backups were being done. Why so little? Because, no one would ever overwrite
their log backup without having backed it up, or archived it first ... would
they?
I have since spoken to the guy that set it up (my boss) and he said he
thought that he was making a complete copy of the .ldf changes for the day
each time he wrote to the .trn. I literally had to stop myself when I heard
my tone in replying to him. Not a good idea to speak to your boss like
you're talking to an idiot.
Anyway, when drive space is available, I will be changing to my own backup
program that was based on RealSQLGuy's backup program. The only problem with
the program is that it assumes the job that puts it to tape will also be
removing old files. A situation that is not the case here.
Thanks,
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
> You should really think about generating backups with a unique filename
> each time. Appending to a single file can get pretty ugly since you can't
> delete individual backups in the file. It's all or nothing.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>|||One possible way is to base the backups on weekday, day in month or similar. Take the easy route,
day of week. You have a Monday file, a Tuesday file etc. The first backup of the day, you do INIT,
then the rest of the backups of the day you do NONIT. You now have a tail of 6 backup files
(excluding the current day). This will cut down number of physical files (nice of you have lots of
databases and frequent log backups). However, some SQL persons aren't that familiar with several
backups on same file, so they don't understand the WITH FILE = option of the RESTORE command...
Pretty much a matter of taste. And, remember that the code you write need to be understood and
maintained by somebody... ;-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ui%23tvhn6HHA.5984@.TK2MSFTNGP04.phx.gbl...
> Actually, I've been here a month now and had initially only verified that backups were being done.
> Why so little? Because, no one would ever overwrite their log backup without having backed it up,
> or archived it first ... would they?
> I have since spoken to the guy that set it up (my boss) and he said he thought that he was making
> a complete copy of the .ldf changes for the day each time he wrote to the .trn. I literally had to
> stop myself when I heard my tone in replying to him. Not a good idea to speak to your boss like
> you're talking to an idiot.
> Anyway, when drive space is available, I will be changing to my own backup program that was based
> on RealSQLGuy's backup program. The only problem with the program is that it assumes the job that
> puts it to tape will also be removing old files. A situation that is not the case here.
> Thanks,
> Jay
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
>> You should really think about generating backups with a unique filename each time. Appending to
>> a single file can get pretty ugly since you can't delete individual backups in the file. It's all
>> or nothing.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log
>> Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>

does this call for a cursor? how?

Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))
now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.|||wonder if changing the RI / Relationship to "Delete Cascade" would help?

there are times and places where delete cascade isnt that smart, but it may well be appropriate here.

btw I think you ought to rethink your table design... calling them tab1,2,3 & Col1,2,3 is just being masochistic (unless you are trying to hide the nature of the table here which is fine...)|||Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))
now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.|||hi healdem,

thanks for the reply, and yes I am trying to hide the table names... btw, I have transferred this query to the sql forum.

thanks again... g11DB|||If not really sure ...but

DELETE FROM Table3 WHERE Exists (SELECT * FROM Table2 Correlate)|||Dupe thread from Access forum merged into this one

:)