(local) vs. the actual name of SQL Server when using a named instance

Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leo
WHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,
sql

(local) vs. the actual name of SQL Server when using a named instance

Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,|||Referencing (local) implies that you are creating a new connection. This
connection won't know which instance the command comes from so will connect
to the default instance.
Is this bcp or osql? Can't think of another reason for it.
If you want to connect to the same instance from an sp then use @.@.servername
and put it in [].
"Leo" wrote:
> Hello,
> My question is this: in a named instance installation of a SQL Server, when
> you reference the server as (local) from a Stored Procedure that accesses a
> table for example, will that point to the Named Instance or will it access
> the table on the actual database on the main installation?
>
> Thank you
> leo
>
>|||I am actually running an Instance of SQL server on the same machine as an
installation of SQL server. The reason for that is QA testing. When I make a
change to the database I copy it to the instance os SQL on the same machine.
I am using store procedures to run DTS packages. So I am doing a DTSRUN
command on the package. On my SQL server I was using (local) as my server.
The reason I asked was that I didn't want to go into the Instance of the SQL
and change the name of the server on the stored procedures manually. I
wanted the stored proc to pick up the name of the server it;s running on
without manual changes to the code. I was affraid that if I leave that code
unchanged as (local) that the stored proc will look for the wrong DTS
package, meaning the one on the SQL server, not the one in the instance of
SQL. I am not sure if I made this more confusing or more explained.
in short I am trying to provide the samew functionality within one physical
sever using an installation of a SQL instance, as if it was two separate
physical servers, where you can reference (local) and whenever you more a
stored proc from one server to the other you do not need to change that code
to point to the right SQL server
Thanks
Leo
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:15408704-EE37-4A96-97D2-1BF39A6CE5AE@.microsoft.com...
> Referencing (local) implies that you are creating a new connection. This
> connection won't know which instance the command comes from so will
connect
> to the default instance.
> Is this bcp or osql? Can't think of another reason for it.
> If you want to connect to the same instance from an sp then use
@.@.servername
> and put it in [].
> "Leo" wrote:
> > Hello,
> >
> > My question is this: in a named instance installation of a SQL Server,
when
> > you reference the server as (local) from a Stored Procedure that
accesses a
> > table for example, will that point to the Named Instance or will it
access
> > the table on the actual database on the main installation?
> >
> >
> >
> > Thank you
> >
> > leo
> >
> >
> >

(local) vs. the actual name of SQL Server when using a named instance

Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

or

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

sql

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

or

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"Views"

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

or

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"sysft_Content" - what is it?

upgraded 2000 - 2005 and applied sp1.

also same failure with SQL 2005 from scratch

tried to do a simple Backup Database of a db named 'msu' to a disk file on a local hard drive just like I would with 2000. failed with:

System.Data.SqlClient.SqlError: The backup of the file or filegroup
"sysft_Content" is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the
selection to include only online data. (Microsoft.SqlServer.Smo)

That is your fulltext catalog. It needs to be rebuilt after the upgrade.

In sql2005, backups include fulltext, just like regular database and log files.

The fulltext catalog is modeled as a single "file".

You can take one of these actions and then try the backup again:

1) rebuild the catalog (the rebuild doesn't need to complete, and the re-crawl doesn't have to be triggered either)

2) drop the catalog

3) perform a file backup rather than full backup.

We are working on a KB article to address this problem, but it's not ready yet.

|||

Rebuild All - Failed to rebuild full-text catalog.
--Property PopulationStatus is not available for FullTextCatalog '[Content]'.
This property may not exist for this object,
or may not be retrievable due to insufficient access rights.
New Catalog & Drop Catalog - Cannot execute changes.
--Create failed for FullTextCatalog 'dfgdfg'. (Microsoft.SqlServer.Smo)
Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database. The functionality to disable and enable full-text search for a database is deprecated. Please change your application. (Microsoft SQL Server, Error: 7616)

EXEC sp_fulltext_database 'enable'; - "Completed Successfully"
but still get the "Full-Text Search is not enabled" messages

login 'sa'

|||

Could you check if msftesql service is running properly?

Is msftesql service running in a different service account?

I suspect there is some problem due to upgrade so that full-text is not running properly.

thanks,

Jingwei

"Incorrect syntax" exception when prefacing SP names with "dbo." and named p

We're currently trying to evaluate SQLJDBC 2005 1.1 June CTP's support for database mirroring automatic failover. Unfortunately we're getting unexpected exceptions for calls that work fine w/ jtds that our blocking our ability to perform these evaluations without us making substantial changes to our codebase.

The first issue is with the name used when calling a stored procedure -- SP names that start with "dbo." give us the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. src:{call dbo.xyz(?,?,?,?,?,?,?,?)}

The call will work if we change the SQL statement to {call xyz(...)}. I don't understand why we would need to do this, especially given that the documentation for the driver shows call statements with the "dbo." prefix.

We're also having problems using named parameters with stored procedures (for both in and out parametes). Our code has parameter names of the form "@.param" as is standard with TSQL (and is required when using jtds). However, this won't work with SQLJDBC -- it only seems to accept parameter names w/o the leading "@.". Why is this so?

Finally, we were able to cause a NullPointerException within the driver due to an incorrectly built Properties object that contained an Integer for loginTimeout instead of a String:

java.lang.NullPointerException

at java.util.Hashtable.put(Hashtable.java:396)

at java.util.Properties.setProperty(Properties.java:128)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.fixupProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.mergeURLAndSuppliedProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

While this was due to a bug in our code I would think that such common errors would be better handled.

Hi,

I am not able to reproduce the error that you are seeing with executing a stored procedure with the dbo prefix. What you have provided there looks like it should work fine. Could you provide a code sample that demonstrates the error?

Thank you,

--David Olix

JDBC Development

|||

We're using the Spring JDBC template helper classes in this instance :

JdbcTemplate t = getJdbcTemplate();

Object result = t.execute("{call dbo.SP(?, ?, ?, ?, ?, ?, ?)}",
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.registerOutParameter( "@.pParam1", Types.INTEGER );
cs.registerOutParameter( "@.pParam2", Types.DATE );
cs.registerOutParameter( "@.pParam3", Types.DATE );
cs.registerOutParameter( "@.pParam4", Types.DATE );
cs.registerOutParameter( "@.pParam5", Types.INTEGER );
cs.setInt("@.pParam6", i)
cs.setString( "@.pParam7", s1);
cs.setString( "@.pParam8", s2);
cs.execute();

return result; // object created based on out params

}

The template is basically doing a "connection.prepareCall()" with the passed in SQL string, and then calling doInCallableStatement from within a try/catch block that translates

exceptions.

Were you able to reproduce the issue with "@." not being accepted for named params? That problem is effectively impossible for us to workaround, as we would need

to alter large sections of code when switching between jtds and sqljdbc.

|||

Ok, I was able to track down the cause of the Incorrect syntax exception by running a DB trace. The core problem appears to be the use of named parameters -- the call to registerOutParameter resulted in the following being executed on the server:

exec sp_sproc_columns dbo.SP, @.ODBCVer=3

This statement is incorrect -- it needs to be exec sp_sproc_columns [dbo.SP], @.ODBCVer=3

|||

Yes, that's it. If you'd like to submit a bug so that you can track progress on the fix, you may do so through the MSDN Product Feedback center at http://msdn.microsoft.com/feedback . You may have to jump through one small hoop (search for a resolution to your problem) before being taken to the "submit feedback" button that goes to the bug submission form.

I am still researching the "@." issue and will get back to you when I have an answer there.

Thank you,

--David Olix

JDBC Development

#Error when trying to SUM field in dataset

I created a calculation field (Named Posted_InHouse in my dataset as such:

=IIf(Fields!Type.Value = "In-House", Fields!PostedAmount.Value, 0)

My purpose was to then be able to sum PostedAmount in my table Group field only if it comes from an In-House record.

I put this in

=SUM(Fields!Posted_InHouse.Value)

Now I get #Error in my group field when in preview

Have you tried: Cint(IIf(Fields!Type.Value = "In-House", Fields!PostedAmount.Value, 0))

I have found that sometimes you need to specifically specify the output of an iif statement.