Good morning,
I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.
How do I do this?
I have searched the forum for some ideas, but did not find any.
Thanks in advance...
Hi there,
I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...
My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?
My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:
EXEC sp_mergearticlecolumn
@publication = <publicationname>,
@article = <article name>,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
on each table I modifiy, after I have added the new column?
Thanks for your help
Graham
Hi,
I have a database that is being set up for merge replication (Sql 2005), but there is one table that I only want the schema replicating, not the data - I never want the data to be replicated in either direction. I can see from sp_addmergearticle that you can do something like this for sp's or functions but is it possible to do this for tables?
Regards
Graham
Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/
View 1 Replies View RelatedI have an table with duplicate data. I need to delete the duplicate records based on the RequestId.
I want to delete the records based on the RequestIDÂ 1001, Delete the duplicates in 1002, 1003 .
DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)
[Code] ....
Expected Result
select * from @table
Hi friends,I need to select columns for specific data from all tables in DataBasePls give me reply asap Regards,M.Raj
View 4 Replies View RelatedTill recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the followingÂ
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
But instead of TABLE_NAME i would like to give a query like
SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].
Hi all,
does anyone know of a way in which you can backup and restore objects that belong to a specific schema?
So in this example, I€™d want to only back up and be able to restore the tables that belong to schema 1, leaving Schema2 stuff alone.
--DB
----Schema1.Table1
----Schema1.Table2
----Schema2.Table1
----Schema2.Table2
Thanks in advance,
Andy.
EDIT: I forgot to add that I want to backup the tables and the data contained within.
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
Having some trouble getting my head around setting access to specificschemas- here's my problem:I've created a specific schema that I only want certain users tocontrolProblem: Even though I give them full access....the cannot createtables under that schema...my code is below (flyer is the schema,eflyerAdmin is the role, and eflyer is the user):GRANTALTER,CONTROL,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,TAKE OWNERSHIP,UPDATE,VIEW DEFINITIONON SCHEMA::flyerTO eflyerAdminGO-- Add an existing user to the roleEXEC sp_addrolemember N'eflyerAdmin', N'eflyer'
View 1 Replies View RelatedWe have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies View RelatedHello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
Hi,
I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.
I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?
I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.
I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!
Thanks a mill,
Sophie
Hello,
We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.
John
Are there any known problems with replicating structure changes (New table, etc...), as well as data in MSSQL Server 7.0?
View 2 Replies View RelatedWe have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.
I have SQL Server running on my internal LAN. I want to have a second SQL Server running on a hosted (shared) website. I then would like these servers to talk to each other. At some scheduled time I need to publish data to the web, and I then need to subscribe to data input on the web by various clients. My internal LAN can see the Internet via our cable modem.
What is the best way to do this? What software will I need to run. I'm looking for the big picture.
Thanks for the help everyone.
Hi, need some help.
Basically I need to replicate 2 tables from a Visual Fox Pro application to SQL server 2005
From an Intranet to a hosted Center. (Different domains)
Is this possible?
Any suggestions?
Thanks in advance
I need to find a solution for having the same database objects on two servers without the data, and be able to synchronize them on demand.
Whenever a table changes (alter, create, delete) I need to be able to replicate those changes to the destination server without affecting its data.
If I add, alter or delete a stored procedure, or other objects such as functions, etc. I need to be able to see those changes on the destination.
any ideas.
I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?
Thanks,
Kirk
I am trying to do a BULK insert using BCP and a XML format file.
I have a file with 32 columns, and a table with 34 columns that i want to bulk insert
i have read here:
http://msdn.microsoft.com/en-us/library/ms179250.aspx
to try and do the bulk insert. but it doesn't seem to insert the data into the correct columns.
here is the SQL to create the table.
SQL Code:
Original
- SQL Code
CREATE TABLE [dbo].[prep_SomeTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Last_Update] [smalldatetime] null,
[ClientID] [varchar](10) NOT NULL DEFAULT (''),
[pollSystem] [varchar](10) NOT NULL DEFAULT(''),
[vin] [varchar](17) NOT NULL DEFAULT(''),
[year] [varchar](4) NOT NULL DEFAULT(''),
[make] [varchar](50) NOT NULL DEFAULT(''),
[model] [varchar](50) NOT NULL DEFAULT(''),
[trim] [varchar](50) NOT NULL DEFAULT(''),
[mileage] [varchar](10) NOT NULL DEFAULT(''),
[factColor] [varchar](100) NOT NULL DEFAULT(''),
[intFactColor] [varchar](100) NOT NULL DEFAULT(''),
[doors] [varchar](10) NOT NULL DEFAULT(''),
[newused] [varchar](10) NOT NULL DEFAULT(''),
[stockno] [varchar](10) NOT NULL DEFAULT(''),
[cpo] [varchar](10) NOT NULL DEFAULT(''),
[certType] [varchar](50) NOT NULL DEFAULT(''),
[certid] [varchar](10) NOT NULL DEFAULT(''),
[otherprice] [varchar](10) NOT NULL DEFAULT(''),
[webprice] [varchar](10) NOT NULL DEFAULT(''),
[invprice] [varchar](10) NOT NULL DEFAULT(''),
[msrp] [varchar](10) NOT NULL DEFAULT(''),
[mktClass] [varchar](50) NOT NULL DEFAULT(''),
[options1] [varchar](8000) NOT NULL DEFAULT(''),
[instock] [varchar](50) NOT NULL DEFAULT(''),
[manfModelNum] [varchar](50) NOT NULL DEFAULT(''),
[trans] [varchar](100) NOT NULL DEFAULT(''),
[enginedesc] [varchar](100) NOT NULL DEFAULT(''),
[drivetrain] [varchar](100) NOT NULL DEFAULT(''),
[overviewtext] [varchar](8000) NOT NULL DEFAULT(''),
[options2] [varchar](8000) NOT NULL DEFAULT(''),
[chromestyleid] [varchar](10) NOT NULL DEFAULT(''),
[photourls] [varchar](8000) NOT NULL DEFAULT(''),
[photosupdated] [varchar](10) NOT NULL DEFAULT(''),
CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
CREATE TABLE [dbo].[prep_SomeTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Last_Update] [smalldatetime] NULL, [ClientID] [varchar](10) NOT NULL DEFAULT (''), [pollSystem] [varchar](10) NOT NULL DEFAULT(''), [vin] [varchar](17) NOT NULL DEFAULT(''), [year] [varchar](4) NOT NULL DEFAULT(''), [make] [varchar](50) NOT NULL DEFAULT(''), [model] [varchar](50) NOT NULL DEFAULT(''), [TRIM] [varchar](50) NOT NULL DEFAULT(''), [mileage] [varchar](10) NOT NULL DEFAULT(''), [factColor] [varchar](100) NOT NULL DEFAULT(''), [intFactColor] [varchar](100) NOT NULL DEFAULT(''), [doors] [varchar](10) NOT NULL DEFAULT(''), [newused] [varchar](10) NOT NULL DEFAULT(''), [stockno] [varchar](10) NOT NULL DEFAULT(''), [cpo] [varchar](10) NOT NULL DEFAULT(''), [certType] [varchar](50) NOT NULL DEFAULT(''), [certid] [varchar](10) NOT NULL DEFAULT(''), [otherprice] [varchar](10) NOT NULL DEFAULT(''), [webprice] [varchar](10) NOT NULL DEFAULT(''), [invprice] [varchar](10) NOT NULL DEFAULT(''), [msrp] [varchar](10) NOT NULL DEFAULT(''), [mktClass] [varchar](50) NOT NULL DEFAULT(''), [options1] [varchar](8000) NOT NULL DEFAULT(''), [instock] [varchar](50) NOT NULL DEFAULT(''), [manfModelNum] [varchar](50) NOT NULL DEFAULT(''), [trans] [varchar](100) NOT NULL DEFAULT(''), [enginedesc] [varchar](100) NOT NULL DEFAULT(''), [drivetrain] [varchar](100) NOT NULL DEFAULT(''), [overviewtext] [varchar](8000) NOT NULL DEFAULT(''), [options2] [varchar](8000) NOT NULL DEFAULT(''), [chromestyleid] [varchar](10) NOT NULL DEFAULT(''), [photourls] [varchar](8000) NOT NULL DEFAULT(''), [photosupdated] [varchar](10) NOT NULL DEFAULT(''), CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED ( [ID] ASC )) ON [PRIMARY]
the format file:
Code:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="26" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="27" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="28" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="29" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="30" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="31" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="32" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ClientID" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="pollSystem" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="vin" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="year" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="make" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="model" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="trim" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="8" NAME="mileage" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="factColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="intFactColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="11" NAME="doors" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="12" NAME="newused" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="13" NAME="stockno" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="14" NAME="cpo" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="15" NAME="certType" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="16" NAME="certid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="17" NAME="otherprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="18" NAME="invprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="19" NAME="webprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="20" NAME="msrp" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="21" NAME="mktClass" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="22" NAME="options1" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="23" NAME="instock" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="24" NAME="manfModelNum" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="25" NAME="trans" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="26" NAME="enginedesc" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="27" NAME="drivetrain" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="28" NAME="overviewtext" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="29" NAME="options2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="30" NAME="chromestyleid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="31" NAME="photourls" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="32" NAME="photosupdated" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
and the SQL:
SQL Code:
Original
- SQL Code
BULK INSERT prep_SomeTable
FROM '\devd_driveftproot7415_519.dat'
WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' )
BULK INSERT prep_SomeTableFROM '\devd_driveftproot7415_519.dat' WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' )
error message:
Quote: Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I am not sure why it is trying to insert into the Last_Update Column, which is the only smalldatetime field in the table. but in the format file, i do not have any data going into that column.
it looks like everything is set correctly in the format file
in the SQL Server Library Link, section "Using an XML Format File" 2nd example, it looks to be the same thing as I am?
not sure what I am doing wrong?
Hi Guys,
I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...
Is their a way to force 1 column to always be unique?
Heres the query:
SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC
The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.
Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:
ItemID
83630Mathew Smith
8 366Tony Wattage
Which is kinda annoying.... is there anyway i can prevent this?
And also apart from the Where Joins... is there a more efficient way of writing this?
thx for reading :-)
--Philkills
Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
In 2007 we plan on moving many of our SQL Servers (2000 & 2005) to a SAN. We will also be investigating the best way to replicate data from our production SAN to a Disaster Recovery SAN. I would like to know if people rely on built-in SQL Server replication / log shipping or third party products like DoubleTake or MirrorView. What's the advantage, if any, to using a third-party product?
Thanks, Dave
Hello,
I've been searching for information on this for awhile unsuccessully. I am using SQL Server 2005 Enterprise Edititon and transactional replication with separate publisher, distributor and subscribers. I need to encrypt data on our publisher and then I need to replicate that data out (i.e., encrypted data, not encrypted transmission). Since the keys are server specific, I imaging there might be some difficulty in doing this, but I figure I can't be the only one with this problem. Does anyone have a resource or know what must be done to get this accomplished?
Thanks,
Aaron Lowe
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies View RelatedI have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.
View 1 Replies View RelatedHi,
I need to implement SQL Reporting Services but do not wish to use the production database for running the reports against. I intend to set a seperate server for reporting services. The data only needs to be refreshed on a daily basis, during slient hours. What would be the best way to copy data from the production to the reporting services server?
Thanks
hi,
is it possible to replicate data from one table to another which have different names,different schemas and different column names.
Im trying this using transactional replication.
i achieved this for different schema, different table names.but it is failing for different column names.
till now i haven't got a proper reply for my problem.
regards
Baji Prasad.B
How do you setup replication from SQL Server 2000 to a oracle database? We want to push changes from sql sever to oracle, evertime sql server table is changed. I have a working ODBC connection. When I try setting up a push to the oracle side I can not find the subscriber In the list. Can someone point me in a direction, with a detailed way of doing this.
View 1 Replies View RelatedHello! I'm new to these forums.
I have been trying to replicate from Sql Server to Postgres using Enterprise Manager and the Postgres ANSI ODBC driver version 8.02.02.
With enterprise manager, I was able to copy tables over when I used the option to "DROP existing table and re-create it," but columns of data types datetime and decimal were copied as varchar.
So I tried drop the tables manually in Postgres and then create them with the proper data types (timestamp without time zone, and numeric). I then tried changing the article option to "Keep the existing table unchanged" and I get the following error from the Distribution agent: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
So I then tried using the article snapshot option "Delete all data in the existing table" and I got the same error.
Anyone has any luck replicating in this fashion? Or should I maybe go about it differently?