Hi;
im trying to create sort of a contract in RS, ill get some information from
an employee table, but most of the report is plain-fixed text. Im not sure
but i think the best way to do it is using only a detail band (ill need to
print a contract by employee), and use textboxes for the text...
but... i noticed i cant insert blank lines in a textbox (nothing happends
when i press return) and im not sure about my approach...
Any advice?
TIAOn May 30, 7:41 pm, "Willo" <willobe...@.yahoo.com.mx> wrote:
> Hi;
> im trying to create sort of a contract in RS, ill get some information from
> an employee table, but most of the report is plain-fixed text. Im not sure
> but i think the best way to do it is using only a detail band (ill need to
> print a contract by employee), and use textboxes for the text...
> but... i noticed i cant insert blank lines in a textbox (nothing happends
> when i press return) and im not sure about my approach...
> Any advice?
> TIA
If I understand you correctly, you should concatenate the employee
table info w/the plain-fixed text as part of the stored procedure/
query that sources your report. Also, you could try using a table
control to create spaces if necessary. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
long query alert
Hi!
How to create an alert that responses in case of long querya. I haven't
noticed any counters that deals with these kind of situations?
Thx for your help!Darko Jovisic (djovisic@.fesb.hr) writes:
> How to create an alert that responses in case of long querya. I haven't
> noticed any counters that deals with these kind of situations?
First you would need to ask, whom is to be alerted? The user? The DBA?
And when is the alert to be issued? Directly? (*) When the query has been
running for a minute?
(*) This would of course not be possible.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94F652F21045Yazorman@.127.0.0.1...
> First you would need to ask, whom is to be alerted? The user? The DBA?
The DBA, through e-mail notification!
> And when is the alert to be issued? Directly? (*) When the query has been
> running for a minute?
Directly.
> (*) This would of course not be possible.
Why is this not possible? There is a counter in Profiler so I expected to
have that in SQL Agent!|||Darko Jovisic (djovisic@.fesb.hr) writes:
>> First you would need to ask, whom is to be alerted? The user? The DBA?
> The DBA, through e-mail notification!
>> And when is the alert to be issued? Directly? (*) When the query has been
>> running for a minute?
>>
> Directly.
>> (*) This would of course not be possible.
> Why is this not possible? There is a counter in Profiler so I expected to
> have that in SQL Agent!
How would SQL Server know in advance that a query will run for a long
time?
I don't know what Profiler feature you are thinking off, but Duration
is not filled in until query completes.
The way to do this would be to have an app that everyonce in a while
scans sysprocesses for active process with a last_batch that is more
than, say, one minute ago, and in such case extracts DBCC INPUTBUFFER
and fn_get_sql() for that process. Of course, it should also include
blocking (and locking) information for that process.
(Hey, use aba_lockinfo, and send all rows with a last_since > 60000
in the mail. aba_lockinfo is on my web site:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 26, 2012
long numbers in SQL server
Hi,
I'm designing a table in SQL server and I'm trying to create a column for a number that is 10 characters long. The only datatype that comes near the 10 characters is a BIGINT wich is 8 characters long. But this is still to short.
Is it possible to store a number of 10 characters or is the only way to store this number to store it as a char?
JoachimUse money data type, especially if you are going to make calculations with it. It's the only numeric datatype that really works, and suports very large numbers.
IONUT|||THNX|||That's not really true... the datatype "float" definetly has the largest number support and supports numbers with 309 (!!!) digits. Acording to BOL float is "...a floating-point number from -1.79E+308 to 1.79E+308" ...which is rather large...|||...oh...and decimal would also do the trick...|||What do you mean by 10 characters? The BIGINT datatype is 8 bytes long, which should give it a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is 19 digits. A more efficient use would be Decimal (10,0), which I think uses about 5 bytes.
I don't have access to BOL at this site to verify this, but look in there for datatypes. Numerics use fewer bytes than strings to hold numbers.
Richard|||I don't want to be rude, but Frettmaestro, I didn't said that money accepts the largest number values, I only said that if you want to make calculation with that field value, then the only solution is money datatype, not real and not float. Try this one in Query Analiser:
declare @.val float
set @.val=920
select @.val,(@.val/100) as result
SURPRISE result=9.19999999999999993. It really works doesn't it?
Ha Ha Ha
IONUT
PS
You can be a member, not a junior like me or JRECKERS, but please when you post something here try it first to be damm' sure about it.|||JReckers, when we talk about numbers and the best datatype to store those numbers we talk in terms of integer and real values not number of characters.
If you have integer values then we further talk about range, when you have real values we talk about range but also precision and scale.
If your numbers range from -9,999,999,999 to 9,999,999,999 yoor only choice is to store that number in a bigint data type who's range is -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The Integer data type only covers -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Of course I am assuming that you WANT to store the data as a number rather than a string of numbers (varchar).
As for real numbers you are looking at a decimal or a float data type. What is the diffrence? A decimal is fixed presision and scale where as a float is an approximation, not all values in the data type range can be precisely represented BUT as the name implies the decimal point can float.|||ionut calin, you may want to head your own words! A money data type isn't the only data type that will yield a non-apporiximation!|||declare @.val money
set @.val=920
select @.val,(@.val/1000000*1000000) as result
GO
declare @.val float
set @.val=920
select @.val,(@.val/1000000*1000000) as result
Free advice is seldom cheap.
Originally posted by ionut calin
I don't want to be rude, but Frettmaestro ...
declare @.val float
set @.val=920
select @.val,(@.val/100) as result
SURPRISE result=9.19999999999999993. It really works doesn't it?
Ha Ha Ha
IONUT|||Wow ispaleny, that last one was really cool. Did you try it ionut? You said that "It's the only numeric datatype that really works" and I wasn't trying to mock you or anything, just telling you that this wasn't the case. No need to be all cocky and "all that" even if I didn't provide the right answer... I'm just trying to help people here and if you don't appreciate that then that's your problem. And I _really_ could care less what your or mine or anybody elses member status is.|||So, if I am correct, you are saying that when I create a table with a column with the datatype INT that the length of this datatype is the number of bytes that is used to store the number in and not the amount of characters.
So, when I want to store a number of 10 digits (min. 0 and max. 9.999.999.999), I have to use a BIGINT, because an INT goes up to 2,147,483,647 which is nog enough.
Am I correct?
Joachim|||DataType | Bytes | Digits
--------
int 4 9(10)
numeric(9) 5 9
bigint 8 18(19)
numeric(10) 9 10
char(9) 9 9
char(10) 10 10
nchar(9) 18 9
nchar(10) 20 10
If you really need 10 digits, with bigint you get +13% performance in comparison with numeric(10)
and at least +25% in comparison with char(10). Compared with 9 digits, the performance is -50% !!!!!!!
Good luck !|||For the original question, if you only want to store the number (integer) and not ever make calculations based on that number, the bigint is a good solution.
Now, because I see there are very upset persons around here:
I had tried it, and I've also tried this one:
declare @.val as money
set @.val=920
select @.val,(@.val*1000000/1000000) as result
It works isn't it??.
The float datatype sucks (or real for that matter), because for :
declare @.val as float
set @.val=920
select @.val,(@.val/100) as result
you really have no workaround, to get a correct result.
The money datatype is designed to work with four decimal numbers, and in this range it works correctly. The float datatype may be bigger, but it doesn't work correctly even with small numbers.
The last example with @.Val/1000000*1000000 works in float datatype only because the errors are leveled. So the example is good only like a "joke" nothing more. I advise everyone to try with @.val declared as float:
@.val/1000
@.val/10000
...
@.val*0.001
@.val*0.0001
and now try:
@.val/10000000 -> Surprise it works, why? No one knows.........
All numbers above are within the four digits range of money datatype(more than enough, for banks for example)
So, Frettmaestro don't be so happy because for,the so called "errors" with money datatype there is a logical explanation, but for float?
For the flot datatype I can't find one. Maybe you will find one and share with us, will you?
IONUT
PS!
Now I really wanted to be rude.|||Why do you _want_ to be rude? As I said way up there I didn't try to mock you with my initial post and I'm still not trying (or wanting) to be rude. You have to admint that I wasn't wrong when I said that "It's the only numeric datatype that really works" isn't really true, but recomending float probably wasn't the best solution in this case. I recomended using decimal in my second post which you haven't taken into account at all. I'm positive that money will do the trick in this case as with bigint, decimal, numeric and actually float aswell (the real issue had nothing to do with dividing any numbers, just storing it).|||Who wants to use this post thread to speak more about aproximations and good practices how to prevent them here?
I am interested.|||Originally posted by ispaleny
Who wants to use this post thread to speak more about aproximations and good practices how to prevent them here?
I am interested.
No problem.
Joachim|||I would be happy to contribute my .0200 cents worth but I am more interested in knowing if JReckers got his question answered.
Maybe a new thread would be better?|||Originally posted by Paul Young
I would be happy to contribute my .0200 cents worth but I am more interested in knowing if JReckers got his question answered.
Maybe a new thread would be better?
I have my questiong for a big part answered Paul Young. A have one question left which I would like to be answered.
The question is:
I thought that the 'length' option in SQL Server was to set the amount of digits (and with a char datatype the amount of characters) that can be used to store the data in, but it seems to be the amount of bytes where the data is stored in. Please correct me if I'm wrong.
Now my question is why Microsoft did this? I've worked with other databases before (Sybase and MySQL) and in these databases it is possible to set the length of the field (the maxium amount of characters the data can use). At least I always thought it was like this.
Joachim|||Microsoft & Sybase are exactly the same on this, there is no difference.
When you specify length you are stateing the amount of digits (and with a char datatype the amount of characters) that can be store and also the amount of bytes used to store the data, they are directly related.
I think in terms of a range when I specify length, for char datatypes I can store Null or 0 to n characters, for an integer, the range is fixed, for a decimal I cna adjust the range to sute my needs and not take up more space than needed to store my data.
Are we just talking past each other?|||Originally posted by Paul Young
Microsoft & Sybase are exactly the same on this, there is no difference.
When you specify length you are stateing the amount of digits (and with a char datatype the amount of characters) that can be store and also the amount of bytes used to store the data, they are directly related.
I think in terms of a range when I specify length, for char datatypes I can store Null or 0 to n characters, for an integer, the range is fixed, for a decimal I cna adjust the range to sute my needs and not take up more space than needed to store my data.
Are we just talking past each other?
;) Yes I think so.
Thanx anyway. You have been a great help. I think I got it now.
Joachim|||Maybe my first reply on this subject was a little too ... unpolite. If it was I'm sorry. Then I got angry, but now I think it's time to stop all this fight, 'cause it's going nowhere. I'm sorry.
I want to explain why I've got so angry about money datatype:
One year ago I was working for Academy of Economic Studies in Bucharest, were I was dba and programer for a database which deals with the activity of students(grades, collecting taxes and so on). One day a huge problem arrised: one secretary told me that the report oin which were listed the averages of grades for students is wrong, because one average is listed like 9.19 and not 9.20 (and from here a lot of problems). I had spent two days in front of my computer to figure out what was the problem. As you allready suspect, in the table were the grades was stored, the field Nota (means grade in romanian) was declared real.
Because of that real the result was 9.19999999... (truncated from the 2-nd decimal, that is the reglementation in Romania for averages of grades)=9.19, instead of 9.20.
The fun part was that if the the average was for instance 9.30 then the result was good because the long number format was 9.3000000000001 so... I hate datatypes
So, I hope you understand why I don't want to here about real or flot datatype.
IONUT
PS
This problem seems to arrise also in MS Access. DataTypes name there is Double for float, Single for Real and Currency for money.
I'm designing a table in SQL server and I'm trying to create a column for a number that is 10 characters long. The only datatype that comes near the 10 characters is a BIGINT wich is 8 characters long. But this is still to short.
Is it possible to store a number of 10 characters or is the only way to store this number to store it as a char?
JoachimUse money data type, especially if you are going to make calculations with it. It's the only numeric datatype that really works, and suports very large numbers.
IONUT|||THNX|||That's not really true... the datatype "float" definetly has the largest number support and supports numbers with 309 (!!!) digits. Acording to BOL float is "...a floating-point number from -1.79E+308 to 1.79E+308" ...which is rather large...|||...oh...and decimal would also do the trick...|||What do you mean by 10 characters? The BIGINT datatype is 8 bytes long, which should give it a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is 19 digits. A more efficient use would be Decimal (10,0), which I think uses about 5 bytes.
I don't have access to BOL at this site to verify this, but look in there for datatypes. Numerics use fewer bytes than strings to hold numbers.
Richard|||I don't want to be rude, but Frettmaestro, I didn't said that money accepts the largest number values, I only said that if you want to make calculation with that field value, then the only solution is money datatype, not real and not float. Try this one in Query Analiser:
declare @.val float
set @.val=920
select @.val,(@.val/100) as result
SURPRISE result=9.19999999999999993. It really works doesn't it?
Ha Ha Ha
IONUT
PS
You can be a member, not a junior like me or JRECKERS, but please when you post something here try it first to be damm' sure about it.|||JReckers, when we talk about numbers and the best datatype to store those numbers we talk in terms of integer and real values not number of characters.
If you have integer values then we further talk about range, when you have real values we talk about range but also precision and scale.
If your numbers range from -9,999,999,999 to 9,999,999,999 yoor only choice is to store that number in a bigint data type who's range is -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The Integer data type only covers -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Of course I am assuming that you WANT to store the data as a number rather than a string of numbers (varchar).
As for real numbers you are looking at a decimal or a float data type. What is the diffrence? A decimal is fixed presision and scale where as a float is an approximation, not all values in the data type range can be precisely represented BUT as the name implies the decimal point can float.|||ionut calin, you may want to head your own words! A money data type isn't the only data type that will yield a non-apporiximation!|||declare @.val money
set @.val=920
select @.val,(@.val/1000000*1000000) as result
GO
declare @.val float
set @.val=920
select @.val,(@.val/1000000*1000000) as result
Free advice is seldom cheap.
Originally posted by ionut calin
I don't want to be rude, but Frettmaestro ...
declare @.val float
set @.val=920
select @.val,(@.val/100) as result
SURPRISE result=9.19999999999999993. It really works doesn't it?
Ha Ha Ha
IONUT|||Wow ispaleny, that last one was really cool. Did you try it ionut? You said that "It's the only numeric datatype that really works" and I wasn't trying to mock you or anything, just telling you that this wasn't the case. No need to be all cocky and "all that" even if I didn't provide the right answer... I'm just trying to help people here and if you don't appreciate that then that's your problem. And I _really_ could care less what your or mine or anybody elses member status is.|||So, if I am correct, you are saying that when I create a table with a column with the datatype INT that the length of this datatype is the number of bytes that is used to store the number in and not the amount of characters.
So, when I want to store a number of 10 digits (min. 0 and max. 9.999.999.999), I have to use a BIGINT, because an INT goes up to 2,147,483,647 which is nog enough.
Am I correct?
Joachim|||DataType | Bytes | Digits
--------
int 4 9(10)
numeric(9) 5 9
bigint 8 18(19)
numeric(10) 9 10
char(9) 9 9
char(10) 10 10
nchar(9) 18 9
nchar(10) 20 10
If you really need 10 digits, with bigint you get +13% performance in comparison with numeric(10)
and at least +25% in comparison with char(10). Compared with 9 digits, the performance is -50% !!!!!!!
Good luck !|||For the original question, if you only want to store the number (integer) and not ever make calculations based on that number, the bigint is a good solution.
Now, because I see there are very upset persons around here:
I had tried it, and I've also tried this one:
declare @.val as money
set @.val=920
select @.val,(@.val*1000000/1000000) as result
It works isn't it??.
The float datatype sucks (or real for that matter), because for :
declare @.val as float
set @.val=920
select @.val,(@.val/100) as result
you really have no workaround, to get a correct result.
The money datatype is designed to work with four decimal numbers, and in this range it works correctly. The float datatype may be bigger, but it doesn't work correctly even with small numbers.
The last example with @.Val/1000000*1000000 works in float datatype only because the errors are leveled. So the example is good only like a "joke" nothing more. I advise everyone to try with @.val declared as float:
@.val/1000
@.val/10000
...
@.val*0.001
@.val*0.0001
and now try:
@.val/10000000 -> Surprise it works, why? No one knows.........
All numbers above are within the four digits range of money datatype(more than enough, for banks for example)
So, Frettmaestro don't be so happy because for,the so called "errors" with money datatype there is a logical explanation, but for float?
For the flot datatype I can't find one. Maybe you will find one and share with us, will you?
IONUT
PS!
Now I really wanted to be rude.|||Why do you _want_ to be rude? As I said way up there I didn't try to mock you with my initial post and I'm still not trying (or wanting) to be rude. You have to admint that I wasn't wrong when I said that "It's the only numeric datatype that really works" isn't really true, but recomending float probably wasn't the best solution in this case. I recomended using decimal in my second post which you haven't taken into account at all. I'm positive that money will do the trick in this case as with bigint, decimal, numeric and actually float aswell (the real issue had nothing to do with dividing any numbers, just storing it).|||Who wants to use this post thread to speak more about aproximations and good practices how to prevent them here?
I am interested.|||Originally posted by ispaleny
Who wants to use this post thread to speak more about aproximations and good practices how to prevent them here?
I am interested.
No problem.
Joachim|||I would be happy to contribute my .0200 cents worth but I am more interested in knowing if JReckers got his question answered.
Maybe a new thread would be better?|||Originally posted by Paul Young
I would be happy to contribute my .0200 cents worth but I am more interested in knowing if JReckers got his question answered.
Maybe a new thread would be better?
I have my questiong for a big part answered Paul Young. A have one question left which I would like to be answered.
The question is:
I thought that the 'length' option in SQL Server was to set the amount of digits (and with a char datatype the amount of characters) that can be used to store the data in, but it seems to be the amount of bytes where the data is stored in. Please correct me if I'm wrong.
Now my question is why Microsoft did this? I've worked with other databases before (Sybase and MySQL) and in these databases it is possible to set the length of the field (the maxium amount of characters the data can use). At least I always thought it was like this.
Joachim|||Microsoft & Sybase are exactly the same on this, there is no difference.
When you specify length you are stateing the amount of digits (and with a char datatype the amount of characters) that can be store and also the amount of bytes used to store the data, they are directly related.
I think in terms of a range when I specify length, for char datatypes I can store Null or 0 to n characters, for an integer, the range is fixed, for a decimal I cna adjust the range to sute my needs and not take up more space than needed to store my data.
Are we just talking past each other?|||Originally posted by Paul Young
Microsoft & Sybase are exactly the same on this, there is no difference.
When you specify length you are stateing the amount of digits (and with a char datatype the amount of characters) that can be store and also the amount of bytes used to store the data, they are directly related.
I think in terms of a range when I specify length, for char datatypes I can store Null or 0 to n characters, for an integer, the range is fixed, for a decimal I cna adjust the range to sute my needs and not take up more space than needed to store my data.
Are we just talking past each other?
;) Yes I think so.
Thanx anyway. You have been a great help. I think I got it now.
Joachim|||Maybe my first reply on this subject was a little too ... unpolite. If it was I'm sorry. Then I got angry, but now I think it's time to stop all this fight, 'cause it's going nowhere. I'm sorry.
I want to explain why I've got so angry about money datatype:
One year ago I was working for Academy of Economic Studies in Bucharest, were I was dba and programer for a database which deals with the activity of students(grades, collecting taxes and so on). One day a huge problem arrised: one secretary told me that the report oin which were listed the averages of grades for students is wrong, because one average is listed like 9.19 and not 9.20 (and from here a lot of problems). I had spent two days in front of my computer to figure out what was the problem. As you allready suspect, in the table were the grades was stored, the field Nota (means grade in romanian) was declared real.
Because of that real the result was 9.19999999... (truncated from the 2-nd decimal, that is the reglementation in Romania for averages of grades)=9.19, instead of 9.20.
The fun part was that if the the average was for instance 9.30 then the result was good because the long number format was 9.3000000000001 so... I hate datatypes
So, I hope you understand why I don't want to here about real or flot datatype.
IONUT
PS
This problem seems to arrise also in MS Access. DataTypes name there is Double for float, Single for Real and Currency for money.
long html string
Hi All
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:
> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:
> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org
Friday, March 23, 2012
LogShipping question
Hi guys
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanks
Standard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>
|||Both need to be EE. If you want to involve SE in log shipping, use your own scripts. You can Google
and you will probably find examples of home-grown log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficient.
> thanks
>
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanks
Standard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>
|||Both need to be EE. If you want to involve SE in log shipping, use your own scripts. You can Google
and you will probably find examples of home-grown log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficient.
> thanks
>
LogShipping question
Hi guys
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanksStandard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>|||Both need to be EE. If you want to involve SE in log shipping, use your own scripts. You can Google
and you will probably find examples of home-grown log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficient.
> thanks
>sql
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanksStandard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>|||Both need to be EE. If you want to involve SE in log shipping, use your own scripts. You can Google
and you will probably find examples of home-grown log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficient.
> thanks
>sql
LogShipping question
Hi guys
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanksStandard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>|||Both need to be EE. If you want to involve SE in log shipping, use your own
scripts. You can Google
and you will probably find examples of home-grown log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...[vb
col=seagreen]
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 fr
om Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficien
t.
> thanks
>[/vbcol]
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanksStandard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>|||Both need to be EE. If you want to involve SE in log shipping, use your own
scripts. You can Google
and you will probably find examples of home-grown log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...[vb
col=seagreen]
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 fr
om Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficien
t.
> thanks
>[/vbcol]
Logshipping help
As part of my effort to create my own log shipping method, i'm reading
the
documentation people have suggested here. I'm not familiar with some
concepts, the following in particular. From the website
(http://www.sql-server-performance.co...g_shipping.asp
I do have a backup device called DEV_device and the complete path is
D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
The article contains the statement below which I am not sure I
completely understand...
RESTORE LOG database_name
FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_ log_backup_device.bak'
The device name in the Authors scenario is
database_name_log_backup_device
My understanding is that a device encompasses the whole path not a
subset of the path(like above).Tested this with little success and my
hope is that there is some one out here with a better understanding
than myself to help out
TIA
TY
TY,
The two flavors of BACKUP/RESTORE concerning how to address your file/tape are:
BACKUP...
FROM DISK = 'C:\...'
BACKUP...
FROM logical_device_name
In the first example, you address the physical file. No backup devices are needed.
A backup device is like a pointer to a physical file. These are the ones you see in the backup
folder in Enterprise Manager. If you want to address the file using a backup device, use the second
alternative.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410120431.386cd44f@.posting.google.c om...
> As part of my effort to create my own log shipping method, i'm reading
> the
> documentation people have suggested here. I'm not familiar with some
> concepts, the following in particular. From the website
> (http://www.sql-server-performance.co...g_shipping.asp
> I do have a backup device called DEV_device and the complete path is
> D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
>
> The article contains the statement below which I am not sure I
> completely understand...
> RESTORE LOG database_name
> FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_ log_backup_device.bak'
> The device name in the Authors scenario is
> database_name_log_backup_device
>
> My understanding is that a device encompasses the whole path not a
> subset of the path(like above).Tested this with little success and my
> hope is that there is some one out here with a better understanding
> than myself to help out
>
> TIA
> TY
the
documentation people have suggested here. I'm not familiar with some
concepts, the following in particular. From the website
(http://www.sql-server-performance.co...g_shipping.asp
I do have a backup device called DEV_device and the complete path is
D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
The article contains the statement below which I am not sure I
completely understand...
RESTORE LOG database_name
FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_ log_backup_device.bak'
The device name in the Authors scenario is
database_name_log_backup_device
My understanding is that a device encompasses the whole path not a
subset of the path(like above).Tested this with little success and my
hope is that there is some one out here with a better understanding
than myself to help out
TIA
TY
TY,
The two flavors of BACKUP/RESTORE concerning how to address your file/tape are:
BACKUP...
FROM DISK = 'C:\...'
BACKUP...
FROM logical_device_name
In the first example, you address the physical file. No backup devices are needed.
A backup device is like a pointer to a physical file. These are the ones you see in the backup
folder in Enterprise Manager. If you want to address the file using a backup device, use the second
alternative.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410120431.386cd44f@.posting.google.c om...
> As part of my effort to create my own log shipping method, i'm reading
> the
> documentation people have suggested here. I'm not familiar with some
> concepts, the following in particular. From the website
> (http://www.sql-server-performance.co...g_shipping.asp
> I do have a backup device called DEV_device and the complete path is
> D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
>
> The article contains the statement below which I am not sure I
> completely understand...
> RESTORE LOG database_name
> FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_ log_backup_device.bak'
> The device name in the Authors scenario is
> database_name_log_backup_device
>
> My understanding is that a device encompasses the whole path not a
> subset of the path(like above).Tested this with little success and my
> hope is that there is some one out here with a better understanding
> than myself to help out
>
> TIA
> TY
Logshipping help
As part of my effort to create my own log shipping method, i'm reading
the
documentation people have suggested here. I'm not familiar with some
concepts, the following in particular. From the website
(http://www.sql-server-performance.com/sql_server_log_shipping.asp
I do have a backup device called DEV_device and the complete path is
D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
The article contains the statement below which I am not sure I
completely understand...
RESTORE LOG database_name
FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_log_backup_device.bak'
The device name in the Authors scenario is
database_name_log_backup_device
My understanding is that a device encompasses the whole path not a
subset of the path(like above).Tested this with little success and my
hope is that there is some one out here with a better understanding
than myself to help out
TIA
TYTY,
The two flavors of BACKUP/RESTORE concerning how to address your file/tape are:
BACKUP...
FROM DISK = 'C:\...'
BACKUP...
FROM logical_device_name
In the first example, you address the physical file. No backup devices are needed.
A backup device is like a pointer to a physical file. These are the ones you see in the backup
folder in Enterprise Manager. If you want to address the file using a backup device, use the second
alternative.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410120431.386cd44f@.posting.google.com...
> As part of my effort to create my own log shipping method, i'm reading
> the
> documentation people have suggested here. I'm not familiar with some
> concepts, the following in particular. From the website
> (http://www.sql-server-performance.com/sql_server_log_shipping.asp
> I do have a backup device called DEV_device and the complete path is
> D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
>
> The article contains the statement below which I am not sure I
> completely understand...
> RESTORE LOG database_name
> FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_log_backup_device.bak'
> The device name in the Authors scenario is
> database_name_log_backup_device
>
> My understanding is that a device encompasses the whole path not a
> subset of the path(like above).Tested this with little success and my
> hope is that there is some one out here with a better understanding
> than myself to help out
>
> TIA
> TY
the
documentation people have suggested here. I'm not familiar with some
concepts, the following in particular. From the website
(http://www.sql-server-performance.com/sql_server_log_shipping.asp
I do have a backup device called DEV_device and the complete path is
D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
The article contains the statement below which I am not sure I
completely understand...
RESTORE LOG database_name
FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_log_backup_device.bak'
The device name in the Authors scenario is
database_name_log_backup_device
My understanding is that a device encompasses the whole path not a
subset of the path(like above).Tested this with little success and my
hope is that there is some one out here with a better understanding
than myself to help out
TIA
TYTY,
The two flavors of BACKUP/RESTORE concerning how to address your file/tape are:
BACKUP...
FROM DISK = 'C:\...'
BACKUP...
FROM logical_device_name
In the first example, you address the physical file. No backup devices are needed.
A backup device is like a pointer to a physical file. These are the ones you see in the backup
folder in Enterprise Manager. If you want to address the file using a backup device, use the second
alternative.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410120431.386cd44f@.posting.google.com...
> As part of my effort to create my own log shipping method, i'm reading
> the
> documentation people have suggested here. I'm not familiar with some
> concepts, the following in particular. From the website
> (http://www.sql-server-performance.com/sql_server_log_shipping.asp
> I do have a backup device called DEV_device and the complete path is
> D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
>
> The article contains the statement below which I am not sure I
> completely understand...
> RESTORE LOG database_name
> FROM DISK = 'g:\mssql7\backup\log\database_name\database_name_log_backup_device.bak'
> The device name in the Authors scenario is
> database_name_log_backup_device
>
> My understanding is that a device encompasses the whole path not a
> subset of the path(like above).Tested this with little success and my
> hope is that there is some one out here with a better understanding
> than myself to help out
>
> TIA
> TY
Logshipping help
As part of my effort to create my own log shipping method, i'm reading
the
documentation people have suggested here. I'm not familiar with some
concepts, the following in particular. From the website
(http://www.sql-server-performance.c...og_shipping.asp
I do have a backup device called DEV_device and the complete path is
D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
The article contains the statement below which I am not sure I
completely understand...
RESTORE LOG database_name
FROM DISK = 'g:\mssql7\backup\log\database_name\data
base_name_log_backup_dev
ice.bak'
The device name in the Authors scenario is
database_name_log_backup_device
My understanding is that a device encompasses the whole path not a
subset of the path(like above).Tested this with little success and my
hope is that there is some one out here with a better understanding
than myself to help out
TIA
TYTY,
The two flavors of BACKUP/RESTORE concerning how to address your file/tape a
re:
BACKUP...
FROM DISK = 'C:\...'
BACKUP...
FROM logical_device_name
In the first example, you address the physical file. No backup devices are n
eeded.
A backup device is like a pointer to a physical file. These are the ones you
see in the backup
folder in Enterprise Manager. If you want to address the file using a backup
device, use the second
alternative.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410120431.386cd44f@.posting.google.com...
> As part of my effort to create my own log shipping method, i'm reading
> the
> documentation people have suggested here. I'm not familiar with some
> concepts, the following in particular. From the website
> (http://www.sql-server-performance.c...og_shipping.asp
> I do have a backup device called DEV_device and the complete path is
> D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
>
> The article contains the statement below which I am not sure I
> completely understand...
> RESTORE LOG database_name
> FROM DISK = 'g:\mssql7\backup\log\database_name\data
base_name_log_backup_d
evice.bak'
> The device name in the Authors scenario is
> database_name_log_backup_device
>
> My understanding is that a device encompasses the whole path not a
> subset of the path(like above).Tested this with little success and my
> hope is that there is some one out here with a better understanding
> than myself to help out
>
> TIA
> TY
the
documentation people have suggested here. I'm not familiar with some
concepts, the following in particular. From the website
(http://www.sql-server-performance.c...og_shipping.asp
I do have a backup device called DEV_device and the complete path is
D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
The article contains the statement below which I am not sure I
completely understand...
RESTORE LOG database_name
FROM DISK = 'g:\mssql7\backup\log\database_name\data
base_name_log_backup_dev
ice.bak'
The device name in the Authors scenario is
database_name_log_backup_device
My understanding is that a device encompasses the whole path not a
subset of the path(like above).Tested this with little success and my
hope is that there is some one out here with a better understanding
than myself to help out
TIA
TYTY,
The two flavors of BACKUP/RESTORE concerning how to address your file/tape a
re:
BACKUP...
FROM DISK = 'C:\...'
BACKUP...
FROM logical_device_name
In the first example, you address the physical file. No backup devices are n
eeded.
A backup device is like a pointer to a physical file. These are the ones you
see in the backup
folder in Enterprise Manager. If you want to address the file using a backup
device, use the second
alternative.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410120431.386cd44f@.posting.google.com...
> As part of my effort to create my own log shipping method, i'm reading
> the
> documentation people have suggested here. I'm not familiar with some
> concepts, the following in particular. From the website
> (http://www.sql-server-performance.c...og_shipping.asp
> I do have a backup device called DEV_device and the complete path is
> D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\QMISDEV
>
> The article contains the statement below which I am not sure I
> completely understand...
> RESTORE LOG database_name
> FROM DISK = 'g:\mssql7\backup\log\database_name\data
base_name_log_backup_d
evice.bak'
> The device name in the Authors scenario is
> database_name_log_backup_device
>
> My understanding is that a device encompasses the whole path not a
> subset of the path(like above).Tested this with little success and my
> hope is that there is some one out here with a better understanding
> than myself to help out
>
> TIA
> TY
Monday, March 19, 2012
Logon Failed
OK. I have my Reporting Server running and able to create report with BS and
run it in BS, but when I try to get the report in my Web app or using Report
Manager I get "Logon Failed" error. I'm using Win Athentication credientials.
SQL Server 2005, SP2.
Thanks for help.
FereydoonI found the answer by myself:
In the Reporting Server Configuration page in Execution Account, uncheck the
Specify Account, so any user that have access to the reporting server can
execute the report.
"Fereydoon" wrote:
> OK. I have my Reporting Server running and able to create report with BS and
> run it in BS, but when I try to get the report in my Web app or using Report
> Manager I get "Logon Failed" error. I'm using Win Athentication credientials.
> SQL Server 2005, SP2.
> Thanks for help.
> Fereydoon
run it in BS, but when I try to get the report in my Web app or using Report
Manager I get "Logon Failed" error. I'm using Win Athentication credientials.
SQL Server 2005, SP2.
Thanks for help.
FereydoonI found the answer by myself:
In the Reporting Server Configuration page in Execution Account, uncheck the
Specify Account, so any user that have access to the reporting server can
execute the report.
"Fereydoon" wrote:
> OK. I have my Reporting Server running and able to create report with BS and
> run it in BS, but when I try to get the report in my Web app or using Report
> Manager I get "Logon Failed" error. I'm using Win Athentication credientials.
> SQL Server 2005, SP2.
> Thanks for help.
> Fereydoon
Friday, March 9, 2012
Login without Connect Permission somehow managed to become db_owner!?
I tried asking the same question in other forum but couldn't get an answer.
I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'
Without granting any permission, straight away, i log out sa and login as yy in management studio, yy somehow can access one of my ApplicationDatabase! So i check with the following SQL in my ApplicationDatabase:
IS_MEMBER('db_owner')
and it returns me 1, meaning yy is db_owner. I then quickly login as sa and double check on the security|login|yy|properties|User Mapping, NONE of the database is mapped! and i also ensure "Guest account enabled for: ApplicationDatabase" is unchecked.
What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!
It is strange... have u run this Create login statement from QA or was it ran from your customized frontend application for access control. I tried all the way, but i could not re-create your problem.
also post the reslut of
sp_helplogins 'yy'
sp_helpuser 'yy'
Madhu
|||You can also use the following two queries to determine the role memberships and the permissions associated with login yy:
select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id('yy')
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('yy')
Thanks
Laurentiu
Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.
Madhu,
i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':
LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no
and the 2nd result set is empty.
Laurentiu,
I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.
After running the queries, i find myself in even worse situation as the result proves to me that nothing is wrong with 'yy', but still somehow i can connect to the application db. by the way, i tried the same thing in another pc, same thing happened. i'm really lost, hope somebody can help me, thank q
|||Are you connecting to server using this Login from your application ? If that is the case, somewhere in the code the loging must have hardcoded. open a query analyser using this login and try to connect to the database.
Madhu
|||
Check the context you are logged in:
select suser_name()
and also check who owns the database by querying sys.databases:
select name, suser_sname(owner_sid) from sys.databases
Thanks
Laurentiu
Madhu,
Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.
Laurentiu,
sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:
Use ApplicationDB
GO
select suser_name()
Result: yy
And also i run the following:
select name, suser_sname(owner_sid) from sys.databases
Result: indicating my applicationDB is own by domain\Administrator
I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?
|||OK, so the queries you ran confirm that you are connected as 'yy' and that the database is not owned by 'yy', but by a Windows login. Were you still getting db_owner privileges when connecting to the database?
I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.
Thanks
Laurentiu
Laurentiu,
Thank q for ur continuous replies.
I'm not sure if i'm gettin db_owner if i'm using windows login as i'm not able to connect the query analzer using windows logins.
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
Returns me:
permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5
this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.
|||See BOL under of DB RolesEvery database user belongs to the public database role. When
a user has not been granted or denied specific permissions on a securable, the
user inherits the permissions granted to public on that securable.
You must have changed your "public" role on the 2nd database, which the user does not see.
|||The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?
Thanks
Laurentiu
PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.
|||Tom,
i have compare my public role between the accessible application db and other none accessible db, i find no difference.
Laurentiu,
if the public server role is normal, means i'm still lost. i have check the server role 'public' in my application database, everything looks just fine and normal, none of the schema is owned, but it is still accessible for my 'yy' login.
i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q
|||I'm out of ideas. Here are the two suggestions I have left:
(1) Try to reproduce the same issue on another SQL Server instance. You could install SQL Express and try to recreate the login and the database, as you did it on the original system - see if you can get the same behavior.
(2) You could try to backup all your SQL Server databases, then strip them of all data (whether it's sensitive or not), so you leave just the definitions of the login and database, and the permissions set for these. Verify that the issue still happens.
Once you get the problem isolated in a database with no sensitive data, you can fill a report (using link in sticky forum post) and attach the databases to the report (master and the app database). For (2), you should restore your databases after filling the report.
Thanks
Laurentiu
Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:
SELECT * FROM sys.login_token ORDER BY type, usage, name
SELECT * FROM sys.user_token ORDER BY type, usage, name
go
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Login without Connect Permission somehow managed to become db_owner!?
I tried asking the same question in other forum but couldn't get an answer.
I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'
Without granting any permission, straight away, i log out sa and login as yy in management studio, yy somehow can access one of my ApplicationDatabase! So i check with the following SQL in my ApplicationDatabase:
IS_MEMBER('db_owner')
and it returns me 1, meaning yy is db_owner. I then quickly login as sa and double check on the security|login|yy|properties|User Mapping, NONE of the database is mapped! and i also ensure "Guest account enabled for: ApplicationDatabase" is unchecked.
What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!
It is strange... have u run this Create login statement from QA or was it ran from your customized frontend application for access control. I tried all the way, but i could not re-create your problem.
also post the reslut of
sp_helplogins 'yy'
sp_helpuser 'yy'
Madhu
|||You can also use the following two queries to determine the role memberships and the permissions associated with login yy:
select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id('yy')
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('yy')
Thanks
Laurentiu
Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.
Madhu,
i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':
LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no
and the 2nd result set is empty.
Laurentiu,
I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.
After running the queries, i find myself in even worse situation as the result proves to me that nothing is wrong with 'yy', but still somehow i can connect to the application db. by the way, i tried the same thing in another pc, same thing happened. i'm really lost, hope somebody can help me, thank q
|||Are you connecting to server using this Login from your application ? If that is the case, somewhere in the code the loging must have hardcoded. open a query analyser using this login and try to connect to the database.
Madhu
|||
Check the context you are logged in:
select suser_name()
and also check who owns the database by querying sys.databases:
select name, suser_sname(owner_sid) from sys.databases
Thanks
Laurentiu
Madhu,
Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.
Laurentiu,
sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:
Use ApplicationDB
GO
select suser_name()
Result: yy
And also i run the following:
select name, suser_sname(owner_sid) from sys.databases
Result: indicating my applicationDB is own by domain\Administrator
I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?
|||OK, so the queries you ran confirm that you are connected as 'yy' and that the database is not owned by 'yy', but by a Windows login. Were you still getting db_owner privileges when connecting to the database?
I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.
Thanks
Laurentiu
Laurentiu,
Thank q for ur continuous replies.
I'm not sure if i'm gettin db_owner if i'm using windows login as i'm not able to connect the query analzer using windows logins.
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
Returns me:
permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5
this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.
|||See BOL under of DB RolesEvery database user belongs to the public database role. When
a user has not been granted or denied specific permissions on a securable, the
user inherits the permissions granted to public on that securable.
You must have changed your "public" role on the 2nd database, which the user does not see.
|||The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?
Thanks
Laurentiu
PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.
|||Tom,
i have compare my public role between the accessible application db and other none accessible db, i find no difference.
Laurentiu,
if the public server role is normal, means i'm still lost. i have check the server role 'public' in my application database, everything looks just fine and normal, none of the schema is owned, but it is still accessible for my 'yy' login.
i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q
|||I'm out of ideas. Here are the two suggestions I have left:
(1) Try to reproduce the same issue on another SQL Server instance. You could install SQL Express and try to recreate the login and the database, as you did it on the original system - see if you can get the same behavior.
(2) You could try to backup all your SQL Server databases, then strip them of all data (whether it's sensitive or not), so you leave just the definitions of the login and database, and the permissions set for these. Verify that the issue still happens.
Once you get the problem isolated in a database with no sensitive data, you can fill a report (using link in sticky forum post) and attach the databases to the report (master and the app database). For (2), you should restore your databases after filling the report.
Thanks
Laurentiu
Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:
SELECT * FROM sys.login_token ORDER BY type, usage, name
SELECT * FROM sys.user_token ORDER BY type, usage, name
go
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Login without Connect Permission somehow managed to become db_owner!?
I tried asking the same question in other forum but couldn't get an answer.
I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'
Without granting any permission, straight away, i log out sa and login as yy in management studio, yy somehow can access one of my ApplicationDatabase! So i check with the following SQL in my ApplicationDatabase:
IS_MEMBER('db_owner')
and it returns me 1, meaning yy is db_owner. I then quickly login as sa and double check on the security|login|yy|properties|User Mapping, NONE of the database is mapped! and i also ensure "Guest account enabled for: ApplicationDatabase" is unchecked.
What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!
It is strange... have u run this Create login statement from QA or was it ran from your customized frontend application for access control. I tried all the way, but i could not re-create your problem.
also post the reslut of
sp_helplogins 'yy'
sp_helpuser 'yy'
Madhu
|||You can also use the following two queries to determine the role memberships and the permissions associated with login yy:
selectsuser_name(role_principal_id),suser_name(member_principal_id)fromsys.server_role_memberswhere member_principal_id =suser_id('yy')
select permission_name, class_desc, major_id fromsys.server_permissionswhere grantee_principal_id =suser_id('yy')
Thanks
Laurentiu
Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.
Madhu,
i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':
LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no
and the 2nd result set is empty.
Laurentiu,
I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.
After running the queries, i find myself in even worse situation as the result proves to me that nothing is wrong with 'yy', but still somehow i can connect to the application db. by the way, i tried the same thing in another pc, same thing happened. i'm really lost, hope somebody can help me, thank q
|||Are you connecting to server using this Login from your application ? If that is the case, somewhere in the code the loging must have hardcoded. open a query analyser using this login and try to connect to the database.
Madhu
|||Check the context you are logged in:
selectsuser_name()
and also check who owns the database by querying sys.databases:
selectname,suser_sname(owner_sid)fromsys.databases
Thanks
Laurentiu
Madhu,
Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.
Laurentiu,
sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:
Use ApplicationDB
GO
select suser_name()
Result: yy
And also i run the following:
select name, suser_sname(owner_sid) from sys.databases
Result: indicating my applicationDB is own by domain\Administrator
I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?
|||OK, so the queries you ran confirm that you are connected as 'yy' and that the database is not owned by 'yy', but by a Windows login. Were you still getting db_owner privileges when connecting to the database?
I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.
Thanks
Laurentiu
Laurentiu,
Thank q for ur continuous replies.
I'm not sure if i'm gettin db_owner if i'm using windows login as i'm not able to connect the query analzer using windows logins.
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
Returns me:
permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5
this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.
|||See BOL under of DB RolesEvery database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
You must have changed your "public" role on the 2nd database, which the user does not see.
|||The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?
Thanks
Laurentiu
PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.
|||Tom,
i have compare my public role between the accessible application db and other none accessible db, i find no difference.
Laurentiu,
if the public server role is normal, means i'm still lost. i have check the server role 'public' in my application database, everything looks just fine and normal, none of the schema is owned, but it is still accessible for my 'yy' login.
i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q
|||I'm out of ideas. Here are the two suggestions I have left:
(1) Try to reproduce the same issue on another SQL Server instance. You could install SQL Express and try to recreate the login and the database, as you did it on the original system - see if you can get the same behavior.
(2) You could try to backup all your SQL Server databases, then strip them of all data (whether it's sensitive or not), so you leave just the definitions of the login and database, and the permissions set for these. Verify that the issue still happens.
Once you get the problem isolated in a database with no sensitive data, you can fill a report (using link in sticky forum post) and attach the databases to the report (master and the app database). For (2), you should restore your databases after filling the report.
Thanks
Laurentiu
Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:
SELECT*FROMsys.login_tokenORDERBYtype, usage,name
SELECT*FROMsys.user_tokenORDERBYtype, usage,name
go
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Login without Connect Permission somehow managed to become db_owner!?
I tried asking the same question in other forum but couldn't get an answer.
I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'
Without granting any permission, straight away, i log out sa and login as yy in management studio, yy somehow can access one of my ApplicationDatabase! So i check with the following SQL in my ApplicationDatabase:
IS_MEMBER('db_owner')
and it returns me 1, meaning yy is db_owner. I then quickly login as sa and double check on the security|login|yy|properties|User Mapping, NONE of the database is mapped! and i also ensure "Guest account enabled for: ApplicationDatabase" is unchecked.
What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!
It is strange... have u run this Create login statement from QA or was it ran from your customized frontend application for access control. I tried all the way, but i could not re-create your problem.
also post the reslut of
sp_helplogins 'yy'
sp_helpuser 'yy'
Madhu
|||You can also use the following two queries to determine the role memberships and the permissions associated with login yy:
select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id('yy')
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('yy')
Thanks
Laurentiu
Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.
Madhu,
i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':
LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no
and the 2nd result set is empty.
Laurentiu,
I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.
After running the queries, i find myself in even worse situation as the result proves to me that nothing is wrong with 'yy', but still somehow i can connect to the application db. by the way, i tried the same thing in another pc, same thing happened. i'm really lost, hope somebody can help me, thank q
|||Are you connecting to server using this Login from your application ? If that is the case, somewhere in the code the loging must have hardcoded. open a query analyser using this login and try to connect to the database.
Madhu
|||
Check the context you are logged in:
select suser_name()
and also check who owns the database by querying sys.databases:
select name, suser_sname(owner_sid) from sys.databases
Thanks
Laurentiu
Madhu,
Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.
Laurentiu,
sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:
Use ApplicationDB
GO
select suser_name()
Result: yy
And also i run the following:
select name, suser_sname(owner_sid) from sys.databases
Result: indicating my applicationDB is own by domain\Administrator
I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?
|||OK, so the queries you ran confirm that you are connected as 'yy' and that the database is not owned by 'yy', but by a Windows login. Were you still getting db_owner privileges when connecting to the database?
I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.
Thanks
Laurentiu
Laurentiu,
Thank q for ur continuous replies.
I'm not sure if i'm gettin db_owner if i'm using windows login as i'm not able to connect the query analzer using windows logins.
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')
Returns me:
permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5
this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.
|||See BOL under of DB RolesEvery database user belongs to the public database role. When
a user has not been granted or denied specific permissions on a securable, the
user inherits the permissions granted to public on that securable.
You must have changed your "public" role on the 2nd database, which the user does not see.
|||The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?
Thanks
Laurentiu
PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.
|||Tom,
i have compare my public role between the accessible application db and other none accessible db, i find no difference.
Laurentiu,
if the public server role is normal, means i'm still lost. i have check the server role 'public' in my application database, everything looks just fine and normal, none of the schema is owned, but it is still accessible for my 'yy' login.
i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q
|||I'm out of ideas. Here are the two suggestions I have left:
(1) Try to reproduce the same issue on another SQL Server instance. You could install SQL Express and try to recreate the login and the database, as you did it on the original system - see if you can get the same behavior.
(2) You could try to backup all your SQL Server databases, then strip them of all data (whether it's sensitive or not), so you leave just the definitions of the login and database, and the permissions set for these. Verify that the issue still happens.
Once you get the problem isolated in a database with no sensitive data, you can fill a report (using link in sticky forum post) and attach the databases to the report (master and the app database). For (2), you should restore your databases after filling the report.
Thanks
Laurentiu
Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:
SELECT * FROM sys.login_token ORDER BY type, usage, name
SELECT * FROM sys.user_token ORDER BY type, usage, name
go
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Friday, February 24, 2012
Login scripting to a file
I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis.
Anyone have any examples to share?
Use the following procs, you will generate the login scripts.
Cut and paste the following proc and then execute
exec sp_script_generator 'c:\scripts\', <sa pwd>.
if sql server authentication, provide the password.
Please note that, I wrote the scripts long back.
If any issues in the procs, please post the same.
if exists (select * from sysobjects where id = object_id('dbo.sp_hexadecimal') and sysstat & 0xf = 4)
drop procedure dbo.sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(255),
@.hexvalue varchar(255) OUTPUT
AS
DECLARE @.charvalue varchar(255)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH(@.binvalue)
SELECT @.hexstring = '0123456789abcdef'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
if exists (select * from sysobjects where id = object_id('dbo.sp_displayoaerrorinfo') and sysstat & 0xf = 4)
drop procedure dbo.sp_displayoaerrorinfo
GO
CREATE PROCEDURE sp_displayoaerrorinfo
@.object int,
@.hresult int
AS
DECLARE @.output varchar(255)
DECLARE @.hrhex char(10)
DECLARE @.hr int
DECLARE @.source varchar(255)
DECLARE @.description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @.hresult, @.hrhex OUT
SELECT @.output = ' HRESULT: ' + @.hrhex
PRINT @.output
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT " sp_OAGetErrorInfo failed."
RETURN
END
GO
set quoted_identifier off
go
if exists (select * from sysobjects where id = object_id('dbo.sp_srv_obj_scr_gen') and sysstat & 0xf = 4)
drop procedure dbo.sp_srv_obj_scr_gen
GO
/****************************************************************************************************************/
/* Procedure : sp_srv_obj_scr_gen */
/* Purpose : To generate server objects namely logins, devices and databases in a SQL Server */
/* Called From : sp_Script_Generator [Main] */
/* Author : Narasimhan Jayachandran */
/* Date : 09/28/98 */
/* Date : 03/15/00 */
/****************************************************************************************************************/
create proc sp_srv_obj_scr_gen
@.object int,
@.srvObjColl varchar(30),
@.strObjQry varchar(255),
@.strFile varchar(6000),
@.strFilePath varchar(6000)=null
as
begin
declare @.srvObjname varchar(128), @.strMethod varchar(6000),@.hr int,@.return varchar(255), @.strCmd varchar(6000)
declare @.dbCtr int, @.FileCtr int, @.DestFile varchar(6000)
select @.dbctr=0
select @.strCmd = "echo use master >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output
select @.strCmd = "echo go >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output
exec("declare server_obj_cursor cursor for "+ @.strObjQry)
open server_obj_cursor
fetch next from server_obj_cursor into @.srvObjname
while @.@.fetch_status = 0
begin
-- select @.srvobjname
select @.strMethod =' '+@.srvObjColl+'("'+@.srvObjname+'").Script(324,"'+@.strFile+'")'
exec @.hr = sp_OAMethod @.object, @.strMethod, @.return out
if @.hr <> 0
begin
close server_obj_cursor
deallocate server_obj_cursor
return 1
end
if @.srvObjColl="Databases" -- database files --
begin
select @.dbCtr = @.dbCtr + 1
select @.strCmd = "copy "+ @.strFile+" "+@.strFilePath+convert(varchar(30),@.dbCtr)+".sql"
end
exec master..xp_cmdshell @.strCmd, no_output
fetch next from server_obj_cursor into @.srvObjname
end
close server_obj_cursor
deallocate server_obj_cursor
if @.srvObjColl="Databases" -- database files --
begin
select @.DestFile=@.strFile
select @.strCmd = "copy ",@.FileCtr=1,@.strFile=" "
while @.FileCtr <= @.dbCtr
begin
select @.strFile = @.strFile + @.strFilePath+convert(varchar(30),@.FileCtr)+".sql + "
select @.FileCtr = @.FileCtr + 1
end
select @.strFile = left(@.strFile,len(@.strFile)-2)
select @.strCmd = @.strCmd+@.strFile+" "+@.destFile
exec master..xp_cmdshell @.strCmd, no_output
select @.strCmd = "del ",@.FileCtr=1
while @.FileCtr <= @.dbCtr
begin
select @.strCmd = "Del "+@.strFilePath+convert(varchar(30),@.FileCtr)+".sql"
exec master..xp_cmdshell @.strCmd, no_output
select @.FileCtr = @.FileCtr + 1
end
end
return 0
end
GO
GRANT EXECUTE ON dbo.sp_srv_obj_scr_gen TO public
GO
--exec sp_script_generator "D:\backup\sqlserver\Script\"
if exists (select * from sysobjects where id = object_id('dbo.sp_script_generator') and sysstat & 0xf = 4)
drop procedure dbo.sp_script_generator
GO
/* ************************************************************************************************************** */
/* Procedure : sp_script_generator */
/* Purpose : To generate scripts for all objects of all databases in a SQL Server 6.5/7.0 */
/* Sub Procedures : sp_srv_obj_scr_gen,sp_db_obj_scr_gen,sp_tb_obj_scr_gen,sp_con_obj_scr_gen */
/* Author : Narasimhan Jayachandran */
/* Version : 1.0 */
/* Date : 09/28/98 */
/* Version : 1.1 */
/* Date : 03/25/99 */
/* Version : 2.0 */
/* Date : 02/15/00 */
/* Usage Hints : sp_Script_Generator <Output File Path>,[SA Password - optional ] */
/* Example : sp_script_generator "c:\scripts\", [SA Password] */
/* Outputs : c:\scripts\SQLSERVER_logins.sql */
/* c:\scripts\SQLSERVER_devices.sql */
/* c:\scripts\SQLSERVER_databases.sql */
/* c:\scripts\SQLSERVER_<databaseName1>.sql */
/* ... */
/* ... */
/* ... */
/* c:\scripts\<SQLSERVER>_<databaseNameN>.sql */
/* Modified Date Reason */
/* 06/25/2001 Jobs Scripts added */
/* 07/03/2001 syslogins used to pick the loginnames */
/* Windows Authentication Mode added */
/* Use the System function ServerName instead of as a parameter */
/****************************************************************************************************************/
create proc sp_script_generator
@.strFilePath varchar(6000) = null,
@.pwd varchar(128) = null
as
begin
declare @.object int, @.hr int,@.return varchar(255)
declare @.dbname varchar(128),@.ObjColl varchar(30)
declare @.strFile varchar(6000), @.strCmd varchar(6000)
declare @.strObjQry varchar(6000), @.strObjQry1 varchar(255), @.strMethod varchar(255)
declare @.dbObjColl varchar(30), @.tbObjColl varchar(30),@.srvObjColl varchar(30)
declare @.dbobjname varchar(30), @.tbobjname varchar(30),@.ObjcollName varchar(60)
declare @.srvObjFlag tinyint,@.sts tinyint,@.TbCtr int,@.ScriptType int
declare @.Ver6570 char(4),@.ProgID varchar(16), @.Property varchar(6000)
declare @.src varchar(255), @.desc varchar(255)
declare @.objJobserver int
set nocount on
if @.strFilePath is null
begin
print "Usage : sp_Script_Generator <Output File Path>, [SA Password]"
print "-"
print "Example : sp_script_generator 'c:\scripts\',[SA Password]"
print "Outputs : c:\scripts\SQLSERVER_logins.sql"
print " c:\scripts\SQLSERVER_devices.sql"
print " c:\scripts\SQLSERVER_databases.sql"
print " c:\scripts\SQLSERVER_<databaseName1>.sql"
print " .."
print " .."
print " .."
print " c:\scripts\SQLSERVER_<databaseNameN>.sql"
print "-"
return
end
-- version check
select @.Ver6570=substring(@.@.version,23,4)
select @.ProgID =
case
when @.Ver6570 = '6.50' then 'SQLOLE.SQLServer'
else 'SQLDMO.SQLServer'
-- when @.Ver6570 = '7.00' then 'SQLDMO.SQLServer'
end
-- Create an object
print "Creating SQL SERVER Object..."
exec @.hr = sp_OACreate @.ProgID, @.object out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
return
end
print "Connecting to the SQL SERVER "+@.Ver6570+"..."
-- Set the property value to true if it is Windows Authentication
if @.pwd is null
begin
-- Set a property
Print "Connecting to SQLServer using Windows Authentication Mode...!"
exec @.hr = sp_OASetProperty @.object, 'LoginSecure', TRUE
if @.hr <> 0
begin
exec sp_OAGetErrorInfo @.object, @.src out, @.desc out
select hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
return
end
-- Connect to the sql server -- Windows Authentication Mode
select @.strMethod = 'Connect("'+@.@.ServerName+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end
else
begin
Print "Connecting to SQLServer using SQLServer Authentication Mode...!"
-- Connect to the sql server
select @.strMethod = 'Connect("'+@.@.ServerName+'" , "sa", "'+@.pwd+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end
-- Verify that the connection
exec @.hr = sp_OAMethod @.object, 'VerifyConnection' ,@.return out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return end
select @.srvObjFlag = 0
-- Server Objects
-- Logins
print "Creating Scripts for "+@.@.servername+" Server Logins..."
select @.strFile=@.strFilePath+@.@.servername+"_logins.sql"
select @.strCmd = "del "+@.strFile
exec master..xp_cmdshell @.strCmd, no_output
select @.srvObjColl = "Logins", @.sts = 0
select @.strObjQry =
case
when @.Ver6570 = "6.50" then
" select name from master..syslogins where status = 8 and name != 'probe' "
else
" select loginname from master..syslogins where loginname not in ('sa') "
end
exec @.sts = sp_srv_obj_scr_gen @.object,@.srvObjColl,@.strObjQry,@.strFile
if @.sts = 1 goto ProcErr
goto CloseDbCur
ProcErr: exec sp_displayoaerrorinfo @.object,@.hr
if @.srvObjFlag = 0 goto ProcFine
CloseDbCur:
close database_cursor
deallocate database_cursor
ProcFine:
exec @.hr = sp_OADestroy @.object
if @.hr <> 0
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.ObjJobServer
if @.hr <> 0
exec sp_displayoaerrorinfo @.ObjJobServer, @.hr
set nocount off
return
end
GO
--
Thanks.
Naras.
|||Actually, you don't need DMO to do this - just search for sp_help_revlogin and install the procedure in your master database. It generates a script which creates all the logins on your server with the correct hash for the password in place for each login.|||Cool.
Thanks Allen White.
But let him learn DMO...too
Thanks
Naras.
Login problem by not being in sysadmin group
Hi All
I experience a very strange login problem:
I create standard security login, let say test1/test1 with a default db test and assign it sysadmin group.
All is working well.
The moment remove sysadmin group from this login, i start getting errors:
Login failed for user 'test1'
... when I login from remote server. If I login from the same host - it continues with no problem.
When I go to sql server configuration manager, I see next:
sql native client configuration(32bit):
shared memeory enabled
tcp/ip enabled
named pipes enabled
VIA disabled
The same settings from sql server 2005 network configuration / protocols for mssqlserver
sql native client configuration / client protocols
sql 2005 surface area configuration / remote connections is configured:
local and remote connections (checked), using both tcp/ip and named pipes.
Does anybody have a clue?
What is the default database for your login?
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Hi Wesley
Default db is named 'test'. I made sure login has default db and can browse it.
|||Try changing the default database to master. If you can then connect with that login, there is a problem with the users access to what you were using for the default database.
-Sue
Login problem by not being in sysadmin group
Hi All
I experience a very strange login problem:
I create standard security login, let say test1/test1 with a default db test and assign it sysadmin group.
All is working well.
The moment remove sysadmin group from this login, i start getting errors:
Login failed for user 'test1'
... when I login from remote server. If I login from the same host - it continues with no problem.
When I go to sql server configuration manager, I see next:
sql native client configuration(32bit):
shared memeory enabled
tcp/ip enabled
named pipes enabled
VIA disabled
The same settings from sql server 2005 network configuration / protocols for mssqlserver
sql native client configuration / client protocols
sql 2005 surface area configuration / remote connections is configured:
local and remote connections (checked), using both tcp/ip and named pipes.
Does anybody have a clue?
What is the default database for your login?
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Hi Wesley
Default db is named 'test'. I made sure login has default db and can browse it.
|||Try changing the default database to master. If you can then connect with that login, there is a problem with the users access to what you were using for the default database.
-Sue
login problem after restoring a database in SQL7
Since the database server was removed to a new Windows Advanced Server 2000, I restored a database from the backup file. When I tried to create a new Login under Security, I got the following error message:
Error 15023: User or role 'MyDatabaseLoginName' already exists in current database.
But I checked it and it doesn't exist. I even could not create a system DSN without the Login. Someone told me to create a new one with another name, but I do not want to change the connection string in my ASP files. Does anyone has any idea about it? Thanks in advance.Did you try to fix it with this system stored proc, can be used in sql server login, should fit ur situation.
sp_change_users_login
Error 15023: User or role 'MyDatabaseLoginName' already exists in current database.
But I checked it and it doesn't exist. I even could not create a system DSN without the Login. Someone told me to create a new one with another name, but I do not want to change the connection string in my ASP files. Does anyone has any idea about it? Thanks in advance.Did you try to fix it with this system stored proc, can be used in sql server login, should fit ur situation.
sp_change_users_login
Monday, February 20, 2012
Login Problem
I just tried to create a Transactional Replication and it failed because the
Login for sa failed...
I assume that the destination server (for Push Subscription) doesn't
recognize the sa of the Distributor
What can be done to solve that (when running the wizard I wasn't given any
chance to solve that)
Thank you,
Samuel
if this is a pull subscription you must make sure that the sa account, the
SQL Server agent account on the subscriber, or the account that you are
using to pull the subscription has dbo rights on the distribution database,
and is in the PAL of the publication database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:eeCvEFN5GHA.4868@.TK2MSFTNGP03.phx.gbl...
> I just tried to create a Transactional Replication and it failed because
> the Login for sa failed...
> I assume that the destination server (for Push Subscription) doesn't
> recognize the sa of the Distributor
> What can be done to solve that (when running the wizard I wasn't given any
> chance to solve that)
> Thank you,
> Samuel
>
|||It is a push subscription
Samuel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OG4si8N5GHA.4256@.TK2MSFTNGP03.phx.gbl...
> if this is a pull subscription you must make sure that the sa account, the
> SQL Server agent account on the subscriber, or the account that you are
> using to pull the subscription has dbo rights on the distribution
> database, and is in the PAL of the publication database.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:eeCvEFN5GHA.4868@.TK2MSFTNGP03.phx.gbl...
>
|||Samuel,
please see if this applies to your issue:
http://support.microsoft.com/default...b;en-us;320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Your publisherlogin property must be the sa account (or an account in the
dbo role in the distribution database and the dbo role in the publication
database). can you ensure that this is the case?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O$JJeDO5GHA.2596@.TK2MSFTNGP06.phx.gbl...
> It is a push subscription
> Samuel
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OG4si8N5GHA.4256@.TK2MSFTNGP03.phx.gbl...
>
Login for sa failed...
I assume that the destination server (for Push Subscription) doesn't
recognize the sa of the Distributor
What can be done to solve that (when running the wizard I wasn't given any
chance to solve that)
Thank you,
Samuel
if this is a pull subscription you must make sure that the sa account, the
SQL Server agent account on the subscriber, or the account that you are
using to pull the subscription has dbo rights on the distribution database,
and is in the PAL of the publication database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:eeCvEFN5GHA.4868@.TK2MSFTNGP03.phx.gbl...
> I just tried to create a Transactional Replication and it failed because
> the Login for sa failed...
> I assume that the destination server (for Push Subscription) doesn't
> recognize the sa of the Distributor
> What can be done to solve that (when running the wizard I wasn't given any
> chance to solve that)
> Thank you,
> Samuel
>
|||It is a push subscription
Samuel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OG4si8N5GHA.4256@.TK2MSFTNGP03.phx.gbl...
> if this is a pull subscription you must make sure that the sa account, the
> SQL Server agent account on the subscriber, or the account that you are
> using to pull the subscription has dbo rights on the distribution
> database, and is in the PAL of the publication database.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:eeCvEFN5GHA.4868@.TK2MSFTNGP03.phx.gbl...
>
|||Samuel,
please see if this applies to your issue:
http://support.microsoft.com/default...b;en-us;320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Your publisherlogin property must be the sa account (or an account in the
dbo role in the distribution database and the dbo role in the publication
database). can you ensure that this is the case?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O$JJeDO5GHA.2596@.TK2MSFTNGP06.phx.gbl...
> It is a push subscription
> Samuel
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OG4si8N5GHA.4256@.TK2MSFTNGP03.phx.gbl...
>
Subscribe to:
Posts (Atom)