Hi all,
I am creating reports using crystal report in VB.NET. The reports are created successfully and can be converted to PDF and Word.
After the creation i send generated documents as email. And also have to print them.
I don't want to use Adobe/Word for the printing. I want to create .prn files for printing in the generation process.
Is there any other option which can be used to print the created report.
I can print the reports directly from crystal reports but i want to split the process in two phases
1, Generation
2, Dispatching
Looking for ideaCant you make use of Print button avaliable in the Report?|||Thanks for your reply.
But the scenario is entierly different in my case. I am using Crystal Reports as window service and the process is splited in two phases
1, Generation
2, Dispatching
One process will generate a document and then save it
the other process will print that generated document
I can generate and print in one process with out storing the file on disk. But i want to do it in two steps.
Regards|||What is the difficulty in doing this in two steps?|||The difficulty is,
I have to create a report in Step one and have to save it to the disk. Right in which format i would store it so i can again open it through crystal report and send its print?
Or is there any other format to store the generated report? SO i can print it again through crystal report.
I don't want to use MS Word or Adobe automation for printing.
Regards|||If you show the report in your application, enable print button so that you can print it from CR|||Madhi
Thanks for the reply but i think you are not getting the issue which i am facing.
My application runs as Windows Service it has no GUI.
Also first i have to create a report , save it to the disk. The other process will print that saved report file.
I can save the report in pdf, word and other suported formats but to print pdf i have to use Adobe or to print word i need MS Word. Which i don't want.
I directly want to print the saved file.
Regards|||Well. Which front end application are you using?|||There is no such Front End.
The request is submitted to the data base and the application checks the data base for any new request.
On the new request it generates the report and stor/export in specific format also updates the data base.
The other application picks the stored documents/files and prints then, email or fax them then again update the database.
Email and Faxing is working well but the print is creating problem as i described in my previous reply.|||See if you are able find solution here
http://support.businessobjects.com/
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Primary Keys When importing data
I am creating a stored procedure for our SQL server which does the following:
1. Drops a table called 'Teachers'
drop table [users_dd].[dbo].[Teachers]
2. Recreates that table ready for new data like so...
CREATE TABLE [users_dd].[dbo].[Teachers] (
[username] char (30) NOT NULL,
[SL_Teachcode] nvarchar (12) NULL,
[DueDate] smalldatetime DEFAULT GetDate(),
[ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL)
3. The last step attempts to import new data. I have used the import/export data wizard to create this query to import new data.
select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]
The last step returns an error because the ID field will not accept Nulls. Of course I don't want it to have nulls as this is to be the primary key for this table. What I want is an automatically generated number there but how do I make that happen? I thought it was all in the definition of the field, ie [ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL). It seems there is something I missing here however.
Can anybody tell me what I doing wrong (or not doing)?
Murrayuse some thing like:
insert into [users_dd].[dbo].[Teachers] (
[username],].[SL_Teachcode])
select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]
tel us if it works
i try it on my side , it works.
Ridwan,,,|||If you do not need transactional recovery during this load you might do one or a combination of the folowing.
Instead of delete the table with corresponding rows, you might truncate it first and then delete it, less logged and faster. If you do drop the table, you can use select into instead of insert as that is minimally logged and faster as well.
HTH|||SELECT INTO is not faster and is not recommended because of concurrency implications. Truncating and then deleting just doesn't make sence. Why not just TRUNCATE?|||select into is not faster than insert into? I beg to differ, as inserts are fully logged they will take longer. They will also bloat your transaction log and if you have to reallocate space on a drive that could really hurt performance and slow things down. With select into there is no question of drive space allocation and it will run faster. Just for kicks I just ran a select into statement for 1.4 million rows, it took 26 seconds. For that same structure and table an insert into took 2 minutes and 54 seconds.
Now, obviously the argument would be the log and possibly the database file was growing in this time span so to make it an even playing field I truncated the table and did it again with no hard drive growth. The insert into only took 15 seconds without the hard drive growth but after a truncate and a drop table the select into only took 9 seconds.
So, I do not see any situation where the insert into is as fast as the select into and you get the bonus of knowing the hard drive will not have to reallocate space for the log file.
Yes, I meant truncate then DROP the table, not delete. Truncate with delete does not make sense, agreed.|||yup, blurred it out too fast :o
rhigdon, you're absolutely right, - did a 1M-row select into in 20 seconds and insert has been running for the past 6 minutes (Personal edition).
Now you got me experimenting with increased preallocated trx. log.
But I also noticed that we're assuming that recovery mode is Simple. Will it be the same difference in speed if I have it set to Full? Well, I'm still waiting on my insert :)|||No problem :)
I would assume so about the full and simple, you can watch that t-log grow by playing with the dbcc sqlperf('logspace') before and after inserts or select into's.|||BTW, do you have your auto-grow settings enabled? Most of our apps here got converted from 6.5 to either directly 2K or to 7.0 then 2K. Since in 6.5 world we didn't have this luxury of auto-growing, once discovered, - we first got relieved, but then started noticing that for 150+ servers we will have to implement space utilization monitors based on activity within each database. And then - react really quickly once the size reaches the disk limitation. So instead we performed a 2-month space growth analysis and pre-estimated potential annual growth for each database. Then we pre-allocated space on both data and trx. log devices and turned off auto-growth feature. Now we can tell with the level of accuracy to +/- 10MB how much disk space will be needed for any given database by the beginning of the next year.|||Thanks Ridwan,
I tried that code and worked fine. I think I went wrong by using the import/exprot data wizard to construct my query. As soon as I did it 'by hand' it worked fine.
Interesting conversation about the difference between select into and insert into. In this example there will never be more than about 90 -100 records so speed is not a big issue to this task as it takes less than a second to run anyhow.
Thank you all for your input... I hope I can return the favour someday.
Murray|||rdjabarov - that sounds pretty interesting, yes, we use auto-grow for everything. I do have a script that uses some stored procs that runs daily to check for free space and alert me if it gets low though.
1. Drops a table called 'Teachers'
drop table [users_dd].[dbo].[Teachers]
2. Recreates that table ready for new data like so...
CREATE TABLE [users_dd].[dbo].[Teachers] (
[username] char (30) NOT NULL,
[SL_Teachcode] nvarchar (12) NULL,
[DueDate] smalldatetime DEFAULT GetDate(),
[ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL)
3. The last step attempts to import new data. I have used the import/export data wizard to create this query to import new data.
select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]
The last step returns an error because the ID field will not accept Nulls. Of course I don't want it to have nulls as this is to be the primary key for this table. What I want is an automatically generated number there but how do I make that happen? I thought it was all in the definition of the field, ie [ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL). It seems there is something I missing here however.
Can anybody tell me what I doing wrong (or not doing)?
Murrayuse some thing like:
insert into [users_dd].[dbo].[Teachers] (
[username],].[SL_Teachcode])
select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]
tel us if it works
i try it on my side , it works.
Ridwan,,,|||If you do not need transactional recovery during this load you might do one or a combination of the folowing.
Instead of delete the table with corresponding rows, you might truncate it first and then delete it, less logged and faster. If you do drop the table, you can use select into instead of insert as that is minimally logged and faster as well.
HTH|||SELECT INTO is not faster and is not recommended because of concurrency implications. Truncating and then deleting just doesn't make sence. Why not just TRUNCATE?|||select into is not faster than insert into? I beg to differ, as inserts are fully logged they will take longer. They will also bloat your transaction log and if you have to reallocate space on a drive that could really hurt performance and slow things down. With select into there is no question of drive space allocation and it will run faster. Just for kicks I just ran a select into statement for 1.4 million rows, it took 26 seconds. For that same structure and table an insert into took 2 minutes and 54 seconds.
Now, obviously the argument would be the log and possibly the database file was growing in this time span so to make it an even playing field I truncated the table and did it again with no hard drive growth. The insert into only took 15 seconds without the hard drive growth but after a truncate and a drop table the select into only took 9 seconds.
So, I do not see any situation where the insert into is as fast as the select into and you get the bonus of knowing the hard drive will not have to reallocate space for the log file.
Yes, I meant truncate then DROP the table, not delete. Truncate with delete does not make sense, agreed.|||yup, blurred it out too fast :o
rhigdon, you're absolutely right, - did a 1M-row select into in 20 seconds and insert has been running for the past 6 minutes (Personal edition).
Now you got me experimenting with increased preallocated trx. log.
But I also noticed that we're assuming that recovery mode is Simple. Will it be the same difference in speed if I have it set to Full? Well, I'm still waiting on my insert :)|||No problem :)
I would assume so about the full and simple, you can watch that t-log grow by playing with the dbcc sqlperf('logspace') before and after inserts or select into's.|||BTW, do you have your auto-grow settings enabled? Most of our apps here got converted from 6.5 to either directly 2K or to 7.0 then 2K. Since in 6.5 world we didn't have this luxury of auto-growing, once discovered, - we first got relieved, but then started noticing that for 150+ servers we will have to implement space utilization monitors based on activity within each database. And then - react really quickly once the size reaches the disk limitation. So instead we performed a 2-month space growth analysis and pre-estimated potential annual growth for each database. Then we pre-allocated space on both data and trx. log devices and turned off auto-growth feature. Now we can tell with the level of accuracy to +/- 10MB how much disk space will be needed for any given database by the beginning of the next year.|||Thanks Ridwan,
I tried that code and worked fine. I think I went wrong by using the import/exprot data wizard to construct my query. As soon as I did it 'by hand' it worked fine.
Interesting conversation about the difference between select into and insert into. In this example there will never be more than about 90 -100 records so speed is not a big issue to this task as it takes less than a second to run anyhow.
Thank you all for your input... I hope I can return the favour someday.
Murray|||rdjabarov - that sounds pretty interesting, yes, we use auto-grow for everything. I do have a script that uses some stored procs that runs daily to check for free space and alert me if it gets low though.
Monday, March 12, 2012
Primary Key
Please help:
I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo
... what would be the primary key?! I know it would be DateTo and sumfing..... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"
... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to?
Thanks...... but I need a PK to make sure a bonus is not entered twice... not entered twice for what?
if you pay a $50 bonus, does this mean you will never pay another bonus for $50? of course not!
so you have to ask yourself how do you identify the separate entities that are getting the bonus
what are these things, anyway? products? you want to pay a bonus on a product?
don't the product headings relate to a product table or something?
can the same three product headings occur again on different dates?
you need to give some more thought to exactly what this table represents|||Soz sounds like i aint explained the table properly!!
headings are product groups...
eg.
Heading1 - Heading2 - Heading3- ProductID
Clothing - T-shirt - Lady - 705921
Bonus (or Commission) is likely to be a percentage value, the sales team get for the products that fall into that heading...
eg Clothing might have an overall value of 20%
while Clothing - T-shirt - Lady might have a specfic value of 10% for 10/10/04 to 10/10/06 and then Clothing - T-shirt - Lady has value of 30% 11/10/06 - 10/10/07...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself
the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels
you should really be putting the bonus on the product group in the product group table
and if you don't have a product group table, you should|||Sounds like time for a new table:
ProductID Bonus DateFrom DateTo
Unless you plan on offering different bonus amounts for the same product id in overlapping data ranges.
Or do you change the ProductID and have the same product under 2 (or more) ProductID numbers? (I once worked backend for a MAJOR big-box retailer ... that's how their folks handled discounted items ... same thing with different product id ... didn't agree with it, but was in no position to voice my concerns!)|||[QUOTE=tomh53]Sounds like time for a new table:
ProductID Bonus DateFrom DateTo
QUOTE]
that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...
a product can onli be in 1 heading group, no overlapping...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself
the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels
you should really be putting the bonus on the product group in the product group table
and if you don't have a product group table, you should
yup, its hierarchical...but what would the product group table have:
GroupID , heading1, heading2, heading3, bonus
BUT what do I do when the bonus is on
heading1, heading2... and not the whole Group?!|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)
a typical hierarchical structure, known as the adjacency model
see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure
link each product to whichever product group it belongs to
product groups at the top of their hierarchy have parent_id null|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)
a typical hierarchical structure, known as the adjacency model
see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure
link each product to whichever product group it belongs to
product groups at the top of their hierarchy have parent_id null
THANKS...looks promising (yet hard... think there is a lot of data so be a while 2 populate the table... currently they are just normal columns in my product table!) I'll give it a try and let u no how it goes...
Thanks agian :)|||sounds like it's time to normalize the model|||can i not write a insert query something lik this?!:
INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)
basically I want to insert what is in my subquery in2 ma new table?!|||sounds like it's time to normalize the model
Someone else created the database...lucki me... i remba how much i LUVED normalisation at uni :S
Thanks :)|||Stucky,
Please read the sticky (is he a relation of yours) at the top of this board, and post your question in the terms that it asks for.
Probably will get a solution in, oh say, 5 minutes|||can i not write a insert query something lik this?!:
INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)
basically I want to insert what is in my subquery in2 ma new table?!yes, you can write a query likje that
not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two
but you are on the right track
however, something is of great concern -- you have 3 headings in the product table?
this database is going to require a lot of redesign work...|||Stucky,
(is he a relation of yours)
nope :) just good minds fink alike :p
thanks again :)|||not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two
this database is going to require a lot of redesign work...
yeh onli supplied 2 cos i made the id an identity so gets assigned by system!!
yeh it does seem lik it will be a long job... I have created the table and filled it wiv sum records... will create the front end (VB.Net) before filling the rest... just in chance the project spec gets changed!! :p
Thanks for all the quick responses :) :angel:|||that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...
a product can onli be in 1 heading group, no overlapping...
Just a thought ... will the bonus be cumulative or exclusive?
For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?|||Just a thought ... will the bonus be cumulative or exclusive?
For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?
exclusive i fink?!|||Hey ... im bak LOLS!!!
well now i have created the application which saves the headingID and bonus.... (Quick refresh: 1 product has three heading levels - the new app assigns a bonus to a heading at ANY level)
I have to now write an update query that will calculate the Bonus for each transaction on the products.... where it selects the Bonus for the heading (and makes sure the transaction date is between bonus dateTo and bonus dateFrom)... this is all fine!
The problem is the update query should be:
If heading 1 has an assigned bonus, the transaction should be calcuted using it
else if Heading2 has an assigned bonus, use this
else if heading3 has an assigned bonus, use this
else bonus is Null
how would I write this case select so that it stops as soon as it finds an assigned bonus???
THANKS :)|||hey, ive got a similar problem. actually, if i start explaining the exact situation, ppl will get confused.
but anyway, i only want to say that this situation is absolutely possible.
the problem is if we make them as a composite primary key, all attribs which are part of the primary key have not null constraints.
is there a way to create composite keys such that either of the attributes must have not null constraint or are the databases built in such a fashion to support only the former?
i think the only other way out would be to provide some default values to prevent the violation of not null!!! this method is very local(i mean non-standard) i guess.|||i still think the hierarchy is the better design
no problem with nulls then
see post #8 in this thread|||Hi
I have used the hierarchy table to create a table which has the headingID and assigned bonus(productheadings)... what I'm stuck wiv is, how do I use this table to calculate the bonus for each transaction (Calculted bonus = sales*assignedbonus)...
Each transaction is with a product which always has 3 headings...
what i need to do is
1) check if heading1 has an assigned bonus (within transaction date)
2) If not, check heading2
3) if no bonus for heading2, check for heading3
If a bonus is assigned at any of the 3 stages, the query should calculate bonus and stop (or else bonus column in link table is null)... I dont no how to write this part of the query...
If I was only looking at 1 heading I would write something like:
(SALES TABLE - where I would store TranscationID and calculated bonus
SALES_STOCK - product table
productheadings - heading table
SALES_STOCK_BONUS - Link table... where I would store headingID and calculated bonus...
SELECT SALES.Product, Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3)
inner join SALES_STOCK_BONUS on
SALES_STOCK_BONUS.id = productheadings.id
Group by SALES.Product, Bonus, SALES.[Date], SALES_STOCK_BONUS.DateFrom, SALES_STOCK_BONUS.DateTo
having SALES.[Date] >= SALES_STOCK_BONUS.DateFrom and SALES.[Date] <= SALES_STOCK_BONUS.DateTo
how would I change this query so that it is in a if loop.... please help!!!|||dear mr/ms stuck1234
please can you show your exact table layout
in one sentence you say you have created the hierarchy table, but in the next sentence you say "Each transaction is with a product which always has 3 headings"
what is going on? i fail to understand|||productheading hierarchy table has
headingID, dateto, datefrom, bonus, headinglevel (ie 1, 2,3)
SALES_STOCK
PK - ProductID, WHse
- here there are 3 headings associated with each product
SALES
PK - transaction table which should contain a column CalculatedBonus = (productheadings.Bonus * Sale /100)
I can see that the query I wrote is all wrong, it has an extra table in it...should a bit more like:
SELECT SALES.Product, productheadings.Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3) -- This bit is wrong.... how do get it 2 do the if statement I explained above
Group by SALES.Product, productheadings.Bonus, SALES.[Date], productheadings.DateFrom, productheadings.DateTo
having SALES.[Date] >=productheadings.DateFrom and SALES.[Date] <= productheadings.DateTo|||Would this work:
DECLARE @.Date INT
SET @.Date = (Select Date from SALES)
SET @.ID1 = (Select ID1 from SALES)
SET @.ID2 = (Select Date from SALES)
SET @.ID3 = (Select Date from SALES)
If (select Bonus from productheadings where ID = @.ID1
and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
ELSE
If (select Bonus from productheadings where ID = @.ID2 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
ELSE
If (select Bonus from productheadings where ID = @.ID3 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
END
I will check 2moro if it wrks... but I got a feeling the if statement is right...but declaring the variables at the top is wrong... but how else would i get that data for each line...|||you did not understand the hierarchy table at all
i am sorry, but i cannot help you any further
good luck|||The structure I sed yesda was wrong soz...
this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus
Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...
I have seemed to have got majorly confused on this last night.... :(|||The structure I sed yesda was wrong soz...
this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus
Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...
I have seemed to have got majorly confused on this last night.... :(|||I just wana know if my thinkin of this is right?!
SALES_STOCK now has a foreign key called headingID which is linked to productheadings.id... headingID will be the ID of heading1... so now i need to write a query which looks for the Bonus (in SALES_STOCK_BONUS) for headingID, which is the NODE in the hierarchy table, if NULL then look at node child1, if NULL look for Bonus in node child2...
Is this correct line of thought??
I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo
... what would be the primary key?! I know it would be DateTo and sumfing..... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"
... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to?
Thanks...... but I need a PK to make sure a bonus is not entered twice... not entered twice for what?
if you pay a $50 bonus, does this mean you will never pay another bonus for $50? of course not!
so you have to ask yourself how do you identify the separate entities that are getting the bonus
what are these things, anyway? products? you want to pay a bonus on a product?
don't the product headings relate to a product table or something?
can the same three product headings occur again on different dates?
you need to give some more thought to exactly what this table represents|||Soz sounds like i aint explained the table properly!!
headings are product groups...
eg.
Heading1 - Heading2 - Heading3- ProductID
Clothing - T-shirt - Lady - 705921
Bonus (or Commission) is likely to be a percentage value, the sales team get for the products that fall into that heading...
eg Clothing might have an overall value of 20%
while Clothing - T-shirt - Lady might have a specfic value of 10% for 10/10/04 to 10/10/06 and then Clothing - T-shirt - Lady has value of 30% 11/10/06 - 10/10/07...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself
the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels
you should really be putting the bonus on the product group in the product group table
and if you don't have a product group table, you should|||Sounds like time for a new table:
ProductID Bonus DateFrom DateTo
Unless you plan on offering different bonus amounts for the same product id in overlapping data ranges.
Or do you change the ProductID and have the same product under 2 (or more) ProductID numbers? (I once worked backend for a MAJOR big-box retailer ... that's how their folks handled discounted items ... same thing with different product id ... didn't agree with it, but was in no position to voice my concerns!)|||[QUOTE=tomh53]Sounds like time for a new table:
ProductID Bonus DateFrom DateTo
QUOTE]
that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...
a product can onli be in 1 heading group, no overlapping...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself
the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels
you should really be putting the bonus on the product group in the product group table
and if you don't have a product group table, you should
yup, its hierarchical...but what would the product group table have:
GroupID , heading1, heading2, heading3, bonus
BUT what do I do when the bonus is on
heading1, heading2... and not the whole Group?!|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)
a typical hierarchical structure, known as the adjacency model
see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure
link each product to whichever product group it belongs to
product groups at the top of their hierarchy have parent_id null|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)
a typical hierarchical structure, known as the adjacency model
see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure
link each product to whichever product group it belongs to
product groups at the top of their hierarchy have parent_id null
THANKS...looks promising (yet hard... think there is a lot of data so be a while 2 populate the table... currently they are just normal columns in my product table!) I'll give it a try and let u no how it goes...
Thanks agian :)|||sounds like it's time to normalize the model|||can i not write a insert query something lik this?!:
INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)
basically I want to insert what is in my subquery in2 ma new table?!|||sounds like it's time to normalize the model
Someone else created the database...lucki me... i remba how much i LUVED normalisation at uni :S
Thanks :)|||Stucky,
Please read the sticky (is he a relation of yours) at the top of this board, and post your question in the terms that it asks for.
Probably will get a solution in, oh say, 5 minutes|||can i not write a insert query something lik this?!:
INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)
basically I want to insert what is in my subquery in2 ma new table?!yes, you can write a query likje that
not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two
but you are on the right track
however, something is of great concern -- you have 3 headings in the product table?
this database is going to require a lot of redesign work...|||Stucky,
(is he a relation of yours)
nope :) just good minds fink alike :p
thanks again :)|||not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two
this database is going to require a lot of redesign work...
yeh onli supplied 2 cos i made the id an identity so gets assigned by system!!
yeh it does seem lik it will be a long job... I have created the table and filled it wiv sum records... will create the front end (VB.Net) before filling the rest... just in chance the project spec gets changed!! :p
Thanks for all the quick responses :) :angel:|||that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...
a product can onli be in 1 heading group, no overlapping...
Just a thought ... will the bonus be cumulative or exclusive?
For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?|||Just a thought ... will the bonus be cumulative or exclusive?
For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?
exclusive i fink?!|||Hey ... im bak LOLS!!!
well now i have created the application which saves the headingID and bonus.... (Quick refresh: 1 product has three heading levels - the new app assigns a bonus to a heading at ANY level)
I have to now write an update query that will calculate the Bonus for each transaction on the products.... where it selects the Bonus for the heading (and makes sure the transaction date is between bonus dateTo and bonus dateFrom)... this is all fine!
The problem is the update query should be:
If heading 1 has an assigned bonus, the transaction should be calcuted using it
else if Heading2 has an assigned bonus, use this
else if heading3 has an assigned bonus, use this
else bonus is Null
how would I write this case select so that it stops as soon as it finds an assigned bonus???
THANKS :)|||hey, ive got a similar problem. actually, if i start explaining the exact situation, ppl will get confused.
but anyway, i only want to say that this situation is absolutely possible.
the problem is if we make them as a composite primary key, all attribs which are part of the primary key have not null constraints.
is there a way to create composite keys such that either of the attributes must have not null constraint or are the databases built in such a fashion to support only the former?
i think the only other way out would be to provide some default values to prevent the violation of not null!!! this method is very local(i mean non-standard) i guess.|||i still think the hierarchy is the better design
no problem with nulls then
see post #8 in this thread|||Hi
I have used the hierarchy table to create a table which has the headingID and assigned bonus(productheadings)... what I'm stuck wiv is, how do I use this table to calculate the bonus for each transaction (Calculted bonus = sales*assignedbonus)...
Each transaction is with a product which always has 3 headings...
what i need to do is
1) check if heading1 has an assigned bonus (within transaction date)
2) If not, check heading2
3) if no bonus for heading2, check for heading3
If a bonus is assigned at any of the 3 stages, the query should calculate bonus and stop (or else bonus column in link table is null)... I dont no how to write this part of the query...
If I was only looking at 1 heading I would write something like:
(SALES TABLE - where I would store TranscationID and calculated bonus
SALES_STOCK - product table
productheadings - heading table
SALES_STOCK_BONUS - Link table... where I would store headingID and calculated bonus...
SELECT SALES.Product, Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3)
inner join SALES_STOCK_BONUS on
SALES_STOCK_BONUS.id = productheadings.id
Group by SALES.Product, Bonus, SALES.[Date], SALES_STOCK_BONUS.DateFrom, SALES_STOCK_BONUS.DateTo
having SALES.[Date] >= SALES_STOCK_BONUS.DateFrom and SALES.[Date] <= SALES_STOCK_BONUS.DateTo
how would I change this query so that it is in a if loop.... please help!!!|||dear mr/ms stuck1234
please can you show your exact table layout
in one sentence you say you have created the hierarchy table, but in the next sentence you say "Each transaction is with a product which always has 3 headings"
what is going on? i fail to understand|||productheading hierarchy table has
headingID, dateto, datefrom, bonus, headinglevel (ie 1, 2,3)
SALES_STOCK
PK - ProductID, WHse
- here there are 3 headings associated with each product
SALES
PK - transaction table which should contain a column CalculatedBonus = (productheadings.Bonus * Sale /100)
I can see that the query I wrote is all wrong, it has an extra table in it...should a bit more like:
SELECT SALES.Product, productheadings.Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3) -- This bit is wrong.... how do get it 2 do the if statement I explained above
Group by SALES.Product, productheadings.Bonus, SALES.[Date], productheadings.DateFrom, productheadings.DateTo
having SALES.[Date] >=productheadings.DateFrom and SALES.[Date] <= productheadings.DateTo|||Would this work:
DECLARE @.Date INT
SET @.Date = (Select Date from SALES)
SET @.ID1 = (Select ID1 from SALES)
SET @.ID2 = (Select Date from SALES)
SET @.ID3 = (Select Date from SALES)
If (select Bonus from productheadings where ID = @.ID1
and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
ELSE
If (select Bonus from productheadings where ID = @.ID2 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
ELSE
If (select Bonus from productheadings where ID = @.ID3 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
END
I will check 2moro if it wrks... but I got a feeling the if statement is right...but declaring the variables at the top is wrong... but how else would i get that data for each line...|||you did not understand the hierarchy table at all
i am sorry, but i cannot help you any further
good luck|||The structure I sed yesda was wrong soz...
this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus
Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...
I have seemed to have got majorly confused on this last night.... :(|||The structure I sed yesda was wrong soz...
this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus
Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...
I have seemed to have got majorly confused on this last night.... :(|||I just wana know if my thinkin of this is right?!
SALES_STOCK now has a foreign key called headingID which is linked to productheadings.id... headingID will be the ID of heading1... so now i need to write a query which looks for the Bonus (in SALES_STOCK_BONUS) for headingID, which is the NODE in the hierarchy table, if NULL then look at node child1, if NULL look for Bonus in node child2...
Is this correct line of thought??
Monday, February 20, 2012
Preventing Extended Properties
Is there a way to tell SQL Server not to bother storing extended
properties?
I see no use for these, except bothering me when i am creating SQL
scripts for recreation on another database...
Any way to do that?
TascienHow do you mean "bothering" you?
How are you generating the scripts? Extended properties are excluded by
default from scripts generated by sql server (both EM and QA).
tascienu@.ecoaches.com wrote:
>Is there a way to tell SQL Server not to bother storing extended
>properties?
>I see no use for these, except bothering me when i am creating SQL
>scripts for recreation on another database...
>Any way to do that?
>Tascien
>
>|||I am using Adept SQL diff to compare my databases and extended
properties are bothering me... beside that, i really don't need them.
Anyway, sorry for bothering the group at this time, i've found a
solution for anyone else who doesn't like extended properties like
me...
1. Right click the SQL Server > Properties > Server Settings > and
Enable the checkbox "Allow modifications to be made directly to the
system catalog".
2. run the script below in the Query Analyser:
use [YourDatabaseName]
DELETE FROM sysproperties
3. Right click the SQL Server > Properties > Server Settings > and
"Disable" the checkbox "Allow modifications to be made directly to the
system catalog".
That should do!!!
Thank you for anyone who tried to help.
Tascien|||> DELETE FROM sysproperties
YIKES! I'm not sure this is such a good recommendation.|||Not good, but it works for me... USE AT YOUR OWN RISK!!!!
properties?
I see no use for these, except bothering me when i am creating SQL
scripts for recreation on another database...
Any way to do that?
TascienHow do you mean "bothering" you?
How are you generating the scripts? Extended properties are excluded by
default from scripts generated by sql server (both EM and QA).
tascienu@.ecoaches.com wrote:
>Is there a way to tell SQL Server not to bother storing extended
>properties?
>I see no use for these, except bothering me when i am creating SQL
>scripts for recreation on another database...
>Any way to do that?
>Tascien
>
>|||I am using Adept SQL diff to compare my databases and extended
properties are bothering me... beside that, i really don't need them.
Anyway, sorry for bothering the group at this time, i've found a
solution for anyone else who doesn't like extended properties like
me...
1. Right click the SQL Server > Properties > Server Settings > and
Enable the checkbox "Allow modifications to be made directly to the
system catalog".
2. run the script below in the Query Analyser:
use [YourDatabaseName]
DELETE FROM sysproperties
3. Right click the SQL Server > Properties > Server Settings > and
"Disable" the checkbox "Allow modifications to be made directly to the
system catalog".
That should do!!!
Thank you for anyone who tried to help.
Tascien|||> DELETE FROM sysproperties
YIKES! I'm not sure this is such a good recommendation.|||Not good, but it works for me... USE AT YOUR OWN RISK!!!!
Preventing driver from creating nuisance temp files
Hi,
I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack 2,
Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
I am having trouble with the driver creating huge numbers of temporary files
and never deleting them.
Each time I call the getPrimaryKeys(catalog, schema, table) method of the
DatabaseMetaData class I get two temperary file saved to the windows
temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and YYYYY are
numbers).
The code I am using is as follows;
try {
Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
}
catch (ClassNotFoundException ce) {
}
Connection con;
con =
DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMethod=cursor;DatabaseName=data base1, user, passwd);
DatabaseMetaData md = con.getMetaData();
String catalog = null;
String schema = null;
String table = "table1";
ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
The scb file is empty and the srt file always containes the same binary
number 00 00 00 01. These files are never deleted and quickly build up to
many thousands of files.
Can anybody suggest how to prevent these files from being created?
Any assistance would be much appreciated.
Regards,
Ian555
| Thread-Topic: Preventing driver from creating nuisance temp files
| thread-index: AcS7DnlA9D48A8QHSk6192TimiCXsQ==
| X-WBNR-Posting-Host: 203.59.26.12
| From: =?Utf-8?B?SWFuNTU1?= <Ian555@.discussions.microsoft.com>
| Subject: Preventing driver from creating nuisance temp files
| Date: Mon, 25 Oct 2004 20:47:11 -0700
| Lines: 43
| Message-ID: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6419
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi,
|
| I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack 2,
| Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
|
| I am having trouble with the driver creating huge numbers of temporary
files
| and never deleting them.
|
| Each time I call the getPrimaryKeys(catalog, schema, table) method of the
| DatabaseMetaData class I get two temperary file saved to the windows
| temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and YYYYY
are
| numbers).
|
| The code I am using is as follows;
|
| try {
| Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
| }
| catch (ClassNotFoundException ce) {
| }
| Connection con;
| con =
|
DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMe
thod=cursor;DatabaseName=database1, user, passwd);
|
| DatabaseMetaData md = con.getMetaData();
|
| String catalog = null;
| String schema = null;
| String table = "table1";
|
| ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
|
| The scb file is empty and the srt file always containes the same binary
| number 00 00 00 01. These files are never deleted and quickly build up
to
| many thousands of files.
|
| Can anybody suggest how to prevent these files from being created?
|
| Any assistance would be much appreciated.
|
| Regards,
|
| Ian555
|
Hello,
We are going to investigate this problem and post back the results. Thanks
for bringing this to our attention.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||| X-Tomcat-ID: 447862052
| References: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]")
| Organization: Microsoft Corporation
| Date: Tue, 26 Oct 2004 17:41:42 GMT
| Subject: RE: Preventing driver from creating nuisance temp files
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| Message-ID: <YIBoRM4uEHA.2092@.cpmsftngxa10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Lines: 77
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6423
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
|
| --
| | Thread-Topic: Preventing driver from creating nuisance temp files
| | thread-index: AcS7DnlA9D48A8QHSk6192TimiCXsQ==
| | X-WBNR-Posting-Host: 203.59.26.12
| | From: =?Utf-8?B?SWFuNTU1?= <Ian555@.discussions.microsoft.com>
| | Subject: Preventing driver from creating nuisance temp files
| | Date: Mon, 25 Oct 2004 20:47:11 -0700
| | Lines: 43
| | Message-ID: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 7bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6419
| | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| |
| | Hi,
| |
| | I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack
2,
| | Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
| |
| | I am having trouble with the driver creating huge numbers of temporary
| files
| | and never deleting them.
| |
| | Each time I call the getPrimaryKeys(catalog, schema, table) method of
the
| | DatabaseMetaData class I get two temperary file saved to the windows
| | temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and
YYYYY
| are
| | numbers).
| |
| | The code I am using is as follows;
| |
| | try {
| |
Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
| | }
| | catch (ClassNotFoundException ce) {
| | }
| | Connection con;
| | con =
| |
|
DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMe
| thod=cursor;DatabaseName=database1, user, passwd);
| |
| | DatabaseMetaData md = con.getMetaData();
| |
| | String catalog = null;
| | String schema = null;
| | String table = "table1";
| |
| | ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
| |
| | The scb file is empty and the srt file always containes the same binary
| | number 00 00 00 01. These files are never deleted and quickly build up
| to
| | many thousands of files.
| |
| | Can anybody suggest how to prevent these files from being created?
| |
| | Any assistance would be much appreciated.
| |
| | Regards,
| |
| | Ian555
| |
|
| Hello,
|
| We are going to investigate this problem and post back the results.
Thanks
| for bringing this to our attention.
|
| Carb Simien, MCSE MCDBA MCAD
| Microsoft Developer Support - Web Data
|
| Please reply only to the newsgroups.
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| Are you secure? For information about the Strategic Technology
Protection
| Program and to order your FREE Security Tool Kit, please visit
| http://www.microsoft.com/security.
|
|
Hello,
Are you closing your ResultSet in your repro code? If I close the
ResultSet in my test, then the files are not left over any more. Please
post back indicating whether or not this changes the behavior.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hi Carb,
Yes, explicitly closing the resultset has prevented the temp files from
remaining in the windows temp directory.
Thank you for your assistance with this problem.
Best regards,
Ian
""Carb Simien [MSFT]"" wrote:
> --
> | X-Tomcat-ID: 447862052
> | References: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain
> | Content-Transfer-Encoding: 7bit
> | From: CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]")
> | Organization: Microsoft Corporation
> | Date: Tue, 26 Oct 2004 17:41:42 GMT
> | Subject: RE: Preventing driver from creating nuisance temp files
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> | Message-ID: <YIBoRM4uEHA.2092@.cpmsftngxa10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | Lines: 77
> | Path: cpmsftngxa10.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6423
> | NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
> |
> |
> | --
> | | Thread-Topic: Preventing driver from creating nuisance temp files
> | | thread-index: AcS7DnlA9D48A8QHSk6192TimiCXsQ==
> | | X-WBNR-Posting-Host: 203.59.26.12
> | | From: =?Utf-8?B?SWFuNTU1?= <Ian555@.discussions.microsoft.com>
> | | Subject: Preventing driver from creating nuisance temp files
> | | Date: Mon, 25 Oct 2004 20:47:11 -0700
> | | Lines: 43
> | | Message-ID: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
> | | MIME-Version: 1.0
> | | Content-Type: text/plain;
> | | charset="Utf-8"
> | | Content-Transfer-Encoding: 7bit
> | | X-Newsreader: Microsoft CDO for Windows 2000
> | | Content-Class: urn:content-classes:message
> | | Importance: normal
> | | Priority: normal
> | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6419
> | | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> | |
> | | Hi,
> | |
> | | I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack
> 2,
> | | Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
> | |
> | | I am having trouble with the driver creating huge numbers of temporary
> | files
> | | and never deleting them.
> | |
> | | Each time I call the getPrimaryKeys(catalog, schema, table) method of
> the
> | | DatabaseMetaData class I get two temperary file saved to the windows
> | | temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and
> YYYYY
> | are
> | | numbers).
> | |
> | | The code I am using is as follows;
> | |
> | | try {
> | |
> Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
> | | }
> | | catch (ClassNotFoundException ce) {
> | | }
> | | Connection con;
> | | con =
> | |
> |
> DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMe
> | thod=cursor;DatabaseName=database1, user, passwd);
> | |
> | | DatabaseMetaData md = con.getMetaData();
> | |
> | | String catalog = null;
> | | String schema = null;
> | | String table = "table1";
> | |
> | | ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
> | |
> | | The scb file is empty and the srt file always containes the same binary
> | | number 00 00 00 01. These files are never deleted and quickly build up
> | to
> | | many thousands of files.
> | |
> | | Can anybody suggest how to prevent these files from being created?
> | |
> | | Any assistance would be much appreciated.
> | |
> | | Regards,
> | |
> | | Ian555
> | |
> |
> | Hello,
> |
> | We are going to investigate this problem and post back the results.
> Thanks
> | for bringing this to our attention.
> |
> | Carb Simien, MCSE MCDBA MCAD
> | Microsoft Developer Support - Web Data
> |
> | Please reply only to the newsgroups.
> | This posting is provided "AS IS" with no warranties, and confers no
> rights.
> |
> | Are you secure? For information about the Strategic Technology
> Protection
> | Program and to order your FREE Security Tool Kit, please visit
> | http://www.microsoft.com/security.
> |
> |
> Hello,
> Are you closing your ResultSet in your repro code? If I close the
> ResultSet in my test, then the files are not left over any more. Please
> post back indicating whether or not this changes the behavior.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack 2,
Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
I am having trouble with the driver creating huge numbers of temporary files
and never deleting them.
Each time I call the getPrimaryKeys(catalog, schema, table) method of the
DatabaseMetaData class I get two temperary file saved to the windows
temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and YYYYY are
numbers).
The code I am using is as follows;
try {
Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
}
catch (ClassNotFoundException ce) {
}
Connection con;
con =
DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMethod=cursor;DatabaseName=data base1, user, passwd);
DatabaseMetaData md = con.getMetaData();
String catalog = null;
String schema = null;
String table = "table1";
ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
The scb file is empty and the srt file always containes the same binary
number 00 00 00 01. These files are never deleted and quickly build up to
many thousands of files.
Can anybody suggest how to prevent these files from being created?
Any assistance would be much appreciated.
Regards,
Ian555
| Thread-Topic: Preventing driver from creating nuisance temp files
| thread-index: AcS7DnlA9D48A8QHSk6192TimiCXsQ==
| X-WBNR-Posting-Host: 203.59.26.12
| From: =?Utf-8?B?SWFuNTU1?= <Ian555@.discussions.microsoft.com>
| Subject: Preventing driver from creating nuisance temp files
| Date: Mon, 25 Oct 2004 20:47:11 -0700
| Lines: 43
| Message-ID: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6419
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi,
|
| I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack 2,
| Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
|
| I am having trouble with the driver creating huge numbers of temporary
files
| and never deleting them.
|
| Each time I call the getPrimaryKeys(catalog, schema, table) method of the
| DatabaseMetaData class I get two temperary file saved to the windows
| temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and YYYYY
are
| numbers).
|
| The code I am using is as follows;
|
| try {
| Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
| }
| catch (ClassNotFoundException ce) {
| }
| Connection con;
| con =
|
DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMe
thod=cursor;DatabaseName=database1, user, passwd);
|
| DatabaseMetaData md = con.getMetaData();
|
| String catalog = null;
| String schema = null;
| String table = "table1";
|
| ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
|
| The scb file is empty and the srt file always containes the same binary
| number 00 00 00 01. These files are never deleted and quickly build up
to
| many thousands of files.
|
| Can anybody suggest how to prevent these files from being created?
|
| Any assistance would be much appreciated.
|
| Regards,
|
| Ian555
|
Hello,
We are going to investigate this problem and post back the results. Thanks
for bringing this to our attention.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||| X-Tomcat-ID: 447862052
| References: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]")
| Organization: Microsoft Corporation
| Date: Tue, 26 Oct 2004 17:41:42 GMT
| Subject: RE: Preventing driver from creating nuisance temp files
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| Message-ID: <YIBoRM4uEHA.2092@.cpmsftngxa10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Lines: 77
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6423
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
|
| --
| | Thread-Topic: Preventing driver from creating nuisance temp files
| | thread-index: AcS7DnlA9D48A8QHSk6192TimiCXsQ==
| | X-WBNR-Posting-Host: 203.59.26.12
| | From: =?Utf-8?B?SWFuNTU1?= <Ian555@.discussions.microsoft.com>
| | Subject: Preventing driver from creating nuisance temp files
| | Date: Mon, 25 Oct 2004 20:47:11 -0700
| | Lines: 43
| | Message-ID: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 7bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6419
| | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| |
| | Hi,
| |
| | I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack
2,
| | Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
| |
| | I am having trouble with the driver creating huge numbers of temporary
| files
| | and never deleting them.
| |
| | Each time I call the getPrimaryKeys(catalog, schema, table) method of
the
| | DatabaseMetaData class I get two temperary file saved to the windows
| | temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and
YYYYY
| are
| | numbers).
| |
| | The code I am using is as follows;
| |
| | try {
| |
Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
| | }
| | catch (ClassNotFoundException ce) {
| | }
| | Connection con;
| | con =
| |
|
DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMe
| thod=cursor;DatabaseName=database1, user, passwd);
| |
| | DatabaseMetaData md = con.getMetaData();
| |
| | String catalog = null;
| | String schema = null;
| | String table = "table1";
| |
| | ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
| |
| | The scb file is empty and the srt file always containes the same binary
| | number 00 00 00 01. These files are never deleted and quickly build up
| to
| | many thousands of files.
| |
| | Can anybody suggest how to prevent these files from being created?
| |
| | Any assistance would be much appreciated.
| |
| | Regards,
| |
| | Ian555
| |
|
| Hello,
|
| We are going to investigate this problem and post back the results.
Thanks
| for bringing this to our attention.
|
| Carb Simien, MCSE MCDBA MCAD
| Microsoft Developer Support - Web Data
|
| Please reply only to the newsgroups.
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| Are you secure? For information about the Strategic Technology
Protection
| Program and to order your FREE Security Tool Kit, please visit
| http://www.microsoft.com/security.
|
|
Hello,
Are you closing your ResultSet in your repro code? If I close the
ResultSet in my test, then the files are not left over any more. Please
post back indicating whether or not this changes the behavior.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hi Carb,
Yes, explicitly closing the resultset has prevented the temp files from
remaining in the windows temp directory.
Thank you for your assistance with this problem.
Best regards,
Ian
""Carb Simien [MSFT]"" wrote:
> --
> | X-Tomcat-ID: 447862052
> | References: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain
> | Content-Transfer-Encoding: 7bit
> | From: CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]")
> | Organization: Microsoft Corporation
> | Date: Tue, 26 Oct 2004 17:41:42 GMT
> | Subject: RE: Preventing driver from creating nuisance temp files
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> | Message-ID: <YIBoRM4uEHA.2092@.cpmsftngxa10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | Lines: 77
> | Path: cpmsftngxa10.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6423
> | NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
> |
> |
> | --
> | | Thread-Topic: Preventing driver from creating nuisance temp files
> | | thread-index: AcS7DnlA9D48A8QHSk6192TimiCXsQ==
> | | X-WBNR-Posting-Host: 203.59.26.12
> | | From: =?Utf-8?B?SWFuNTU1?= <Ian555@.discussions.microsoft.com>
> | | Subject: Preventing driver from creating nuisance temp files
> | | Date: Mon, 25 Oct 2004 20:47:11 -0700
> | | Lines: 43
> | | Message-ID: <248D2293-B2AF-4FF0-B5E2-DB42E12357DD@.microsoft.com>
> | | MIME-Version: 1.0
> | | Content-Type: text/plain;
> | | charset="Utf-8"
> | | Content-Transfer-Encoding: 7bit
> | | X-Newsreader: Microsoft CDO for Windows 2000
> | | Content-Class: urn:content-classes:message
> | | Importance: normal
> | | Priority: normal
> | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6419
> | | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> | |
> | | Hi,
> | |
> | | I am using the Microsoft SQL Server 2000 Driver for JDBC, Service Pack
> 2,
> | | Version 2.2.0037 with windows XP and j2sdk1.4.2_03.
> | |
> | | I am having trouble with the driver creating huge numbers of temporary
> | files
> | | and never deleting them.
> | |
> | | Each time I call the getPrimaryKeys(catalog, schema, table) method of
> the
> | | DatabaseMetaData class I get two temperary file saved to the windows
> | | temporary directory, a srt_XXXXX.tmp and a scb_YYYYY.tmp (XXXXX and
> YYYYY
> | are
> | | numbers).
> | |
> | | The code I am using is as follows;
> | |
> | | try {
> | |
> Class.forName(com.microsoft.jdbc.sqlserver.SQLServ erDriver);
> | | }
> | | catch (ClassNotFoundException ce) {
> | | }
> | | Connection con;
> | | con =
> | |
> |
> DriverManager.getConnection(jdbc:microsoft:sqlserv er://Server1:1433;SelectMe
> | thod=cursor;DatabaseName=database1, user, passwd);
> | |
> | | DatabaseMetaData md = con.getMetaData();
> | |
> | | String catalog = null;
> | | String schema = null;
> | | String table = "table1";
> | |
> | | ResultSet rs = rs = md.getPrimaryKeys(catalog, schema, tables);
> | |
> | | The scb file is empty and the srt file always containes the same binary
> | | number 00 00 00 01. These files are never deleted and quickly build up
> | to
> | | many thousands of files.
> | |
> | | Can anybody suggest how to prevent these files from being created?
> | |
> | | Any assistance would be much appreciated.
> | |
> | | Regards,
> | |
> | | Ian555
> | |
> |
> | Hello,
> |
> | We are going to investigate this problem and post back the results.
> Thanks
> | for bringing this to our attention.
> |
> | Carb Simien, MCSE MCDBA MCAD
> | Microsoft Developer Support - Web Data
> |
> | Please reply only to the newsgroups.
> | This posting is provided "AS IS" with no warranties, and confers no
> rights.
> |
> | Are you secure? For information about the Strategic Technology
> Protection
> | Program and to order your FREE Security Tool Kit, please visit
> | http://www.microsoft.com/security.
> |
> |
> Hello,
> Are you closing your ResultSet in your repro code? If I close the
> ResultSet in my test, then the files are not left over any more. Please
> post back indicating whether or not this changes the behavior.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
prevent users from modifying tables or data but allow creating and modifying store procedu
Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!
Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!
Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
prevent users from modifying tables or data but allow creating and modifying store procedu
Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
prevent users from modifying tables or data but allow creating and modifying store pro
Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
Subscribe to:
Posts (Atom)