Help! Transfer of database from SQL Express to SQL 2005 host environment

Help!

I have been testing my system on my development PC using SQL Express. Great! Now its all working and I have 4.5Mb of data, and about 50 tables plus queries.

HOW DO I GET THIS TO MY NEWLY SETUP HOST ENVIRONMENT!

Argh... There seems to be no way to export from SQL Express to SQL 2005. The host company cannot restore from backup (I think this is reasonable.)

What was microsofts plan here? What am I missing? I have searched the net, and I have found about 20 people asking the same question with no answer.

Best answer I found was on this forum where somebody said download SQL 2000 as a trial, then steal a tool called DTS which apparently does not expire. Not clear if DTS 2000 will work with 2005 express so I haven't tried that yet.

Somebody else said download SQL 2005 as a trial. I made the mistake of doing this. It doesn't load unless I unload my SQL Express. If I do that then how do I make development changes after the trial ends?

Somebody else says use SQL 2005 developer edition. Only 80 bucks. GREAT... I'll take it. So I search for how to get this wonder tool, and all the links end up with the generic 2005 system. No mention of how to obtain this "developer edition"

I really feel I must be missing something here.

HOW DID MICROSOFT FIGURE that people would deploy their systems?

What I am missing?

Mitch

Hello Mitch -

I'm not certain I understand your issue. If you need to copy an entire database from SQL Server 2005 Express or MSDE to SQL Server 2005, you have many options. The first is that you can back up the database in question and restore it to the 2K5 server. I have done this many times and it works fine. You can also "detach" the database, copy the MDF and LDF files to the 2K5 server and "attach" it there using stored procedures or graphical methods. You can also use a wizard within SQL Server 2005 to transfer the database. Another method is to use the bcp program to copy out the data and import it into a SQL2K5 database. All of these methods have been tested and work correctly.

From your other statements it may be that you're trying to move a SQL Server 2005 Express database to SQL Server 2000. This is also possible, as long as you haven't used any of the extended features in 2005 on the source database. Set the database compatibility level to 80, and then use the database transfer wizard to transfer the database to SQL Server 2000, or use the bcp method.

Buck

|||

Buck,

Thanks for the reply.

You are correct that I am trying to move from SQL Server 2005 Express to SQL Server 2005.

The reason the backup/restore approach doesn't work, is that the hosting company does not do restores for people. I can understand why. They can't spend 15 minutes everytime somebody wants to upload their SQL Server 2005 Express DB. And for whatever reason when I try to do the restore myself, I can't get it to access my backup file.

I do not understand the detach, reattach method, and doubt it would work from the host I am on.

The hosting company recommends using the SQL2K5 transfer wizard.

However as noted, I don't have SQL2K5.

I am currently in the process of installing the "trial version" of SQL2K5. To do this I first had to uninstall SQL 2005 Express. Now I am doing the SQL2K5 install, and it has taken over an hour and its not done yet.

I am hoping to figure out how to enable this $80.00 developers license, but while its mentioned here and there, there is no clear instructions on how exactly to buy such a thing.

By the way the reason that I don't want to go with the BCP program, is that I have about 50 tables and queries, and it seems that each table is a manual step.

Again thanks for the response, any advice would be appreciated.

Mitch

|||

That is a little clearer. So the problem isn't with SQL Server, it's with the hosting company that won't restore the DB. If you're able to get to the SQL Server instance on your hosting service, you can just ask them to place the file for you and you can restore it yourself. If they won't do that, I'd investigate another hosting service.

The detach and attach method will have those same file copy and placement issues. It's better to use the backup and restore method if you can.

As far as the developer's license, you're not able to put that into production. You're only allowed to use that on your own machine for development purposes. If you're talking about installing it on your system, It may take a while to install, since it contains the Visual Studio-type interface for the BI Development Studio.

Buck

|||

Mitch,

For deployment, take a look at "Distributing SQL Server Express Applications" in Books Online: http://msdn2.microsoft.com/en-us/library/ms165639(SQL.90).aspx

You don't have to uninstall SQL Server 2005 Express to install the Evaluation edition. SQL Server is a multi-instance product which means it can be installed side-by-side so long as the instance name is unique.

Cheers,
Dan

btw: SQL Server Express is SQL2K5.

HELP! Synchronize db and maintain foriegn key relations?

I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls--gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Sounds like a nice problem :p
Never had this one but it's fun to think about it, so this is what I came up with:

Use different ranges... Set the IDENTITY (or AUTO_INCREMENT) on MySQL on a very high number, one you won't expect to reach in this product life cycle. Let say 10000000.

When synchronizing set the IDENTITY_INSERT ON, insert the records from the other server. Set IDENTITY_INSERT OFF, reseed to the lower value and continue.

Example:
CREATE TABLE tst (c1 INT IDENTITY, c2 INT)

INSERT tst(c2) VALUES (1)
INSERT tst(c2) VALUES (2)
INSERT tst(c2) VALUES (3)
INSERT tst(c2) VALUES (4)

SET IDENTITY_INSERT tst ON

INSERT tst(c1, c2) VALUES (10000000, 5)
INSERT tst(c1, c2) VALUES (10000001, 6)
INSERT tst(c1, c2) VALUES (10000002, 7)

SET IDENTITY_INSERT tst OFF

DECLARE @.i INT
SELECT @.i = MAX(c1) FROM tst WHERE c1 < 10000000
DBCC CHECKIDENT ('tst', RESEED, @.i)

INSERT tst(c2) VALUES (8)
INSERT tst(c2) VALUES (9)
INSERT tst(c2) VALUES (10)

SELECT * FROM tst

DROP TABLE tst
Ofcourse this could be useless in your case, 'cause what happens when someone is inserting while you're synchronizing!? But maybe it's a start.|||or else u can use another set of tables for remote data and use views to combine both local & remote data. u might be needing an additional flag field in the view to identify the source.

HELP! Synchronize db and maintain foreign key relations?

I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql...local is ms sql..

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls--gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Hi,

The way i'm using is to create my own table with two columns...

The columns are Old_ID and New_ID

Before syncronization u inserts all elements from the synchronized table there!

After appending ur data into the main table u need to identify using other field /s/ which id corresponds to ur ancient id /with Update action query

The last step is appending the info from the table with foreign keys linked with ur temporary table. Instaed using the old foreign key u need to put the new entry and the information will be at its place!

Hope this helps as conception!

:)

help! substract two columns from two tables

I hope you can understand what I mean !
my english is not very good , i live in taiwan! i have a problem
table A :(income of year 2004) table B: (income of year 2003)
ITEM NAME INCOME INCOME growth
_______________________________________________
A 1000 500 -500
B 2000 2500 500
C 3000 1000 -200
How do I get then value "growth" , table A and table B are from
diferent datasource , if you know , please help me!! thank you very much!sorry ! growth of the third line(ITEM NAME = C) IS -2000
"Kline" wrote:
> I hope you can understand what I mean !
> my english is not very good , i live in taiwan! i have a problem
> table A :(income of year 2004) table B: (income of year 2003)
> ITEM NAME INCOME INCOME growth
> _______________________________________________
> A 1000 500 -500
> B 2000 2500 500
> C 3000 1000 -200
> How do I get then value "growth" , table A and table B are from
> diferent datasource , if you know , please help me!! thank you very much!|||Hello! Chris , your answer is helpful to me !! thanks a lot!
"Chris McGuigan" wrote:
> Kline,
> If by 'Table' you mean a Reporting Services Table, then you can't refer to
> items from different tables like that. Data regions in RS can only reference
> one dataset.
> When you say 'datasources' I am assuming you mean data on different servers.
> If so in MS SQL Server you can create "Linked Servers" to any OLE DB
> compliant datasource.
> If it's merely different databases on the same server, you just prefix the
> table name with the database and owner name, for example
> db2004.dbo.IncomeTable.
> The trick is to make one RS dataset pull the data from all the 'physical'
> datasources. Then you will have access to all your fields in one table.
> Your query may look something like this;
> SELECT A.Item as Item, A.Income as Income2004, B.Income as Income2003,
> B.Income - A.Income as Growth
> FROM ServerA.DB2004.dbo.IncomeTable as A
> JOIN ServerB.DB2003.dbo.IncomeTable as B
> You need to get all the data in one query and hence one RS table.
> Hope that helps.
> Chris McGuigan
> "Kline" wrote:
> > sorry ! growth of the third line(ITEM NAME = C) IS -2000
> >
> > "Kline" wrote:
> >
> > > I hope you can understand what I mean !
> > > my english is not very good , i live in taiwan! i have a problem
> > >
> > > table A :(income of year 2004) table B: (income of year 2003)
> > > ITEM NAME INCOME INCOME growth
> > > _______________________________________________
> > > A 1000 500 -500
> > > B 2000 2500 500
> > > C 3000 1000 -200
> > >
> > > How do I get then value "growth" , table A and table B are from
> > > diferent datasource , if you know , please help me!! thank you very much!

help! report users permission on a SQL 2000 server

hi
There are over 10 databases on my SQL server. I would like to have a report
that shows the user's permission on each database including tables, views an
d
stored procedures. At this stage, I want to know which user can run or not
stored procedures on each database. Is it possible to find permission using
system stored procedures? I read the online book of SQL server for 3 days.
But I still don't find the system stored procedures.
If this stored produres doesn't exist, I have to write a stored proceduce to
display these user's permission. Could you tell which system tables need to
be used?
Waiting for you
Million thanks in adv
Wen OUYou can use sp_helprotect, it is ran as follows:
use db1
sp_helprotect @.username = 'Username'
use db2
sp_helprotect @.username = 'Username'|||thank you for your reply
if there are more than 1 database, i have to use a cursor to access each
database and read the info.
do you have another idea?
waiting for you
thanks in adv
Wen OU
"Fany Vargas" wrote:

> You can use sp_helprotect, it is ran as follows:
> use db1
> sp_helprotect @.username = 'Username'
> use db2
> sp_helprotect @.username = 'Username'
>|||If you need to get this info for each database then you will indeed need to
loop through each database. So using a cursor to loop through each database
is a good option.
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||thank you
I have another question. If a domain user belongs to a domain user group, is
it possible to find if this user get permissions to execute a stored
procedure? At the moment, the stored procedure i wrote only shows the
permisssion of user groups or independent users. what system stored
procedure can I use?
million thanks in adv
wen ou
"Fany Vargas [MSFT]" wrote:

> If you need to get this info for each database then you will indeed need t
o
> loop through each database. So using a cursor to loop through each databas
e
> is a good option.
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>|||Look into the PERMISSIONS function. It returns a value containing a bitmap
that indicates the statement, object, or column permissions for the current
user. So you can run something like:
SELECT PERMISSIONS(OBJECT_ID('nameofobject'))
For details on how to decipher the bitmap you will need to reference BOL
article: "PERMISSIONS"
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ts
qlref.chm::/ts_pa-pz_6f78.htm) - in BOL, select Go->Url...
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Also look at the BOL topic: SETUSER
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||thank you for your help
it's very useful for me
"Fany Vargas [MSFT]" wrote:

> Also look at the BOL topic: SETUSER
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>sql

Help! Report Model Project doesn't like my primary key

I'm creating a report model in VS2005 I've created my data source fine and I have selected all the tables I want in the report model data view.

The problem is that for one of the tables it is refusing to acknowledge the promary key. If I try to create the report model it compains that the table doesn't have a primary key.

So I went into SQL Management Studio and checked the table, Lo and behold the primary key is there!!! I tried droping the primary key and recreating it but it still says there is no primary ley on the table.

Any ideas?!?Just did some fiddling and managed to find the problem.

There is a Unique Clustered Index on the table which the problem comes from, with the index it doesn't see the primary key, without it the key suddenly appears

Help! Replication jobs fails when it was working fine.

Hello,
I have a push subscription setup to replicate some tables. Initially, this
was setup properly and everything was working perfectly. Until recently, the
jobs started failing even though nothing was changed. The errors do not give
me enough information to find out what the problem is. Can someone give me
some hints or ideas of what to do?
FYI, I've re-generated snapshots and re-initialized the subscriptions but it
still fails.
FIRST ERROR
==============================================
Date4/21/2005 9:01:35 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID2
Step NameRun agent.
MessageExecuted as user: xxx\Administrator. The step did not generate any
output. NOTE: The step was retried the requested number of times (10)
without succeeding. The step failed.
Duration00:05:10
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted10
SECOND ERROR
==============================================
Date4/21/2005 9:06:46 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID3
Step NameDetect nonlogged agent shutdown.
MessageExecuted as user: NT AUTHORITY\SYSTEM. Replication-Replication
Distribution Subsystem: agent XXXXXXXX for XXXXXXXXXX failed. Executed as
user: Domain\Administrator. The step did not generate any output. NOTE: The
step was retried the requested number of times (10) without succeeding. The
step failed. [SQLSTATE 42000] (Error 14151). The step failed.
Duration00:00:00
Sql Severity18
Sql Message ID14151
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
its hard to say. Can you do logging to see if it reveals anything? also
look for dumps in case there were any access violations.
Follow these instructions to enable logging.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Also run profiler on the subscriber, check for blocking, or any exceptions
being thrown.
Donna
"Hilary Cotter" wrote:

> its hard to say. Can you do logging to see if it reveals anything? also
> look for dumps in case there were any access violations.
> Follow these instructions to enable logging.
> http://support.microsoft.com/default...b;en-us;312292
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>

Help! Problem with XSD mapping schema

Hello,
I have a simple mapping schema and XML document for import into MSSQL. The tables will be created on import by the bulkloader.
Im getting the cryptic error message: "Schema: invalid value for 'column' on 'bl_advertiser_listing'." when I start the import. I cant figure out why this is and I have reduced the job down to a very simple state. What 'column' is this error referring to and why? Thankyou.

The XML

<listings>
<advertiser id="470000016">
<listing>
<ibl-id>3406095</ibl-id>
</listing>
</advertiser>
</listings>

The Schema:

<xschema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlnsql="urnchemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="advertiser_listings" parent="bl_advertiser" parent-key="id" child="bl_advertiser_listing" child-key="id"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="listings" sql:is-constant="1">
<xs:complexType>
<xsequence>
<xs:element name="advertiser" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser" sql:key-fields="id">
<xs:complexType>
<xsequence>
<xs:element name="listing" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser_listing" sql:relationship="advertiser_listings">
<xs:complexType>
<xsequence>
<xs:element name="ibl-id" type="xs:integer"/>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="id" type="xs:integer"/>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

Moving thread...

|||I worked this out, it was quite simple! <xs:element name="ibl-id" type="xs:integer"/> is invalid since the dash - is not allowed in column names.

Help! Problem with XSD mapping schema

Hello,
I have a simple mapping schema and XML document for import into MSSQL. The tables will be created on import by the bulkloader.
Im getting the cryptic error message: "Schema: invalid value for 'column' on 'bl_advertiser_listing'." when I start the import. I cant figure out why this is and I have reduced the job down to a very simple state. What 'column' is this error referring to and why? Thankyou.

The XML

<listings>
<advertiser id="470000016">
<listing>
<ibl-id>3406095</ibl-id>
</listing>
</advertiser>
</listings>

The Schema:

<xschema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlnsql="urnchemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="advertiser_listings" parent="bl_advertiser" parent-key="id" child="bl_advertiser_listing" child-key="id"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="listings" sql:is-constant="1">
<xs:complexType>
<xsequence>
<xs:element name="advertiser" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser" sql:key-fields="id">
<xs:complexType>
<xsequence>
<xs:element name="listing" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser_listing" sql:relationship="advertiser_listings">
<xs:complexType>
<xsequence>
<xs:element name="ibl-id" type="xs:integer"/>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="id" type="xs:integer"/>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

Moving thread...

|||

I worked this out, it was quite simple! <xs:element name="ibl-id" type="xs:integer"/> is invalid since the dash - is not allowed in column names.

Help! Problem with XSD mapping schema

Hello,
I have a simple mapping schema and XML document for import into MSSQL. The tables will be created on import by the bulkloader.
Im getting the cryptic error message: "Schema: invalid value for 'column' on 'bl_advertiser_listing'." when I start the import. I cant figure out why this is and I have reduced the job down to a very simple state. What 'column' is this error referring to and why? Thankyou.

The XML

<listings>
<advertiser id="470000016">
<listing>
<ibl-id>3406095</ibl-id>
</listing>
</advertiser>
</listings>

The Schema:

<xschema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlnsql="urnchemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="advertiser_listings" parent="bl_advertiser" parent-key="id" child="bl_advertiser_listing" child-key="id"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="listings" sql:is-constant="1">
<xs:complexType>
<xsequence>
<xs:element name="advertiser" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser" sql:key-fields="id">
<xs:complexType>
<xsequence>
<xs:element name="listing" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser_listing" sql:relationship="advertiser_listings">
<xs:complexType>
<xsequence>
<xs:element name="ibl-id" type="xs:integer"/>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="id" type="xs:integer"/>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

Moving thread...

|||I worked this out, it was quite simple! <xs:element name="ibl-id" type="xs:integer"/> is invalid since the dash - is not allowed in column names.

Help! Join question

Hi, All
I have two tables as below, TABLE1 and TABLE2.
TABLE 1: Base
ID PName PPrice
--
1 A 30
2 B 20
TABLE 2: History
ID Ldate Amount
--
1 2005/8/7 50
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:
Ldate PName Amount
--
2005/8/7 A 50
2005/8/7 B null
and when I pass the date of 2005/8/8, it will return the result as below:
Ldate PName Amount
--
2005/8/8 A null
2005/8/8 B nullHere you go..
CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
CREATE TABLE #History(id int, Ldate datetime, amount int)
INSERT INTO #Base VALUES(1, 'A',30)
INSERT INTO #Base VALUES(2, 'B',20)
INSERT INTO #History VALUES(1,'20050807',50)
DECLARE @.DateParam datetime
SET @.DateParam = '20050807'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
SET @.DateParam = '20050808'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Cool! Thomas. That is what i need.
Many Thanks
"Roji. P. Thomas" wrote:

> Here you go..
>
> CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
> CREATE TABLE #History(id int, Ldate datetime, amount int)
> INSERT INTO #Base VALUES(1, 'A',30)
> INSERT INTO #Base VALUES(2, 'B',20)
> INSERT INTO #History VALUES(1,'20050807',50)
> DECLARE @.DateParam datetime
> SET @.DateParam = '20050807'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
> SET @.DateParam = '20050808'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "OKLover" <OKLover@.discussions.microsoft.com> wrote in message
> news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
>
>|||Hi
CREATE TABLE #t1
(
rowid int not null primary key,
pname char(1) not null,
pprice decimal(5,2)
)
insert into #t1 values (1,'A',20)
insert into #t1 values (2,'B',30)
CREATE TABLE #t2
(
rowid int ,
ldate datetime not null,
amn decimal(5,2)
)
insert into #t2 values (1,'20050807',20)
select coalesce(Ldate,'20050808'), PName,sum(pprice+amn)
from #t1 left join #t2
on #t2.rowid=#t1.rowid
and #t2.ldate='20050808'
group by Ldate, PName
Note: you will have to change a coded date value to the parameter.
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Hi
Probably you can try this
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
where B.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"OKLover" wrote:

> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign ke
y.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Your solution will not work because you put the joining condition in the
where clause.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
> Hi
> Probably you can try this
> declare
> @.compDate datetime
> set @.compDate = '20050807'
> select ISNULL(Ldate,@.compDate), PName, Amount
> from Base B
> full join History H on H.ID=B.ID
> where B.ldate=@.compDate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "OKLover" wrote:
>|||sorry! thank you for the correction
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
and H.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roji. P. Thomas" wrote:

> Your solution will not work because you put the joining condition in the
> where clause.
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
>
>

HELP! How to Upsize from Access 2002 to SQL Express

Problem:
Upsize a backend MSAccess 2002 Database to SQL Express 2005
Explored:
Tried using the upsizing wizard from Office XP(2002), Two tables always get skipped.
***! The two tables skipped data only, the tablename and data structure were created.
Tried to install UPSize Pro, installation failed.
I decide to try it in VWD 2005, here is my code so far but it keeps erroring out.

Dim cnAs System.Data.OleDb.OleDbConnection
Dim cmdAs System.Data.OleDb.OleDbDataAdapter
Dim dsAsNew System.Data.DataSet()
cn =New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\Bill\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\Data for Database.mdb';Persist Security Info=True")
cmd =New System.Data.OleDb.OleDbDataAdapter("select * from Service_Orders", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()

Dim connDestAsNew Data.SqlClient.SqlConnection("Data Source=WLOCKLAPTOP\SQLEXPRESS;Initial Catalog='Data for DatabaseSQLND1';Integrated Security=True")
connDest.Open()
Dim oBCPAsNew Data.SqlClient.SqlBulkCopy(connDest)
oBCP.DestinationTableName ="Service_Orders"
oBCP.WriteToServer(ds)
oBCP.Close()
connDest.Close()

It erors on Line
oBCP.WriteToServer(ds)
with.....
System.InvalidCastException was unhandled by user code
Message="Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.IDataReader'."
Source="App_Web_hb6xyamq"
StackTrace:
at ASP.xfer_data_aspx.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Bill\My Documents\Visual Studio 2005\WebSites\WebSite1\xfer data.aspx:line 30
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

I'm open for ANY suggestions, I do not have access to DTS, its not in the Express addition.
Thanks in advance.
Bill

You can use access. You can use the export feature in access togo straight to SQL Server and the data types will be auto converted.
|||I really appreciazte the help, using the export was a learning exprience, and also it showed the exact errors I was having. It also failed, but it gave me the reason for the failure instead of the generic 'table was skipped' error from the upsizing wizard report.
I ran this query in MS Access to 'prep' the tables before upsizing.
SELECT * FROM TableName
WHERE (((Date_Entered)<#1/1/1753# Or (Date_Entered)>#12/31/9999#));
That showed me the bad dates, there were only about four records returned from 8000. I fixed the dates, some were 2/10/085, etc, and I changed them to the real date 2/10/2005, how they got like that is beyond me.
I also had to change a field that was using currency to general number.
I ran the upsize wizard and all tables upsized.
In SQL Server Management Studio Express (Free Download and very helpful GUI from Microsoft) I then changed the old currency field to a money field without a hitch.
It seems that SQL is stricter on dates than MS Access.
Life is good!

help! H/W spec for SQL Server 2000

Hi,

I have a SQL server 2000 database running in Windows Server 2000. The database only consists of a handful of tables and is taking multiple inserts from @.40 client PCs.

The server exists on two servers with half the clients talking to each. The databases use transactional replication to stay synchronised.

Each PC generates about 100 SQL transactions per busy hour, each being an insert.

The customer is expanding the number of client PCs from 40, through 300 to, ultimately, about 3000. They are asking me what hardware spec is required and want to be told it in a for each additional 50 client applications you need XYZ type of format to allow for gradual growth.

I have to say I am unsure how to go about scaling and configuring the servers!

So, is there such a thing as an automatic configurator which automatically produces a required spec for a given number of transactions and clients? Are there other criteria they/I need to take into account such as number of logins etc? For archiving I am guessing that a CD/RW drive is the way to go?

Also, in terms of design between the two servers they have. With clustering, mirroring, replication etc I am unsure as to which is most appropriate.

The thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.

They also pull significant amount of reports which I think logically equates to a third server to keep this traffic away from the live database. So, is replication the best way to go? Say replication every 15 minutes to a reports server?

I am also of the opinion that they need to move from Windows 2000 Server to Advanced Server for added scalability but will pursue that elsewhere.

Any input MUCH appreciated.

Out of my depth! :confused:
PaulThe thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.

Then they better be willing to spend some money. They'll need it.

I would try to get them to move to Advanced Server or wait for 2003 SP1 to come out. Enterprise Edition will be needed for many of the features you are talking about. It's impossible to answer your question without knowing the database. You need to figure out how many rows in the tables will be taken at different levels of users, what the average fill ratio will be on those tables, then how much size you'll need along the way. In addition, you need to figure out how many transactions, and how much those will cost you as the database grows do to increased size of the tables.

I would purchase the Microsoft Press book: Permformance Tuning Manual technical reference if I was you and have a look at the worksheets and formulas in the book.

HELP! full text catalogs disapearing!

I have one publisher that pushes out a database to two subscribers, each with
Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
column full text indexed. The publisher is full text indexed so that QA can
test the data before replication happens.
my problem is that after the replication finishes sync'ing up the
subscribers, My tables are no longer full text enabled and my catalogue
disappears.
Can someone tell me how to avoid this? It takes about 5 ours to drop and
rebuild the catalog.
Thank you in advance!
Carl,
Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
Replication. The one-time snapshot of your table does not re-create the FT
Catalog parameter for the FT-enable table and once the table is created on
your subscriber, you will need to manually re-create the FT Catalog and set
Change Tracking with Update Index in Background and this will run a Full
Population (if the FT Catalog is un-populated).
Could you provide more details on how your subscribers tables are setup?
Thanks,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> I have one publisher that pushes out a database to two subscribers, each
with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||I am using transactional replication to push the changes from my publisher to
the subscribers. Here is the schema for one subscriber/publisher table:
CREATE TABLE defdba (
UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
DEFAULT (0),
NAME varchar(256) NULL ,
DBAID int NULL ,
JUDGEMENTID int NULL ,
MERLINNAME varchar(300) NULL ,
ADDDATE varchar(8) NOT NULL ,
msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
(
UID
) ON PRIMARY
)
I had set up the FTI to check for changes in the background, and then update
itself when replication push the data changes accross to the subscriber. From
what I am hearing you say, after I replicate the changes over, I must rebuild
the catalog from scratch each time. Is that correct?
"John Kane" wrote:

> Carl,
> Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
> Replication. The one-time snapshot of your table does not re-create the FT
> Catalog parameter for the FT-enable table and once the table is created on
> your subscriber, you will need to manually re-create the FT Catalog and set
> Change Tracking with Update Index in Background and this will run a Full
> Population (if the FT Catalog is un-populated).
> Could you provide more details on how your subscribers tables are setup?
> Thanks,
> John
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> with
> can
>
>
|||Carl,
No, it's the initial snapshot that is the problem as when the table schema
is created the on the subscribers, the FT Catalogs parameters (FT Catalog
name, FT-enabled columns) are not created and Change Tracking and Update
Index in background is not defined. Assuming that you do the snapshot only
once you will have to re-create this metadata only once, but when ever you
schedule another snapshot, you will have to re-do the FT Catalog metadata.
This may be the source of why your FT Catalogs are disappearing!
Furthermore, depending upon the amount (number of rows) and frequency
(batch/second or batch/hour), you should be able to use "Change Tracking"
and "Update Index in Background" on the Push Subscribers.
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:ECE0559F-C45A-4F14-A84F-9F5753BD1B1F@.microsoft.com...
> I am using transactional replication to push the changes from my publisher
to
> the subscribers. Here is the schema for one subscriber/publisher table:
> CREATE TABLE defdba (
> UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
> DEFAULT (0),
> NAME varchar(256) NULL ,
> DBAID int NULL ,
> JUDGEMENTID int NULL ,
> MERLINNAME varchar(300) NULL ,
> ADDDATE varchar(8) NOT NULL ,
> msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
> DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
> CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
> (
> UID
> ) ON PRIMARY
> )
> I had set up the FTI to check for changes in the background, and then
update
> itself when replication push the data changes accross to the subscriber.
From
> what I am hearing you say, after I replicate the changes over, I must
rebuild[vbcol=seagreen]
> the catalog from scratch each time. Is that correct?
> "John Kane" wrote:
with[vbcol=seagreen]
FT[vbcol=seagreen]
on[vbcol=seagreen]
set[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]
|||I think your problem is with the way replication modifies tables for
replication.
It looks like you are complaining about the dropping of the full text
indexing on your publisher, correct? I think this is a consequence of using
queued updating which does modify both the publisher and the subscriber
tables.
I am curious as to why you are using queued? You might be able to get away
with using pure bi-directional transactional replication, depending on
certain factors.
Can you tell me exactly what you are trying to do, where do most of your
updates happen and how many subscribers you have? Also have you implemented
any partitioning scheme to avoid conflicts?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>I have one publisher that pushes out a database to two subscribers, each
>with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
> can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||Hilary, thank you for your help!!!
My publisher server is where I process incoming raw files to update my
delivery servers (subscribers). There are six tables, two of which have one
column (MerlinName) that are FTI-enabled. Each table has between 4-6 million
rows in them.
I have Transactional Replication running once a week (The Publisher is only
updated 1-3 times a week.), and I rebuild the catalog three hours later in a
sql agent job. I rebuild that catalog each time because that only takes about
5 hours, where incremental rebuild can take up to 12 hours. I can run the sql
agent job seperately and it works fine.
Here is the sql agent job code:
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Rebuild'
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Start_Full'
When I come back the next day, the Subscribers data is sync'ed, but all FTI
configurations are gone. There is a catalog, but it is empty.
I dont understand how a transactional replication, which is just adding rows
to a table, can remove the FTI configurations.
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Were you able to find out anything about this?
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Carl,
As I said in my initial posting SQL Server 2000 FT Catalogs are not directly
supported with Replication. So, the replication of textual data & objects
(views, functions etc) containing full-text predicates such as CONTAINS* or
FREETEXT* during the initial snapshot is not supported. However, a
workaround is to manually create and maintain a post-snapshot script that
contains the FTS code as well as the referenced full-text indexes should be
placed in a post-snapshot script instead of being published as articles. You
can setup a SQLServerAgent job step to do this automatically and for some
T-SQL script examples , see KB article 240867 (Q240867) "INF: How to Move,
Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2CC14822-F27F-44ED-9046-5C72714A8D79@.microsoft.com...[vbcol=seagreen]
> Were you able to find out anything about this?
> "Hilary Cotter" wrote:
using[vbcol=seagreen]
away[vbcol=seagreen]
implemented[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]

HELP! - Problem with hints...

Hello everyone,
I'm having this problem, I'm trying to run a query that consists of six
tables and one view (all joined together)
When I run it without anything on the where (or just one criteria in the
where clause) works just fine, but if I run it with two criteria in the
where clause I get the following message:
<<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
larger than the page size into a hash table. Resubmit the query with the
ROBUST PLAN hint. >>
And when I do so -adding the ROBUST PLAN option-, I get the following
message:
<<< 'Query hints' cannot be used in this query type. >>
Any idea what is going on?!? please let me know!!!
TIA,
sb-rEveryone,
I forgot to put this...
I'm using SQL Server 2000 on Win2003 Server, SP 1
Thanks again...
"segis bata" <segisbata@.hotmail.com> wrote in message
news:exo1vo0qGHA.4192@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>|||segis bata (segisbata@.hotmail.com) writes:
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
><<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
><<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
Sounds like you are in dire straits. But at least you could post the
query you are having problem with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||What happen if you try the query without the view in it? with and without the
robust plan in the query.
"segis bata" wrote:
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>
>

Help! - BCP / Bulk Copy

I need top update several tables with new values from an external text file. That would notrmally be a no brainer, (even for me) :) But, what is important this time around, is I can NOT fire the triggers, or every user would get notified of a 150,000 updates.

The text file contains 4 fields
KEYNUM|UpdateField1|UpdateField2|Class

Class will be either 1 or 2, if class = 1 then table 1 contains the record, if class = 2 then table 2 contains the record...

Any help, as always is greatly appreciatedHi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?|||Originally posted by bmalar
Hi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?

That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)|||Originally posted by GregCrossan
That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)

Sorry I didn't read your post correctly.. I thought you were inserting new records.

help!

hi!
accidently I deleted a table from a database that has 1200 tables. I do not
want to restore the whole database, but just the table I deleted. I backup
the transaction log every hour. What should I do?
thanks,
Hi,
Do a POINT_IN_TIME Recovery... Still you have to restore the full database
backup in to a new database followed with transaction
log backups.
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY,
STOPAT= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#FdiLZofEHA.3412@.TK2MSFTNGP11.phx.gbl...
> hi!
> accidently I deleted a table from a database that has 1200 tables. I do
not
> want to restore the whole database, but just the table I deleted. I backup
> the transaction log every hour. What should I do?
> thanks,
>

Help!

Okay I'm totally new to SQL, I need to write a program that has 2 tables, can any one help me get started?

Quote:

Originally Posted by ghostrider

Okay I'm totally new to SQL, I need to write a program that has 2 tables, can any one help me get started?

Can you give us any more specifics?

What kind of program are you writing, what are the specs? What platform/programming language are you using?|||I'm using MS SQL 2005 Express. I'm trying to write a INSERT statement pulling data from my online school database and I'm not sure how to do it.|||

Quote:

Originally Posted by ghostrider

Okay I'm totally new to SQL, I need to write a program that has 2 tables, can any one help me get started?

Hi,
can you tell me clearly your requirements.really you want help for creating tables|||

Quote:

Originally Posted by ghostrider

I'm using MS SQL 2005 Express. I'm trying to write a INSERT statement pulling data from my online school database and I'm not sure how to do it.

If I understand correctly, you are trying to insert data from one table into another.

If these tables exist on different platforms you will need to write an application that does this.

I will assume both tables are in the same database.

INSERT INTO Table1(Column1,Column2)
SELECT Column1,Column2
FROM Table2|||Thanks this is what I was looking for. Thanks again!!!!

Help writing a report query

OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction with the transactions, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?

Can you post a bit more info, particularly a small sample set of data to work with and what you want the results to be? I am having a bit of trouble envisioning what you want.

|||

OK, it works something like this:

table ShareInfo

ShareInfoId int

CurrentPrice int

table SharePurchase

SharePurchaseId int

PortfolioId int

ShareInfoId int

SharesPurchased int

PricePerShare int

table SharePortfolio

PortfolioId int

and some other columns for names, etc. So, on the bottom level, I want to list all share purchases within a portfolio, and how their value has changed in each instance to toay. This data will be collapsible on the report, and the row that shows always will show the share name, and the total value of shares purchased, and amount lost/gained. This data is also collapsible, and on the top level, I show the Portfolio name, and the total value/total amount lost or gained on the portfolio to date.

|||

Hi you can use the ROLLUP operator,

Example,
Create table #Shares
(
PortfolioId int,
ShareId int,
NoOfShares int,
TimePurchased varchar(10),
SharesPerPrice int
)

Insert Into #Shares values(1,1,10,'10:00 AM',30);
Insert Into #Shares values(1,2,100,'10:00 AM',5);
Insert Into #Shares values(1,2,50,'11:00 AM',6);
Insert Into #Shares values(1,3,112,'10:00 AM',5);
Insert Into #Shares values(1,4,112,'10:00 AM',5);

Select
PortfolioId
,ShareId
,TimePurchased
,Sum(NoOfShares)
,Sum(SharesPerPrice)
,Sum(NoOfShares * SharesPerPrice)
from
#Shares
Group By PortfolioId, ShareId, TimePurchased With Rollup

OUTPUT:

PortfolioId ShareId TimePurchased NoOfShares SharesPerPrice Totalvalue
-- -- - -- -- --
1 1 10:00 AM 10 30 300
1 1 NULL 10 30 300
1 2 10:00 AM 100 5 500
1 2 11:00 AM 50 6 300
1 2 NULL 150 11 800
1 3 10:00 AM 112 5 560
1 3 NULL 112 5 560
1 4 10:00 AM 112 5 560
1 4 NULL 112 5 560
1 NULL NULL 384 51 2220
NULL NULL NULL 384 51 2220

If TimePurchased is NOT NULL then it is Down Level Data
If TimePurchased is NULL and ShareId is not null then it is One level Collopsed from the Down Level
If TimePurchased is NULL and ShareId is NULL and PortfolioId is not NULL then it is at Portfolio Level

|||

OK - that looks good, but how would I then populate my report from this ? I expected I'd return three tables, one for each level.

Thanks for helping...

|||

Here you can change the query as follow as,

Select
PortfolioId
,ShareId
,TimePurchased
,SUM(NoOfShares) NoOfShares
,SUM(SharesPerPrice) SharesPerPrice
,Sum(NoOfShares * SharesPerPrice) TotalValue
INTO #RESULT
from
Shares
Group By PortfolioId, ShareId, TimePurchased wITH ROLLUP
Select PortfolioId,ShareId,TimePurchased,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is not null

Select PortfolioId,ShareId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NOT NULL

Select PortfolioId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NULL And PortfolioId is Not Null

help with writting a trigger

Hi,
I have 2 tables ''Data' and 'LatestData' with same fields:
- ID,
- TruckNo,
- DateTime,
- Comment.
My application adds data to the Data table. I would like to have a trigger
which automatically updates or adds (if not exists) newly added records into
LatestData table. This trigger should also look at DateTime, and add records
only if they are newer then existing for a TruckNo.
So LatestData table contains only one and newest record per one TruckNo.
Thanks for help.
PrzemoHi
(untesed)
IF NOT EXISTS
(SELECT * FROM LatestData WHERE LatestData.TruckNo=inserted.TruckNo)
INSERT INTO LatestData SELECT * FROM Inserted
IF NOT EXISTS
(SELECT * FROM LatestData WHERE LatestData.ID=inserted.ID)
INSERT INTO LatestData SELECT * FROM Inserted
ELSE
UPDATE LatestData SET col=(SELECT i.col FROM inserted i JOIN deleted d
ON d.ID=i.ID)
WHERE EXISTS
(SELECT * FROM LatestData WHERE LatestData.ID=deleted.ID)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:85F7771F-96E1-42FB-9B2D-F8EC02BCCED1@.microsoft.com...
> Hi,
> I have 2 tables ''Data' and 'LatestData' with same fields:
> - ID,
> - TruckNo,
> - DateTime,
> - Comment.
> My application adds data to the Data table. I would like to have a trigger
> which automatically updates or adds (if not exists) newly added records
> into
> LatestData table. This trigger should also look at DateTime, and add
> records
> only if they are newer then existing for a TruckNo.
> So LatestData table contains only one and newest record per one TruckNo.
> Thanks for help.
> Przemo|||CREATE TRIGGER [TR1] ON [dbo].[Data]
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS
(SELECT * FROM LatestData,Inserted WHERE LatestData.ID=inserted.ID)
DELETE FROM LatestData WHERE ID IN( SELECT INSERTED.ID FROM
LatestData,Inserted WHERE LatestData.ID=inserted.ID)
INSERT INTO LatestData SELECT * FROM Inserted
---
Hi Uri,
I used your logic only, thanks for that but thought not to mess up with
update. I tested your code but it failed the update part and tehre was few
syntax error also.
The above logic delete updated record from LatestData so we consider it as a
new Insert and not to mess with update.
Thanks,
Sree
"Uri Dimant" wrote:

> Hi
>
> (untesed)
>
> IF NOT EXISTS
> (SELECT * FROM LatestData WHERE LatestData.TruckNo=inserted.TruckNo)
> INSERT INTO LatestData SELECT * FROM Inserted
>
> IF NOT EXISTS
> (SELECT * FROM LatestData WHERE LatestData.ID=inserted.ID)
> INSERT INTO LatestData SELECT * FROM Inserted
> ELSE
> UPDATE LatestData SET col=(SELECT i.col FROM inserted i JOIN deleted d
> ON d.ID=i.ID)
> WHERE EXISTS
> (SELECT * FROM LatestData WHERE LatestData.ID=deleted.ID)
>
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:85F7771F-96E1-42FB-9B2D-F8EC02BCCED1@.microsoft.com...
>
>