November 11, 2008 at 4:39 pm
I want you to actually read the article I keep telling you to read. I want you to help us help you. If you can't provide the necessary info I have been asking for, I can't help you any more.
We can keep providing you with "help" but everything we "give" you doesn't seem to work for you. When we ask for things from you we get (IMO) halfhearted posts. For example, the post you just put up with a single create table statement and one row of sample data. First, I'd have to rewrite the create statement, as it won't work for me as is. And what good is one row of sample data for the target table. What about the source of the data? A view over a table. Where are the definitions for those? Where is the sample data for those? Where is the expected results from the process? Where is the COMPLETE code you are trying to use, that isn't working?
For all this to work, you have to help us to help you. We aren't King Midas. We need to know what you know if we are to truly help you.
November 11, 2008 at 8:21 pm
November 11, 2008 at 8:50 pm
what does seconded mean?
November 11, 2008 at 9:01 pm
Seconded -- to agree with a motion presented during a meeting, usually resulting in a vote on the motion. Check Robert's Rules of Order.
November 11, 2008 at 9:10 pm
i think i have a solution i just dont know why the cast function im using doesnt work when i run my stored procedure? with the script i gave you, you should be able to tell me if im writing the cast correctly.
November 11, 2008 at 9:26 pm
I have nothing against which to run your script. You haven't provided us with the tables or sufficient test data to do anything except guess.
November 12, 2008 at 3:07 pm
Does this help you create a table?
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: Table [dbo].[Customers] Script Date: 11/12/2008 16:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerID] [dbo].[C_ID] NOT NULL,
[ParentCustomerID] [dbo].[C_ID] NULL,
[ParentContactID] [dbo].[C_ID] NULL,
[Name] [dbo].[C_Name] NOT NULL,
[ShortName] [dbo].[C_Caption] NULL,
[ReferenceNo] [dbo].[C_Name] NULL,
[Logo] [dbo].[C_SmallLink] NULL,
[CustomerTypeID] [dbo].[C_ID] NULL,
[Templates] [dbo].[C_Link] NULL,
[CorpURL] [dbo].[C_Link] NULL,
[CorpEmail] [dbo].[C_Email] NULL,
[SupportEmail] [dbo].[C_Email] NULL,
[SalesEmail] [dbo].[C_Email] NULL,
[OtherEmail] [dbo].[C_Email] NULL,
[DefaultContactID] [dbo].[C_ID] NULL,
[Markup] [dbo].[C_Percentage] NULL CONSTRAINT [DF__Customers__Marku__2116E6DF] DEFAULT ((0)),
[DefaultCurrencyID] [dbo].[C_ID] NULL,
[PriceListID] [dbo].[C_ID] NOT NULL,
[FreightID] [dbo].[C_ID] NULL,
[PaymentTypeID] [dbo].[C_ID] NULL,
[PaymentTermID] [dbo].[C_ID] NULL,
[PaymentTermNotes] [dbo].[C_Comments] NULL,
[ShippingPolicyID] [dbo].[C_ID] NULL,
[CarrierID] [dbo].[C_ID] NULL,
[ShippingMethodID] [dbo].[C_ID] NULL,
[EffectiveDate] [dbo].[C_Date] NOT NULL CONSTRAINT [DF__Customers__Effec__220B0B18] DEFAULT (getdate()),
[ExpiryDate] [dbo].[C_Date] NOT NULL CONSTRAINT [DF__Customers__Expir__22FF2F51] DEFAULT ('12/31/2020'),
[DefaultTaxRate] [dbo].[C_Percentage] NULL CONSTRAINT [DF__Customers__Defau__23F3538A] DEFAULT ((0)),
[DefaultQuoteDiscount] [dbo].[C_Percentage] NULL CONSTRAINT [DF__Customers__Defau__24E777C3] DEFAULT ((0)),
[DiscountComments] [dbo].[C_Comments] NULL,
[Notes] [dbo].[C_Text] NULL,
[Deleted] [dbo].[C_Boolean] NOT NULL CONSTRAINT [DF__Customers__Delet__25DB9BFC] DEFAULT ((0)),
[Status] [dbo].[C_Status] NULL CONSTRAINT [DF__Customers__Statu__26CFC035] DEFAULT ((1)),
[StatusComments] [dbo].[C_Comments] NULL,
[StatusChangeDate] [dbo].[C_Date] NULL,
[StatusChangeUser] [dbo].[C_UserName] NULL,
[CreateDate] [dbo].[C_Date] NOT NULL CONSTRAINT [DF__Customers__Creat__27C3E46E] DEFAULT (getdate()),
[CreateUser] [dbo].[C_UserName] NULL CONSTRAINT [DF__Customers__Creat__28B808A7] DEFAULT (suser_sname()),
[MaintenanceDate] [dbo].[C_Date] NOT NULL CONSTRAINT [DF__Customers__Maint__29AC2CE0] DEFAULT (getdate()),
[MaintenanceUser] [dbo].[C_UserName] NULL CONSTRAINT [DF__Customers__Maint__2AA05119] DEFAULT (suser_sname()),
[LockUser] [dbo].[C_UserName] NULL,
[LockSessionID] [dbo].[C_GUID] NULL,
[LockDate] [dbo].[C_Date] NULL,
[PriceList_MaintenanceDate] [dbo].[C_Date] NULL,
[SyncStatus] [dbo].[C_Status] NOT NULL CONSTRAINT [DF__Customers__SyncS__2B947552] DEFAULT ((0)),
[SyncDate] [dbo].[C_Date] NULL,
[SyncUser] [dbo].[C_UserName] NULL,
[DiscountMode] [dbo].[C_Integer] NULL CONSTRAINT [DF__Customers__Disco__2C88998B] DEFAULT ((0)),
[UpchargeMode] [dbo].[C_Integer] NULL CONSTRAINT [DF__Customers__Upcha__2D7CBDC4] DEFAULT ((0)),
[CreateCustomerID] [dbo].[C_ID] NULL,
[CreateContactID] [dbo].[C_ID] NULL,
[MaintenanceCustomerID] [dbo].[C_ID] NULL,
[MaintenanceContactID] [dbo].[C_ID] NULL,
[InUse] [dbo].[C_Boolean] NOT NULL CONSTRAINT [DF__Customers__InUse__2E70E1FD] DEFAULT ((0)),
[CustomerSalesModel] [dbo].[C_Integer] NULL CONSTRAINT [DF__Customers__Custo__2F650636] DEFAULT ((0)),
[CustomerPrefix] [dbo].[C_Caption] NULL,
[State] [dbo].[C_Status] NOT NULL CONSTRAINT [DF__Customers__State__30592A6F] DEFAULT ((0)),
[DefaultCustomerAddressID] [dbo].[C_ID] NULL,
[DefaultCustomerID] [dbo].[C_ID] NULL,
CONSTRAINT [Customers_PK] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK1] FOREIGN KEY([ParentCustomerID], [ParentContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK2] FOREIGN KEY([MaintenanceCustomerID], [MaintenanceContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK2]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK3] FOREIGN KEY([CreateCustomerID], [CreateContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK3]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Contacts_Customers_FK4] FOREIGN KEY([CustomerID], [DefaultContactID])
REFERENCES [dbo].[Contacts] ([CustomerID], [ContactID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Contacts_Customers_FK4]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [CustomerAddresses_Customers_FK1] FOREIGN KEY([DefaultCustomerAddressID], [DefaultCustomerID])
REFERENCES [dbo].[CustomerAddresses] ([CustomerAddressID], [CustomerID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [CustomerAddresses_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [Customers_Customers_FK1] FOREIGN KEY([ParentCustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [Customers_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [CustomerTypes_Customers_FK1] FOREIGN KEY([CustomerTypeID])
REFERENCES [dbo].[CustomerTypes] ([CustomerTypeID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [CustomerTypes_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [FreightTerms_Customers_FK1] FOREIGN KEY([FreightID])
REFERENCES [dbo].[FreightTerms] ([FreightID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FreightTerms_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [PaymentTerms_Customers_FK1] FOREIGN KEY([PaymentTermID])
REFERENCES [dbo].[PaymentTerms] ([PaymentTermID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [PaymentTerms_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [PaymentTypes_Customers_FK1] FOREIGN KEY([PaymentTypeID])
REFERENCES [dbo].[PaymentTypes] ([PaymentTypeID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [PaymentTypes_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [ShippingCarriers_Customers_FK1] FOREIGN KEY([CarrierID])
REFERENCES [dbo].[ShippingCarriers] ([CarrierID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [ShippingCarriers_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [ShippingMethods_Customers_FK1] FOREIGN KEY([CarrierID], [ShippingMethodID])
REFERENCES [dbo].[ShippingMethods] ([CarrierID], [ShippingMethodID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [ShippingMethods_Customers_FK1]
GO
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [ShippingPolicies_Customers_FK1] FOREIGN KEY([ShippingPolicyID])
REFERENCES [dbo].[ShippingPolicies] ([ShippingPolicyID])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [ShippingPolicies_Customers_FK1]
November 12, 2008 at 3:24 pm
Nope. Take a close look at the code. Everything appears to be using user-defined types. Those all need to be the base types for me to do anything.
Also, still only one table, the target table. Still need the source table/view, sample data for both, your current code that you are having problems with, and what the expected output should be based on the sample data.
November 12, 2008 at 3:32 pm
how do i find the base defined types for my Customers table?
November 13, 2008 at 6:53 am
doesn't SQL Server 2008 have a MERGE capability?
November 13, 2008 at 6:56 am
will it work in 2005?
November 13, 2008 at 7:01 am
paul.starr (11/13/2008)
will it work in 2005?
MERGE is new to SQL Server 2008, doesn't exist in SQL Server 2005 and earlier versions of SQL Server.
November 13, 2008 at 7:37 am
paul.starr (11/12/2008)
how do i find the base defined types for my Customers table?
User Defined Data types are so much fun! I have over 1500 of them in my database.:crying:
You can go to enterprise manager and look them up in the category: User Defined Data types, or you get around the issue by pulling the types from INFORMATION_SCHEMA.COLUMNS. (Probably a couple other ways to get them too).
November 13, 2008 at 7:48 am
Does this help any:
COLUMN NAMES
CustomerID
ParentCustomerID
ParentContactID
Name
ShortName
ReferenceNo
Logo
CustomerTypeID
Templates
CorpURL
CorpEmail
SupportEmail
SalesEmail
OtherEmail
DefaultContactID
Markup
DefaultCurrencyID
PriceListID
FreightID
PaymentTypeID
PaymentTermID
PaymentTermNotes
ShippingPolicyID
CarrierID
ShippingMethodID
EffectiveDate
ExpiryDate
DefaultTaxRate
DefaultQuoteDiscount
DiscountComments
Notes
Deleted
Status
StatusComments
StatusChangeDate
StatusChangeUser
CreateDate
CreateUser
MaintenanceDate
MaintenanceUser
LockUser
LockSessionID
LockDate
PriceList_MaintenanceDate
SyncStatus
SyncDate
SyncUser
DiscountMode
UpchargeMode
CreateCustomerID
CreateContactID
MaintenanceCustomerID
MaintenanceContactID
InUse
CustomerSalesModel
CustomerPrefix
State
DefaultCustomerAddressID
DefaultCustomerID
datatypes, character length
nvarchar30
nvarchar30
nvarchar30
nvarchar50
nvarchar20
nvarchar50
nvarchar200
nvarchar30
nvarchar250
nvarchar250
nvarchar50
nvarchar50
nvarchar50
nvarchar50
nvarchar30
decimalNULL
nvarchar30
nvarchar30
nvarchar30
nvarchar30
nvarchar30
nvarchar500
nvarchar30
nvarchar30
nvarchar30
datetimeNULL
datetimeNULL
decimalNULL
decimalNULL
nvarchar500
ntext1073741823
intNULL
intNULL
nvarchar500
datetimeNULL
nvarchar50
datetimeNULL
nvarchar50
datetimeNULL
nvarchar50
nvarchar50
nvarchar36
datetimeNULL
datetimeNULL
intNULL
datetimeNULL
nvarchar50
intNULL
intNULL
nvarchar30
nvarchar30
nvarchar30
nvarchar30
intNULL
intNULL
nvarchar20
intNULL
nvarchar30
nvarchar30
November 13, 2008 at 7:57 am
In SSMS, you can right click on the database and in the pop-up menu select Generate Scripts. Using the wizard, you can select the databae, in the options page you change Convert UDDT's to Base Types to true. You can then select tables,views, and stored procedures. Then select the tables, views, and stored procedures you need for this particular issue you are having for this thread.
You can then create a file, script it to a query window where you can copy and paste, whatever.
You then still need to follow the instructions in the article below to create the sample data you need to provide as well.
Observation: You really need to learn how to figure things out without having to everything handed to you. A lot of this you should be able to figure out by reading Books Online (BOL) and just plain looking around at the tools provided with SQL Server.
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply