Showing posts with label below. Show all posts
Showing posts with label below. Show all posts

Friday, March 30, 2012

I encountered problem after .NET framework upgraded to 2.0 in the server

I posted the question below to this website 6 hours ago, the subject was "SQL server registration failed". I couldn't find this question just now. This is my first time to post a question to microsoft website. Please let me know the related website if the question shouldn't be posted here.(I think it 's related to this site)
URGENT, I need help, thanks.

I installed .Net framework 2.0 in a Window 2003 Server and VS2005 in my PC a few days ago (.NET framework 1.1 is still in the server and VS.NET2003 is still in my PC), SQL server 2000 is with web server together. there is no problem to run ASP.net 2.0 application, the application also can query the database in SQL server.
But I encounter a problem that I can't create new SQL server registration from my PC to the above server. the error msg is "SQL Server registration failed. SQL Server does not exist or access denied ConnectionOpen(Connect())"
There is no problem to connect SQL server remotely before I upgraded .net framework to 2.0 and VS.NET2003 to VS2005.
I checked the SQL service (started), TCP/IP(enabled). I also installed SQL server SP4 in the server, but I still encounter the same problem.
Anybody can help? Thanks


moving to SQL forum.

|||I guess the firewall on the Windows 2003 server doesn′t allow incoming communication on port 1433. Change that and as a stateful firewall Windows will open the outgoing ports for communication.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Wednesday, March 28, 2012

I don't understand how this works, however it works (sometimes)

I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS( sections work however usualy they do, in this case it does not andt I can't seem to find out why.

Does anyone see the error?

Thanks

--=========================================
--Create a local temporary table that hold
--all the data from the source table
--=========================================

SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC

--=========================================
--Remove table entries that are no longer
--needed or that have to be updated
--=========================================

DELETE FROM LTTSTOCK

WHERE NOT EXISTS( SELECT * FROM #TEMP

WHERE LTTSTOCK.WarehouseNo = LTWHLO

AND LTTSTOCK.Location = LTWHSL

AND LTTSTOCK.ItemNo = LTITNO

AND LTTSTOCK.NumberAvail = LTAVAL

)

--=========================================

--Insert data that is missing or that

--needed to be updated and was previously

--deleted

--=========================================

INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)

SELECT DISTINCT LTWHLO,LTWHSL,LTITNO,LTAVAL, NEWID()

FROM #TEMP

WHERE NOT EXISTS( SELECT * FROM LTTSTOCK

WHERE WarehouseNo = LTWHLO

AND Location = LTWHSL

AND ItemNo = LTITNO

AND NumberAvail = LTAVAL

)

--========================================

--Remove local temporary table.

--========================================

DROP TABLE #TEMP

Walter:

You have the delete statement:

DELETE FROM LTTSTOCK
WHERE NOT EXISTS( SELECT * FROM #TEMP
WHERE LTTSTOCK.WarehouseNo = LTWHLO
AND LTTSTOCK.Location = LTWHSL
AND LTTSTOCK.ItemNo = LTITNO
AND LTTSTOCK.NumberAvail = LTAVAL
)

Does the the combination of (1) ItemNo, (2) WarehouseNo, and (3) Location guarantee uniqueness in your table?

Dave

|||

Hi Dave

Yes this is the case

Walter

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%

Monday, March 12, 2012

I bet this is simple, -But see if you can help. Hard to come up with Title -Have a lo

Hi,
What I would like to be able to do in simple SQL is to group on a term
that has come from a longer string. As below.
I begin with data in this format.
PARTNO DESC
12345-01 Capacitor C1 requires resoldering
12345-01 Capcitor broken, replace C1
12345-01 Solder Quality IC U1
12345-01 U1 Misaligned
25698-05 R115 Cracked
25698-05 Dry solder joing R115
And would like it to appear like this.
PARTNO DESC Count
12345-01 C1 2
12345-01 U1 2
25698-05 R115 2
If you could help that would be awesome. I also have to find the faults
in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
Many Thanksphilipbennett25 wrote:
> Hi,
> What I would like to be able to do in simple SQL is to group on a term
> that has come from a longer string. As below.
> I begin with data in this format.
> PARTNO DESC
> 12345-01 Capacitor C1 requires resoldering
> 12345-01 Capcitor broken, replace C1
> 12345-01 Solder Quality IC U1
> 12345-01 U1 Misaligned
> 25698-05 R115 Cracked
> 25698-05 Dry solder joing R115
> And would like it to appear like this.
> PARTNO DESC Count
> 12345-01 C1 2
> 12345-01 U1 2
> 25698-05 R115 2
>
> If you could help that would be awesome. I also have to find the faults
> in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
> Many Thanks
It would help if yuo could create a table of the descriptsion first:
CREATE TABLE part_descriptions (descr VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO part_descriptions (descr)
SELECT 'C1' UNION ALL
SELECT 'U1' UNION ALL
SELECT 'R115' ;
Then try this:
SELECT T.partno, P.descr, COUNT(*) AS cnt
FROM tbl AS T
JOIN part_descriptions AS P
ON T.descr LIKE '%'+P.descr+'%'
WHERE P.descr LIKE '%'
GROUP BY T.partno, P.descr ;
You could also make use of PATINDEX:
DECLARE @.p VARCHAR(20)
SET @.p = 'C1'
SELECT partno, descr, COUNT(*) AS cnt
FROM
(SELECT partno, SUBSTRING(descr,PATINDEX('%'+@.p+'%',desc
r),LEN(@.p))
FROM tbl
WHERE descr LIKE '%'+@.p+'%') AS T(partno,descr)
GROUP BY partno, descr ;
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL,
col VARCHAR(50) NOT NULL
)
INSERT INTO #Test VALUES ('20050101','Hello world C1')
INSERT INTO #Test VALUES ('20050101','Hello C1 world ')
INSERT INTO #Test VALUES ('20050201','M favorite is D1')
INSERT INTO #Test VALUES ('20050201','D1 Is my Favorite ')
SELECT dt,descr,COUNT(*)
FROM
(
SELECT dt,CASE WHEN col LIKE '%c1%' THEN 'c1'
ELSE CASE WHEN col LIKE '%d1%'THEN 'd1' END END descr
FROM #Test
) AS D GROUP BY dt,descr
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1133779185.731612.3100@.g49g2000cwa.googlegroups.com...
> Hi,
> What I would like to be able to do in simple SQL is to group on a term
> that has come from a longer string. As below.
> I begin with data in this format.
> PARTNO DESC
> 12345-01 Capacitor C1 requires resoldering
> 12345-01 Capcitor broken, replace C1
> 12345-01 Solder Quality IC U1
> 12345-01 U1 Misaligned
> 25698-05 R115 Cracked
> 25698-05 Dry solder joing R115
> And would like it to appear like this.
> PARTNO DESC Count
> 12345-01 C1 2
> 12345-01 U1 2
> 25698-05 R115 2
>
> If you could help that would be awesome. I also have to find the faults
> in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
> Many Thanks
>|||Hey guys, thanks for your help on this. The Database I am querying is
not a SQL Server therefore it has to be done using basic SQL. It also
needs to be able to grow and shrink depending on what it Finds in the
DESC Field. I will be searching for the keywords in the DESC column by
looking for patterns, not having specific keywords already defined. IE
I would just have "Like 'C_' ".
Many Thanks

Friday, March 9, 2012

I am getting an error in creating a simple store procedure!

Hi ,
Here is my stored proce and I am not sure why its complaining about
the below error
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_InsertUserInfo]
@.aspnet_userid uniqueidentifier(50)
,@.DealershipID int
,@.LastName varchar(50)
,@.FirstName varchar(50)
,@.AddressLine1 varchar(50)
,@.AddressLine2 varchar(50)
,@.City varchar(50)
,@.State varchar(50)
,@.Zip varchar(50)
,@.Country varchar(50)
,@.HomePhoneNumber varchar(50)
,@.MobileNumber varchar(50)
,@.OfficePhoneNumbre varchar(50)
,@.OfficePhoneExtension varchar(5)
,@.EmailAddress varchar(50)
,@.PreferredCommunication varchar(50)
,@.InsertedBy uniqueidentifier
,@.InsertedDate datetime
,@.ModifiedBy uniqueidentifier
,@.ModifiedDate datetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [GroundingDemo].[dbo].[Users]
(aspnet_userid
,DealershipID
,LastName
,FirstName
,AddressLine1
,AddressLine2
,City
,State
,Zip
,Country
,HomePhoneNumber
,MobileNumber
,OfficePhoneNumbre
,OfficePhoneExtension
,EmailAddress
,PreferredCommunication
,InsertedBy
,InsertedDate
,ModifiedBy
,ModifiedDate)
VALUES
(aspnet_userid
,DealershipID
,LastName
,FirstName
,AddressLine1
,AddressLine2
,City
,State
,Zip
,Country
,HomePhoneNumber
,MobileNumber
,OfficePhoneNumbre
,OfficePhoneExtension
,EmailAddress
,PreferredCommunication
,InsertedBy
,InsertedDate
,ModifiedBy
,ModifiedDate)
END
GO
*************************************her
e is the error
***********************************
Msg 128, Level 15, State 1, Procedure USP_InsertUserInfo, Line 63
The name "aspnet_userid" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.
****************************************
***********************
if I take off the aspnet_userid column from the stored proce and also
the table and re execute it then complains about DealerShipID with the
same kind of error.
Am I missing some thing here?
Thanks
-Lyou are missing a comma after @.aspnet_userid uniqueidentifier(50)
and also
VALUES
(aspnet_userid
,DealershipID
has to be
VALUES
(@.aspnet_userid, --comma here also
,@.DealershipID etc etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi,
You are using the procedure's parameters in the values list, but forgot to
prefix them with the "@.". Like this:
VALUES
(@.aspnet_userid
,@.DealershipID
etc.
hth,
Dean
"Learner" <pradev@.gmail.com> wrote in message
news:1145648788.592029.198590@.g10g2000cwb.googlegroups.com...
> Hi ,
> Here is my stored proce and I am not sure why its complaining about
> the below error
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[USP_InsertUserInfo]
> @.aspnet_userid uniqueidentifier(50)
> ,@.DealershipID int
> ,@.LastName varchar(50)
> ,@.FirstName varchar(50)
> ,@.AddressLine1 varchar(50)
> ,@.AddressLine2 varchar(50)
> ,@.City varchar(50)
> ,@.State varchar(50)
> ,@.Zip varchar(50)
> ,@.Country varchar(50)
> ,@.HomePhoneNumber varchar(50)
> ,@.MobileNumber varchar(50)
> ,@.OfficePhoneNumbre varchar(50)
> ,@.OfficePhoneExtension varchar(5)
> ,@.EmailAddress varchar(50)
> ,@.PreferredCommunication varchar(50)
> ,@.InsertedBy uniqueidentifier
> ,@.InsertedDate datetime
> ,@.ModifiedBy uniqueidentifier
> ,@.ModifiedDate datetime
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO [GroundingDemo].[dbo].[Users]
> (aspnet_userid
> ,DealershipID
> ,LastName
> ,FirstName
> ,AddressLine1
> ,AddressLine2
> ,City
> ,State
> ,Zip
> ,Country
> ,HomePhoneNumber
> ,MobileNumber
> ,OfficePhoneNumbre
> ,OfficePhoneExtension
> ,EmailAddress
> ,PreferredCommunication
> ,InsertedBy
> ,InsertedDate
> ,ModifiedBy
> ,ModifiedDate)
> VALUES
> (aspnet_userid
> ,DealershipID
> ,LastName
> ,FirstName
> ,AddressLine1
> ,AddressLine2
> ,City
> ,State
> ,Zip
> ,Country
> ,HomePhoneNumber
> ,MobileNumber
> ,OfficePhoneNumbre
> ,OfficePhoneExtension
> ,EmailAddress
> ,PreferredCommunication
> ,InsertedBy
> ,InsertedDate
> ,ModifiedBy
> ,ModifiedDate)
> END
> GO
> *************************************her
e is the error
> ***********************************
> Msg 128, Level 15, State 1, Procedure USP_InsertUserInfo, Line 63
> The name "aspnet_userid" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
> ****************************************
***********************
> if I take off the aspnet_userid column from the stored proce and also
> the table and re execute it then complains about DealerShipID with the
> same kind of error.
>
> Am I missing some thing here?
> Thanks
> -L
>|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649123.181217.315180@.u72g2000cwu.googlegroups.com...
> you are missing a comma after @.aspnet_userid uniqueidentifier(50)
Actually, he's not :) The comma *is* there, but not where one would normally
expect it to be.
Dean

> and also
> VALUES
> (aspnet_userid
> ,DealershipID
> has to be
> VALUES
> (@.aspnet_userid, --comma here also
> ,@.DealershipID etc etc etc
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||I am not sure if I am understanding it right... I have another stored
proc that works and it doesn't have commas after it . I mean I am
trying to do the same thing with my new one ...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_InsertNewVehicleFeed]
@.VIN char(17),
@.AccountNum varchar(14),
@.CustomerName varchar(50),
@.CoByerName varchar(50),
@.Owner varchar(15),
@.SubOwner varchar(15),
@.IssuingFSO varchar(10),
@.OwningFSO varchar(10),
@.DealerShipID int,
@.ReturnDate smallDateTime,
@.Odometer varchar(6),
@.Year int,
@.MakeID int,
@.ModelID int,
@.SubModelID int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO VehicleInformation
(VIN,
AccountNum,
CustomerName,
CoBuyerName,
Owner,
SubOwner,
IssuingFSO,
OwningFSO,
DealerShipID,
ReturnDate,
Odometer,
Year,
MakeID,
ModelID,
SubModelID)
VALUES
(@.VIN,
@.AccountNum,
@.CustomerName,
@.CoByerName,
@.Owner,
@.SubOwner,
@.IssuingFSO,
@.OwningFSO,
@.DealerShipID,
@.ReturnDate,
@.Odometer,
@.Year,
@.MakeID,
@.ModelID,
@.SubModelID)
END
IF (@.@.ERROR > 0)
Begin
Select 'Failure'
End
Else
Begin
Select 'Success'
End|||He Dean,
yes that was silly :) jezz I was so stupid abou it..
THANK YOU
-L|||yes thats right. I just put infront of the each parameter not after :)
I don't know when I started it but just doing it.

I added a column to a table, but, I can't see it.

I ran the code below that adds a column to an SQL table.

I refreshed Data Sources, but, the new column does not appear.

I refreshed Server Explorer and the new column does not appear.

I tried running the code again and I got:

"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."

so the column did actually get added, but, I can't see it.

Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Try

Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Patient.mdf;Integrated Security=True;User Instance=True")

Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"

Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)

sqlConnection.Open()

command.ExecuteNonQuery()

sqlConnection.Close()

'Catch

'End Try

Close()

End Sub

End Class

here it runs in query analyzer ...

u do try it..

it must work ..

|||YOu are using an user instance. Data Files of user instances in during development time will be overwritten everytime you start the application from Visual Studio if the setting of "Copy Always" is set to true. Disable this option in order to leave the change in the file.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I had everything set to Do Not Copy.|||If not error tis thrown, the column was created, try to select the information from the INFORMATION_SCHEMA.COLUMNS View after executing your code to create a new column, you will see that the column was added to the table. There has to be a misconfiguration of the deployment where the debug process always copy a new datafile to the directory.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Wednesday, March 7, 2012

HyperThreading and NT Fibers

We've got a customer whose system is performing below average. Example, a billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000, 2-1.5GHz, 2GB RAM, RAID5 array
, SQL 2000 SP3). We turned on the performance monitors recently and watched a bill run. The hard drives and RAM were barely used while the 4 processor instances (2 physical = 4 logical with hyperthread) were all consistently above 90%.
We deduced that the processors were the obvious bottleneck but we are unsure why a less powerful machine (my laptop) out-performs the more powerful server. The only thing I can think is that the hyper-threading or NT Fiber setting is adversely affecting t
he processors.
Has anyone had any problems with hyperthreading or NT fibers or have any ideas as to where I might start looking?
Thanks in advance,
Dean
Does the calculation use the same query plan on both systems?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>
|||You can't compare these two systems side by side. For instance your laptop
has a faster processor (even though it only has one) and most likely has
more level 1 cache. The disk in the laptop probably has more cache as well
which can make a big difference over a Raid 5. That said you can still be
running into a multi-processor issue but it is hard to say for sure. Try
running the process with MAXDOP set to 1 and see if that makes a difference.
Are you sure the disk queues are fine? Is there just one RAID 5 and is
everything (data, tempdb, logs) on the same array?
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>
|||I expect so. On my laptop I just took a copy of their database and re-ran the process.
|||We monitored the server with performance counters on the processors, RAM and drives. Yes, it is one big RAID partition but the monitors showed almost no activity on the disks and very little on the RAM while the processors were very high.
|||Look at the estimated query execution plan on both systems. Also, check the
disk queue length on our data drives. A long queue length may indicate a
bottleneck at the hardware layer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:601177D1-D700-441B-9607-A15EA29855B4@.microsoft.com...
> I expect so. On my laptop I just took a copy of their database and re-ran
the process.
|||I just resolved a performance issue related to the fact that the execution plan was different between my development & production.
It was directly related to differences in hardware, specifically the environments you describe. I wouldn't assume that the execution
plans are the same. I'd run an execution plan in both environments to confirm.
If you find that the execution plans are different (and parallelism is involved), I would suggest looking up the query hint OPTION
(MAXDOP 1) in BOL.
ChrisG
|||I would still change the MAXDOP and see what happens.
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:827EF5AF-5FC0-43FD-BFD5-BD7D7A89C3D8@.microsoft.com...
> We monitored the server with performance counters on the processors, RAM
and drives. Yes, it is one big RAID partition but the monitors showed almost
no activity on the disks and very little on the RAM while the processors
were very high.
|||Exactly, I had this issue when we were doing the migration, taking half time when we turned that off. Is that a Dell machine?
Yes, you should turn that off.
|||I'll try it and see what happens...

HyperThreading and NT Fibers

We've got a customer whose system is performing below average. Example, a bi
lling calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB R
AM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000, 2-1
.5GHz, 2GB RAM, RAID5 array
, SQL 2000 SP3). We turned on the performance monitors recently and watched
a bill run. The hard drives and RAM were barely used while the 4 processor i
nstances (2 physical = 4 logical with hyperthread) were all consistently abo
ve 90%.
We deduced that the processors were the obvious bottleneck but we are unsure
why a less powerful machine (my laptop) out-performs the more powerful serv
er. The only thing I can think is that the hyper-threading or NT Fiber setti
ng is adversely affecting t
he processors.
Has anyone had any problems with hyperthreading or NT fibers or have any ide
as as to where I might start looking?
Thanks in advance,
DeanDoes the calculation use the same query plan on both systems?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>|||You can't compare these two systems side by side. For instance your laptop
has a faster processor (even though it only has one) and most likely has
more level 1 cache. The disk in the laptop probably has more cache as well
which can make a big difference over a Raid 5. That said you can still be
running into a multi-processor issue but it is hard to say for sure. Try
running the process with MAXDOP set to 1 and see if that makes a difference.
Are you sure the disk queues are fine? Is there just one RAID 5 and is
everything (data, tempdb, logs) on the same array?
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>|||I expect so. On my laptop I just took a copy of their database and re-ran th
e process.|||We monitored the server with performance counters on the processors, RAM and
drives. Yes, it is one big RAID partition but the monitors showed almost no
activity on the disks and very little on the RAM while the processors were
very high.|||Look at the estimated query execution plan on both systems. Also, check the
disk queue length on our data drives. A long queue length may indicate a
bottleneck at the hardware layer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:601177D1-D700-441B-9607-A15EA29855B4@.microsoft.com...
> I expect so. On my laptop I just took a copy of their database and re-ran
the process.|||I just resolved a performance issue related to the fact that the execution p
lan was different between my development & production.
It was directly related to differences in hardware, specifically the environ
ments you describe. I wouldn't assume that the execution
plans are the same. I'd run an execution plan in both environments to confir
m.
If you find that the execution plans are different (and parallelism is invol
ved), I would suggest looking up the query hint OPTION
(MAXDOP 1) in BOL.
ChrisG|||I would still change the MAXDOP and see what happens.
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:827EF5AF-5FC0-43FD-BFD5-BD7D7A89C3D8@.microsoft.com...
> We monitored the server with performance counters on the processors, RAM
and drives. Yes, it is one big RAID partition but the monitors showed almost
no activity on the disks and very little on the RAM while the processors
were very high.|||Exactly, I had this issue when we were doing the migration, taking half time
when we turned that off. Is that a Dell machine?
Yes, you should turn that off.|||I'll try it and see what happens...

Friday, February 24, 2012

Hyperlinks - Revisited...

Hi All,
I was able to get some hyperlinks to work within the context of a report
(from a header section to details below). However, it seems that these
hyperlinks within the report will only work when the report is accessed
online with the Report Server (through the Report Manager).
Is there a way to have the hyperlinks work in a report that is delivered via
an email subscription (i.e., the recipient's machine will not have live
access to the Report Server)?
Thanks in advance!
Kind regards - FredThis can be done using bookmark where ever you want your report to jump go to
the properties ->bookmark and enter a text for identification. and in the
link select Jump to Bookmark and select the text, this works even when you
export to different formats eg. excel etc...
Amarnath
"Fred Block" wrote:
> Hi All,
> I was able to get some hyperlinks to work within the context of a report
> (from a header section to details below). However, it seems that these
> hyperlinks within the report will only work when the report is accessed
> online with the Report Server (through the Report Manager).
> Is there a way to have the hyperlinks work in a report that is delivered via
> an email subscription (i.e., the recipient's machine will not have live
> access to the Report Server)?
> Thanks in advance!
> Kind regards - Fred
>
>|||Thanks Amarnath,
I'm going to try this out!
Kind regards - Fred