Hi All,
I setup a Transactional replication between SQL Server 2000 and SQL Server 2005 via
wide area network.
The intial snapshoot failed because run into problems on a table with column 379 columns.
How do ceate Vertical Partioning a table on Publication?
Thank you.
TJ_1
Hi,
Does anyone know how to format a text in a textbox or a table cell to show vertically?
Or, rotating a textbox with 90 degree angle? Thanks.
I'm struggling with trying to display a single vertical stripline at a date value along my x-axis.
I'm using the following non-default settings within the stripline editor:
bordercolor = tomato
borderstyle = solid
borderwidth = 1pt
intervaloffset = Fields!uidate.Value.ToOADate()
where uidate is a calculated field in my dataset that always has the value of 05/29/2012 (using formatdatetime("05/29/2012",dateformat.ShortDate))
Everything else is set to Auto, with stripwidth=0
I'm trying to display a single vertical line at the x-axis value of 05/29/2012
I have previously tried setting the interval offset to "05/29/2012" but that didn't work. I've also tried setting the value of a hidden text box on the report to "05/29/2012" and setting the interval offset to the value of that text box, but that gave me a "text box not declared" error.
How I can simply display a vertical stripline on my line graph at the x-axis value of 05/29/2012 ?
I am making a warehouse managment system. The system will cotain much data, but only a small portion of the data will be accessed frequently. Most of the data will only be accessed seldomly, but the customer wants to keep all historic data (just in case they should need it sometime). I have figured I need to partion the tables somehow to keep what is fresh in one place, and historical data in another place. What is the best way to do this? I am thinking about making historical tables. For example I can have a table named PickList and another table named PickListHistorical. When a picklist is processed/complete I can move it over to the PicklistHistorical table, but when the users need to search for a specific picklist I have to look in both tables. I can ofcourse create a view for this to make it transparant. Sql server 2005 introduced some automatically partioning. Will it be better to use this than create my own historic tables? If so, can you please tell me how I do it?
Thank you!
This full version of this reporting table will have about 12 million row for each of three years. Prior years will be in separate partitions and frozen but the current year will be reloaded each night by source_key, probably in parallel.
I am trying to do this with a computed column but I can't slide the partition back into the main table due to an apparent problem with the Check constraint. I have tried everything I can think of and still can't get it to work.
I hope I am missing something simple. Anyone know why this does not work or how to fix it?
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'db_template.dbo.foo_year_source_partition_test_stage' allows values that are not allowed by check constraints or partition function on target table 'db_template.dbo.foo_year_source_partition_test'.
------------------------------------------------------------
CREATE PARTITION SCHEME zzYearSourcePScheme
AS
PARTITION zzYearSourceRangePFN
TO
(
[fg_template_0],
[fg_template_0],
[fg_template_0],
[fg_template_0],
[fg_template_0]
)
go
CREATE TABLE [dbo].[foo_year_source_partition_test](
[detail_date] [datetime] NULL,
[source_key] [int] NULL,
[year_source] AS ((CONVERT([char](2),right(datepart(year,[detail_date]),2),0)+'-')
+right('0000'+CONVERT([varchar](3),[source_key],0),(3))) PERSISTED,
[ys_id] int identity (1,1)
) ON zzYearSourcePScheme(year_source)
go
create unique clustered index ix_year_source_ys_id on [foo_year_source_partition_test] ([year_source], [ys_id]) ON zzYearSourcePScheme(year_source)
go
insert into [foo_year_source_partition_test] values('20060131',2)
insert into [foo_year_source_partition_test] values('20060131',3)
insert into [foo_year_source_partition_test] values('20060131',4)
SELECT *, $PARTITION.zzYearSourceRangePFN(year_source) AS Partition from [foo_year_source_partition_test] order by detail_date
go
CREATE TABLE [dbo].[foo_year_source_partition_test_stage](
[detail_date] [datetime] NULL,
[source_key] [int] NULL,
[year_source] AS ((CONVERT([char](2),right(datepart(year,[detail_date]),2),0)+'-')
+right('0000'+CONVERT([varchar](3),[source_key],0),(3))) PERSISTED,
[ys_id] int identity (1,1)
) --on same one ON YearSourcePScheme(year_source)
create unique clustered index ix_year_source_ys_id
on [foo_year_source_partition_test_stage] ([year_source], [ys_id]) --ON YearSourcePScheme(year_source)
ALTER TABLE db_template.dbo.foo_year_source_partition_test
SWITCH PARTITION 3 to db_template.dbo.[foo_year_source_partition_test_stage]
TRUNCATE TABLE db_template.dbo.[foo_year_source_partition_test_stage]
ALTER TABLE db_template.dbo.foo_year_source_partition_test_stage
WITH CHECK
ADD CONSTRAINT CK_foo_year_source_partition_test_stage_YearSource
CHECK
(
[year_source] = '06-003'
)
insert into foo_year_source_partition_test_stage values('20060202',3)
insert into foo_year_source_partition_test_stage values('20060303',3)
insert into foo_year_source_partition_test_stage values('20060404',3)
insert into foo_year_source_partition_test_stage values('20060505',3)
ALTER TABLE db_template.dbo.foo_year_source_partition_test_stage
SWITCH TO db_template.dbo.foo_year_source_partition_test PARTITION 3
To the Forum,
Hello, i wanted to give the forum my current process flow to see if i am close or have some more work to do. The object is to import the data as fast as possible without loosing query responsiveness of the search on the web side. Any type of response will be greatly appreciated.
Current Process
I receive multiple product inventory lists form multiple vendors. These inventory lists are in many different formats like .xls format, .txt format, .csv format and .dbf format. My server converts the file format to raw .csv. The table is very large and will consist of millions of rows. Inventory comes in alphanumeric format.
Each of the inventory lists are NOT in the same format. What i mean by this is that there are different header names for the users inventory list than what matches our database table. For Example, a user may have an excel document with the header name "Amt" but our database table field name is "Amount". In order to make this import process automated I make a single mapping file for each user. This mapping file relates the users field header in their inventory file to that of the database table.
Currently I have a process converting all files to .csv format. Every 15 minutes a routine runs that converts the .csv to XML and then performs the import Bulk Insert routine. The process deletes the users entire inventory then imports to a "tempinventory" table, which then updates the inventory table. During this 15 minutes we may get 20 inventory lists to import which could be a half a million records. I have this inventory table indexed using the primary key which is the inventory number. This is the search criteria used (Inventory Number) on the web side. The import routine to SQL works quickly ONLY if Full-Text Index is turned OFF. I assume i need the Full Text Indexing on so the queries of the full inventory lists a fast response on the web side.
Assumed Issues
Right now if I were to turn off the Indexing for each import then we would have slow queries against the database on the web side.
To have one table with millions of rows that is constantly updated as well as queried at the same time is not efficient.
Assumed Corrections
Right now it seems that partitioning the table into 10 numeric partitions, one for each number would leverage the import routine as well as the web side search routine. I would then partition the alpha partition in 1 letter increments so we would have 26 partitions, one for each letter for a total of 36 partitions.
If I have the partitions seperated then it will be easier to update the seperate index as well as perform maintenance on the index, i assume this is correct.
Future Plans
I am upgrading the web side application to .NET 3.5 so we can take advantage of XLINQ and LINQ so our searches from the web side are faster and more efficient.
I am also looking into building a SilverLight application that will allow the user to install the application locally that will take their live database file and send updates to my server so they do not need to send in their inventory file at all and the inventory lists are live. This will alleviate the need to delete the users full listing in order to make a complete update. As stated above sometimes an update may just include updating the amount of only 2 of 40,000 rows.
I was also looking into db4o to see if it would be beneficial as well, http://www.db4o.com/, has anyone worked with this before in a similar manner?
Questions
I would like to make this process much more efficient from the import routine to the search routine. Is setting up the partitions as discussed a stable plan for both routines?
Is the BULK INSERT using XML to SQL the most efficient way of importing the data to SQL?
How would i handle the full text indexing to allow fast import routines without slowing down the web side searches?
After import of new data do i need to "update" the index as well?
What is a good set of "preventative maintenance" standards should i follow when dealing with this many table updates as well as the catalogs and table data?
I know there are benefits of using LINQ when querying the database from the web side but are there any other benefits that would fit into this current process?
As for the SilverLight application would it be beneficial for the user as well as me to have the application poll their database file to find changes and send only the updated values of the list to the server via XML, which is then updated by SQL?
I am unsure what is the best way to make this process as easy and automated as possible giving the user the fastest experience possible when searching from the web side.
Is this a smarter idea so i can track just the changes made by the user on their inventory list instead of importing the same redundant data they have?
Would implementing something like db4o be beneficial for this process, http://www.db4o.com/?
Please let me know if i am way off on this process or if there are some benefits that i am not using in this process. I have been doing a lot of research and this is what i have come up with so i wanted to ask the community what they thought about it as many heads are better than one. Please feel free to rip the process apart to, i take constructive criticism.
Thanks again.
RC
Snehalata writes "does view for vertical partitioning improves the performance, since the view will have all the columns which exist in the original table(without partitioning?"
View 1 Replies View RelatedHi
is it possible to set the vertical line to stretch from top of list box to the bottom even If the list box might grow at run time I want the line should stretch from top to bottom
We can merge two horizontal cells but how to merge two table cells vertically, Means R1C1 with R2C1 to make R12C1?
Thanks lot...
I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables.
For example:
If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical.
But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that?
I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?
I'm running SQL Server 2008 Standard.I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).
Record Number, Status, Column Name, Date Started, DateCompleted
1, Open, Column 1, 1/1/2012, 2/1/2012,
2, Hold, Column 2, 1/3/2012, 3/1/2012,
1, Open, Column 3, 2/5/2012, 4/6/2012,
3, Closed, Column 4, 5/10/2012, 7/25/2012,
2, Hold, Column 5, 3/9/2012, 4/1/2012,
1, open, Column 6, 10/10/2012, 12/12/2012,
id name
--- -----------------
236 SERVICE REQUEST
236 HARDWARE
236 Desktop
336 Loan
id name
-- ----------------------------
236 SERVICE REQUEST/ HARDWARE/ Desktop/Laptop/ Loan
I have a table like this:
weight type factory1 factory2 factory3 factory4.... to factory 150
1kg goods 5.00 5.50 5.20 5.00...
2kg goods 6.00 6.20 6.15 6.30...
3kg goods 4.00 4.50 5.00 4.30...
...
and would like to extract data this way:
producer type weight price
factory1 goods 1kg 5.00
factory1 goods 2kg 6.00
factory1 goods 3kg 4.00
factory1.....
then
factory2 goods 1kg 5.50
factory2 goods 2kg 6.20
and so on for all factories.
I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".
Hi all
I dont want to use functions or proceedures but with select itself I want to get the above mentioned result. Can somebody help
SURESH IYER
Following is my table and its content:YearTargetedBudgetFirstQuarterSecondQuarterThirdQuarter-----------------------------------------------------------------------------------------------------20002500012000110001000200135000220002100020002002450003200031000300020035500042000410004000I want a query which returns the result in this format:Yr_Col1Yr_Col2Yr_Col3Yr_Col4----------------------------------------------------20002001200220032500035000450005500012000220003200042000110002100031000410001000200030004000There could be many work around, but what would be fasted single queryfor this?
View 8 Replies View RelatedI am using ReportViewer Control on my asp.net page. Report consists of three datasets with each having its own table. All thses three tables are vertcally and horizonatlly aligned and have same numbers of columns. Report looks like a homgeneous single report. Output of report is on three pages since there are many rows. Page 1 and 2 displayes rows from dataset1/table1(it has large amout of data compared to other two datasets) and last page has remaining rows from dataset1/table and and rows from dataset2/table2 and dataset3/table3. While on page 1 and 2 vertical scrollbar runs fine but on page 3 vertical scrollbar crashes as soon as one tries to scroll, with follwoing
errror message:Microsoft JScript runtime error: 'children.0.children.0.children.1.children' is null or not an object. Horizontal scrollbar runs ok without any problme. While in preview mode and after deploying and viewing on server it does not crash. It crashes only while viewing on webpage in asp.net application.
By setting FixedHeader property to false the crashing behaviour is stopped.
But now how to achieve FixedHeader without crashing is question.
Any help to solve this problem will be highly appricated and I would like to thank you in advance.
Thank you.
Bharat Gadhia
I have a table as follows
opendate (datetime) callnumber (int) closed (bit)
I want to find how many calls were opened today and of those how many are closed
I have come up with the code below but again am looking for
1. a more elegant solution
2. a way to generalise this to show the same information for x number of days
create table #holdit1
(opencount int)
create table #holdit2
(closedcount int)
insert into #holdit1
SELECT count(*) as opencount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 0
group by closed
insert into #holdit2
SELECT count(*) as closedcount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 1
group by closed
select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls
from #holdit1 cross join #holdit2 #holdit2
DROP TABLE #holdit1
DROP TABLE #holdit2
this gives me
CallsOpen CallsClosed AllCalls
----------- ----------- -----------
1 3 4
Hi,
What I need is the following, a StoredProc that make a Vertical (normal table) into a Horizontal Table :
So From:
CustNo__Item
__1______1
__1______3
__1______4
__2______2
__2______3
__2______7
To:
CustNo___1__2___3___4___7
_1_______X______X___X
_2__________X___X_______X
Anny help would be verry appriciated
Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1 a 2 b
3 c
How can i do this with select statement or inner join?? or an posible way , please help me
Thnks
Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1 a 2 b
3 c
How can i do this with select statement or inner join?? or an posible way , please help me
Thnks
Hi,
I'm using Rpt Svc in SQL 2000. I have a report that uses a table data region. Is there a way to control placement of vertical page breaks? As in when the page is just too wide to print on a single sheet of paper.
Thanks.
I want to append vertical row result into horizontal
CREATE TABLE #mable(mid INT, token nvarchar(16))
INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')
Actual output
[code]....
Hi,
Is it possible to display Report headings text vertically bottom-up?
It goes from top to bottom by default now, which seems unusual.
Thanks,
Alex
From:
Cust | Qstion | Answer
_________________________________
1,1,Bike
1,4,Blue
1,6,No ensurance
2,1,Car
2,3,silver
2,9,ensurance
2,11,Yes
To the following
Cust______1______3______4______6______9______11
__________________________________________________ __________
1_______Bike___________Blue__No ENsur_________
2_______car____silver_________________Ens____Yes"
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select
count(case when salesman Like 'Geo%' then id else null end) As [George]
,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
100 50 10 90 150 100
And I want the data to be displayed like
George - 100
Lista - 50
Jorge - 10
Richard - 90
Team 1 - 150
Team 2 - 100
This is SQL Server 2008 if that matters.
I liked the default appearance of SSMS in 2005 and 2008. 2012 is lousy by default.
My quesion is whether it can be made to approximate the way it behaved in 2008.
What I liked: Registered Servers and Object Explorer resided on nested vertical tabs on the left-hand side of the screen. Queries stacked up on the right-hand side of the screen.
I managed to get the Registered Servers and Object explorer to display with nested vertical tabs (tabs at the top, rather than the bottom - that's ok). But if there aren't any other vertical tabbed displays, then the tab on top fills the screen. There's no point to that. Both Registered Servers and Object explorer are narrow trees. The rest of the screen is white nothingness.
If a query is opened, it then fills the screen - empty. If I want that in a vertical tab I have to manually make it one (right click, choose New Vertical Tab Group).
s there a way to make the doggone thing behave?
The way I got the Registered Servers and Object explorer to behave this way was to right click on a tab and play with the vertical tabbing options.
create table #temp1
(
col1 float,
col2 float,
col3 float,
clo4 float)
insert into #temp1 values (1.5, 1.6,1.7,1.8)
insert into #temp1 values (1.9, 1.0,1.2,1.8)
o/p should display as below is there a way we can do this with cte or some other option
col1 1.5 1.9
col2 1.6 1.0
col3 1.7 1.2
col4 1.8 1.8
Hi All,
I am trying to understand, when would I do a vertical partition in a Dimensional Data Warehouse ? What are the things I need to consider, before I take the decision?
Necessity is the mother of all inventions!
TaskID PID task Milestone
83manasi task2
83manasi task3
83manasi task4
Above is the query of the result
I want to show this resultset horizontaly
for that I have to use the cursor such that
@M1=Where Milestone=1
@M2=Where Milestone=2
@M3=Where Milestone=3
and putting this into a temp table with its projectid and taskid.
Plz guide me that how to achiev this exactly
Swati
Whenever I open a new query I see 3 vertical lines on the screen, from top to button. Did a full reinstall, and they are still there. How to get rid of them?
View 7 Replies View Related
hi,
i have a sales database as follows
InvNo Date Customer ProductID Qty Rate Amount
1 01/04/2008 XYZ Pro1 100 10.15 1015.00
2 01/04/2008 ABC Pro1 100 10.15 1015.00
3 03/04/2008 XYZ Pro2 100 10.15 1015.00
4 04/04/2008 XYZ Pro2 100 10.15 1015.00
5 05/04/2008 ABC Pro2 100 10.15 1015.00
I Want to generate a daywise report for each product as foolows
ProductID 01/04/2008 02/04/2008 03/04/2008 04/04/2008 upto 30/04/2008 ( may be upto n date)
Pro1 200 - - -
Pro2 - - 100 100
Is it possible using the report viewer (*.rdlc) reports in VB.Net 2005
I have a Chart report which is larger than the PDF width, is it possible to make it vertical to fit the PDF size ?
View 7 Replies View RelatedMonth Discount
Expenses GST
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d14582be-eecd-4def-be7e-0cfe00b13c80" id="4279c12f-e03f-4b38-9fc8-eb1a991c25ac"><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7f0da910-3775-4ee4-821e-1a0e7ee2ce0b"
id="92ce074f-ee0b-4794-839b-ee50c88d380c">ServiceCharge</gs></gs>
[Code] ....