'Invalid Object name'

Hello

Once i have created a new query and it all works fine I save the query and then I run into trouble. When I come to re use the query at a later date it dosen't work and brings up the following error.

Msg 208, Level 16, State 1, Line 15

Invalid object name 'kup_regions'.

which equates to this line -

if (select sitetype from kup_regions where region_code = @.Location) = 10

I am using 'sa' as my username and sql server management studio

Cheers for any help

hi,

SimonJohns wrote:

Hello

Once i have created a new query and it all works fine I save the query and then I run into trouble. When I come to re use the query at a later date it dosen't work and brings up the following error.

Msg 208, Level 16, State 1, Line 15

Invalid object name 'kup_regions'.

which equates to this line -

if (select sitetype from kup_regions where region_code = @.Location) = 10

I am using 'sa' as my username and sql server management studio

Cheers for any help

assuming the kup_regions table/view exists at successive call in the current database, I'd just try with the complete object's name, that's the schemaName.objectName, in order to avoid eventual different default schema problems among different database users.. so it all becomes

IF (SELECT sitetype FROM dbo.kup_regions WHERE region_code = @.Location ) = 10 BEGIN

-- whatever

END;

but I see a side effect if region_code is not a "unique" value in the underlying table... if this is the case, your code will actually fail with
"Msg 512, Level 16, State 1, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
exception...

regards

Invalid column name using BCP

Hi,

I am having trouble creating the data files. I received the error that there is an invalid column name 'Name' when using the BCP to create the files. How can I find out where this error is coming from?

Thanks

Quote:

Originally Posted by sarah21

Hi,

I am having trouble creating the data files. I received the error that there is an invalid column name 'Name' when using the BCP to create the files. How can I find out where this error is coming from?

Thanks

Sarah,

Name is a funny word in SQL Server as it is also a programming command and that is why you may be experiencing problems, try putting the column name in [] or change column name to be something more meaingfull like FullName.

Hope that helps

Invalid Column Name error,

Hi,

I'm having trouble with the following query.

select convert(datetime, convert(int, audit_timestamp - 0.5)) as auditdate, database_name, sum(FileSize) as FileSize, sum(fileUsed) as FileUsed, sum(FileFree) as FileFree
from tbl_dbSize
where auditdate > getDate() -7
and lower(server_name) = 'xxx'
group by auditdate, database_name

Basically what I am trying to do is convert my records in the select statement (as I don't want to update the actual data) which were recorded on the same day (however with different times, i.e. 27/12/2003 00:01:03 , 27/12/2003 00:01:03) to be the same (i.e. 27/12/2003 00:00:00).

I keep getting the error,

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'auditdate'.

Any help appreciated.First, use this to truncate your datetime values:

cast(Convert(varchar(10), audit_timestamp, 120) as datetime) as auditdate

Second, you can't reference AuditDate by name; you have to reference it by the formula:

where cast(Convert(varchar(10), audit_timestamp, 120) as datetime) > getDate() -7
.
.
.
group by cast(Convert(varchar(10), audit_timestamp, 120) as datetime), database_name

It would be nice if TSQL allowed you to define the formula once and then refer to it by name, but the name isn't assigned until the query is completed and so is not available to the parser. (The exception is if you query is a subquery of another query, but that is another discussion...).

blindman|||reference to a column alias is allowed only in order by clause|||Thanks for the help.

It's working now!

Invalid buffer received from client.

I'm having seriouos trouble after installing SP4. I have an application writ
ten in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after upgrading
to SP4, the application totally crashed while trying to access the database.
It gives an error saying a
severe error has occued on the server. The
event log on the server has the following logged:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
For more information, see Help and Support Center at http://go.microsoft.com/fwlin
k/events.asp.
I'm sure it's SP4 because after I reinstalled SQL Server with SP3, everythin
g worked fine again. The server is running Windows 2003 SP1.
Anyone else having this problem or have a solution?Sp4 reduces the network packet size to 32767. Consider adjusting your app to
avoid the error.
<quote>
In SP4, the maximum value for the network packet size option (set using
sp_configure) is 32767. This is slightly less than half the previous maximum
of 65536. During upgrade, existing values larger than 32767 will
automatically be adjusted to 32767. If a script attempts to use sp_configure
to set a value larger than 32767 but less than or equal to 65536, the value
will also be set to 32767. Setting the network packet size to a value larger
than 65536 results in an error.
</quote>
There's used to be a bug even when the network packet size was set to
65535...
http://support.microsoft.com/kb/875411
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42813312@.duster.adelaide.on.net...
> I'm having seriouos trouble after installing SP4. I have an application
> written in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after
> upgrading to SP4, the application totally crashed while trying to access
> the database. It gives an error saying a severe error has occued on the
> server. The event log on the server has the following logged:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> I'm sure it's SP4 because after I reinstalled SQL Server with SP3,
> everything worked fine again. The server is running Windows 2003 SP1.
> Anyone else having this problem or have a solution?|||Thanks oj...
Do you have an example of how to change the packet size to 32767 - is it thr
ough the connection string? At the moment, it has packet size set to 4096.
oj wrote:
> Sp4 reduces the network packet size to 32767. Consider adjusting your app
to
> avoid the error.
> <quote>
> In SP4, the maximum value for the network packet size option (set using
> sp_configure) is 32767. This is slightly less than half the previous maxim
um
> of 65536. During upgrade, existing values larger than 32767 will
> automatically be adjusted to 32767. If a script attempts to use sp_configu
re
> to set a value larger than 32767 but less than or equal to 65536, the valu
e
> will also be set to 32767. Setting the network packet size to a value larg
er
> than 65536 results in an error.
> </quote>
> There's used to be a bug even when the network packet size was set to
> 65535...
> http://support.microsoft.com/kb/875411
>|||Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
According to the article, you might want to limit each of your insert batch
to 32767 rows.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42817d1b$1@.duster.adelaide.on.net...
> Thanks oj...
> Do you have an example of how to change the packet size to 32767 - is it
> through the connection string? At the moment, it has packet size set to
> 4096.
> oj wrote:|||I tried what you suggested but with no vail.
Here's my connection string:
UID=user;PWD=password;Initial Catalog=DatabaseName;Data Source=ServerName;Pa
cket Size=4096;
I tried the packet size 2048, 1024, 768 but they all return the same error.
Any help is greatly appreciated!
oj wrote:
> Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
> According to the article, you might want to limit each of your insert batc
h
> to 32767 rows.
>|||Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
and be sure the length is not exceeding the allowable for the specified
type.
If you post your code (.Net call + sql), someone will take a closer look.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:428293aa$1@.duster.adelaide.on.net...
>I tried what you suggested but with no vail.
> Here's my connection string:
> UID=user;PWD=password;Initial Catalog=DatabaseName;Data
> Source=ServerName;Packet Size=4096;
> I tried the packet size 2048, 1024, 768 but they all return the same
> error.
> Any help is greatly appreciated!
> oj wrote:|||Thanks oj... I changed my code as you suggested and it is now working! :)
But now it runs extremely slow and frequently timeout. Any solution to this
problem as well?
oj wrote:
> Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
> and be sure the length is not exceeding the allowable for the specified
> type.
> If you post your code (.Net call + sql), someone will take a closer look.
>|||Double check your sql to make sure proper indexes installed. Check out these
to see if they help:
http://support.microsoft.com/?id=308049
http://support.microsoft.com/kb/224587/
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282bc9e@.duster.adelaide.on.net...
> Thanks oj... I changed my code as you suggested and it is now working! :)
> But now it runs extremely slow and frequently timeout. Any solution to
> this problem as well?
>
> oj wrote:|||Thanks oj... It's working fine now...
The problem was caused by creating an SQL parameter using DbType instead of
SqlDbType. I was trying to create a generic data provider that connects to b
oth Oracle and SQL Server. Now I think I have to map the individual DbType t
o SqlDbType.
Anyway, thanks for your help!! Much appreciated!
oj wrote:
> Double check your sql to make sure proper indexes installed. Check out the
se
> to see if they help:
> http://support.microsoft.com/?id=308049
> http://support.microsoft.com/kb/224587/
>|||You're very welcome.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282ce51@.duster.adelaide.on.net...
> Thanks oj... It's working fine now...
> The problem was caused by creating an SQL parameter using DbType instead
> of SqlDbType. I was trying to create a generic data provider that connects
> to both Oracle and SQL Server. Now I think I have to map the individual
> DbType to SqlDbType.
> Anyway, thanks for your help!! Much appreciated!
> oj wrote: