Resolving

Hello!

I am having trouble connection to my SQL 2000 SP4 using TCP IP.

I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?

Carl
Norway

What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.

The following link have plenty of the content on troubleshooting connectivity issues.

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

cheers,

|||

Thanks!

But that was concerning SQL2005. My server is 2000 SP4.

But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.

Carl

|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||

How can I identify the Browser Service?

I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.

As of now I can not see any difference in the configuration.

sql

Resolving

Hello!

I am having trouble connection to my SQL 2000 SP4 using TCP IP.

I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?

Carl
Norway

What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.

The following link have plenty of the content on troubleshooting connectivity issues.

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

cheers,

|||

Thanks!

But that was concerning SQL2005. My server is 2000 SP4.

But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.

Carl

|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||

How can I identify the Browser Service?

I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.

As of now I can not see any difference in the configuration.

Resolving

Hello!

I am having trouble connection to my SQL 2000 SP4 using TCP IP.

I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?

Carl
Norway

What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.

The following link have plenty of the content on troubleshooting connectivity issues.

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

cheers,

|||

Thanks!

But that was concerning SQL2005. My server is 2000 SP4.

But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.

Carl

|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||

How can I identify the Browser Service?

I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.

As of now I can not see any difference in the configuration.

Reset Excel Destination for Error Logging

I am using sheets in an Excel spreadsheet to receive redirected error rows. The trouble is that it keeps appending the rows to the bottom of the sheet - even if I delete the rows in the spreadsheet. If I remove the spreadsheet all together then I need to set-up the Excel Desitnation Sheets again.

Each time I run the Control Flow I would like it to be removing old log entries and writing in the new ones.

There must be an easy way?

Unfortunately the Excel driver does not support anything like TRUNCATE TABLE. Even if you opened Excel and cleared the contents of those rows, the driver would still see them as "used" ... the only solution is to delete the rows themselves from within Excel. And you would want to check the range definition to make sure that you were in fact deleting all the rows in the range.

Of course the cleaner solution would be to drop and recreate the table. You could use an Execute SQL task for this purpose, and probably get one of the SSIS components to write the SQL for you to copy and paste in.

-Doug

Required permissions for running TextCopy?

I posted this last week, but unfortunately it was lost. So here 'tis again.

I am having trouble with working out what permissions a user needs to run the Textcopy function.

I have setup a stored procedure on the server which runs Textcopy to insert/export a GIF file to/from a network location to an Image field of a database. The stored procedure is called from an Access97 frontend.

It all works fine when the user on the client PC has SQL Server System Administrator permissions, however when the client PC is logged in as a normal user without these permission, the operation fails. Giving the user System Admin permissions fixes the problem, but obviously I can't do this for all the users!

Does anyone have any idea exactly what individual items I need to set permissions for to enable Textcopy to run??

Any hints at all, (no matter how small) would be much appreciated.

Thanks,

Ian.
:confused:What messages appear in the event viewer ? What are the current permissions on the textcopy.exe file ?|||Thanks for those thoughts.

Permissions on the file and directory are set to Full Control Everyone. (can't be that)

I can't find any messages being writen to the event log when this error occurs.

Ian.|||Please provide the stored procedure code.|||Here is SQL stored procedure:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (60),
@.whereclause varchar (50),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'D:\Temp\textcopy /S ' +@.srvname +
' /U ' + @.login +
' /P ' + @.password+
' /D ' + @.dbname+
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause+
'" /F ' +@.filename+
' /' + @.direction+
' /Z'
EXEC master..xp_cmdshell @.exec_str
GO|||What are the permissions on xp_cmdshell ?|||The permission on xp_cmdshell (in the master DB) are set to enable the cleint user run it.

(They weren't before but they are now. Still won't work)|||Did you create a sql server agent proxy account ?|||Do you mean a Proxy Server account for the same account that SQL Server agent runs under?

How does this effect it?

I would have thought that the permissions are within SQL as setting the account to be a SQL administrator fixes the problem? (I could be wrong)|||SQL server uses this account to execute the xp_cmdshell - it also uses this account for execution of an agent job (both of these apply to non-sysadmin accounts). For non-sysadmin accounts, these commands will be executed under the security context of this account. So you have to create a sql server agent proxy account.|||You were Right!!

I had not created a SQL Server Agent Proxy Account.

When I eventually found this and created an account it works.

Thanks a lot!

Ian.|||Originally posted by rnealejr
SQL server uses this account to execute the xp_cmdshell - it also uses this account for execution of an agent job (both of these apply to non-sysadmin accounts). For non-sysadmin accounts, these commands will be executed under the security context of this account. So you have to create a sql server agent proxy account.

how do you create a sql server agent proxy account?

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments?
Thanks!
Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments?
Thanks!
|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments?
> Thanks!
>
|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
David Portas
SQL Server MVP

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments'
> Thanks!
>|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
--
David Portas
SQL Server MVP
--

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments'
> Thanks!
>|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
David Portas
SQL Server MVP
--

Repost: Installation trouble

Removed the previous version and tried installing the new trial - was able
to see reports in visual but not in reportmanager. So now I tried to drop it
all including all iis-catalogues and installed it from scratch again.
No luck...
My goal is just to get it running to see reports - not to get it running
with the right security settings...so in IIS i changed reports and
reportserver to anonymous access and then stopped and started iis and
reportserver service.
But when activating reportmanager it keeps on saying:
unable to finde script library
'/aspnet_client/system_web/1_1_4322/webuivalidation.js - try placing this
file manually or reinstall by running 'aspnet_regiis -c'
tried running to above and tried aspnet_regiis -i - no luck.
Have an xp operating system - is there a platform limitation for the trial
version ?
Is it related to my IIS-settings ? It shouldn't try to get the file above
when using anonymous access. I have had the previous version to function.
--MichaelNo hints ?
When using reportmanager I can do nothing in there - tried to make a
datasource but nothing happens when trying to save it. When switching from
one tab to another the error below pops up...
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> skrev i en
meddelelse news:eJGqdb4kEHA.3984@.TK2MSFTNGP14.phx.gbl...
> Removed the previous version and tried installing the new trial - was able
> to see reports in visual but not in reportmanager. So now I tried to drop
it
> all including all iis-catalogues and installed it from scratch again.
> No luck...
> My goal is just to get it running to see reports - not to get it running
> with the right security settings...so in IIS i changed reports and
> reportserver to anonymous access and then stopped and started iis and
> reportserver service.
> But when activating reportmanager it keeps on saying:
> unable to finde script library
> '/aspnet_client/system_web/1_1_4322/webuivalidation.js - try placing this
> file manually or reinstall by running 'aspnet_regiis -c'
> tried running to above and tried aspnet_regiis -i - no luck.
> Have an xp operating system - is there a platform limitation for the trial
> version ?
> Is it related to my IIS-settings ? It shouldn't try to get the file above
> when using anonymous access. I have had the previous version to function.
> --Michael
>|||Not quite sure what did the trick but now it's working ...
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> skrev i en
meddelelse news:%23lDRJ8OlEHA.3760@.TK2MSFTNGP12.phx.gbl...
> No hints ?
> When using reportmanager I can do nothing in there - tried to make a
> datasource but nothing happens when trying to save it. When switching from
> one tab to another the error below pops up...
> "Michael Vardinghus" <michaelvardinghus@.notexisting.com> skrev i en
> meddelelse news:eJGqdb4kEHA.3984@.TK2MSFTNGP14.phx.gbl...
> > Removed the previous version and tried installing the new trial - was
able
> > to see reports in visual but not in reportmanager. So now I tried to
drop
> it
> > all including all iis-catalogues and installed it from scratch again.
> >
> > No luck...
> >
> > My goal is just to get it running to see reports - not to get it running
> > with the right security settings...so in IIS i changed reports and
> > reportserver to anonymous access and then stopped and started iis and
> > reportserver service.
> >
> > But when activating reportmanager it keeps on saying:
> >
> > unable to finde script library
> > '/aspnet_client/system_web/1_1_4322/webuivalidation.js - try placing
this
> > file manually or reinstall by running 'aspnet_regiis -c'
> >
> > tried running to above and tried aspnet_regiis -i - no luck.
> >
> > Have an xp operating system - is there a platform limitation for the
trial
> > version ?
> >
> > Is it related to my IIS-settings ? It shouldn't try to get the file
above
> > when using anonymous access. I have had the previous version to
function.
> >
> > --Michael
> >
> >
>