Padd Character Function To Return Variable Type

All,
I have a UDF for SQL that will padd a varchar value to a specific width and
align left or right. I found this code online somewhere, but the problem is
that it accpets a value up to Varchar(8000) and returns a varchar(8000). I
am writing a query to upload to a mainframe and need fixed-width fields, but
each field is not the same size. I can pass in the correct width to the
function and it padds correctly, but in a DTS transformation to a text file,
it thinks all fields are 8000 characters wide.
I am asking if it is possible to return a variable size data type instead of
a fixed type.
Here is the orig function:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(8000) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
and what I want basically and which doesn't seem to work, is:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(@.Width) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
Does anyone have any ideas on this?
Thanks,
NathanWhen I need to export fixed-width fields my approach is a bit
different. I create a view that formats each column the way I need it
to a fixed length, then export from the view. Examples of formatting:
convert(char(30), ItemName) as ItemName
STR(ItemValue,9,2)
Roy|||Thank you, I rarely use CHAR since it does padd for storage in the database,
duh! That worked to replace my instances of the dbo.PaddChar in my query fo
r
all the intstances where I am just padding on the right with spaces.
However, I had to just wrap a couple of them that I am right aligning, or
padding with 0's.
Since my query only pulls back a few rows a day the performance isn't bad.
Thank you again,
Nathan
"Roy Harvey" wrote:

> When I need to export fixed-width fields my approach is a bit
> different. I create a view that formats each column the way I need it
> to a fixed length, then export from the view. Examples of formatting:
> convert(char(30), ItemName) as ItemName
> STR(ItemValue,9,2)
> Roy
>

Packages not executing right on production server?

Hi,

I have developed about 20 to 30 packages that are executed on a specific order by a parent package that iterates a foreach cycle...

Has in development server all packages run just fine... when deployed to the production server and executed the first child package to run gives error and says that the logging text file was unable to find the file? It was supposed for the log to create and send information to the file right? So i think this is a little bit weird...

Any idea of what is going on here?

Regards,

Does the lggoing file folder exists, and does the execution security context (account) used have permissions to write to that folder? Different machines, so maybe the drives and folders are different, and your may well be using a different security context.|||

Where should i see the security context?

Anyway i have done the directory full previledges to "everyone" and nothing... one of the tasks creates the log just fine... but the other don't... the directory exists and i can't find a reason for it not create the file......

|||

The security context is the user who executes the package. Packages still execute on the machine which hosts the calling process, so were you are running DTEXEC or Visual studio, and they run under the security context of the user running the host program.

For scheduled jobs this is the SQL Server Agent service account or proxy account depending on your setup.

|||

I have already checked and all machines are setup the same...

I'm using visual studio 2005 in the production machine now with the project open and i can't really figure out what this error comes from... It seems that the variables are sent to the child package after the ssis logging so when ssis logging wants to start beeing used it can't...

Imagine i pass a variable called SOLUTION_DIRECTORY and that variable setups many things the log file path is one of them... os if this variable comes after the start of logging then we have problems....

Does this happens? Regards

|||

It depends how you're setting it. If its via a configuration then it should occur before you start logging.

If you pass it via the command-line, I'm not so sure.

Try outputting the the connection string of the log file to a different log provider, perhaps the event viewer. Output the connection string using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

It really seems that the logging is starting first then the package configuration that receives the variables from the parent package... this is really stupid... damn!

I tried to setup the variable with a default value correct and it worked... so how will i overcome this problem? I really need to log onto files and the file destination is dynamic... :( !!!

|||

I do a very similar thing. I use an XML config to set a variable to contain a path to my rootfolder and then dynamically build the logfile connection string from that. Exactly as you are doing.

This works fine. I'd be very very surprised if parent configurations occur at a different time.

-Jamie

|||

Has for what it seems it really is this way...

How can you explain the fact that if i set the variable in the child package to the right path it works and if not and pass the right path by the parent package it doens't? At least it can be very weird indead...

Another thing... is there anyway to validate if a directory already exists before creating it without using a script?

OUTPUT:

Error: 0xC001404B at CCCTT000D, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070003.

The system cannot find the path specified.

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x0 at Script Task: ligacao: CCCTT000D_<EMPRESA> UNL connectionstring: D:\CMSINTRABI\CG\DADOS\200401\CCCTT000D_CMS.UNL

Information: 0x0 at Script Task: ligacao: CONNECTION connectionstring: Data Source=CANARIO;Initial Catalog=CMSINTRACG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Information: 0x0 at Script Task: ligacao: FICHEIRO_LOG connectionstring: D:\CMSINTRABI\CG\LOGS\200401\CCCTT000D_CMS_CT.LOG

Has you can see the log gives error first then the variables are caught... :(

Regards,

|||

Yes its confirmed... it really seems that the logging starts first then the package configuration...

My text file for the ssis logging has this path configured in expressions:

@.[IN::SOLUTION_DIR] + "\\"+ @.[IN::MODULO]+"\\LOGS\\" + (DT_WSTR,6)@.[IN::PERIODO_CURRENTE] + "\\" + @.[System::PackageName] + "_" + @.[IN::COD_EMPRESA] +"_"+ @.[IN::STEP] +".LOG"

Well, all these variables come straight from the parent package.... if i don't set them with a default value the package will blow... but it i set them with an existing path it works just fine...

So this is it... Now WHY the hell don't the package configuration run in first place?

How will i make the logging this way? I really need help here! :(

Regards,

|||Can you not use a single configuration file and set it on all packages. It can supply the values you require to each and every package, rather than using the parent package variables. Configurations do happen before validation and execution. What is the problem with configurations you mention?|||

Well imagine that the parent package runs childs packages and send paremeters to each package...

Imagine for instance i run all packages by year month and i want the child packages to log the file to a directory somewhere in the corresponding year month... Ex: C:\logs\200604

but if that directory doenst exists at first then the error occurs... if the directory already exists then it works just fine :)

Basically i must be shure that the first directory setup must exist...

sql

Package Configuration in Dev enviornment

Am I missing something, I can't find any way to specific the XML configuration file when I try and run my package in Debug of BIDS. My OLE DB connections are all failing due to a lack of a password. I can set the password in BIDS but its forgotten as soon as I start Debug or build the solution. The only way I seem to be able to run my package (now that I have enabled configurations) is to de-select the enable configurations box.

I've tried putting a configuartion file in the Deployment folder, the bin folder and the project root folder all to the same result.

Unless you are using Indirect Configurations (http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx) the path to the .dtsconfig file is stored in your package so you have to make sure that that is where the file resides.

Its got nothing to do with the Deployment, bin or project root folder.

-Jamie

|||

The Indirect Configurations look handy I think I might try and change my package to use those.

So I finally figured out what was going on, this was driving me crazy. So when using the Package configuration wizard, in the specify configuration settings directly, I had typed a filename. I had clicked on the Browse button to see that it was looking into the project directory for the default directory on the file dialog. Apparently I never used the Save button while trying to set this up. I just assumed that was the directory and thus I never had the fully qualified path to the file. I had redone this while searching for a solution and even then, with the filename in the wizard text box I had clicked Browse, when I saw it was the directory where the file actually was I think I clicked Cancel.

So now I'm able to read the values. One error left and my first package is deployed, this might be a good friday after all! (edit: *no pun was intended*)

package configuration for generic pathnames and specific filenames

It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages

Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.

For example I use the following flat files in my project:

c:\AAA.txt

c:\BBB.txt

c:\CCC.txt

If I move the packages to the production database the flat file sources are located in another directory.

So in fact for the flat file sources there is a generic part for all files (in this case 'c:\ ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').

Can I indicate this in the package configurations sections? And how?

Thanks,

John

Create two variables in your package. One to hold the "generic" part of the path, the other to hold the specific part of the path. Include these two variables in your configuration file so that you can alter them.

Then using expressions on the flat file source, you can concatenate the two variables to form the full path, which is dynamically adjusted based on the configuration file.|||

Using expressions in combination with the variables solved my problem.

Thanks a lot Phil

p/c selfjoin question

Hi,
I need some help on a specific self join question.
I have a parent/child table:
CREATE TABLE [TAB_PC_KST] (
[Element] [varchar] (255) NULL ,
[Parent] [varchar] (255) NULL ,
[EType] [char] (1) NULL ,
[Weight] [float] NULL ,
) ON [PRIMARY]
GO
where EType is the element type and can be 'C' (for consoloidated element)
or 'N' (non consolidated element).
Lets say in that table is the following content:
Element Parent EType Weight
All Members <NULL> C <Null>
First Child All Members C 1
Second Child All Members N 1
First Sub First Child N 1
Second Sub First Child N 1
That will give a structure like the following
All Members
|--First Child
|--First Sub
|--Second Sub
|--Second Child
What I need is a select on that table, with the following result set
EType Element Weight
C All Members <NULL>
<NULL> First Child 1
<NULL> Second Child 1
C First Child <NULL>
<NULL> First Sub 1
<NULL> Second Sub 1
Is that possible without an cursor on all C-Elements? Perhaps with selfjoin
or
subqueries?
Thanks for any help!
Cheers
ThomasThomas
Look at greate script (example) written by Itzik Ben-Gan
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Thomas Seidel" <tseidel@.misag.com> wrote in message
news:%234qjvclSFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need some help on a specific self join question.
> I have a parent/child table:
> CREATE TABLE [TAB_PC_KST] (
> [Element] [varchar] (255) NULL ,
> [Parent] [varchar] (255) NULL ,
> [EType] [char] (1) NULL ,
> [Weight] [float] NULL ,
> ) ON [PRIMARY]
> GO
> where EType is the element type and can be 'C' (for consoloidated element)
> or 'N' (non consolidated element).
> Lets say in that table is the following content:
> Element Parent EType Weight
> All Members <NULL> C <Null>
> First Child All Members C 1
> Second Child All Members N 1
> First Sub First Child N 1
> Second Sub First Child N 1
> That will give a structure like the following
> All Members
> |--First Child
> |--First Sub
> |--Second Sub
> |--Second Child
> What I need is a select on that table, with the following result set
> EType Element Weight
> C All Members <NULL>
> <NULL> First Child 1
> <NULL> Second Child 1
> C First Child <NULL>
> <NULL> First Sub 1
> <NULL> Second Sub 1
> Is that possible without an cursor on all C-Elements? Perhaps with
selfjoin
> or
> subqueries?
> Thanks for any help!
>
> Cheers
> Thomas
>|||On Tue, 26 Apr 2005 14:04:23 +0200, Thomas Seidel wrote:
(snip)
>What I need is a select on that table, with the following result set
>EType Element Weight
>C All Members <NULL>
><NULL> First Child 1
><NULL> Second Child 1
>C First Child <NULL>
><NULL> First Sub 1
><NULL> Second Sub 1
>Is that possible without an cursor on all C-Elements? Perhaps with selfjoin
>or
>subqueries?
Hi Thomas,
The query below will not return the exact same set as above; the
differrence is basically formatting that's better done at the client side.
(It can be done in SQL - but it ain't pretty and it won't run quick)
SELECT a.Element, b.Element, b.Weight
FROM TAB_PC_KST AS a
INNER JOIN TAB_PC_KST AS b
ON b.Parent = a.Element
WHERE a.EType = 'C'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

owner advice

I'm not an expert so please be gentle if I say anyting incorrect in here.
Just looking for advice. Not any specific problem right now. I'm working on
a db that will be run independently in several offices. The owner of every
object is currently dbo. I move it (by doing backup and restore) between me
and another developer. We're both logging in as sa and don't have any
problems. We also don't use dbo in front of any objects when we reference
them in procedures, views and such (except for when referencing UDF's which
seem to require it). Are there any issues we need to be aware of with
respect to this? Are we ok with everythign being dbo and not qualifying
objects with it? We plan on creating another user (not sa) that the front
end app will use to get to the db. That sql user name and pwd will be coded
into the front end before it's compiled. Our tests seem to inidcate that
this works fine but we just want to be sure.
One reason this came to my attention is that I ran into an issue with
another db that I didnt' work on that got moved. I guess the object (an SP I
think) had been created by some other owner and when I went in to modify it
I couldnt' save it without putting dbo in front of every table in the SP. So
I did that but never really looked into what was going on.
Like I said, just looking for advice on this area. I know some of you out
there can help.
Thanks,
Keith"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> I'm not an expert so please be gentle if I say anyting incorrect in here.
> Just looking for advice. Not any specific problem right now. I'm working
> on
> a db that will be run independently in several offices. The owner of every
> object is currently dbo. I move it (by doing backup and restore) between
> me
> and another developer. We're both logging in as sa and don't have any
> problems. We also don't use dbo in front of any objects when we reference
> them in procedures, views and such (except for when referencing UDF's
> which
> seem to require it). Are there any issues we need to be aware of with
> respect to this? Are we ok with everythign being dbo and not qualifying
> objects with it? We plan on creating another user (not sa) that the front
> end app will use to get to the db. That sql user name and pwd will be
> coded
> into the front end before it's compiled. Our tests seem to inidcate that
> this works fine but we just want to be sure.
> One reason this came to my attention is that I ran into an issue with
> another db that I didnt' work on that got moved. I guess the object (an SP
> I
> think) had been created by some other owner and when I went in to modify
> it
> I couldnt' save it without putting dbo in front of every table in the SP.
> So
> I did that but never really looked into what was going on.
> Like I said, just looking for advice on this area. I know some of you out
> there can help.
> Thanks,
> Keith
>
What you're doing there seems OK to me, basically everything is owned by
dbo, and it's all in the same database - as simple as you can get.
One thing that I would suggest that you look at when hooking in your
front-end is perhaps not creating a specific user, but instead allowing
people to login to the database using windows authentication - don't give
the user any access, but instead setup an application role which your
program uses, this way your maximise the security of your DB and
application.
Regards
Colin Dawson
www.cjdawson.com|||Also, if you are not the owner and you are executing a stored procedure, you
WILL want to qualify it with dbo. There is a performance hit when you are
not the owner of a stored procedure and you execute it without qualifying
the owner.
See http://support.microsoft.com/defaul...kb;en-us;243586 (look for
"Best Practice")
See also http://support.microsoft.com/defaul...kb;en-us;263889
(look for "More Information")
Mike
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:u4GNe.92710$G8.85208@.text.news.blueyonder.co.uk...
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
> What you're doing there seems OK to me, basically everything is owned by
> dbo, and it's all in the same database - as simple as you can get.
> One thing that I would suggest that you look at when hooking in your
> front-end is perhaps not creating a specific user, but instead allowing
> people to login to the database using windows authentication - don't give
> the user any access, but instead setup an application role which your
> program uses, this way your maximise the security of your DB and
> application.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Hi
If you move a database and the SIDs don't match the original logins, then
you will get orphaned users as described in
121120120" target="_blank">http://support.microsoft.com/defaul...r />
121120120
John
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> I'm not an expert so please be gentle if I say anyting incorrect in here.
> Just looking for advice. Not any specific problem right now. I'm working
> on
> a db that will be run independently in several offices. The owner of every
> object is currently dbo. I move it (by doing backup and restore) between
> me
> and another developer. We're both logging in as sa and don't have any
> problems. We also don't use dbo in front of any objects when we reference
> them in procedures, views and such (except for when referencing UDF's
> which
> seem to require it). Are there any issues we need to be aware of with
> respect to this? Are we ok with everythign being dbo and not qualifying
> objects with it? We plan on creating another user (not sa) that the front
> end app will use to get to the db. That sql user name and pwd will be
> coded
> into the front end before it's compiled. Our tests seem to inidcate that
> this works fine but we just want to be sure.
> One reason this came to my attention is that I ran into an issue with
> another db that I didnt' work on that got moved. I guess the object (an SP
> I
> think) had been created by some other owner and when I went in to modify
> it
> I couldnt' save it without putting dbo in front of every table in the SP.
> So
> I did that but never really looked into what was going on.
> Like I said, just looking for advice on this area. I know some of you out
> there can help.
> Thanks,
> Keith
>

owned schema or role members?

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles members: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartBart
--db_datareader
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1cd
ab0fab43.htm
--db_datawriter
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a511
3f41e8c8.htm
"Bart" <b@.sdq.dc> wrote in message
news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles members: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>
>
>|||Thanks, but my question was more about the difference between Owned schema
and Role members.
Is it enough to take db_anything in Role members or must the user also owns
a schema with db_anything?
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
> Bart
> --db_datareader
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1
cdab0fab43.htm
>
> --db_datawriter
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/08a5c847-f993-4402-b3ac-a5
113f41e8c8.htm
>
> "Bart" <b@.sdq.dc> wrote in message
> news:OMfgIZlbHHA.3420@.TK2MSFTNGP05.phx.gbl...
>|||Bart
SCHEMA and Roles are different things. When you create a user ut should be
mapped to schema you have specified ir DEFAULT schema--DBO.
Can you elaborate on what you are trying to achive?
"Bart" <b@.sdq.dc> wrote in message
news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
> Thanks, but my question was more about the difference between Owned schema
> and Role members.
> Is it enough to take db_anything in Role members or must the user also
> owns a schema with db_anything?
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:eDumYbsbHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||Well, i want to create a new login for account ASPNET (which runs under
ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user must
get read/write prights to the db. At that level, i see in the window
configuration two things about read /write: Owned Schema and Role members.
So my question is: should i take db_readreader / db_writer in the Ownd
Schema or in Role members or in both?
Thanks
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
> Bart
> SCHEMA and Roles are different things. When you create a user ut should be
> mapped to schema you have specified ir DEFAULT schema--DBO.
> Can you elaborate on what you are trying to achive?
>
> "Bart" <b@.sdq.dc> wrote in message
> news:uEq8NwvbHHA.4140@.TK2MSFTNGP06.phx.gbl...
>|||Bart
Does the user should run queries which manipulate with tables belong to
anohter SCHEMA?
I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
user needs to query
"Bart" <b@.sdq.dc> wrote in message
news:u1ddKM3bHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Well, i want to create a new login for account ASPNET (which runs under
> ASP.NET) and then define an user (aspnet) for 'mydatabase'. That user
> must get read/write prights to the db. At that level, i see in the window
> configuration two things about read /write: Owned Schema and Role members.
> So my question is: should i take db_readreader / db_writer in the Ownd
> Schema or in Role members or in both?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> schreef in bericht
> news:%23p8We%232bHHA.4632@.TK2MSFTNGP03.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> schreef in bericht
news:OOCCsw3bHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Bart
> Does the user should run queries which manipulate with tables belong to
> anohter SCHEMA?
> I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the
> user needs to query
Thanks, but I still don't understand the difference between giving the Role
db_datareader / db_datawriter to user 'aspnet' and adding user 'aspnet' to
Schema dbdatareader and Schema db_datawriter ...|||Bart (b@.sdq.dc) writes:
> Thanks, but I still don't understand the difference between giving the
> Role db_datareader / db_datawriter to user 'aspnet' and adding user
> 'aspnet' to Schema dbdatareader and Schema db_datawriter ...
You should not need the schemss. They exist of legacy reasons. In SQL 2000
there was no difference between a role/user on the one hand and a schema
on the other. If you created a user/role X, you also got a schema X included
in the price.
In SQL 2005 schemas and database principals (users and roles) are separated.
But Microsoft still by default creates schemas for all pre-defined roles
and users, since old applications may rely on these and create objects
in these schemas.
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|||ok, thanks
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns99004B34D29Yazorman@.127.0.0.1...
> Bart (b@.sdq.dc) writes:
> You should not need the schemss. They exist of legacy reasons. In SQL 2000
> there was no difference between a role/user on the one hand and a schema
> on the other. If you created a user/role X, you also got a schema X
> included
> in the price.
> In SQL 2005 schemas and database principals (users and roles) are
> separated.
> But Microsoft still by default creates schemas for all pre-defined roles
> and users, since old applications may rely on these and create objects
> in these schemas.
>
> --
> 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