Fixing A Messy Database After The Fact...?

Jan 28, 2005

2 questions, actually:





I am new to database design and a lot of things never made any sense to me regarding relationships and such. I have been working on a very large design that started out well enough, but as tables were added a lot of organization fell by the wayside. Now that I am getting closer to the end, I am finding a lot of places where there should be Foreign keys, maybe some triggers, etc (I have the same data item in 5 different places, when it is deleted in one place it must go from all). Assuming that the datatypes and sizes are identical for the duplicated bits of data, can I go about making FK-PK relationships and such now that there is a lot of stuff in the database, or do I have to start from scratch and rebuild the whole thing.





The other question is much more simple:





How do I make multiple rows "unique". I have a primary key, and an identity column, but I can't add a secong primary key, and Enterprise Manager only lets me make 'int' datatypes identity columns. I have tried the "add constraints" but it asks for an expression and I have no idea what the syntax might be.





Any help is appreciated.

View 1 Replies


ADVERTISEMENT

Messy SQL

May 6, 2008

Hello,

I have a messy (and slow) piece of SQL that I'm sure can be written much more efficiently.

I have a table called parts:

Part(id, stopcode, s_num, a_num, tagnum, description)

The nature of the table is such that it is possible for two entries to share a tag number; when this is the case, it represents an 'in' part and an 'out' part. I would like to run a query which returns the in and out details in one row for parts. The id field is simply an autonumber and is unique for each entry.
Currently, I am pairing parts on their tag number using a join, so I have

Part p1, Part p2

I join them on the tagnum and select p1.s_num AS 's_num in' and p2.s_num AS 's_num out'. I also know how to tell that p1 is definitely an in part and that p2 is definitely an out part by their description, which will always be 'in' and 'out' respectively. So I have:

SELECT p1.s_num as 's_num in' p2.s_num as 's_num out' ... etc
FROM Part p1 JOIN Part p2 ON (p1.tagnum = p2.tagnum AND p1.description = 'in' AND p2.description = 'out')

This is great -- it will pair my in and out parts perfectly.

However, there isn't always an out part for an in part, meaning that I have a table filled with stuff to be paired and not-paired. I would like to display both of these in the result, with null values for any out value when there isn't a pair. So for this, I can do:

SELECT s_num as 's_num in', NULL as 's_num out' ... etc
FROM Part
WHERE description = 'in'

This will return ALL in parts, even the ones where there IS an associated out part for it. Consequently, when I union the two queries, I get an entry for the paired parts twice -- one where it thinks it just shows its in data (with out fields set to null), and one where it shows both the in and out, i.e., the correct result. I don't want the former.

To get round this, I have to use a NOT IN on the second query before I union it with the first. So, the final query looks like this:

SELECT p1.s_num as 's_num in' p2.s_num as 's_num out' ... etc
FROM Part p1 JOIN Part p2 ON (p1.tagnum = p2.tagnum AND p1.description = 'in' AND p2.description = 'out')

UNION

SELECT s_num as 's_num in', NULL as 's_num out' ... etc
FROM Part
WHERE description = 'in' AND NOT IN (
SELECT p1.id
FROM Part p1 JOIN Part p2 ON (p1.tagnum = p2.tagnum AND p1.description = 'in' AND p2.description = 'out')
)

As you can see, this is horribly messy and very inefficient.

I am not very competent with SQL (as you can see!), and I feel there must be a nicer way to accomplish what I'm trying to do.

If anyone can help me on this I would appreciate it (and if I haven't explained very well please do say).

I apologise if I have posted this in the wrong forum, too, and would ask that it could be moved accordingly if this is the case.

Thanks,

Simon

View 5 Replies View Related

This Seems Messy To Me... [OOP-related]

Nov 20, 2006

I've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";

SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231");
SqlCommand cmd = new SqlCommand(sql, myConn);
SqlDataReader dr;

myConn.Open();
dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.

View 8 Replies View Related

Graph Shows Crowded And Looks Messy

Mar 17, 2007

Hi

I am showing graph in my report as Column Chart .When my graph have more values it shows all values as crowded and look messy.

Is there is any way to restrict values on X axis up to limited number.

so that graph will be seen clearly.



Regards

Pankaj

View 4 Replies View Related

SQL Server 2012 :: Joining Dim To Fact Table Where Dim Table Key Exists In Multiple Fact Columns

Oct 26, 2015

Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?

USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;

[Code] ....

I'm using very small data at the moment, and the query plan and statistics don't really say which way.

View 2 Replies View Related

Copying Dimension And Fact Tables From One Database To Another...

Dec 17, 2007

Hi there, my question is really simple. I want to setup an automatic task in SSIS that drops the tables in the target database and substitutes them with tables from the source database. We are talking about two or three dimension tables and one fact table. The dimension tables are pretty small. The fact table will contain, at maximum, 300,000 rows and 12 columns. I do not use delta or flag historisation btw. What tasks in SSIS would you suggest to use?

BTW I'm new to SSIS... ;-) Thanks in advance!

View 9 Replies View Related

Please Help Me In Fixing This Bug...................!!!!!!!

Dec 7, 2007

 Generating user instances in SQL Server is disabled. Use sp_configure 'user
instances enabled' to generate user instances.  

View 1 Replies View Related

SP - Fixing

Aug 18, 2005

Any SQL guru's out there know a better way of writing this SP, its very slow.....CREATE PROCEDURE  SP_LIST_ACTIVITY_CLASS_BY_ENROLLMENT( @int_activity_class_Id INT,)
asDECLARE @ENROLL_COUNT INTDECLARE @WAITLIST_COUNT INTDECLARE @CONFIRM  INTDECLARE @PENDING   INTDECLARE @WITHDRAWN INTDECLARE @APPROVED INTDECLARE @DELETED   INT
SET @ENROLL_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id AND ENROLLMENT_STATUS_ID NOT IN (4,5,6))  --TAKES 5 SECONDSSET @WAITLIST_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 5) --TAKES 5 SECONDSSET @CONFIRM = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 3) --TAKES 5 SECONDSSET @PENDING = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 2) --TAKES 5 SECONDSSET @WITHDRAWN = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 4) --TAKES 5 SECONDSSET @APPROVED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id  AND ENROLLMENT_STATUS_ID = 1) --TAKES 5 SECONDSSET @DELETED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID =  @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 6) --TAKES 5 SECONDS
Select A.ACTIVITY_NAME,    A.DESCR,    C.CUSTOMER_ID,    C.CUSTOMER_NAME,    cast(A.PROVIDER_CODE as varchar)  + '-'  +  cast(FY.FISCAL_YY as varchar) + '-' + cast(AC.CEU_ACTIVITY_CODE as varchar)  + '-' + cast(isnull(ax.activity_seq_number,'XXX') as varchar) as    ACTIVITY_CODE, MIN(S.SCHEDULE_DATE) AS SCHEDULE_DATE, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.ECOMMERCE_IND, @ENROLL_COUNT AS ENROLLMENT, @WAITLIST_COUNT AS WAITLIST, @CONFIRM AS CONFIRMED, @PENDING AS PENDING, @WITHDRAWN AS WITHDRAWN, @APPROVED AS APPROVED, @DELETED as DELETED, AX.WAITLIST_INDFrom ACTIVITY_CLASS AS  AX JOIN ACTIVITY AS A ON (AX.ACTIVITY_ID = A.ACTIVITY_ID) JOIN CUSTOMER AS C ON (A.CUSTOMER_ID = C.CUSTOMER_ID) JOIN  FISCAL_YEAR AS FY ON (AX.FISCAL_YEAR_ID = FY.FISCAL_YEAR_ID) JOIN  ACTIVITY_CODE AS AC ON (AX.ACTIVITY_CODE_ID = AC.ACTIVITY_CODE_ID) JOIN SCHEDULE AS S ON (AX.ACTIVITY_CLASS_ID = S.ACTIVITY_CLASS_ID) JOIN ACTIVITY_STATUS AS SS ON (AX.ACTIVITY_STATUS_ID = SS.ACTIVITY_STATUS_ID)Where AX.ACTIVITY_CLASS_ID= @int_activity_class_Id GROUP BY A.ACTIVITY_NAME, A.DESCR,C.CUSTOMER_NAME,C.CUSTOMER_ID, A.PROVIDER_CODE, FY.FISCAL_YY, AC.CEU_ACTIVITY_CODE, ax.activity_seq_number, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.WAITLIST_IND,AX.ECOMMERCE_IND
 --TAKES 1 SECONDS--TOTAL 36 SECONDS

View 1 Replies View Related

Fixing The DBO

Dec 3, 2007

Hi,

I have got dbo , in the database security - logins. But I do not find it in the System - Security - Logins. When I try to re-create it throws an error. The server principle - dbo already exists.

I have got all the schemas in the database under dbo.So I cannot drop the dbo from the database.

How can I fix this

Thanks

View 8 Replies View Related

Help Fixing Script.

May 7, 2007

Hello everybody... I have a SQL 2000 Script that is not working on SQL 2005.. Or 2000 for that matter.. I wanted to know if somebody can help me fix this script.



/****** Object: Database NetManage_SQL ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NetManage_SQL')
DROP DATABASE [NetManage_SQL]
GO

CREATE DATABASE [NetManage_SQL] ON (NAME = N'NetManage_SQL_Data', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNetManage_SQL_Data.MDF' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = N'NetManage_SQL_Log', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNetManage_SQL_Log.LDF' , SIZE = 10, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'NetManage_SQL', N'autoclose', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'bulkcopy', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'trunc. log', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'torn page detection', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'read only', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'dbo use', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'single', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'autoshrink', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI null default', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'recursive triggers', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI nulls', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'concat null yields null', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'cursor close on commit', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'default to local cursor', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'quoted identifier', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI warnings', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'auto create statistics', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'NetManage_SQL', N'db chaining', N'false'
GO

use NetManage_SQL
GO
exec sp_addlogin 'NetManageAdmin', 'DigitalNetrixdbadmin', 'NetManage_SQL', 'us_english'
GO


/****** Object: Table [dbo].[Device_SwitchPorts] ******/
CREATE TABLE [dbo].[Device_SwitchPorts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_IPAddress_ID] [int] Default 0 ,
[SwitchPortDevice_Type_ID] [int] Default 0 ,
[SwitchPortSlotNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortDeviceID] [int] Default 0 ,
[SwitchPortDeviceName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


/****** Object: Table [dbo].[Device_Type] ******/
CREATE TABLE [dbo].[Device_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_Type_ID] [int] Default 0 ,
[Device_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress] ******/
CREATE TABLE [dbo].[IPAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_ID] [int] Default 0 ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPDecimal] [float] Default 0 ,
[IPAddress_TypeID] [int] Default 0 ,
[Device_Type_ID] [int] Default 0 ,
[IPAddress_Host_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddressLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPComments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress_Type] ******/
CREATE TABLE [dbo].[IPAddress_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[IPAddress_Type_ID] [int] Default 0 ,
[Can_Edit] [int] NULL ,
[IPAddress_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[User_Activity] ******/
CREATE TABLE [dbo].[User_Activity] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login_Date_Time] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_IP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Users] ******/
CREATE TABLE [dbo].[Users] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree_Label] [nvarchar] (50) Default Contact_Name ,
[DisplayStyle] [int] Default 0 ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Full_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDelete] [int] Default 1 ,
[Node_Access] [ntext] Default 0 ,
[Priv] [int] Default 3
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[settings] ******/
CREATE TABLE [dbo].[settings] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LicenseKey] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[subnet] ******/
CREATE TABLE [dbo].[subnet] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parent_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company_Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Node_Location] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VLAN_Info] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created_By] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert into [settings](Company, LicenseKey, comments) values('DEMO COMPANY','|95|49|47|26|253|195|170|232|71|19|151|77|188|231|23|64|87|62|215|53|169|186|27|65|218|111|185|218|238|127|2|115|187|245','Product License Key')
Insert into [subnet](Subnet_Name, Subnet_Mask, Parent_Subnet) values('Network Enterprise','000000000000','0')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(0,'Not Assigned')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(1,'PC')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(2,'Printer')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(3,'Router')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(4,'Switch')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(5,'Hub')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(6,'Web Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(7,'FTP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(8,'Mail Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(9,'DNS Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(10,'DHCP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(11,'Other')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(12,'Virtual Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(13,'Other Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(14,'VoIP Phone')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(15,'SQL Server')

Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(0,0, 'Not Assigned')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(1,0, 'Static')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(2,0, 'DHCP')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(3,0, 'Reserved')

Insert into [Users](UserName, DisplayStyle, [Password],Full_Name, EmailAddress, CanDelete, Priv) values('Administrator',0,'admin','Administrator', 'admin@company.com',0,1)



I keep getting error:



Msg 128, Level 15, State 1, Line 6

The name "Contact_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.





Any help pleae..



Thanks..

View 3 Replies View Related

Fixing NT Logins In SQL 2000

Mar 18, 2003

I have a SQL2000 db that uses NT authentication for the users. All users have access to the public group on the db's and are assigned security inside the application. When I restore the db's to another server (can't do master) the logins seem out of synch. Users who had access to db's no longer did. I realize each user has their own sid. 2 questions - 1 if I dropped all the users and used DTS to import the logins would that be better. 2nd - I thought (couldn't find in BOL there was a sp_fixlogin% sp that could be run that might help me with this.
Thanks

View 5 Replies View Related

Do They Ever Plan On Fixing Imports From Excel (and Probably Anything Else ??)

Mar 26, 2007

hours wasted    what else is new with Micro --- crap      try to import an excel into a table,  longest field (via vba macro report) is 278      receiving DB field is 4000     get "Truncation error,  I must stop   I am a piece of s*** program "   the only thing you can find on the web  is make sure you have SQL server 2005   SP2       install it        same thing      and g** only knows what other problems I have just created by installing another piece of Microsoft magic    generated in India or China   by the best technologists making at least  $5   and hour  

so MVPs    when is this rediculous situation going to be fixed  ??   oh    that's right   your answer will be "go to VISTA"    which won't fix my problem   but will probably help your stock situation 

MS owes me (conservatively) 100K  ...       my current plan is to install Linux     get up on MySQL  and NEVER deal with SHoddy half built pieces of garbage again

so where is the fix ???

 

when will we see  it  (short of buying another bloated piece of  ...  oh   i mean Vista)

 

 

an addendum

i am trying to import 25,000  rows with approx 20 columns from Excel

i added a first column with an ID ....    if I import whole table it dies on row 1852   for truncation problems

if I empty that column all goes in    make other exel  get rid of all rows except ID and bad row (contains web addresses   eg http://www. blah blah)

import that excel    it dies on row 2395     in other words  the first offending cell was no problem if it was the 2cnd row instead of the 15th row 

try to tell me this is not a random bug     ....       long live MYSQL

 

    

View 3 Replies View Related

Fixing A Slow, Brute Force Set Of SQL Calls

Dec 24, 2003

I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server.

I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s fields in the temporary table.

Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.

Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?

Thanks in advance for whatever help you can give,
-Starwiz

View 6 Replies View Related

SQL 2012 :: Indexed View - Fixing IDX From Varchar To INT?

Aug 20, 2014

I have an indexed view and i can't get why after "fixing" one of index it became slower then before.

Goal was to switch index to INT column instead of VARCHAR, so the theory looks perfect !!!(?).

I drop index and built new one with same name. Could it be because of Statistics? should I refresh it all ?

The only thing I changed is one of 3 idx:

-- CustTypeCode VARCHAR(10) /* 1,2,3,4,5 */
-- CustTypeID INT /* 1,2,3,4,5 */

-- index Before:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeCode] ASC)

-- index After:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeID] ASC)

-- usage before:
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeCode = 2

-- usage after
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeID = 2

View 3 Replies View Related

Fixing Phone Numbers To Correct Format

Aug 14, 2014

I am trying to find all the records in our database that have the incorrect phone number format and fix them to the correct format.

CREATE TABLE MDR (
SiteName nvarchar(255),
BusinessEmailAddress nvarchar(255),
FirstName nvarchar(255),
LastName nvarchar(255),
JobTitle nvarchar(255),
PersonBusinessPhoneNumber nvarchar(255),
SiteBusinessPhoneNumber nvarchar(255))

[code]....

View 1 Replies View Related

Fixing My Table Based On Dbcc Showcontig Results

Jul 20, 2005

Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.

View 2 Replies View Related

Help Fixing The UPDATE Statement Conflicted With The CHECK Constraint

Apr 8, 2008

Hi all,While using ACCESS and asp for years, I have just had to move my site onto a new server and the opportunity to move my 50MB access DB to msSQL was too good an opportunity to pass up! I used the Migration tools from MS and the data migrated nicely, I can do everything that I used to be able to do on the forum, except when a new user signs up or a member tries to edit their profile, the following error message comes up:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The UPDATE statement conflicted with the CHECK constraint "SSMA_CC$PORTAL_MEMBERS$M_ICQ$disallow_zero_length". The conflict occurred in database "DATABASENAME", table "dbo.PORTAL_MEMBERS", column 'M_ICQ'.


I have checked the database and the dbo.PORTAL_MEMBERS table and except for the autoincrement number, uname and pwrd, everything else is set to accept nulls.


I also went though and filled in everything on the form then I could update.


the same error applies when signing up - the only difference is the name of the table


I then tried to change the data straight in the table by opening it in Server Management Studio Express and got the same error if I did not fill in everything. This tells me that it is not the code, much to my relief!



So I am figuring that there is something somewhere in the DB that I have to change? could someone point me in the right direction - and as I am VERY NEW to this database system, and do not know my way around (it took me about 10 minutes to figure out how to open a table! *LOL*) very clear instructions would be appreciated.



Thank you for your time :-)

View 14 Replies View Related

Fixing Non-printing Character That Breaks SSIS Package

Apr 3, 2008

Hi folks,

I used a non-printing character (hex 97) in one of my derived column transformations which made my data flow task comopnents disappear, and throw the error during execution:


"Warning: The DataFlow task has no components. Add components or remove the task."

In order to fix the package, I thought I could open the .dtsx file in code/raw form, find the misbehaving character, remove it, and I'd be good to go.

But I can't figure out how to read the .dtsx code to find the character (despite my best efforts with a hex editor).

Anyone have any ideas? Fixing this package could save me days of rework...

Thanks a lot everyone.

Ted

View 4 Replies View Related

Reporting Services :: Fixing Ssrs Report Table Column Heading While Scrolling Is Not Working In Report-viewer?

Dec 14, 2012

i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first  top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....

View 6 Replies View Related

PK In Fact Table

May 21, 2008

I only see a need for a PK in a dimension table, not a fact table. Do you agree?

View 4 Replies View Related

Help With A Fact Table

May 28, 2008

I would like to know how to use a fact table so that when I insert or update a row with a word that the table will reference the fact table to make sure that the word I'm using is correct.

for example I have a table with column Fulltext and Abbreviation
in the fulltext column I have a a word "Windows Server 2008" now in the abbreviation I would like to abbreviate this to "Win Srv 08" Now the Fact table would have to columns Fulltext and Abbreviation under Full text the full words would be in it like Windows, Server, and 2008 and under the Abbreviation column Win, srv, and 08

So I want it so that everytime the word Windows comes up and I need to type an Abbreviation for it that it will reference the fact table which is using the Abbreviation Win. To avoid different ways of abbreviating the word windows.

Is there a way to do this automatically so that I don't have to manually go back and forth between the fact table and the table that I'm updating?

View 2 Replies View Related

New Fact Records

Jan 26, 2006

Hi there



Is there functionality in SQL 2005 to update only new records, e.g. only records from yesterday. I've seen functionality for dimension tables to only get new records but nothing for fact tables.



Thanks

View 4 Replies View Related

Combining Two Different Fact Grains

Jan 14, 2005

I'm trying to get one cube to display two different grains of data:

1) Vehicle measures - such as total time and total miles.

2) Trip measures - such as total trip time and total trip miles.

How would I accomplish this? I assume that the measures have to be in the same fact table?

Thanks for your time!

View 1 Replies View Related

Using String Col From Fact Tables

May 11, 2005

Hi
I have a fact table in which i have a String column now i want to show it in the mdx queries..when i add it as a measure it shows some numerical values in the cube i cannot even add it as a member propeties due to some datwarehouse design constraints..so can anyone out there help me please....it urgent
Thankx in advance
regards
Hemant

View 3 Replies View Related

Intermediate Fact Table

May 19, 2008

Hi,

I am writing a BI solution for a recruitment company. In their
business, the can be n number of participants from different
dimensions linked to the same fact record. For example, a client can
be sent the CV of 50 candidates. That's my first problem. My second
problem is the variety of dimension participant types for a given fact
record. This results in the need for nullable dimension FK's - which
I'm trying to avoid. For example, consider the following two business
events. In the first one, a candidate fills a job. Easy, we have a
record in the fact table where the fact table has the following
columns: DateKey, EventType, CandidateKey, VacancyKey. No nullable
columns, great. But there are other events that I want to store in
the fact table too. Let's go back to my first example: The client is
sent CV's of 50 candidates in one transaction. So there is one client
linked to the fact, but 50 candidates. So now I need to extend the
fact table and add another column: CandidateGroupKey (which links to
and Intermediate Fact Table). But in this case there was no vacancy
involved. So do I now have to make the VacancyKey column nullable?
That doesn't seem like a good idea...
Or do I have to go for a completely different approach and have
different fact tables instead of just one?

Anyone have any suggestions?

View 1 Replies View Related

Populate The Fact Table

Jun 6, 2007

Hi!

can any body tell me how to populate the fact table from base tables.

View 4 Replies View Related

How To Populate The Fact Table

Jun 6, 2007

databse name: bookorder

Realtions:

customer (customerid,l_name,f_name,city, district,country)
cust_order(orderid,orderdate,customerid)
order_detail(orderid,itemno,isbn,quantity)
book(isbn,title,edition_no,price)
author(authorid,name,surname)
book_author(authorid,isbn,authorseqno)


Now populate the datawarehouse name: book_orderdw
having fact table & three dimension tables given below from the above bookorder database

fact table

factsales(customerid,timeid,isbn,unit_price,discount_price,sales_quantity,sales_amount)

dimension tables

dimcustomer(customerid,l_name,f_name,city,district,country)
dimtime(timeid,orderdate,dayofweek, month, year)
dimbook(isbn,title,edition_no,price)

Now I have to populate the fact & dimension tables by writing sql scripts.
Now I have already populated the dimension tables by writing sql script,
But I have to populate the fact table taking into account, here I am facing problem in wriring sql script

(i) unit_price is taken from the book base table with reference to the isbn
(ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate)
(iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn
(iv) sales_amount is sales_quantity * discount_price

View 1 Replies View Related

Understanding Dimension And Fact

Sep 12, 2007

A few questions:

1) We have numerous fact tables with surrogate keys which reference just one dimensional surrogate key. How does this work?

2) Are the ‘facts’ feeding data TO the ‘dimensions’ (back end warehousing)? Or are the ‘Dimensions’ feeding facts to the ‘facts’ tables for lookups!?

Nb: Im very inexperienced at database design.

Im really also using this thread to get contacts for future harder questions!

Thanks kindly

View 8 Replies View Related

Insert Uniqueidentifier After The Fact

Sep 1, 2006

I have an existing table that i would like to add a uniquidentifier toeach record of the table. I have already create a column for theuniqueid. What sql script could I run to actually place a value forthe newly created column for each record?thanks for your help ahead of time

View 2 Replies View Related

How To Transform Fact Table Only By SQL?

Jul 20, 2005

Hi,this is easy with OLAP tools, but I need to do it just with MS-SQLserver:fatTableyeartypeval97a197b297c398a498b598c6....yeartype_atype_btype_c971239845699...The problem is number of different types - not just 3 like a,b,c butmore than 100, so I don't want to do it manually likeselectyear, a.val, b.val, c.valfrom(select year, val from factTable where type='a') afull join (select year, val from factTable where type='b') bon a.year = b.yearfull join (select year, val from factTable where type='c') con a.year = c.yearis it possible somehow with DTS or otherwise? I just need to presentthe data in spreadsheet in more readable form, but I cannot find anyway how to export the result from MS-SQLserverOLAPservices to Excel...Martin

View 2 Replies View Related

How Do Deduplication On Fact Table

Apr 13, 2006

Hi, everyone,

l've a fact table DEVICE with following structure,

DEVICE_NAME VARCHAR(50)
DEVICE_DATE DATETIME
DEVICE_NUMBER INT
Where DEVICE_NAME and DEVICE_DATE form a PRIMARY KEY

So l would like to import a text file with same information into this table.

My problem is, text file contains records which will violate my primary key constraint. In that case, l would only insert the record with DEVICE_NUMER not equal to ZERO and discard and log the others.

In case of the records violtae primary key constraints have DEVICE_NUMBER not equal to ZERO, discard both and log it.


So anyone has good suggestion on this?

View 1 Replies View Related

Loading Fact Table

Feb 13, 2006

Hi

I have delta loaded all the dimension tables now and each dimension table is related to fact table through a surrogate key, How do i further load a fact table. Please tell me I am stuck up here.. :( .

If any one has an example to refer please do tell me

View 2 Replies View Related

Truncate Dimensions/fact

May 17, 2007

Hi,



I am relatively new to SSIS/SSAS. I have searched the forums but cannot find an answer to my question.



I created a cube in SSAS and have deployed it. Now I am trying to use SSIS to populate the cube. I have setup a DS that points to the SSAS instance - it uses OLEDB Provider for Analysis services 9.0.



When I try to use a data flow task OLE DB source to truncate the dimension/cubes I do not see the DS in the list to select?



I am finding it hard to get into the SSIS way of organizing the processing.



Any Ideas?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved