Hi,
I've written an application in VB.Net 2005. One of its requirements is to
display a report which is hosted on our report server, and to let people
subscribe to this report (to get a copy once a week by email, for example).
To do this I have created a windows form containing a web browser control,
and set the URL of that control to the URL of the report, which happens to
be:
http://10.9.200.18/Reports/Pages/Report.aspx?ItemPath=%2fBunker+Management+Reports%2fActive+Bunker+Schemes+Due+To+Expire
Whenever I open that form I get prompted for my windows credentials. Also,
if I go straight to that URL in a web browser, I also get prompted for my
credentials. However, if I start from
http://10.9.200.18/Reports I can navigate to the same report without being
prompted for my credentials.
What can I do to be able to jump straight to the report without being
prompted for credentials? I switched on the option to enable anonymous
access for the web site itself (this is an internal web server), which did
the trick, but then I couldn't access the report server via either
http://10.9.200.18/Reports - took me to the report server home page, but
didn't list any contents
or
http://10.9.200.18/ReportServer - told me that the anonymous user account
didn't have permission to access the resource.
Enabling anonymous access wasn't exactly ideal, anyway. For one thing it
applied to the whole site, not just the report, and for another the user are
all going to be authenticated users anyway, so...
Kev"Kevin O'Donovan" <kev1609@.community.nospam> wrote in message
news:uScqcSAsGHA.4208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I've written an application in VB.Net 2005. One of its requirements is to
> display a report which is hosted on our report server, and to let people
> subscribe to this report (to get a copy once a week by email, for
> example). To do this I have created a windows form containing a web
> browser control, and set the URL of that control to the URL of the report,
> which happens to be:
> http://10.9.200.18/Reports/Pages/Report.aspx?ItemPath=%2fBunker+Management+Reports%2fActive+Bunker+Schemes+Due+To+Expire
> Whenever I open that form I get prompted for my windows credentials. Also,
> if I go straight to that URL in a web browser, I also get prompted for my
> credentials. However, if I start from
> http://10.9.200.18/Reports I can navigate to the same report without being
> prompted for my credentials.
> What can I do to be able to jump straight to the report without being
> prompted for credentials? I switched on the option to enable anonymous
> access for the web site itself (this is an internal web server), which did
> the trick, but then I couldn't access the report server via either
> http://10.9.200.18/Reports - took me to the report server home page, but
> didn't list any contents
> or
> http://10.9.200.18/ReportServer - told me that the anonymous user
> account didn't have permission to access the resource.
> Enabling anonymous access wasn't exactly ideal, anyway. For one thing it
> applied to the whole site, not just the report, and for another the user
> are all going to be authenticated users anyway, so...
> Kev
Okay, solved this ourselves, though if anyone can explain why, I'd be
grateful. When I refer to the web server via its IP address I get prompted
for credentials. When I refer to it by its DNS name I don't get prompted. I
can resolve my immediate problem by using the name in the URL, and adding
that name to the remote user's site's DNS, but I'd like to understand why
the two URLs are treated differently?
Kevin
Showing posts with label written. Show all posts
Showing posts with label written. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
Authenticating a report render from a browser
I am copying the question from another member as it is exactly my problem:
We have an extranet application (internet users) written in ASP using
VB COM. All the security of what reports users can see is handled
through this application. Is there anyway we can link RS reports using
a URL? In otherwords, users will login like they currently do now and
our application will provide a report list for them. When they run a
report, they will basically click on a link that points to the RS
report. Ideally, when the user logs in initially, they will be
validated by the current application and then be validated against RS
(background). We need this to be as transparent as possible.
I have seen this posted in several different ways, but without any
resolution. Is there no way to accomplish this?
Thanks.we have the same situation. One way to do it is to create a custom
security extension for RS and call it through web services SOAP. You
can either redirect to an aspx page that calls it or maybe call the
webservice in your asp page directly. You should read this article as
it explains it all http://www.devx.com/dotnet/Article/26759|||Marv,
I appreciate the reply and have checked out the web site you referenced. I
hate to be a wimp, but I'm kind of a one-man band and don't have a lot of
time (or a lot of the requisite experience) to undertake a project for what I
was hoping to be a simple solution.
I take it there is just no simple way to tell the Report Server to run the
report on request without additional authentication. It seems like there
should be some setting either in the manager or on the command line that
could accomplish this. I have tried various role assignments, but they seem
to be limited to the server on which the RS resides and all our user
authenication comes from a different domain (and we don't use active
directory).
Anyway, thanks for the response.
Dave
"Marv" wrote:
> we have the same situation. One way to do it is to create a custom
> security extension for RS and call it through web services SOAP. You
> can either redirect to an aspx page that calls it or maybe call the
> webservice in your asp page directly. You should read this article as
> it explains it all http://www.devx.com/dotnet/Article/26759
>|||"Dave" wrote:
> I take it there is just no simple way to tell the Report Server to run the
> report on request without additional authentication.
Without getting into the benfits of ADS, the security model and the
vulnerabilities, etc., if you supply (or remove) the necessary credentials to
the data source and can render the report; you could display the report in an
iframe. It would appear as though it was part of your existing application.
src=http://someserver/reportserver?/FolderName/Report&rs:Command=Render&rc:toolbar=false
Caveat emptor. Someone will find the links.
Just a thought.
We have an extranet application (internet users) written in ASP using
VB COM. All the security of what reports users can see is handled
through this application. Is there anyway we can link RS reports using
a URL? In otherwords, users will login like they currently do now and
our application will provide a report list for them. When they run a
report, they will basically click on a link that points to the RS
report. Ideally, when the user logs in initially, they will be
validated by the current application and then be validated against RS
(background). We need this to be as transparent as possible.
I have seen this posted in several different ways, but without any
resolution. Is there no way to accomplish this?
Thanks.we have the same situation. One way to do it is to create a custom
security extension for RS and call it through web services SOAP. You
can either redirect to an aspx page that calls it or maybe call the
webservice in your asp page directly. You should read this article as
it explains it all http://www.devx.com/dotnet/Article/26759|||Marv,
I appreciate the reply and have checked out the web site you referenced. I
hate to be a wimp, but I'm kind of a one-man band and don't have a lot of
time (or a lot of the requisite experience) to undertake a project for what I
was hoping to be a simple solution.
I take it there is just no simple way to tell the Report Server to run the
report on request without additional authentication. It seems like there
should be some setting either in the manager or on the command line that
could accomplish this. I have tried various role assignments, but they seem
to be limited to the server on which the RS resides and all our user
authenication comes from a different domain (and we don't use active
directory).
Anyway, thanks for the response.
Dave
"Marv" wrote:
> we have the same situation. One way to do it is to create a custom
> security extension for RS and call it through web services SOAP. You
> can either redirect to an aspx page that calls it or maybe call the
> webservice in your asp page directly. You should read this article as
> it explains it all http://www.devx.com/dotnet/Article/26759
>|||"Dave" wrote:
> I take it there is just no simple way to tell the Report Server to run the
> report on request without additional authentication.
Without getting into the benfits of ADS, the security model and the
vulnerabilities, etc., if you supply (or remove) the necessary credentials to
the data source and can render the report; you could display the report in an
iframe. It would appear as though it was part of your existing application.
src=http://someserver/reportserver?/FolderName/Report&rs:Command=Render&rc:toolbar=false
Caveat emptor. Someone will find the links.
Just a thought.
auditting select statement
We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:
> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:
> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>
Thursday, March 8, 2012
audit trail...
Hi...
A much lamented question, I guess..
A much lamented question, I guess..
I'm trying to create a simple audit trail.
log the changes to an SQL 2000 table, so that they are written into a
mirror table. The entire record, only the updated one, i.e. if say
only one field changes, the audit table will be inserted with one
record that has one field changed. if the record has been deleted, it
still will be written.
I'm not worrying about additional fields to the audit table containing
descriptive flags of what action took place yet. I just want the
mirror image for starters.
I got the script of the 'create table' off Query analyzer. created the
audit table.
the trigger looks like this:
CREATE TRIGGER dt_tbl1_audit
on tbl1
for insert, update, delete
AS
insert into tbl1_audit
select * from inserted
the table has about 50 fields or so, so I tried to make do with *'s.
didn't work, so I tried copying and pasting the explicit list of field
names
instead (though I'm not sure why it needs that if the two tables are
identically structured).
in either case, if I update any field on the audited table, I get this
error:
(after getting the warning that the results may take a long time to
process etc, the original table has over 100,000 rows)
"another user has modified the contents of this table or view,
the database row you are modifying no longer exists in the database
database error: insert error:
column name or number of supplied values does not match table
definition"
I'm not sure what's wrong, the two tables are identical (I copy pasted
the create table script with no changes). no other users except me on
this database.
i've removed all constraints and indexes from the audit table.
thanksHi
It seems that you are having problems with posting!!!
If speed is important you should think about keeping simple copies of the
inserted and deleted tables (possibly with other columns such as a timestamp
and user name) as this will mean that the trigger the least processing and
not causing you transactions to be open for a elongated period. The work of
resolving what columns have been updated can then be done either when
reporting or at a more convenient time.
If you wish to do this processing withing the trigger check out Books online
under the "Create Trigger" topic, you can find information about using the
UPDATED() and COLUMNS_UPDATED() functions and example of how to use them.
Another alternative method is to use a log file reading product such as
those from Lumigent (Lumigent Log Explorer) http://www.lumigent.com/ or PI,
http://www.logpi.com
and process the information in the log files.
HTH
John
"Me" <heruti@.lycos.com> wrote in message
news:2d4c3262.0411261512.6d8072aa@.posting.google.c om...
> Hi...
> A much lamented question, I guess..
> I'm trying to create a simple audit trail.
> log the changes to an SQL 2000 table, so that they are written into a
> mirror table. The entire record, only the updated one, i.e. if say
> only one field changes, the audit table will be inserted with one
> record that has one field changed. if the record has been deleted, it
> still will be written.
> I'm not worrying about additional fields to the audit table containing
> descriptive flags of what action took place yet. I just want the
> mirror image for starters.
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).
> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)
> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.
>
> thanks|||Me (heruti@.lycos.com) writes:
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).
Depends on what columns there are in the tables. If you have an IDENTITY
colunm in the source table, the corresponding table in the audit table
cannot have the IDENTITY property. And if there are timestamp columns,
you would have to make them binary(8) in the target table.
In any case, some sort of a primary key for the target table would be a good
idea.
> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)
So where does this warning come from?
> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.
Well, we don't even know the definition of the tables, so how could we
tell what is going on?
Do you get this error when you perform an update from Query Analyzer? If
so, can you cut and paste the complete error message? The error message
should include procedure name and line number where the message appears.
--
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 did have trouble posting, I do apologize..
IE6 reported some sort of 404 error when I clicked submit in the
dejanews post. I assumed it didn't & tried several dozen times until I
accidentally discovered that it did post (dejanews says it takes
several hours to post, so traditionally I wouldn't have discovered
this, but this time I stumbled on another site, sort of a gateway to
google groups, which showed my post immediately... seems useful.
http://news-reader.org/comp.databases.ms-sqlserver/
re the audit, I did get the code to work both ways, with *'s notation
and also with detail listing of all the fields. so this post is
generally for the benefit of other befuddled customers on my trail..
The following trigger works. audits updates/inserts on the table
tblSource, into the log table tblSource_Audit which has the same
structure plus the two fields 'LogActionType' (varchar 10) and
'LogDate':
CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
INSERT INTO tblSource_audit
select 'insert/upd',GetDate(),
* from Inserted ins
GO
Still, I thought it would be safer (future maintenance wise, so the
trigger won't break if fields are added to the source table)
to convert the whole thing into detailed column notation (too long to
list here), and Later add a little condition code to it that would
post 'insert' and 'update' strings identifying the two operations and
not the combined string above:
CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
Declare @.ActionType VARCHAR(10)
Declare @.DeleteCnt int
set @.DeleteCnt = (select count(*) from deleted)
if @.DeleteCnt = 0
begin
set @.ActionType = 'Insert'
end
ELSE
set @.ActionType = 'Update'
INSERT INTO tblSource_audit
select @.ActionType,GetDate(),
* from Inserted ins
GO
Thanks everyone for your help. Any more comments, of course, welcome.
Sunday, February 19, 2012
attempted to divide by zero
hi,
i had this formula written for a textbox in a table, but yet still encounter the following error:
expression:
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))
error:
attempted to divide by zero.
any way i can solve this problem?
thanks!
IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))
In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))
You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.
-- Robert
|||thanks a lot, Robert.|||
Hello Robert,
Thanks for this post.This helps me a lot.
I have tried on many sites to get help but not getting much
Thanks againSunil Pawar.
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||
HI Everyone,
I generally use the following statement
=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)
For the most part this formula is simple and effective...
BUT (there is always a but!!)
I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.
Regards,
A.Akin
|||Hi,Please try out with this formula,
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))
I think it will work.
Cheers,
Shri
= IIF(Fields!Cash_Resolution.Value = 0 Or Fields!Amount.Value = 0,0,
Sum(Fields!Cash_Resolution.Value)/ Sum(Fields!Amount.Value))
attempted to divide by zero
hi,
i had this formula written for a textbox in a table, but yet still encounter the following error:
expression:
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))
error:
attempted to divide by zero.
any way i can solve this problem?
thanks!
IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))
In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))
You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.
-- Robert
|||thanks a lot, Robert.|||
Hello Robert,
Thanks for this post.This helps me a lot.
I have tried on many sites to get help but not getting much
Thanks againSunil Pawar.
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||
HI Everyone,
I generally use the following statement
=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)
For the most part this formula is simple and effective...
BUT (there is always a but!!)
I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.
Regards,
A.Akin
|||Hi,Please try out with this formula,
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))
I think it will work.
Cheers,
Shri
attempted to divide by zero
hi,
i had this formula written for a textbox in a table, but yet still encounter the following error:
expression:
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))
error:
attempted to divide by zero.
any way i can solve this problem?
thanks!
IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))
In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))
You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.
-- Robert
|||thanks a lot, Robert.|||
Hello Robert,
Thanks for this post.This helps me a lot.
I have tried on many sites to get help but not getting much
Thanks againSunil Pawar.
HI Everyone,
I generally use the following statement
=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)
For the most part this formula is simple and effective...
BUT (there is always a but!!)
I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.
Regards,
A.Akin
|||Hi,Please try out with this formula,
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))
I think it will work.
Cheers,
Shri
attempted to divide by zero
hi,
i had this formula written for a textbox in a table, but yet still encounter the following error:
expression:
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))
error:
attempted to divide by zero.
any way i can solve this problem?
thanks!
IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))
In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))
You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.
-- Robert
|||thanks a lot, Robert.|||
Hello Robert,
Thanks for this post.This helps me a lot.
I have tried on many sites to get help but not getting much
Thanks againSunil Pawar.
HI Everyone,
I generally use the following statement
=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)
For the most part this formula is simple and effective...
BUT (there is always a but!!)
I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.
Regards,
A.Akin
|||Hi,Please try out with this formula,
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))
I think it will work.
Cheers,
Shri
attempted to divide by zero
hi,
i had this formula written for a textbox in a table, but yet still encounter the following error:
expression:
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))
error:
attempted to divide by zero.
any way i can solve this problem?
thanks!
IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))
In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))
You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.
-- Robert
|||thanks a lot, Robert.|||
Hello Robert,
Thanks for this post.This helps me a lot.
I have tried on many sites to get help but not getting much
Thanks againSunil Pawar.
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||
HI Everyone,
I generally use the following statement
=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)
For the most part this formula is simple and effective...
BUT (there is always a but!!)
I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.
Regards,
A.Akin
|||Hi,Please try out with this formula,
=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))
I think it will work.
Cheers,
Shri
Thursday, February 16, 2012
Attaching SQL 2000 DB in SQL 2005 and vice versa from CD-ROM
Hi all:
I am trying to attach db created in SQL 2000, detached and written on CD-ROM
in SQL 2005 and vice versa.
If I try to attach on the same SQL Server where it was created, it works fine.
I am using SQL 2000 generated db files as template to create databases
through API, both on SQL 2000 & on SQL 2005 machine.
1. When the database created on SQL 2005 attached in SQL 2000, I get the
following error.
Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 13, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
2. When the database created on SQL 2000 is attached in SQL 2005, I get the
following error.
Msg 3415, Level 16, State 1, Line 1
Database 'DB1' cannot be upgraded because it is read-only or has read-only
files. Make the database or files writeable, and rerun recovery.
Is there a workaround for this issue.
Any help would be appreciated.
TIA
-srini
You can't attach a 2005 db to 2000, regardless of read-only media or not.
As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
file structure. this means that for attach to work, the media cannot be read-only.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"srini" <srini@.discussions.microsoft.com> wrote in message
news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> Hi all:
> I am trying to attach db created in SQL 2000, detached and written on CD-ROM
> in SQL 2005 and vice versa.
> If I try to attach on the same SQL Server where it was created, it works fine.
> I am using SQL 2000 generated db files as template to create databases
> through API, both on SQL 2000 & on SQL 2005 machine.
>
> 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> following error.
> Server: Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 13, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> 2. When the database created on SQL 2000 is attached in SQL 2005, I get the
> following error.
> Msg 3415, Level 16, State 1, Line 1
> Database 'DB1' cannot be upgraded because it is read-only or has read-only
> files. Make the database or files writeable, and rerun recovery.
> Is there a workaround for this issue.
> Any help would be appreciated.
> TIA
> -srini
|||Thank you Tibor.
"Tibor Karaszi" wrote:
> You can't attach a 2005 db to 2000, regardless of read-only media or not.
> As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
> file structure. this means that for attach to work, the media cannot be read-only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
>
I am trying to attach db created in SQL 2000, detached and written on CD-ROM
in SQL 2005 and vice versa.
If I try to attach on the same SQL Server where it was created, it works fine.
I am using SQL 2000 generated db files as template to create databases
through API, both on SQL 2000 & on SQL 2005 machine.
1. When the database created on SQL 2005 attached in SQL 2000, I get the
following error.
Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 13, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
2. When the database created on SQL 2000 is attached in SQL 2005, I get the
following error.
Msg 3415, Level 16, State 1, Line 1
Database 'DB1' cannot be upgraded because it is read-only or has read-only
files. Make the database or files writeable, and rerun recovery.
Is there a workaround for this issue.
Any help would be appreciated.
TIA
-srini
You can't attach a 2005 db to 2000, regardless of read-only media or not.
As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
file structure. this means that for attach to work, the media cannot be read-only.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"srini" <srini@.discussions.microsoft.com> wrote in message
news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> Hi all:
> I am trying to attach db created in SQL 2000, detached and written on CD-ROM
> in SQL 2005 and vice versa.
> If I try to attach on the same SQL Server where it was created, it works fine.
> I am using SQL 2000 generated db files as template to create databases
> through API, both on SQL 2000 & on SQL 2005 machine.
>
> 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> following error.
> Server: Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 13, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> 2. When the database created on SQL 2000 is attached in SQL 2005, I get the
> following error.
> Msg 3415, Level 16, State 1, Line 1
> Database 'DB1' cannot be upgraded because it is read-only or has read-only
> files. Make the database or files writeable, and rerun recovery.
> Is there a workaround for this issue.
> Any help would be appreciated.
> TIA
> -srini
|||Thank you Tibor.
"Tibor Karaszi" wrote:
> You can't attach a 2005 db to 2000, regardless of read-only media or not.
> As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
> file structure. this means that for attach to work, the media cannot be read-only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
>
Attaching SQL 2000 DB in SQL 2005 and vice versa from CD-ROM
Hi all:
I am trying to attach db created in SQL 2000, detached and written on CD-ROM
in SQL 2005 and vice versa.
If I try to attach on the same SQL Server where it was created, it works fin
e.
I am using SQL 2000 generated db files as template to create databases
through API, both on SQL 2000 & on SQL 2005 machine.
1. When the database created on SQL 2005 attached in SQL 2000, I get the
following error.
Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 13, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
2. When the database created on SQL 2000 is attached in SQL 2005, I get the
following error.
Msg 3415, Level 16, State 1, Line 1
Database 'DB1' cannot be upgraded because it is read-only or has read-only
files. Make the database or files writeable, and rerun recovery.
Is there a workaround for this issue.
Any help would be appreciated.
TIA
-sriniYou can't attach a 2005 db to 2000, regardless of read-only media or not.
As for attaching 2000 database to 2005, it is doable, but SQL Server has to
be able to upgrade the
file structure. this means that for attach to work, the media cannot be read
-only.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"srini" <srini@.discussions.microsoft.com> wrote in message
news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> Hi all:
> I am trying to attach db created in SQL 2000, detached and written on CD-R
OM
> in SQL 2005 and vice versa.
> If I try to attach on the same SQL Server where it was created, it works f
ine.
> I am using SQL 2000 generated db files as template to create databases
> through API, both on SQL 2000 & on SQL 2005 machine.
>
> 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> following error.
> Server: Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 13, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> 2. When the database created on SQL 2000 is attached in SQL 2005, I get t
he
> following error.
> Msg 3415, Level 16, State 1, Line 1
> Database 'DB1' cannot be upgraded because it is read-only or has read-only
> files. Make the database or files writeable, and rerun recovery.
> Is there a workaround for this issue.
> Any help would be appreciated.
> TIA
> -srini|||Thank you Tibor.
"Tibor Karaszi" wrote:
> You can't attach a 2005 db to 2000, regardless of read-only media or not.
> As for attaching 2000 database to 2005, it is doable, but SQL Server has t
o be able to upgrade the
> file structure. this means that for attach to work, the media cannot be re
ad-only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
>
I am trying to attach db created in SQL 2000, detached and written on CD-ROM
in SQL 2005 and vice versa.
If I try to attach on the same SQL Server where it was created, it works fin
e.
I am using SQL 2000 generated db files as template to create databases
through API, both on SQL 2000 & on SQL 2005 machine.
1. When the database created on SQL 2005 attached in SQL 2000, I get the
following error.
Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 13, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
2. When the database created on SQL 2000 is attached in SQL 2005, I get the
following error.
Msg 3415, Level 16, State 1, Line 1
Database 'DB1' cannot be upgraded because it is read-only or has read-only
files. Make the database or files writeable, and rerun recovery.
Is there a workaround for this issue.
Any help would be appreciated.
TIA
-sriniYou can't attach a 2005 db to 2000, regardless of read-only media or not.
As for attaching 2000 database to 2005, it is doable, but SQL Server has to
be able to upgrade the
file structure. this means that for attach to work, the media cannot be read
-only.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"srini" <srini@.discussions.microsoft.com> wrote in message
news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> Hi all:
> I am trying to attach db created in SQL 2000, detached and written on CD-R
OM
> in SQL 2005 and vice versa.
> If I try to attach on the same SQL Server where it was created, it works f
ine.
> I am using SQL 2000 generated db files as template to create databases
> through API, both on SQL 2000 & on SQL 2005 machine.
>
> 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> following error.
> Server: Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 13, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> 2. When the database created on SQL 2000 is attached in SQL 2005, I get t
he
> following error.
> Msg 3415, Level 16, State 1, Line 1
> Database 'DB1' cannot be upgraded because it is read-only or has read-only
> files. Make the database or files writeable, and rerun recovery.
> Is there a workaround for this issue.
> Any help would be appreciated.
> TIA
> -srini|||Thank you Tibor.
"Tibor Karaszi" wrote:
> You can't attach a 2005 db to 2000, regardless of read-only media or not.
> As for attaching 2000 database to 2005, it is doable, but SQL Server has t
o be able to upgrade the
> file structure. this means that for attach to work, the media cannot be re
ad-only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
>
Attaching SQL 2000 DB in SQL 2005 and vice versa from CD-ROM
Hi all:
I am trying to attach db created in SQL 2000, detached and written on CD-ROM
in SQL 2005 and vice versa.
If I try to attach on the same SQL Server where it was created, it works fine.
I am using SQL 2000 generated db files as template to create databases
through API, both on SQL 2000 & on SQL 2005 machine.
1. When the database created on SQL 2005 attached in SQL 2000, I get the
following error.
Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 13, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
2. When the database created on SQL 2000 is attached in SQL 2005, I get the
following error.
Msg 3415, Level 16, State 1, Line 1
Database 'DB1' cannot be upgraded because it is read-only or has read-only
files. Make the database or files writeable, and rerun recovery.
Is there a workaround for this issue.
Any help would be appreciated.
TIA
-sriniYou can't attach a 2005 db to 2000, regardless of read-only media or not.
As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
file structure. this means that for attach to work, the media cannot be read-only.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"srini" <srini@.discussions.microsoft.com> wrote in message
news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> Hi all:
> I am trying to attach db created in SQL 2000, detached and written on CD-ROM
> in SQL 2005 and vice versa.
> If I try to attach on the same SQL Server where it was created, it works fine.
> I am using SQL 2000 generated db files as template to create databases
> through API, both on SQL 2000 & on SQL 2005 machine.
>
> 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> following error.
> Server: Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 13, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> 2. When the database created on SQL 2000 is attached in SQL 2005, I get the
> following error.
> Msg 3415, Level 16, State 1, Line 1
> Database 'DB1' cannot be upgraded because it is read-only or has read-only
> files. Make the database or files writeable, and rerun recovery.
> Is there a workaround for this issue.
> Any help would be appreciated.
> TIA
> -srini|||Thank you Tibor.
"Tibor Karaszi" wrote:
> You can't attach a 2005 db to 2000, regardless of read-only media or not.
> As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
> file structure. this means that for attach to work, the media cannot be read-only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> > Hi all:
> >
> > I am trying to attach db created in SQL 2000, detached and written on CD-ROM
> > in SQL 2005 and vice versa.
> >
> > If I try to attach on the same SQL Server where it was created, it works fine.
> >
> > I am using SQL 2000 generated db files as template to create databases
> > through API, both on SQL 2000 & on SQL 2005 machine.
> >
> >
> > 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> > following error.
> > Server: Msg 602, Level 21, State 50, Line 1
> > Could not find row in sysindexes for database ID 13, object ID 1, index ID
> > 1. Run DBCC CHECKTABLE on sysindexes.
> >
> > 2. When the database created on SQL 2000 is attached in SQL 2005, I get the
> > following error.
> > Msg 3415, Level 16, State 1, Line 1
> > Database 'DB1' cannot be upgraded because it is read-only or has read-only
> > files. Make the database or files writeable, and rerun recovery.
> >
> > Is there a workaround for this issue.
> >
> > Any help would be appreciated.
> >
> > TIA
> > -srini
>
I am trying to attach db created in SQL 2000, detached and written on CD-ROM
in SQL 2005 and vice versa.
If I try to attach on the same SQL Server where it was created, it works fine.
I am using SQL 2000 generated db files as template to create databases
through API, both on SQL 2000 & on SQL 2005 machine.
1. When the database created on SQL 2005 attached in SQL 2000, I get the
following error.
Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 13, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
2. When the database created on SQL 2000 is attached in SQL 2005, I get the
following error.
Msg 3415, Level 16, State 1, Line 1
Database 'DB1' cannot be upgraded because it is read-only or has read-only
files. Make the database or files writeable, and rerun recovery.
Is there a workaround for this issue.
Any help would be appreciated.
TIA
-sriniYou can't attach a 2005 db to 2000, regardless of read-only media or not.
As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
file structure. this means that for attach to work, the media cannot be read-only.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"srini" <srini@.discussions.microsoft.com> wrote in message
news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> Hi all:
> I am trying to attach db created in SQL 2000, detached and written on CD-ROM
> in SQL 2005 and vice versa.
> If I try to attach on the same SQL Server where it was created, it works fine.
> I am using SQL 2000 generated db files as template to create databases
> through API, both on SQL 2000 & on SQL 2005 machine.
>
> 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> following error.
> Server: Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 13, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> 2. When the database created on SQL 2000 is attached in SQL 2005, I get the
> following error.
> Msg 3415, Level 16, State 1, Line 1
> Database 'DB1' cannot be upgraded because it is read-only or has read-only
> files. Make the database or files writeable, and rerun recovery.
> Is there a workaround for this issue.
> Any help would be appreciated.
> TIA
> -srini|||Thank you Tibor.
"Tibor Karaszi" wrote:
> You can't attach a 2005 db to 2000, regardless of read-only media or not.
> As for attaching 2000 database to 2005, it is doable, but SQL Server has to be able to upgrade the
> file structure. this means that for attach to work, the media cannot be read-only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:718B30EB-2CDE-4F7A-B995-2254B4B3217C@.microsoft.com...
> > Hi all:
> >
> > I am trying to attach db created in SQL 2000, detached and written on CD-ROM
> > in SQL 2005 and vice versa.
> >
> > If I try to attach on the same SQL Server where it was created, it works fine.
> >
> > I am using SQL 2000 generated db files as template to create databases
> > through API, both on SQL 2000 & on SQL 2005 machine.
> >
> >
> > 1. When the database created on SQL 2005 attached in SQL 2000, I get the
> > following error.
> > Server: Msg 602, Level 21, State 50, Line 1
> > Could not find row in sysindexes for database ID 13, object ID 1, index ID
> > 1. Run DBCC CHECKTABLE on sysindexes.
> >
> > 2. When the database created on SQL 2000 is attached in SQL 2005, I get the
> > following error.
> > Msg 3415, Level 16, State 1, Line 1
> > Database 'DB1' cannot be upgraded because it is read-only or has read-only
> > files. Make the database or files writeable, and rerun recovery.
> >
> > Is there a workaround for this issue.
> >
> > Any help would be appreciated.
> >
> > TIA
> > -srini
>
Sunday, February 12, 2012
attached in vb8 vs vb6 incompatability
I have two programs which access sql server 2005. One was written in vb6 the other in vb8 (aka vb.net 2005).
vb6 works well with this connection string:
Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Database=MyDbName;Data Source=.\MyServerInstanceName
vb8 (in vs2005) generated the following connection string which works well in the vb8 application:
Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True
Within their own programs each connection string works well. However, if I use one program and then the other (e.g., vb8 program then vb6), I get an error message:
Unable to open the physical file "C:\MyDbPath\MyDbName.mdf". Operating system error 32: "32(The process cannot acces the file because it is being used by another process.)".
If I detach the database using SSMS, I can then access it with the other program. Obviously, this is not an ideal situation and not something I would want the end user to have to do.
Also, when using the vb6 connection string, SSMS lists the database in Object Explorer as: MyDbName. When using the vb8, the database is listed as C:\MyDbPath\MyDbName.mdf. BTW, the database is set for multi-user.
How can I get the two programs to place nice together?
YOu can either use the database file with one process or attach the database to a SQL Server (Express) instance to make it accessible for more than one application.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
When you use the attach command in the connection string the application that is running the command has exclusive access to the files, If you want the database to be used by multiple applications you will need to first make sure that the database will allow remote connections then attach the database to the database engine using the management tools or the command line tools. Once this is done you would then just connect using the following string
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
The above connection string was taken from www.connectionstrings.com
|||
The database absolutely must be used by more than one program. SQL Server Express is not an option because of its 4gb limit. Perhaps I'm not understanding something here. It sounds as tho you are saying an SQL Server database cannot be used by more than one program at the same time unless the database is so small that it is unusable for all except the simplest of applications.
|||Again, perhaps I am missing something. Are you saying SQL Server 2005 cannot be used by multiple users unless they are all using the same program, or they have enough database management experience to use tools such as SSMS, or even more shocking still: they have to use command line tools? This is starting sound like Linux and MySQL.
It is shocking to imagine that everyday users would have to have such extensive skills just to use database enabled Windows XP? applications.
|||Yes, you are right, you got me wrong :-)OK, a database file which is not attached can be attached as a user instance with a SQL Server Express instance. As a user instance, only the application which opened the connection to the user instance is able to access the database. If you attach the database file to a server instance, you can access the data through the SQL Server Express Service as you already know it from the common SQL Server database access.
Hope that clarifies things.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Sorry, but no.
Where am I using the user instance feature?
|||Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True|||Now, I am confused
The user instance login flag is not supported on this version of SQL Server. The connection will be closed.
|||I resolved the problem by starting both vb6 & vb8 programs with a check for whether or not the database is attached. If it is not, I use sp_attach_single_file_db to attach it. Then I changed the part of the vb8 connection AttachDbFilename= to Database=.
Subscribe to:
Posts (Atom)