problem in Installing SQl Server Management studio express _ (ctp)

I'm very new to MS SQl Server, i Downloaded MS SQl Server express edition, the problem is i can't deal with it, when i downloaded (Microsoft SQL Server Management Studio Express - Community Technical Preview (CTP) November 2005) every time i try to install it , it give me error message says "The system administrator has set policies To Prevent this installation"
How can i over come this problem and install this interface to be able to deal with sql server?
is there any other software can do this job "making interface for the sql server express edition"?
ThxI downloaded the Nov CTP as well of the Management studio, but can't go beyond this error.

This installation package cannot be installed by the windows installer service. You must install a windows service pack that contains a newer version of the windows installer service.

I have Windows 2000 SP4 and also the Windows Installer 3.0, MSXML 6.0 Still

Thanks in advance for any help.
Kalyan.|||Go to the following link an install the latest update to Windows Installer: http://www.microsoft.com/downloads/details.aspx?FamilyID=889482fc-5f56-4a38-b838-de776fd4138c&DisplayLang=en

Cheers,
Dan|||hi,
I have the windows installer 3.1 but yet i can't install (TCP) managment studio keep getting the same error "administrator has set policies to prevent this installation"
additional hint : - i use windows xp_sp2 - when i used to counter this problem i downloaded MS Visual Web developer express 2005 and i was able to connect to sql server, but really i'm new to sql and i need to see interface to be able to learn it at least|||Sorry for the messege, i downloaded the SQl Server Management Studio Express Again and when i tried to install it , it worked seems it was a problem in the first time i downloaded it something was wrong in it
anyway thx guys

Problem in Installing SQL Server Management Studio

Hi all, i have already installed Visual Studio 2005 Professional Addition, due to which the following components have been installed on my computer

Microsoft .NET Compact Framework 1.0 SP3 Developer

Microsoft Compact Framework 2.0

Microsoft .NET Framework 2.0

Microsoft Device Emulator version 1.0-ENU

Microsoft Document Explorer 2005

Microsoft SQL Server 2005

Microsoft SQL Server 2005 Mobile [ENU] Developers Tools

Microsoft SQL Server Native Client

Microsoft SQL Server Setup Support Files

Microsoft SQL Server VSS Writer

Microsoft Visual J# 2.0 Redistributable Package

Microsoft Visual Studio 2005 Professional Edition - ENU

MSDN Library for Visual Studio 2005

MSXML 6.0 Parser

My visual studio 2005 professional edition is working correctly, but now i want to install Microsoft SQL Server Management Studio. I have a CD on which i have following folders

1.SQL Kit and Reporting Services(this folder contains folowing setups,

SQLServer2005_SSMSEE,

SQLEXPR_ADV,

SQLEXPR_TOOLKit)

2.Database Publising Wizard 1.0 RC(this folder contains a setup named as Database Publising Wizard 1.0 RC)

3.Book folder contains a setup named as SqlServer2k5_BOL_june2006

My problem is that, i don't know in which sequence i have to run these setups, so that the management studio successfully insatlled on my computer. If anybody understands my problem, plz help.

It looks like you have SQL 2005 Express Edition. To install the Express version of SSMS, run the setup or installer in SQLServer2005_SSMSEE .

|||

You have solved my problem, thank you so much for replying.

Problem in Installing Microsoft SQL Server Management Studio Express - Community Technology Prev

Hi,

I installed SQL Server Express Edition and trying to install Management Studio Express but not successful.

Following Error is thrown while installing MSSMSE.

"The Installation Package could not be opened. Contact the application vendor to verify that this is a valid Windows Installer Package"

I downloaded the software from http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en

Please help me. I am struck.

Shyam

This sounds like a problem with the download. Try turning off anti-virus protection when you do the download. Also, make sure you save the download to a drive that is not near capacity.

Problem in ERD creation in sql server management studio

I have downloaded adventurework database intall it then configure the databases in managemnt studio and then make a ERD of the db all works fine.

Then I have intall asp.net 2.0 starter kits.
1,Club Starter kit
2,Commerce Starter Kit
3,eBay Selling StarterKit
4,Jobs Site Starter Kit
5,Personal Site Starter kit
6,Time Tracker Starter kit

install them and configure the databases in managemnt studio.all thing works fine but when I create erd of any of these databases I get message

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

I have read the microsoft kbarticle but really no help.
If any body have gone through same situation and resolve it then please reply.

Thanks
Kamran Shahid
Software Engineer(MCSD.NET)
Progressive Systems (Pvt) Ltd.
Karachi,Pakistan
Tel :92-21-5675294-96
Cell :920304-2212616
Email :kamranshahid@.netprosys.com
:kamii47@.hotmail.comRight-click on the database in MS, select properties, and on the files tab, specify an owner for the db.|||When I check to database properties then file option it has the same owner that is (KAMI\Administrator) as of the databases (adventureworks,nortwind,pubs) whos ERD I am able to and have created.|||

That issue was also discussed here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=228352&SiteID=1

sql

Problem in attaching Database (Microsoft Management Studio)

I want to change a database to different physical file. So i try the following step:

Detach the database

Attach the new different file

But i get an error "....... SQL server, error:5133".

It still needs the original file.

How to solve this problem?

many-many thanks,

JRLiem

Have you tried something like this?

USE MyDatabase

GO

ALTER DATABASE MyDatabase

MODIFY FILE (NAME = MyDatabase, FILENAME = 'H:SQLData\MyDatabase.mdf')

GO

ALTER DATABASE MyDatabase

MODIFY FILE (NAME = Mylog, FILENAME = 'G:SQLlogs\MyDatabase_log.ldf')

GO

Restart SQL Server |||

a database has at least two files

the .mdb and the .ldf files

you have to specify where these files are located during the attach

|||

Thanks, Andy.

It works.

|||Actually, the LDF file is really not necessary in some cases if you specify to SQL Server to generate a new LDF file during the attach process (provided that the MDF file is not severely damaged)|||Actually the LDF file is only needed if the database was not clearly shutdown. After a clean shutdown, the database is in a consistent state and can be "recovered" by adding another "clean" logfile.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Problem during reinstalling SQL 2000

Hi Group,
I had to unistall my SQL server 2000 SQP4 instalation due to management
console failure.
The problem is, during the reinstallation while the setup is configuring the
server after copy files, setup stops with 'can't not configure the server'
window.
I'm goíng to add the lasts rows of SqlStp.log, and i wish to know the best
practices of cleaning an old installation (registry keys, etc...)
Thank a lot to all...
16:49:51 El programa de instalación no pudo configurar el servidor. Consulte
los registros de errores y C:\Documents and
Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
16:49:51 Action CleanUpInstall:
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
1 -Action 4 -Service SQLSERVERAGENT
16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
servicio instalado.
16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
1 -Action 4 -Service MSSQLSERVER
16:49:51 Process Exit Code: (0)
16:49:51 StatsGenerate returned: 2
16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
16:49:51 StatsGenerate -1,sergior)
16:49:51 Installation Failed.Hi Sergio
"Sergio" wrote:
> Hi Group,
> I had to unistall my SQL server 2000 SQP4 instalation due to management
> console failure.
> The problem is, during the reinstallation while the setup is configuring the
> server after copy files, setup stops with 'can't not configure the server'
> window.
> I'm goíng to add the lasts rows of SqlStp.log, and i wish to know the best
> practices of cleaning an old installation (registry keys, etc...)
> Thank a lot to all...
>
> 16:49:51 El programa de instalación no pudo configurar el servidor. Consulte
> los registros de errores y C:\Documents and
> Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
> 16:49:51 Action CleanUpInstall:
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 4 -Service SQLSERVERAGENT
> 16:49:51 Process Exit Code: (1060) El servicio especificado no existe como
> servicio instalado.
> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
> 1 -Action 4 -Service MSSQLSERVER
> 16:49:51 Process Exit Code: (0)
> 16:49:51 StatsGenerate returned: 2
> 16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
> 16:49:51 StatsGenerate -1,sergior)
> 16:49:51 Installation Failed.
>
This seems to be the same problem as http://tinyurl.com/2c3tdf, the solution
suggested there is to remove the SQL Server installation, download and
install MDAC or check that you current configuration is up-to-date and not
corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
so you can skip over the MDAC installation step.
John|||Ok John,
Thanks for your fast reply, i'm gonna probe it...
"John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
news:24F82C2A-2919-482A-AC7D-C2965BD7D690@.microsoft.com...
> Hi Sergio
> "Sergio" wrote:
>> Hi Group,
>> I had to unistall my SQL server 2000 SQP4 instalation due to management
>> console failure.
>> The problem is, during the reinstallation while the setup is configuring
>> the
>> server after copy files, setup stops with 'can't not configure the
>> server'
>> window.
>> I'm goíng to add the lasts rows of SqlStp.log, and i wish to know the
>> best
>> practices of cleaning an old installation (registry keys, etc...)
>> Thank a lot to all...
>>
>> 16:49:51 El programa de instalación no pudo configurar el servidor.
>> Consulte
>> los registros de errores y C:\Documents and
>> Settings\sergior\WINDOWS\sqlstp.log para obtener más información.
>> 16:49:51 Action CleanUpInstall:
>> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
>> 1 -Action 4 -Service SQLSERVERAGENT
>> 16:49:51 Process Exit Code: (1060) El servicio especificado no existe
>> como
>> servicio instalado.
>> 16:49:51 C:\DOCUME~1\sergior\CONFIG~1\Temp\SqlSetup\Bin\scm.exe -Silent
>> 1 -Action 4 -Service MSSQLSERVER
>> 16:49:51 Process Exit Code: (0)
>> 16:49:51 StatsGenerate returned: 2
>> 16:49:51 StatsGenerate (0x0,0x1,0xf00000,0x100,1034,303,0x0,0x1,0,0,0
>> 16:49:51 StatsGenerate -1,sergior)
>> 16:49:51 Installation Failed.
> This seems to be the same problem as http://tinyurl.com/2c3tdf, the
> solution
> suggested there is to remove the SQL Server installation, download and
> install MDAC or check that you current configuration is up-to-date and not
> corrupted, then install SQL Server in debug mode "setupsql.exe k=dbg"
> so you can skip over the MDAC installation step.
> John

Problem Doing SQL 2005 DB Restore - Media Families

I did a full DB backup that I am trying now to restore via the SQL Server Management Studio.

I select "Restore" and then "From Device" and point to the bak file that I want to restore from.

I then go into the Options page and check "Overwrite the existing database".

Below that, shows the Restore the database files as

RB_Data_Services_MSCRM

RB_Data_Services_MSCRM_Log

sysft_ftcat_documentindex

When I then click OK I get the following error. The Media Set has 3 Media Families but only 1 are provided. All members must be provided.

Any ideas as to what I did to do to be able to complete this restore?

Thank

Rick Bellefond

Have you treid using RESTORE statement from query analyzer?|||Check that Database Name of the Database your are restoring matches that of the database your are resoring too.|||

refer this it has the solution

http://forums.microsoft.com/technet/showpost.aspx?postid=259647&siteid=17&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Madhu

Problem Doing SQL 2005 DB Restore - Media Families

I did a full DB backup that I am trying now to restore via the SQL Server Management Studio.

I select "Restore" and then "From Device" and point to the bak file that I want to restore from.

I then go into the Options page and check "Overwrite the existing database".

Below that, shows the Restore the database files as

RB_Data_Services_MSCRM

RB_Data_Services_MSCRM_Log

sysft_ftcat_documentindex

When I then click OK I get the following error. The Media Set has 3 Media Families but only 1 are provided. All members must be provided.

Any ideas as to what I did to do to be able to complete this restore?

Thank

Rick Bellefond

Have you treid using RESTORE statement from query analyzer?|||Check that Database Name of the Database your are restoring matches that of the database your are resoring too.|||

refer this it has the solution

http://forums.microsoft.com/technet/showpost.aspx?postid=259647&siteid=17&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Madhu

Problem Doing SQL 2005 DB Restore

I did a full DB backup that I am trying now to restore via the SQL Server Management Studio.

I select "Restore" and then "From Device" and point to the bak file that I want to restore from.

I then go into the Options page and check "Overwrite the existing database".

Below that, shows the Restore the database files as

RB_Data_Services_MSCRM

RB_Data_Services_MSCRM_Log

sysft_ftcat_documentindex

When I then click OK I get the following error. The Media Set has 3 Media Families but only 1 are provided. All members must be provided.

Any ideas as to what I did to do to be able to complete this restore?

Thank

Rick Bellefond

Looks like you created a backup that spanned multiple media families. You need to add in all of the pieces of the backup that you took for this to work.|||

Michael,

I did not intend to have my backup span multiple media families but I guess that is what I did.

I was not able to use that backup but found one that I could use.

Thanks.

Rick

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

problem creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

problem creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))
The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

Problem creating database diagram

I have both SQL Server 2000 and SQL Server 2005 installed on my PC running windows XP SP2.

In the Microsoft SQL Server Management Studio, when I right click Database Diagram in the Object Explorer, I cannot find any option to create a database diagram. What is given is "Working with SQL Server 2000 diagrams".

How can I fix my configuration so that I am able to create database diagrams in the SQL Server 2005?

Did you expand the database diagrams folder first to get the support objects created in the database?

What menu items do you get when you right click on the database diagrams folder?

Also in Help -> about, what version of SQL Server Management Studio do you have installed?

|||1. There isn't anything to expand under the database diagrams folder.
2. Right clicking on this folder gives three items:
a. Working with SQL Server 2000 diagrams
b. Reports --> Custom Report ...
c. Refresh
3. Version info:
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Hope these info help. Thanks.

Problem copying table

We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)

Hopefully you have a test-environment where you can try the following procedure:

1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.

As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.

problem copying database with Microsoft SQl Server Management Studio

I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).

I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:

Server user 'username' is not a valid user in database '123foo'

(123foo is the first database of all those on the server)

I need have it look only at my database which I have permissions for to copy it. How do I do this?

I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?

Thanks for any help!

I think you can use the import export option and specify them with primary keys and identities also other option is you can try scripting the tables and run the script in SSMS........

|||

Thats is some sort of Bug in SSMS. You will have to assign a valid database owner top the database as the existing database owner is no server principal on your computer, this can be either based on the fact that you deleted the server principal or as the most common reason, you restored a database backup which is from another computer or a certain point in time where the current database owner did not exist on the computer. To fix that use sp_changedbowner (described int he BOL) to change the db_owner.

Jens K. Suessmeyer

http://www.sqlserver2005.de

problem copying database with Microsoft SQl Server Management Studio

I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).

I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:

Server user 'username' is not a valid user in database '123foo'

(123foo is the first database of all those on the server)

I need have it look only at my database which I have permissions for to copy it. How do I do this?

I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?

Thanks for any help!

I think you can use the import export option and specify them with primary keys and identities also other option is you can try scripting the tables and run the script in SSMS........

|||

Thats is some sort of Bug in SSMS. You will have to assign a valid database owner top the database as the existing database owner is no server principal on your computer, this can be either based on the fact that you deleted the server principal or as the most common reason, you restored a database backup which is from another computer or a certain point in time where the current database owner did not exist on the computer. To fix that use sp_changedbowner (described int he BOL) to change the db_owner.

Jens K. Suessmeyer

http://www.sqlserver2005.de