Need to be able to open/convert Read Only SQL 7.0 databases in SQL 2000
I work in a production shop which creates upwards of 8000 SQL 7.0 = databases a month and we're considering updating all of them to SQL = 2000, BUT there are a lot of SQL 7.0 databases lying around that still = need to be usable in SQL 2000. The problem is that these 7.0 databases = are created with sp_create_removable and sp_certify_removable and they = can't be opened by SQL 2000 because SQL 2000 wants to "convert" them = before allowing the databases to be used.
Obviously I can open each of these databases in 7.0, use the ALTER = DATABASE command to change the readonlyfilegroup to allow read/write = access, BUT I would have to do this on another machine (since I can't = have both 7.0 and 2000 installed on the same machine) and I'd have to do = this for MANY months of data for most of the 8000+ database sets that = have already been created -- way to much work to make it worth the = effort).
Is there a way to change the readonlyfilegroup settings before SQL 2000 = attempts it's "conversion" without using SQL 7.0 to do so (within SQL = 2000 or a third party app of some sort). Or is it possible to open a = read only 7.0 database in SQL 2000 and bypass the "conversion" = completely? I've even looked into the possibility of reverse engineering = the "alter database" functionality so that I can write a stand-alone app = which changes the readonlyfilegroup to read/write, but I'm having a = helluva time working out what changes I need to replicate in the file = structures (a book on "undocumented SQL file formats" would probably = help, but I can't find any such item on the market).
Has anyone run into a similar need before? If so how did you get around = this limitation? Help! =20Jeff,
You might want to do some google searches on this topic since I seem to
remember someone else a little while back asking the same question. If I
remember correctly the answer was to change it in 7.0 first but it's
probably worth looking to be sure. But you mentioned you can't have 7.0 and
2000 on the same machine. Why not?
--
Andrew J. Kelly
SQL Server MVP
"Jeff Thompson" <jefft666@.yahoo.com> wrote in message
news:028401c3603f$c7a52180$a401280a@.phx.gbl...
Need to be able to open/convert Read Only SQL 7.0 databases in SQL 2000
I work in a production shop which creates upwards of 8000 SQL 7.0 databases
a month and we're considering updating all of them to SQL 2000, BUT there
are a lot of SQL 7.0 databases lying around that still need to be usable in
SQL 2000. The problem is that these 7.0 databases are created with
sp_create_removable and sp_certify_removable and they can't be opened by SQL
2000 because SQL 2000 wants to "convert" them before allowing the databases
to be used.
Obviously I can open each of these databases in 7.0, use the ALTER DATABASE
command to change the readonlyfilegroup to allow read/write access, BUT I
would have to do this on another machine (since I can't have both 7.0 and
2000 installed on the same machine) and I'd have to do this for MANY months
of data for most of the 8000+ database sets that have already been
created -- way to much work to make it worth the effort).
Is there a way to change the readonlyfilegroup settings before SQL 2000
attempts it's "conversion" without using SQL 7.0 to do so (within SQL 2000
or a third party app of some sort). Or is it possible to open a read only
7.0 database in SQL 2000 and bypass the "conversion" completely? I've even
looked into the possibility of reverse engineering the "alter database"
functionality so that I can write a stand-alone app which changes the
readonlyfilegroup to read/write, but I'm having a helluva time working out
what changes I need to replicate in the file structures (a book on
"undocumented SQL file formats" would probably help, but I can't find any
such item on the market).
Has anyone run into a similar need before? If so how did you get around this
limitation? Help!
Showing posts with label creates. Show all posts
Showing posts with label creates. Show all posts
Wednesday, March 28, 2012
Need to adjust pricing without affecting old pricing
Our database creates proposals, work orders, and purchase orders. Each of those systems pulls from the same price list. I need to be able to adjust pricing for products without affecting the proposals, work orders, and purchase orders. Does anybody know how to steer me in this direction?
Email: jason73178@.hotmail.comYou need to decide how price is set in your case. Is there one price for each transaction (every lineitem), one price for every item at any point in time (all items have a single price at a given time), or what you appear to have now, a single price for any one item at any time.
Three different problems, that actually aren't too closely related!
-PatP|||What we have now is every product has a price, and if we change the price for that product it will change the price of that product in our proposals, work orders, and purchase orders.
If the price of a product changes, we don't want the prices in our old proposals, work orders, and purchase orders to change. Meaning often times the price of a product will be changed by the manufacturer, and we need to be able to update that price. Now we are having to create new products with the extension "v2" at the end of the new model numbers just to accomodate price changes.
What we would like to happen is that when a proposal, work order, or purchase order is made we would like for the form to grap the price from the price list and store it in the document. And if the price of that product is changed by the manufacturer, it won't affect documents with the old price.|||Good plan. What you need to do is include a price attribute in every entity that references an item. In other words, for each lineitem in the quote table, there should be a price column, and the same for the other tables that need to reference price.
Another thing you might want to consider is including a price_history table, to show how item prices have changed over time. This can be a lifesaver when you are trying to unravel things 18 months from now!
-PatP|||My tables are:
tblProduct
tblWorkOrder
tblWorkOrderDetails
Where would I include the price attribute. I'm a beginner so I had a little hard time following the price attribute solution. Also how would I setup a price_history table?
Thank You for the help so far.
-Jason|||The product table would have a Price column and for workorders, since the price applies to the line item, you would put the price column in the details table.
Price history could look like
ID Identity
ProductNumber Whatever datatype you use
ChangeDate DateTime
OldPrice Money
NewPrice Money|||You would include the price everywhere it was referenced in a lineitem. My guess would be tblWorkOrderDetails given the tables you've named. Are there similar tables for proposals and purchase orders ?
grahamt has the right idea for the price history. I probably wouldn't include the old price, although I can see some reasons why you might want it.
-PatP|||I'm still missing something, probably pretty basic. How do I get the current price in the new column I've just made in the details table? And how do I store the OldPrice and NewPrice in order to create a table based on Old and New prices?
-Jason|||When you create a row in the details table, you'd probably get the price from the items table. When you change the price in the items table, you know the old and new prices, and those are what you put in the price history table.
-PatP|||This is a case where you do NOT want cascading updates to your prices. The item prices themselves should be stored with your quotes and proposals, and not just item IDs that link to a table of item prices.
If you want, you can't create a second field in your table called QuotedPrice that is never automatically updated.|||I'm trying, but it feels like I need my hand held on this one. After I add another field to the details table, what do I do with that field? Do I enter in the new price everytime some makes a work order, that won't work? Do I somehow link the field to the current price, if that is the way, how do I do that? I need a little more hand holding on this one and I think I can get it.
-Jason|||I assume (hope) your work orders are being entered through a stored procedure, and not having the user's access the database table directly.
Your stored procedure can record the current price in your work orders table as part of its processing. Alternatively, you could have an insert trigger on you workorders table that fills in the current item price.|||Jason asked me to offer a bit more help, so I'm responding in the forum in case someone else wants to comment. I need somebody to "keep me honest" these days, I'm a wee bit sleep deprived!
I don't know much about your environment, so I don't know what programming tools you might have available. I would expect that in whatever tool you are using to record the detail lines, you have the ability to look up a price from the item number. Once you have the price, in most environments you can simply write it along with the rest of the row.
If that isn't an option, you can create a trigger on your detail line table. A trigger is essentially a stored procedure (a bit of code stored inside the SQL Server) that runs when an event fires. The events that MS-SQL 2000 supports are INSERT, UPDATE, and DELETE. You could create a trigger for INSERT (and maybe UPDATE) that would replace any NULL price values with the current item price.
After you have a chance to digest this, let me know if you are "good to go" or if you'd like more pointers (and a clue as to what you'd like would help me a bunch)!
-PatP
Email: jason73178@.hotmail.comYou need to decide how price is set in your case. Is there one price for each transaction (every lineitem), one price for every item at any point in time (all items have a single price at a given time), or what you appear to have now, a single price for any one item at any time.
Three different problems, that actually aren't too closely related!
-PatP|||What we have now is every product has a price, and if we change the price for that product it will change the price of that product in our proposals, work orders, and purchase orders.
If the price of a product changes, we don't want the prices in our old proposals, work orders, and purchase orders to change. Meaning often times the price of a product will be changed by the manufacturer, and we need to be able to update that price. Now we are having to create new products with the extension "v2" at the end of the new model numbers just to accomodate price changes.
What we would like to happen is that when a proposal, work order, or purchase order is made we would like for the form to grap the price from the price list and store it in the document. And if the price of that product is changed by the manufacturer, it won't affect documents with the old price.|||Good plan. What you need to do is include a price attribute in every entity that references an item. In other words, for each lineitem in the quote table, there should be a price column, and the same for the other tables that need to reference price.
Another thing you might want to consider is including a price_history table, to show how item prices have changed over time. This can be a lifesaver when you are trying to unravel things 18 months from now!
-PatP|||My tables are:
tblProduct
tblWorkOrder
tblWorkOrderDetails
Where would I include the price attribute. I'm a beginner so I had a little hard time following the price attribute solution. Also how would I setup a price_history table?
Thank You for the help so far.
-Jason|||The product table would have a Price column and for workorders, since the price applies to the line item, you would put the price column in the details table.
Price history could look like
ID Identity
ProductNumber Whatever datatype you use
ChangeDate DateTime
OldPrice Money
NewPrice Money|||You would include the price everywhere it was referenced in a lineitem. My guess would be tblWorkOrderDetails given the tables you've named. Are there similar tables for proposals and purchase orders ?
grahamt has the right idea for the price history. I probably wouldn't include the old price, although I can see some reasons why you might want it.
-PatP|||I'm still missing something, probably pretty basic. How do I get the current price in the new column I've just made in the details table? And how do I store the OldPrice and NewPrice in order to create a table based on Old and New prices?
-Jason|||When you create a row in the details table, you'd probably get the price from the items table. When you change the price in the items table, you know the old and new prices, and those are what you put in the price history table.
-PatP|||This is a case where you do NOT want cascading updates to your prices. The item prices themselves should be stored with your quotes and proposals, and not just item IDs that link to a table of item prices.
If you want, you can't create a second field in your table called QuotedPrice that is never automatically updated.|||I'm trying, but it feels like I need my hand held on this one. After I add another field to the details table, what do I do with that field? Do I enter in the new price everytime some makes a work order, that won't work? Do I somehow link the field to the current price, if that is the way, how do I do that? I need a little more hand holding on this one and I think I can get it.
-Jason|||I assume (hope) your work orders are being entered through a stored procedure, and not having the user's access the database table directly.
Your stored procedure can record the current price in your work orders table as part of its processing. Alternatively, you could have an insert trigger on you workorders table that fills in the current item price.|||Jason asked me to offer a bit more help, so I'm responding in the forum in case someone else wants to comment. I need somebody to "keep me honest" these days, I'm a wee bit sleep deprived!
I don't know much about your environment, so I don't know what programming tools you might have available. I would expect that in whatever tool you are using to record the detail lines, you have the ability to look up a price from the item number. Once you have the price, in most environments you can simply write it along with the rest of the row.
If that isn't an option, you can create a trigger on your detail line table. A trigger is essentially a stored procedure (a bit of code stored inside the SQL Server) that runs when an event fires. The events that MS-SQL 2000 supports are INSERT, UPDATE, and DELETE. You could create a trigger for INSERT (and maybe UPDATE) that would replace any NULL price values with the current item price.
After you have a chance to digest this, let me know if you are "good to go" or if you'd like more pointers (and a clue as to what you'd like would help me a bunch)!
-PatP
Monday, March 19, 2012
Need security advice on xp_cmdshell, bcp, xml procedure
I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.
I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.
Is there a better, more secure way to generate this xml file.
ThanksTerri (terri@.cybernets.com) writes:
> I have a stored procedure that creates an xml file. It executes a SELECT
> statement with the FOR XML clause and then writes the xml file using bcp
> and xp_cmdshell. I am calling this procedure by passing it a parameter
> via ADO. I have configured the SQL Server Agent with a proxy account so
> non-SysAdmin can execute xp_cmdshell.
> I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
> concerned about having to maintain the password on my proxy account when
> that users' password changes.
> Is there a better, more secure way to generate this xml file.
I'm not really sure what you but it sounds like you do something like:
bcp "SELECT ... FOR XML" queryout outfile.bcp
This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.
So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> This is not likely to work very well. ODBC will chop the XML document
> after each 2033 character. See KB 275583.
> So you would need to get the XML document to the client, and have the
> client to create the file and put it where it belongs. Which probably
> is better from a security perspective as well.
Thanks Erland,
I'm not using Query Analyzer so I don't think KB 275583 applies to me.
I'm calling the following procedure via ADO
CREATE PROCEDURE procGenerateXML
@.CheckRequestID int
AS
declare @.sql nvarchar(4000)
set @.sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@.ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @.sql
GO
The procedure proctest looks like:
CREATE PROCEDURE proctest
@.ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO
I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID
cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection= Yes"
cn.Open provstr
Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute
I'm looking for guidance on the following:
-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell
Thanks|||Terri (terri@.cybernets.com) writes:
>> This is not likely to work very well. ODBC will chop the XML document
>> after each 2033 character. See KB 275583.
>>
>> So you would need to get the XML document to the client, and have the
>> client to create the file and put it where it belongs. Which probably
>> is better from a security perspective as well.
> Thanks Erland,
> I'm not using Query Analyzer so I don't think KB 275583 applies to me.
But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.
Erland Sommarskog wrote:
> Terri (terri@.cybernets.com) writes:
> >> This is not likely to work very well. ODBC will chop the XML document
> >> after each 2033 character. See KB 275583.
> >>
> >> So you would need to get the XML document to the client, and have the
> >> client to create the file and put it where it belongs. Which probably
> >> is better from a security perspective as well.
> > Thanks Erland,
> > I'm not using Query Analyzer so I don't think KB 275583 applies to me.
> But you are using BCP which is implemented in ODBC. So I would definitely
> encourage you to test to generate a large XML document, before you
> ponder the issues with access to xp_cmdshell.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||pb648174 (google@.webpaul.net) writes:
> If you are already calling this code from ADO, then instead of BCP and
> all that you can just directly execute the FOR XML statement, loop
> through each 2033 char return results and create the XML file from the
> web server.
Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.
I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.
But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.
(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to those who replied, I'm going to investigate the ADO stream object.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970CB19EE5537Yazorman@.127.0.0.1...
> pb648174 (google@.webpaul.net) writes:
> > If you are already calling this code from ADO, then instead of BCP and
> > all that you can just directly execute the FOR XML statement, loop
> > through each 2033 char return results and create the XML file from the
> > web server.
> Actually, if he would do it the simple-minded way, he would not get
> 2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
> thingie instead.
> I have not investigated it, but I believe the proper way to receive FOR
> XML in ADO with SQLOLEDB is to use the Stream object.
> But apart from that fine detail, I agree with you. Doing this from SQL
> Server will be diffictul.
> (*) If you use the MSDASQL provider, that is ODBC, then you would have
> to as you say. But I would not recommend that.|||I did something like this in C# .NET recently via the native SQL OleDB
provider and had to do the looping action. I think that even when I
submitted the Query using Query Analyzer, I could see it coming back as
multiple rows... I could be wrong though.
Subscribe to:
Posts (Atom)