Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts

Friday, March 30, 2012

i had a problem in inserting data in a table with foriegn key

i hav following 2 tables n i am able to insert data into the second table having a foriegn key

create table Customer_Details(
Customer_ID integer primary key,
Customer_First_Name varchar(75),
Customer_Last_Name varchar(75),
Address varchar(100))

create table Account_Details(
Account_No integer primary key,
Customer_ID integer foreign key references
Customer_Details(Customer_ID),
Debit float,Credit float,Balance float )hi
can u please put ur insert and also when a column is PK it cant be null. when u do not put any think then that means it also can be a null value but pk can't be null at all.

Friday, March 23, 2012

I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%
The server is running SQL Server 2000 sp4
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%
|||Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%
|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%

I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%The server is running SQL Server 2000 sp4
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a fe
w
> foreign keys against this primary key, so it's really hard to drop the ind
ex
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:

> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a fe
w
> foreign keys against this primary key, so it's really hard to drop the ind
ex
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%

I can't get rid of ExtentFragmentation on a few primary keys

I have many tables with primary keys that look like the one below. I've
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
(with a fillfactor of 80). But nothing seems to correct it. There is a few
foreign keys against this primary key, so it's really hard to drop the index
totally and recreate it. Has anyone seen this before?
Thank you
John
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (901578250); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3789.0
- Avg. Page Density (full)................: 53.19%Hi John
There seems to be only two extents in this index, the second of which is
only partially full (probably less than 20%)
John
"John" wrote:
> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation.
--
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:7470559B-3653-4F59-917B-758781E8568D@.microsoft.com...
>I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a
> few
> foreign keys against this primary key, so it's really hard to drop the
> index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%|||The server is running SQL Server 2000 sp4
"John" wrote:
> I have many tables with primary keys that look like the one below. I've
> tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG
> (with a fillfactor of 80). But nothing seems to correct it. There is a few
> foreign keys against this primary key, so it's really hard to drop the index
> totally and recreate it. Has anyone seen this before?
> Thank you
> John
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (901578250); index ID: 1, database ID: 12
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 3789.0
> - Avg. Page Density (full)................: 53.19%

I can't Copy a database from Sql Server 2000 to 2005

Hi,
I can't Copy a database from Sql Server 2000 to 2005. I am using the Copy
Database option from Management menu. I want to transfer all tables, and all
stored procedures. I've tried everything for the past 3 days, and it didn't
work. I always get the same error.
Below is the error that I am getting. Can someone please show/tell me a
simple permission configuration on both servers that would actually work
when I try to do the Copy Database. This would restore my attitude towards
migrating to SQL Server 2005. I don't want to have to write any scripts or
programs for that either.
I've searched all over the internet for a similar problems answered, but
couldn't find anything useful. Can you please help, because I am ready to
toss that SQL Server 2005 into a dumpster, and stay with SQL Server 2000. I
am sure that there are hundreds of users like myself with the same problem.
Can someone please answer it really well once and for all finally? I don't
want to do any Detach or backup/restore methods, or write scripts (once
again). I just want to be able to use COPY database option in the Management
menu (while the database is running) to transfer this database from SQL
server 2000 to 2005, just as I could do it in SQL Server 2000.
Can you make up a simple example to explain it. Just one simple database on
Sql 2000 with the simplest permissions, needs to be transferred to 2005.
If you can actually help me, I will be really happy, and will send you a
nice box of really good chocolates
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 3/25/2007
Time: 5:31:14 PM
User: CAAA3\sqladmin999
Computer: CAAA3
Description:
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query
"EXEC dbo.sp_changedbowner @.loginame = N'BAAA2\Administrator', @.map = false
" failed with the following error: "Cannot find the principal
'BAAA2\Administrator', because it does not exist or you do not have
permission.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
You're probably better off doing a full backup then restore to the new
machine.

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Johan Garst" wrote:
> Hi,
> I can't Copy a database from Sql Server 2000 to 2005. I am using the Copy
> Database option from Management menu. I want to transfer all tables, and
> all
> stored procedures. I've tried everything for the past 3 days, and it
> didn't
> work. I always get the same error.
> Below is the error that I am getting. Can someone please show/tell me a
> simple permission configuration on both servers that would actually work
> when I try to do the Copy Database. This would restore my attitude towards
> migrating to SQL Server 2005. I don't want to have to write any scripts or
> programs for that either.
> I've searched all over the internet for a similar problems answered, but
> couldn't find anything useful. Can you please help, because I am ready to
> toss that SQL Server 2005 into a dumpster, and stay with SQL Server 2000.
> I
> am sure that there are hundreds of users like myself with the same
> problem.
> Can someone please answer it really well once and for all finally? I don't
> want to do any Detach or backup/restore methods, or write scripts (once
> again). I just want to be able to use COPY database option in the
> Management
> menu (while the database is running) to transfer this database from SQL
> server 2000 to 2005, just as I could do it in SQL Server 2000.
> Can you make up a simple example to explain it. Just one simple database
> on
> Sql 2000 with the simplest permissions, needs to be transferred to 2005.
> If you can actually help me, I will be really happy, and will send you a
> nice box of really good chocolates
> Event Type: Error
> Event Source: SQLISPackage
> Event Category: None
> Event ID: 12550
> Date: 3/25/2007
> Time: 5:31:14 PM
> User: CAAA3\sqladmin999
> Computer: CAAA3
> Description:
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query
> "EXEC dbo.sp_changedbowner @.loginame = N'BAAA2\Administrator', @.map =
> false
> " failed with the following error: "Cannot find the principal
> 'BAAA2\Administrator', because it does not exist or you do not have
> permission.". Possible failure reasons: Problems with the query,
> "ResultSet"
> property not set correctly, parameters not set correctly, or connection
> not
> established correctly.
>
>
|||Johan Garst (Johangarst343@.yahoo.com) writes:
> I can't Copy a database from Sql Server 2000 to 2005. I am using the
> Copy Database option from Management menu. I want to transfer all
> tables, and all stored procedures. I've tried everything for the past 3
> days, and it didn't work. I always get the same error.
Are you using the Attach/Detach option, or the SMO method? The latter is
completely hopeless. I think they fixed a couple of bugs in SP2, but I
still don't find it realiable, and I question that it should have ever
been included in the final release of SQL 2005.

>Description:
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query
>"EXEC dbo.sp_changedbowner @.loginame = N'BAAA2\Administrator', @.map = false
>" failed with the following error: "Cannot find the principal
>'BAAA2\Administrator', because it does not exist or you do not have
>permission.".
You *may* get past this if you first do
sp_changedbowner 'sa'
before you expoer that database, but no promises.
Dave's suggestion that simple do a BACKUP/RESTORE is definitely worth
purusing. You will have to remap users with sp_change_users_login,
and change database owner to sa, and then to the real login, but
that is not a big deal.
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
|||I agree
of course, I don't think that SQL Server should have been released until
they fix the SQL Authentication problem OUT OF THE BOX
I always use attach / detach for everything, screw the copy database wizard
the punchline of course-- is that I had a windows network admin that just
REFUSED to use this methodology once upon a time... I just have basically
never had attach / detach fail on me
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99033F7E836AYazorman@.127.0.0.1...[vbcol=seagreen]
> Johan Garst (Johangarst343@.yahoo.com) writes:
> Are you using the Attach/Detach option, or the SMO method? The latter is
> completely hopeless. I think they fixed a couple of bugs in SP2, but I
> still don't find it realiable, and I question that it should have ever
> been included in the final release of SQL 2005.
false
> You *may* get past this if you first do
> sp_changedbowner 'sa'
> before you expoer that database, but no promises.
> Dave's suggestion that simple do a BACKUP/RESTORE is definitely worth
> purusing. You will have to remap users with sp_change_users_login,
> and change database owner to sa, and then to the real login, but
> that is not a big deal.
>
> --
> 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
sql

I can't Copy a database from Sql Server 2000 to 2005

Hi,
I can't Copy a database from Sql Server 2000 to 2005. I am using the Copy
Database option from Management menu. I want to transfer all tables, and all
stored procedures. I've tried everything for the past 3 days, and it didn't
work. I always get the same error.
Below is the error that I am getting. Can someone please show/tell me a
simple permission configuration on both servers that would actually work
when I try to do the Copy Database. This would restore my attitude towards
migrating to SQL Server 2005. I don't want to have to write any scripts or
programs for that either.
I've searched all over the internet for a similar problems answered, but
couldn't find anything useful. Can you please help, because I am ready to
toss that SQL Server 2005 into a dumpster, and stay with SQL Server 2000. I
am sure that there are hundreds of users like myself with the same problem.
Can someone please answer it really well once and for all finally? I don't
want to do any Detach or backup/restore methods, or write scripts (once
again). I just want to be able to use COPY database option in the Management
menu (while the database is running) to transfer this database from SQL
server 2000 to 2005, just as I could do it in SQL Server 2000.
Can you make up a simple example to explain it. Just one simple database on
Sql 2000 with the simplest permissions, needs to be transferred to 2005.
If you can actually help me, I will be really happy, and will send you a
nice box of really good chocolates
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 3/25/2007
Time: 5:31:14 PM
User: CAAA3\sqladmin999
Computer: CAAA3
Description:
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query
"EXEC dbo.sp_changedbowner @.loginame = N'BAAA2\Administrator', @.map = false
" failed with the following error: "Cannot find the principal
'BAAA2\Administrator', because it does not exist or you do not have
permission.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
Answered in another NG
|||That approach has never worked and probably never will, except on rare
occasions I have never met, although I've heard rumours from others that
there are such cases. The issue is not related to a 2000 -> 2005 migration.
Solution: Backup, transfer the backup file, Restore.
"Johan Garst" <Johangarst343@.yahoo.com> wrote in message
news:xp-dndzKguz9JpTbnZ2dnUVZ_tvinZ2d@.comcast.com...
> Hi,
> I can't Copy a database from Sql Server 2000 to 2005. I am using the Copy
> Database option from Management menu. I want to transfer all tables, and
> all
> stored procedures. I've tried everything for the past 3 days, and it
> didn't
> work. I always get the same error.
> Below is the error that I am getting. Can someone please show/tell me a
> simple permission configuration on both servers that would actually work
> when I try to do the Copy Database. This would restore my attitude towards
> migrating to SQL Server 2005. I don't want to have to write any scripts or
> programs for that either.
> I've searched all over the internet for a similar problems answered, but
> couldn't find anything useful. Can you please help, because I am ready to
> toss that SQL Server 2005 into a dumpster, and stay with SQL Server 2000.
> I
> am sure that there are hundreds of users like myself with the same
> problem.
> Can someone please answer it really well once and for all finally? I don't
> want to do any Detach or backup/restore methods, or write scripts (once
> again). I just want to be able to use COPY database option in the
> Management
> menu (while the database is running) to transfer this database from SQL
> server 2000 to 2005, just as I could do it in SQL Server 2000.
> Can you make up a simple example to explain it. Just one simple database
> on
> Sql 2000 with the simplest permissions, needs to be transferred to 2005.
> If you can actually help me, I will be really happy, and will send you a
> nice box of really good chocolates
> Event Type: Error
> Event Source: SQLISPackage
> Event Category: None
> Event ID: 12550
> Date: 3/25/2007
> Time: 5:31:14 PM
> User: CAAA3\sqladmin999
> Computer: CAAA3
> Description:
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query
> "EXEC dbo.sp_changedbowner @.loginame = N'BAAA2\Administrator', @.map =
> false
> " failed with the following error: "Cannot find the principal
> 'BAAA2\Administrator', because it does not exist or you do not have
> permission.". Possible failure reasons: Problems with the query,
> "ResultSet"
> property not set correctly, parameters not set correctly, or connection
> not
> established correctly.
>
>

Wednesday, March 21, 2012

I can't create a table in TEMPDB

I have a USER wich can Create tables in the production DB, but this USER
can't create tables in TEMPDB.
? What's grong ?Does the user have access to TEMPDB database?
"Eduardo6973" <Eduardo6973@.discussions.microsoft.com> wrote in message
news:DC6F4810-17F5-4E3E-A59B-EAE30255E02F@.microsoft.com...
>I have a USER wich can Create tables in the production DB, but this USER
> can't create tables in TEMPDB.
> What's grong ?|||It's unusual to create non-temporary tables in tempdb. Can you elaborate on
why you need to do this? Perhaps there is an alternate approach.
All users have permissions to create local or global temp tables in tempdb
but CREATE TABLE permission is needed to create 'permanent' tables.
Non-sysadmin role members access tempdb under the guest user security
context so you could grant CREATE TABLE to guest so that non-sysadmin users
can create permanent tables owned by guest in tempdb. Alternatively, you
could add users to tempdb and then grant CREATE TABLE permissions to those
users. However, note that tempdb is recreated at instance startup so all
users, permissions and objects created in tempdb will lost. You'll need to
either reapply those permissions at startup or add the permissions to the
model database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eduardo6973" <Eduardo6973@.discussions.microsoft.com> wrote in message
news:DC6F4810-17F5-4E3E-A59B-EAE30255E02F@.microsoft.com...
>I have a USER wich can Create tables in the production DB, but this USER
> can't create tables in TEMPDB.
> What's grong ?

I Cant "show sql query" - Crystal Report 8.5

Hey Guys, all fine ?

I created a report and using 3 tables( or more) with joins, group and etc. And this report calls a subreport. Well, the report is perfect, no problems! But, i can't see your sql definition(In crystal report, menu database,Show sql query) and, in Visual Basic i need to set a similar sql of report, only changing the where clause, by filter controls in VB.

In old times, i have used crystal report 8, and can show sql query.

In VB, how i can input a sql string in report, after execute it ?

How i can show sql query in CR?

Please, i need this help with urgency! The rope is in my neck! =(]

Thanks a lot!"Show SQL Query" option is very much present in crystal reports 8.5, if you don't have the option, then you try re-installing crystal reports.

i cannot solve it

i have 2 tables.the first contains lastname,phone1,adress,etc...the second contains firstname,phone1. I want to insert the firstname from the second table into the first table where phone1 one is identical.
thank you..Do you want to insert a new row or update an existing row?|||Originally posted by r123456
Do you want to insert a new row or update an existing row?
thank you for your reply
i want to update the row.
Your statements add and unfortunatelly dont make all the comparisons,though the compared fields are of the same data type|||update table2
set firstname =
(select firstname
from table1 t1
where key = t1.key);|||Originally posted by r123456
update table2
set firstname =
(select firstname
from table1 t1
where key = t1.key);
ANALYSER'S MSG
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

thank you|||You need to join the tables on a candidate key. If you replaced key with 'phone' then the query is returning multiple phone numbers meaning a phone number is not unique. Join on the primary key as this will ensure only a single row is returned.|||Originally posted by r123456
You need to join the tables on a candidate key. If you replaced key with 'phone' then the query is returning multiple phone numbers meaning a phone number is not unique. Join on the primary key as this will ensure only a single row is returned.

i set [phone] as primary key and is accepted.
Although the querry doesnt write all the fields i selected in my table
.... it seems it doesnt make the comparison.

thank you.|||Select *
from table1 t1
INNER JOIN
table2 t2 ON
t1.phone = t2.phone;

If this does not return macthing rows then the data types of phone in both tables are not identical;|||Originally posted by r123456
Select *
from table1 t1
INNER JOIN
table2 t2 ON
t1.phone = t2.phone;

If this does not return macthing rows then the data types of phone in both tables are not identical;

thanks a lot for your help

Monday, March 19, 2012

I can not see my Tables !

Hello,

I am using " SqlDataSource " and I can not see Talbles of memberships ! I can not only see View or see manule tables !!!

for this when I am using any tool I can not use edit or add fetures :(

Where is problem ?

SqlDataSource is used to retrieve data from database and then be used as datasource of some other control such as GridView. Here is an example:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource(d=ide).aspx

To see database objects in VWD, you can open Server Explorer and add connection to some database on the server.

Monday, March 12, 2012

I am scripting drop PRIMARY KEY script, but got DROP TABLE script

I am scripting the drop PRIMARY KEY script for all tables. But the script I got was DROP TABLE. Script options are as following:

so.DriUniqueKeys = false;

so.DriDefaults = false;

so.DriChecks = false;

so.Indexes = false;

so.DriForeignKeys = false;

so.PrimaryObject = false;

so.DriPrimaryKey = true;

so.IncludeIfNotExists = true;

so.ScriptDrops = true;

The create PRIMARY KEY script is right if I change so.ScriptDrops to false.

Am i doing something wrong or is it a bug? How to script out drop PK script?

hard to tell without the rest of your script, but what about that one here:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table t in s.Databases["Adventureworks"].Tables)

{

foreach (Index i in t.Indexes)

{

if (i.IndexKeyType == IndexKeyType.DriPrimaryKey)

{

i.Drop();

break;

}

}

}

s.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

Console.ReadLine();

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens, It works great.

Based on my test, the DriPrimaryKey=true only works for create script for tables. DriPrimaryObject=false only works for create script for tables too. For drop script,smo always generates drop table script. That's why I got drop table script when i tried to generate drop PK script (DriPrimaryKey=true and

DriPrimaryObject=false ).

The BOL lacks details about the scripting options.

Friday, March 9, 2012

I am new to data warehousing and have a few initial questions

1. Just to clarify a datamart is collection of dimension tables that related
to a particular fact table within the data warehouse? I understand that the
same dimension table can be used more than once e.g. the Time dimension.
2. I would like to use Analysis Services with Reporting Services to create
the cubes etc. However some people are keen to use Cognos Impromptu with
Powerplay to implement the cubes etc. Are there any advantages that this
combination offers over the Cognos alternative other than the cost?I typically think of a datamart as being department specific (such as
Accounting or Payroll) and a data warehouse as being a consolidation of
multiple datamarts across the enterprise.
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:A4C4B887-91B7-4C98-BB72-48F275AE21D3@.microsoft.com...
> 1. Just to clarify a datamart is collection of dimension tables that
related
> to a particular fact table within the data warehouse? I understand that
the
> same dimension table can be used more than once e.g. the Time dimension.
> 2. I would like to use Analysis Services with Reporting Services to create
> the cubes etc. However some people are keen to use Cognos Impromptu with
> Powerplay to implement the cubes etc. Are there any advantages that this
> combination offers over the Cognos alternative other than the cost?

I am new to data warehousing and have a few initial questions

1. Just to clarify a datamart is collection of dimension tables that related
to a particular fact table within the data warehouse? I understand that the
same dimension table can be used more than once e.g. the Time dimension.
2. I would like to use Analysis Services with Reporting Services to create
the cubes etc. However some people are keen to use Cognos Impromptu with
Powerplay to implement the cubes etc. Are there any advantages that this
combination offers over the Cognos alternative other than the cost?
I typically think of a datamart as being department specific (such as
Accounting or Payroll) and a data warehouse as being a consolidation of
multiple datamarts across the enterprise.
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:A4C4B887-91B7-4C98-BB72-48F275AE21D3@.microsoft.com...
> 1. Just to clarify a datamart is collection of dimension tables that
related
> to a particular fact table within the data warehouse? I understand that
the
> same dimension table can be used more than once e.g. the Time dimension.
> 2. I would like to use Analysis Services with Reporting Services to create
> the cubes etc. However some people are keen to use Cognos Impromptu with
> Powerplay to implement the cubes etc. Are there any advantages that this
> combination offers over the Cognos alternative other than the cost?

Wednesday, March 7, 2012

Hyperthreading killing performance of simple query?

We have a new server, 4 physical dual core processors, with hyperthreading,
so it shows up as 16 logical processors.
A simple query joining 3 tables, a distinct, an order by, with maxdop set to
0, takes over 2 seconds. With maxdop set to 1, it takes 200 milliseconds.
setting maxdop between 1 and 15, pretty linear time progression.
Does this sound sane? Is there some magic matrix where under load a higher
maxdop will result in this simple query running faster than where there is
no load (the current case)
Thanks in advance!
Derrick
Derrick,
It is true: Parallelism does not alway help. In fact, for some queries it
may make them perform abysmally.
Although I do not do this personally, several posters recommend setting an
OLTP server to a maximum degree of parallelism of 1. They indicate good
success and few discernable problems because of the setting.
I do follow the guideline to limit the server's maximum degree of
parallelism to the number of physical chips, which in your case would be 4.
Then, if a query is troubled we can specifically run it at MAXDOP 1.
FWIW,
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
> We have a new server, 4 physical dual core processors, with
> hyperthreading, so it shows up as 16 logical processors.
> A simple query joining 3 tables, a distinct, an order by, with maxdop set
> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
> milliseconds. setting maxdop between 1 and 15, pretty linear time
> progression.
> Does this sound sane? Is there some magic matrix where under load a
> higher maxdop will result in this simple query running faster than where
> there is no load (the current case)
> Thanks in advance!
> Derrick
>
|||Russell,
Thanks for the info, one more q, so would you reccomend hyperthreading be
enabled, or disabled?
We ran tests both ways and turning hyperthreading off does not seem the way
to go based on our results, does that match up with what you have seen/read?
Thanks again,
Derrick
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
> Derrick,
> It is true: Parallelism does not alway help. In fact, for some queries it
> may make them perform abysmally.
> Although I do not do this personally, several posters recommend setting an
> OLTP server to a maximum degree of parallelism of 1. They indicate good
> success and few discernable problems because of the setting.
> I do follow the guideline to limit the server's maximum degree of
> parallelism to the number of physical chips, which in your case would be
> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
> FWIW,
> RLF
> "Derrick" <derrick1298@.excite.com> wrote in message
> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>
|||Derrick,
Lara Rubbelke made these comments:
http://blogs.digineer.com/blogs/larar/archive/2006/11/13/maximum-degree-of-parallelism-and-hyperthreading.aspx
He links to Slava Ok's
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
Their bottom seems to be, do not enable hyperthreading unless you have
proved it will help you.
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1F0WU60HHA.464@.TK2MSFTNGP02.phx.gbl...
> Russell,
> Thanks for the info, one more q, so would you reccomend hyperthreading be
> enabled, or disabled?
> We ran tests both ways and turning hyperthreading off does not seem the
> way to go based on our results, does that match up with what you have
> seen/read?
> Thanks again,
> Derrick
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
>

Hyperthreading killing performance of simple query?

We have a new server, 4 physical dual core processors, with hyperthreading,
so it shows up as 16 logical processors.
A simple query joining 3 tables, a distinct, an order by, with maxdop set to
0, takes over 2 seconds. With maxdop set to 1, it takes 200 milliseconds.
setting maxdop between 1 and 15, pretty linear time progression.
Does this sound sane? Is there some magic matrix where under load a higher
maxdop will result in this simple query running faster than where there is
no load (the current case)
Thanks in advance!
DerrickDerrick,
It is true: Parallelism does not alway help. In fact, for some queries it
may make them perform abysmally.
Although I do not do this personally, several posters recommend setting an
OLTP server to a maximum degree of parallelism of 1. They indicate good
success and few discernable problems because of the setting.
I do follow the guideline to limit the server's maximum degree of
parallelism to the number of physical chips, which in your case would be 4.
Then, if a query is troubled we can specifically run it at MAXDOP 1.
FWIW,
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
> We have a new server, 4 physical dual core processors, with
> hyperthreading, so it shows up as 16 logical processors.
> A simple query joining 3 tables, a distinct, an order by, with maxdop set
> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
> milliseconds. setting maxdop between 1 and 15, pretty linear time
> progression.
> Does this sound sane? Is there some magic matrix where under load a
> higher maxdop will result in this simple query running faster than where
> there is no load (the current case)
> Thanks in advance!
> Derrick
>|||Russell,
Thanks for the info, one more q, so would you reccomend hyperthreading be
enabled, or disabled?
We ran tests both ways and turning hyperthreading off does not seem the way
to go based on our results, does that match up with what you have seen/read?
Thanks again,
Derrick
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
> Derrick,
> It is true: Parallelism does not alway help. In fact, for some queries it
> may make them perform abysmally.
> Although I do not do this personally, several posters recommend setting an
> OLTP server to a maximum degree of parallelism of 1. They indicate good
> success and few discernable problems because of the setting.
> I do follow the guideline to limit the server's maximum degree of
> parallelism to the number of physical chips, which in your case would be
> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
> FWIW,
> RLF
> "Derrick" <derrick1298@.excite.com> wrote in message
> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>> We have a new server, 4 physical dual core processors, with
>> hyperthreading, so it shows up as 16 logical processors.
>> A simple query joining 3 tables, a distinct, an order by, with maxdop set
>> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
>> milliseconds. setting maxdop between 1 and 15, pretty linear time
>> progression.
>> Does this sound sane? Is there some magic matrix where under load a
>> higher maxdop will result in this simple query running faster than where
>> there is no load (the current case)
>> Thanks in advance!
>> Derrick
>|||Derrick,
Lara Rubbelke made these comments:
http://blogs.digineer.com/blogs/larar/archive/2006/11/13/maximum-degree-of-parallelism-and-hyperthreading.aspx
He links to Slava Ok's
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
Their bottom seems to be, do not enable hyperthreading unless you have
proved it will help you.
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1F0WU60HHA.464@.TK2MSFTNGP02.phx.gbl...
> Russell,
> Thanks for the info, one more q, so would you reccomend hyperthreading be
> enabled, or disabled?
> We ran tests both ways and turning hyperthreading off does not seem the
> way to go based on our results, does that match up with what you have
> seen/read?
> Thanks again,
> Derrick
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
>> Derrick,
>> It is true: Parallelism does not alway help. In fact, for some queries it
>> may make them perform abysmally.
>> Although I do not do this personally, several posters recommend setting
>> an OLTP server to a maximum degree of parallelism of 1. They indicate
>> good success and few discernable problems because of the setting.
>> I do follow the guideline to limit the server's maximum degree of
>> parallelism to the number of physical chips, which in your case would be
>> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
>> FWIW,
>> RLF
>> "Derrick" <derrick1298@.excite.com> wrote in message
>> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>> We have a new server, 4 physical dual core processors, with
>> hyperthreading, so it shows up as 16 logical processors.
>> A simple query joining 3 tables, a distinct, an order by, with maxdop
>> set to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
>> milliseconds. setting maxdop between 1 and 15, pretty linear time
>> progression.
>> Does this sound sane? Is there some magic matrix where under load a
>> higher maxdop will result in this simple query running faster than where
>> there is no load (the current case)
>> Thanks in advance!
>> Derrick
>>
>

Hyperthreading killing performance of simple query?

We have a new server, 4 physical dual core processors, with hyperthreading,
so it shows up as 16 logical processors.
A simple query joining 3 tables, a distinct, an order by, with maxdop set to
0, takes over 2 seconds. With maxdop set to 1, it takes 200 milliseconds.
setting maxdop between 1 and 15, pretty linear time progression.
Does this sound sane? Is there some magic matrix where under load a higher
maxdop will result in this simple query running faster than where there is
no load (the current case)
Thanks in advance!
DerrickDerrick,
It is true: Parallelism does not alway help. In fact, for some queries it
may make them perform abysmally.
Although I do not do this personally, several posters recommend setting an
OLTP server to a maximum degree of parallelism of 1. They indicate good
success and few discernable problems because of the setting.
I do follow the guideline to limit the server's maximum degree of
parallelism to the number of physical chips, which in your case would be 4.
Then, if a query is troubled we can specifically run it at MAXDOP 1.
FWIW,
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
> We have a new server, 4 physical dual core processors, with
> hyperthreading, so it shows up as 16 logical processors.
> A simple query joining 3 tables, a distinct, an order by, with maxdop set
> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
> milliseconds. setting maxdop between 1 and 15, pretty linear time
> progression.
> Does this sound sane? Is there some magic matrix where under load a
> higher maxdop will result in this simple query running faster than where
> there is no load (the current case)
> Thanks in advance!
> Derrick
>|||Russell,
Thanks for the info, one more q, so would you reccomend hyperthreading be
enabled, or disabled?
We ran tests both ways and turning hyperthreading off does not seem the way
to go based on our results, does that match up with what you have seen/read?
Thanks again,
Derrick
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
> Derrick,
> It is true: Parallelism does not alway help. In fact, for some queries it
> may make them perform abysmally.
> Although I do not do this personally, several posters recommend setting an
> OLTP server to a maximum degree of parallelism of 1. They indicate good
> success and few discernable problems because of the setting.
> I do follow the guideline to limit the server's maximum degree of
> parallelism to the number of physical chips, which in your case would be
> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
> FWIW,
> RLF
> "Derrick" <derrick1298@.excite.com> wrote in message
> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>|||Derrick,
Lara Rubbelke made these comments:
http://blogs.digineer.com/blogs/lar...rthreading.aspx
He links to Slava Ok's
http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
Their bottom seems to be, do not enable hyperthreading unless you have
proved it will help you.
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1F0WU60HHA.464@.TK2MSFTNGP02.phx.gbl...
> Russell,
> Thanks for the info, one more q, so would you reccomend hyperthreading be
> enabled, or disabled?
> We ran tests both ways and turning hyperthreading off does not seem the
> way to go based on our results, does that match up with what you have
> seen/read?
> Thanks again,
> Derrick
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
>

Sunday, February 19, 2012

Hyperlink

I have another question about my Access/SqL Database. one of my tables is going to have 2 or 3 hyperlinks and I was wondering can that be done through sql or would I be better off doing it through access?? (the database consist of sQL tables but the gui for the users is access)
thank youI have another question about my Access/SqL Database. one of my tables is going to have 2 or 3 hyperlinks and I was wondering can that be done through sql or would I be better off doing it through access?? (the database consist of sQL tables but the gui for the users is access)

thank you

I'd like to help, but I have no idea what you mean?

a tables has hyperlinks? Like a column to hold data?

OK so....what's the question?|||I'm sorry I didnt explain that very well. What I meant was in access one of the datatypes can be a Hyperlink. Is that possible in SQL to make a datatype a hyperlink, would that possibly be the Image (though I doubt it) datatype I'm seeing in sQL?

thank you Brett :)|||Nope, sorry!

The "hyperlink-ness" of a piece of text is determined by an agreement between the web-server/client, not by the SQL Server. You'll have to make that majik appear after the data has left the SQL server.

-PatP|||I had a feeling you were going to say that, Ok well plan be is that I can create a hyperlink in access through a form.

Thanks anyways Guys|||That is purely an interface issue. Since Access combines a database engine with a user interface, it is not surprising that it has this functionality. SQL Server is NOT an interface.

Store your data as a string, because that is essentially what Access is doing. It just also makes a mental note for the interface to treat is like a hyperlink.