MSSQL 2000 -> 2005 - Schema stuff?

Hi All,

I imported a database backup with no problems.
I can view the data using the Studio.
However, I noticed that all the tables are now pre-appended with a Schema name.
This also applies to Views.

How do I get 'rid' of this as my application doesn't know about the Schema and claims that it can't find the Tables and Objects I'm referenceing in the web.config.

Thanks,

-Alon

Hi,

schemas were introduced in SQL Server 2005. if the object is no in the default schema of the user, SQL Server will search for it in the dbo namespace. So, there are several options for you to make it work:

-Change default schema of the user which accesses the database to the schema the tables were imported.
-Directly import the tables to the dbo schema / or move them to the appropiate schema.
-Prefix your object in the queries with the schema name
-Schema are quite useful, you should get familiar with them, a lot to read is in the BOL. You should also consider using prefixes in your queries per se.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

While schemas are new in SQL 2005, SQL 2000 had the same basic idea (at least as far as calling the objects). In SQL 2000 objects had owners, and these were prefixed infront of the table name. In most cases dbo was the owner.

Take the application account that is logging into the database. Change it's default schema to the schema that the objects are in (probably the dbo schema) this will fix the problem. By default when you upgrade a database all users are setup with there own schema as there default schema.

You may want to remove any unneeded schemas from the database so that objects don't get created in the wrong schema.

Mssql - Mysql

Hi!

As a newbie I'm wondering if there is a difference between mySql and msSql.

Will mySql -code and -tables work in a msSql database?

Thank you!

Hi !

Well...thats depends. The code ported from mysql ill work if you only used the ANSI SQL compliant features. if you use any specific keywords or features of MySQL you will get a compilation / syntax error because SQL Server won′t know of these keyewords (like LIMIT). The relational data itself it compatible with SQL Server as you find an appropiate matching data type at SQL Server. Data Types differ in the different system, so while importing data in SQL Server you will have to decide how to convert (or match) the data(-types).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

MSP_CUBE tables not updating with current data

Hi there,

I have recently been put into a role working with Project Server Web Access and OLAP cubes. Am experiencing trouble displaying current data in the Portfolio Analyzer in Web Access.

After adding and deleting data, inlcuding projects and resources, the updates are not reflecting when analysing the cube data. I have successfully reprocessed the cube from the Analysis Manager but still does not show current data.

When looking at the MSP_CUBE_* tables in SQL Manager, these tables directly reflect the data displayed in the cube. ie. The non-current data.

Is there a specific way to update these tables with the current data from the ProjectServer tables ?

Thanks in advance,

Ben

Updating this source data is really a Project Server issue rather than an Analysis Services one. There might be some people here that use both, but you would be better off asking in a Project/ProjectServer focused forum or newsgroup.sql

Msg 208 / Temp Tables

I'm Using SQL 2000.
I issue the following commands in the Query Analyzer:
Select * from Prod into #Temp1
Insert into #Temp1 Select * from OldProd
I Get the following error:
Server: Msg 208, Level 16, State 1, Line 121
Invalid object name '#Temp1'.
Any ideas?
Thanks,
KFTo create the table using SELECT...INTO the syntax should be:
Select * into #Temp1 from Prod
Then run
Insert into #Temp1 Select * from OldProd
- Vishal|||Below executes OK on my machine...
Select * into #Temp1 from authors
Insert into #Temp1 Select * from authors
If you post a repro which we can execute, we might be able to help...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kflash" <kflash@.ameriflashtech.com> wrote in message
news:1f36401c389c5$9b9bfdc0$a601280a@.phx.gbl...
> I know. I got the syntax wrong on the original post but
> its right in the Query analyzer.
> When I run these commands:
> Select * into #Temp1 from Prod
> Insert into #Temp1 Select * from OldProd
> I get this Result:
> Server: Msg 208, Level 16, State 1, Line 121
> Invalid object name '#Temp1'.
> If I highlight the above commands and run them by
> themselves, it returns the correct result.
> It seems that Microsoft has issues with Temp tables. I'm
> hoping that there is a work around.
> Keith
>
> >--Original Message--
> >To create the table using SELECT...INTO the syntax
> should be:
> >
> >Select * into #Temp1 from Prod
> >
> >Then run
> >
> >Insert into #Temp1 Select * from OldProd
> >
> >
> >--
> >- Vishal
> >
> >
> >.
> >

MSDTC unavailable / JoinTransaction returned 0x8004d01c

Hi! We have 2 database server. 1 at our company, while the other with a web
hosting company.
We wrote stored procedures to update data from tables in our company's
server to the web hosting company's server. Out of 13 of these stored
procedures, 5 failed, giving me the error message that looks like the one
below,
Server: Msg 8501, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
MSDTC on server 'ORION' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
Can anyone help me explain what the error mean? I can post stored procedure
if required. Thanks.Hi this is one of the stored procedures that doesn't work. tsp is my user
name, server name.
CREATE PROCEDURE sp_ExportSysConf AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS (SELECT * FROM [SysConf])
BEGIN
--Step1: Remove records that do not exist in CIM
DELETE FROM [tsp].[tsp].[tsp].[SysConf]
FROM [tsp].[tsp].[tsp].[SysConf] as C2
WHERE NOT EXISTS (SELECT [C1].[ConfName], [C1].[ConfValue],
[C2].[LastUpdate] FROM [SMSBASE].[DBO].[SysConf] as [C1] where
[C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] = [C2].[ConfValue])
IF (@.@.error = 0)
BEGIN
-- no error --> Step 2: Insert new records from CIM
INSERT [tsp].[tsp].[tsp].[SysConf]([ConfName], [ConfValue])
SELECT [ConfName],[ConfValue]
FROM [SMSBASE].[DBO].[SysConf] as C2
WHERE NOT EXISTS (SELECT * FROM [tsp].[tsp].[tsp].[SysConf] as [C1]
where [C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] =
[C2].[ConfValue])
IF (@.@.error = 0)
-- no error -> commit
COMMIT TRANSACTION
ELSE
-- error occurred -> rollback
ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
GO