Adjust number of decimal places

I have a column used in my report that has values such as:
496.1000
These are money values and thus I require them to be in the format:
496.10
How do I get rid of the two extra zeroes?
(my sql statement actually does it, but they still appear in reporting
services for some reason)
Thanks!Don't worry about the above - I found the answer in the SQL 2005
documentation
For those interested, it was under:
Formatting Numeric and Date Values in a Report
Cheers
On Dec 18, 9:27 am, "David Conte" <davco...@.gmail.com> wrote:
> I have a column used in my report that has values such as:
> 496.1000
> These are money values and thus I require them to be in the format:
> 496.10
> How do I get rid of the two extra zeroes?
> (my sql statement actually does it, but they still appear in reporting
> services for some reason)
> Thanks!

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rick
select serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegr oups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rickselect serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegroups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rickselect serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegroups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

Adding records to an SQL table

I trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.

I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:

use [c:\develope\sql\sqldata\psw.listener]

append from 'c:\develope\sql\data\listener.txt' type sdf

go

and then I pressed F5. I get an error that says:

Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'c:\develope\sql\sqldata\psw.listener'. No entry found with that name. Make sure that the name is entered correctly.

I checked in the directory c:\develope\sql\sqldata\ and the file psw.mdf is there and when I look in the psw database, the table listener is also there.

Could someone tell me what is going wrong? Also, how do I append the whole dbf table onto an SQL table. there's about 6000 records.

Thanks.

Hi Bob,

the question as asked does not seem to be for the Integration Services forum.

You should be able to do this using SSIS though by using a data flow with a source to read rows from DBF and a destination component to load those rows into your SQL Server table.

HTH.

Adding Records in SQL Server Express 2005

When adding records containing a date field in SQL Server 2005 Express I get an errorInput string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

I usually insert dates into SQL in String format. SQL can convert them by himself. BUT at least in our environment you have to create the date string in "MM.DD.YYYY" format so if you are trying "DD.MM.YYYY" It propably wont work though I know this could be solved with localization somewhere, somehow :)
|||

I do have it in string format. Any Ideas? My code excerpt

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Web2005ConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:Web2005ConnectionString1.ProviderName %>"

SelectCommand="SELECT * FROM [People] WHERE ([id] = @.id)"

InsertCommand="INSERT INTO [People] ([LastName], [FirstName], [Price], [LogDate]) VALUES (@.LastName, @.FirstName, @.Price, @.LogDate)"UpdateCommand="UPDATE [People] SET [LastName] = @.LastName, [FirstName] = @.FirstName, [Price] = @.Price, [LogDate] = @.LogDate WHERE [id] = @.id"DeleteCommand="DELETE FROM [People] WHERE [id] = @.id">

<SelectParameters>

<asp:ControlParameterControlID="GridView1"Name="id"PropertyName="SelectedValue"

Type="Int64"/>

</SelectParameters>

<UpdateParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

<asp:ParameterName="id"Type="Int64"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

</InsertParameters>

Adding permissions to an AD user on a remote database

I was wondering if someone can point me in the right direction. I am lookin
g
for a way to automate (in a script format) the addition of a user to a
database on a remote server. We have an application that requires that the
user have Owner permissions on 3 databases and 1 store procedure. Manually
connectiing to 400 workstations will be a huge pain..I'm not sure what you mean by 400 workstations - if a login
needs access to databases and stored procedures, you would
set that at the server level in SQL Server and in whatever
databases.
Unless there is something in the application itself that has
to be coded which would be an application issue
If you are just trying to add the login and user to the
databases, you can script these using t-sql commands. See
books online topics for sp_grantlogin, sp_grantdbaccess,
sp_addrolemember.
-Sue
On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:

>I was wondering if someone can point me in the right direction. I am looki
ng
>for a way to automate (in a script format) the addition of a user to a
>database on a remote server. We have an application that requires that the
>user have Owner permissions on 3 databases and 1 store procedure. Manually
>connectiing to 400 workstations will be a huge pain..|||Just to clarify... the 400 workstations have sql running on it for an
application that has an offline mode...
The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
sp_addrolemember which got me to here <see working code below>
How can I verify or check to see a user is already a member of the role ONE
database?
Example: If I am trying to add a user to the 'db_owner' role on the DB1
database and they are already listed as a member I would like to skip the
sp_addrolemember command.
This is all I could find:
IF SUSER_SID('domain\userid') IS NULL begin
but this only seems to find out if the user has a sid, not if the user is
listed in the DB1 database as a db_owner
Any help would be greatly appreciated.
This is my code so far...
'++++++++++++++++++++++++++++++++++++++
Domain = "DDDDDDDD"
Userid = "HHHHHHHH"
RemoteSQL = "WWW"
full_login = domain & "\" & userid
strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
Catalog='master'; User Id='XXXXXX'; Password='''';"
Set conn = CreateObject("adodb.connection")
conn.Open strconn
m = m & "USE DB0" & vbCrLf
m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "USE DB1" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
m = m & "USE DB2" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
'for the store procedure
m = m & "Use DB3" & vbCrLf
m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbCr
Lf
conn.execute(m)
Conn.close
set conn = nothing
"Sue Hoegemeier" wrote:

> I'm not sure what you mean by 400 workstations - if a login
> needs access to databases and stored procedures, you would
> set that at the server level in SQL Server and in whatever
> databases.
> Unless there is something in the application itself that has
> to be coded which would be an application issue
> If you are just trying to add the login and user to the
> databases, you can script these using t-sql commands. See
> books online topics for sp_grantlogin, sp_grantdbaccess,
> sp_addrolemember.
> -Sue
> On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||So the last piece you are looking for is the IS_MEMBER
function. That will tell you if a user is a member of the
specified database role.
-Sue
On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Just to clarify... the 400 workstations have sql running on it for an
>application that has an offline mode...
>The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
>sp_addrolemember which got me to here <see working code below>
>How can I verify or check to see a user is already a member of the role ONE
>database?
>Example: If I am trying to add a user to the 'db_owner' role on the DB1
>database and they are already listed as a member I would like to skip the
>sp_addrolemember command.
>This is all I could find:
>IF SUSER_SID('domain\userid') IS NULL begin
>but this only seems to find out if the user has a sid, not if the user is
>listed in the DB1 database as a db_owner
>Any help would be greatly appreciated.
>
>This is my code so far...
>'++++++++++++++++++++++++++++++++++++++
>Domain = "DDDDDDDD"
>Userid = "HHHHHHHH"
>RemoteSQL = "WWW"
>full_login = domain & "\" & userid
>strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
>Catalog='master'; User Id='XXXXXX'; Password='''';"
>Set conn = CreateObject("adodb.connection")
>conn.Open strconn
>m = m & "USE DB0" & vbCrLf
>m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "USE DB1" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
>m = m & "USE DB2" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
>'for the store procedure
>m = m & "Use DB3" & vbCrLf
>m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbC
rLf
>conn.execute(m)
>Conn.close
>set conn = nothing
>"Sue Hoegemeier" wrote:
>|||I am having issues with IS_Member...
"Indicates whether the current user is a member of the specified Microsoft
Windows group or Microsoft SQL Server database role. "
I did find this:
sp_helplogins 'domain\userid'
The problem with this is that the records that I want are in the second
record set.
Is there a way I can loop through the second record set instead of the first
one?
"Sue Hoegemeier" wrote:

> So the last piece you are looking for is the IS_MEMBER
> function. That will tell you if a user is a member of the
> specified database role.
> -Sue
> On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||Okay...so you actually want to know if the login exists -
not if it's a member of a database role. You can check if
the login exists before you add it using something like:
if not exists (select * from master.dbo.syslogins where
loginname = N'domain\userid')
exec sp_grantlogin N'domain\userid'
-Sue
On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am having issues with IS_Member...
>"Indicates whether the current user is a member of the specified Microsoft
>Windows group or Microsoft SQL Server database role. "
>I did find this:
>sp_helplogins 'domain\userid'
>The problem with this is that the records that I want are in the second
>record set.
>Is there a way I can loop through the second record set instead of the firs
t
>one?
>
>"Sue Hoegemeier" wrote:
>|||Thank you so much for your help Sue.
I was looking at the second record set for "sp_helplogins" and it has
multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
that when I run it the columns tell me: (UserID, Database Name, Permission
Type, Member/User)
Is that right?
"Sue Hoegemeier" wrote:

> Okay...so you actually want to know if the login exists -
> not if it's a member of a database role. You can check if
> the login exists before you add it using something like:
> if not exists (select * from master.dbo.syslogins where
> loginname = N'domain\userid')
> exec sp_grantlogin N'domain\userid'
> -Sue
> On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||If you are executing this in query analyzer, there will be
two results in the results pane. You need to use the scroll
for the results pane to see the other set of results.
-Sue
On Thu, 2 Mar 2006 07:48:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thank you so much for your help Sue.
>I was looking at the second record set for "sp_helplogins" and it has
>multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
>that when I run it the columns tell me: (UserID, Database Name, Permission
>Type, Member/User)
>Is that right?
>
>"Sue Hoegemeier" wrote:
>

adding leading zeros in select

Hi all,

I'm trying to select a bigint field and format it with leading zeros. I've tried the convert function but it does not seem to support this basic feature. There is no reference in the document on CONVERT or CAST, it only refers to formatting dates.

example:
table contents
123
456
789

desired select result:
000123
000456
000789

Who has a solution for this problem?select
replicate('0', 6-datalength(convert(varchar,NumericField)))
from MyTable

Or, better:

create function LZero(@.MyNum int, @.MyLength int)
returns varchar
as
begin
declare @.MyStr varchar
set @.MyStr = convert(varchar,@.MyNum)
if @.MyLength>datalength(@.MyStr)
set @.MyStr = replicate('0',(@.MyLength-datalength(@.MyStr)))+@.MyStr
return @.MyStr
end
go
select dbo.LZero(NumericField,6) from MyTable|||Thanx for your reply.

The first solution you offer works fine and suits my needs. I can't get the second to work properly however. The query only returns '0' for every row.

But this will get me where I want. Thank you very much|||Ok.
I have no SQL Server near me to debug it, so go for the 1st option.|||Originally posted by kukuk
Ok.
I have no SQL Server near me to debug it, so go for the 1st option.

OK, I will, thanx again|||the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.|||Originally posted by jora
the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.

Thanx for your reply, I already found it out and fixed it.

Adding Hours, Minutes, Seconds (SQL 2000)

Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000try this tricky thing...

declare @.Dt as datetime
set @.Dt = '2007-02-20'
declare @.Dt1 as datetime
set @.Dt1 = '2007-02-20 01:50:10'
declare @.Dt2 as datetime
set @.Dt2 = '2007-02-20 01:20:05'

select convert(varchar,cast((cast(@.Dt1 as float) - cast(@.Dt as float)) + (cast(@.Dt2 as float) - cast(@.Dt as float)) as datetime),114)

now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)

sum1
----
47:10:15

Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5

JulianDayNo
-------
2454154.9927028548|||I really don't think the formula needs to be that complicated...
set nocount on
declare @.TimeStrings table (TimeString varchar(8))

insert into @.TimeStrings (TimeString) values ('01:50:10')
insert into @.TimeStrings (TimeString) values ('01:20:5')

select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
from @.TimeStrings|||UPalsen's way works - thanx