Basically this is ONE table that contains header and detail data ordered sequentially. There are not unique identifiers for the rows. The rows are ordered sequentially so that each SALESPERSON is followed by one or more CLIENTs.
If I could merge the rows, the result would look like:
SALESPERSON SALESPERSON_ID CLIENT DATE_FROM DATE_TO AGE
TOM 12345 MARYSMITH 1/1/2008 12/31/2008 46
TOM 12345 JANEDOW 1/1/2008 12/31/2008 24
ED 56789 TOMJONES 1/1/2008 12/31/2008 65
ANTHONY 243546 BEVBLACK 1/1/2008 12/31/2008 15
ANTHONY 243546 JEANTHOMAS 1/1/2008 12/31/2008 29
I am not how to do this with this data.
I also thought maybe it would be better to add unique identifiers to each set of SALESPERSONs/CLIENTs, and work with the data that way, but I am not sure how to do that.
Any help or suggestions would be appreciated. I have no ability to change this data - I have to try to work with it if possible.
I have some data in Excel - sheet1 which would be static.
Now I need to import data from SQL Server (using a query) and Union above static data with this SQL data and later I will have to create PowerPivot table in Sheet2.
Which is suitable option for me to import data from SQL to excel as I see "From SQL Server" option under "Data" and "Power Query" tab as well.
How to merge above SQL data with existing static data?
I have 2 tables that have the exact same structure and have some common values. T1 = a,b,c,d,e T2 = e,f,g,h
I want to create a single distinct table which captures all unique in both tables. I have a common fields ID in both, I can match on. I don't want duplicates which is what's happening now..
I have a function that returns a range of numbers and a query that returns a varchar field and an int field. I would like to replace the int field in the query with unique ranged numbers from the function(one per row).
Any ideas?
Thanks, Mark
-- use ifms
select * from dbo.fnRange(5101,5152)
select --distinct s.OrgKeyId, 10420 TableNumber, 5100 DetailCodeNumber, s.NewStratalabel CodeDescription, s.NewStratalabel CodeAbbreviation, 1 CodeActiveFlag, Getdate(), 0 distinct s.NewStratalabel, 5100 from _StrataLabel_xref s Left Outer Join(select * from org_Detail_Code where tablenumber = 10420 and orgkeyid in (Select distinct OrgkeyId from _StrataLabel_xref)) o on o.OrgKeyId = s.OrgKeyId and o.CodeDescription = s.NewStratalabel where o.Detailcodenumber is null order by s.NewStratalabel
I manage to get the SSIS working. Now I would need to do these tasks.
I first want to get data from 2 different sql servers. What would be the best method to accomplish this? Both are in Sql Server 2005.
Secondly I want to make sure if any of the servers couldn't be found on the network or in any case the getting data task failed for any one of them the package won't continue and an email should be send to an email address.
Thirdly If everything is ok then I should combine both and generate one sequence no for them and save them on to another location and then generate a file with modified values.
I have two tables say A and Archive. After a certain period of time some records are to be sent to archive table.To copy records to archive table I am using SqlBulkCopy operations.Now I have to delete the records from A Table. I was thinking of sending a Comma seperated id's of rows that are to be deleted to a stored procedure.Are there any better techniques to move data to archive table and to remove data from main table.?Thanks.
Hello, i have a database which is subscrribed into two databases. It is subscribed to one database throught a PUSH SUBSCRIPTION and it is subscribed to another with PULL SUBSCRIPTION. The problem is that it is correctly syncronized with PUSH SUBSCRIPTION publication but it does not merge data correctly to PULL PUBLICATION.
When i try to run Merge Agent from subscriber it gives me following error:
Retrieving publication information Retrieving subscription information The specified subscription type is invalid. Category:NULL Source: Merge Process Number: -2147201020 Message: The specified subscription type is invalid.
P.S : When i run Merge Agent from EM, it says, " No Data to be merged "
We have an MIS system which has approx 100 reports. Each of thesereports can take up to several minutes to run due to the complexity ofthe queries (hundreds of lines each in most cases). Each report can berun by many users, so in effect we have a slow system.I want to seperate the complex part of the queries into a process thatis generated each night. Then the reports will only have to querypre-formatted data with minimal parameters as the hard part will havebeen completed for the users when they are not in. Ideally we willgenerate (stored procedure possibly) a set of data for each report andhold this on the server. We can then query with simpler parameterssuch as by date and get the data back quite quickly.The whole process of how we obtain the data is very complex. There arevarious views which gather data from the back office system. These arevery complex and when queries are run against them including othertables to bring in more data, it gets nicely complicated.The only problem is that the users want to have access to LIVE datafrom the back office system, specifically the Sales team who want toaccess this remotely. My method only allows for data from the nightbefore, so is there an option available to me which will allow me todo this ? The queries can't be improved on an awful lot, so they willtake as long as they take. The idea of running them once is the onlyway I can see to improve the performance in any significant way.True I could just let them carry on as they are and let them sufferwith the performance on live data, but I'd like to do something toimprove the situation for them.Any advice would be appreciated.ThanksRyan
Working on sql integration with hybris. i was new to this hybris environment previously my job was completly admin part. Now i got a project where i need to fetch data from hybris (Data Hub) and then i need to maintain the same records in sql server with same attributes and sometimes we need to add new attributes and tables even.
How we can import data from the excel sheet to sql server. For this i have gone through 2 approaches whether using merge statement through stored procedure or SSIS package (I don't know whether it works out for the same to merge data in sql server, if it works). Which of the procedure is more efficient. previously my tasks were completely admin tasks and i dnt have much knowledge about developing part.
I've seen posts about merging 2 databases but everyone is talking about 2 SQL based databases. I have a data cube based on MDX query giving me all the Sales data. I have a Excel sheet with the Budgets in it, thanks to the connector i can browse through my Excel file using SQL statements.
Now the problem that i have is i want to merge these 2 data's into one report i want to provide Sales figures from the cube with the budgets next to them which are stored in the Excel file.
So basicly is it possible to merge an MDX based database with an SQL database ? Cuz all the solutions i have found to far is by joining databases which is always done on SQL level.
i'm new to this and i am trying to create a statement that would add the values together for Hong Kong and China and then delete the Hong Kong road. (this is a example what i am trying to acheive)
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
I don't know the correct syntax to accomplish this. I've looked in the on-line help but am not coming up with anything.
Basically I have two identically structured tables in two separate databases.
I wanted to select anything from the dbo2.table that isn't in the dbo1.table and insert it into dbo1.table.
I thought I could do something like... quote:
Select INTO dbo1.table
and then add my select statement. But that throws an error: quote:Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named 'bkupproceduresfeeschedule' in the database.
And the on-line documentation says an INTO is only for a new table.
How can I merge records from one table into another table?
ApplicantID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Owner
OwnerID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is 1. how do i get the last three numbers to increment each time a new application is created? 2. how do i retrieve the last record in the table??? 3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???
Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.
Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).
I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.
This info in this table should:
Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)
Be updated: if the customer order has been updated (i.e. the commission was changed)
Be inserted: if the order exists but the order number is not in the new table.
hi i have written a procedure for stock report. its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks
Note: User is required to execute this procedure daily. i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
CREATE PROCEDURE dbo.spUpdateStock @strReturn varchar(70) output AS BEGIN declare @maxDt smalld atetime if exists(Select * from Stock_Dump where Txn_Date= Convert(varchar,Getdate(),101)) BEGIN set @strReturn='Stock Table already generated for the day. cannot generate it again' END
ELSE BEGIN TRUNCATE TABLE Stock_Dump_Temp select @maxDt=max(Txn_Date) from Stock_Dump /* insert (Opening stock) Closing stock for all all the products from last max Date*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date, Closing_Stock as Opening_Stock , 0,0,0,0,0,0,0 from Stock_Dump Where Txn_Date=Convert(varchar,@maxDt,101) /* Issues*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0, Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details Where Issue_No IN(Select Issue_No from Issue_Hdr Where Issue_Date > Convert(varchar,@maxDt,101) and Issue_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Goods receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details Where Dlv_Note_No IN(Select Dlv_Note_No from Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and Dlv_Note_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Rejection after receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,Sum(Qty) as Rejected,0,0,0,0 from Rejection_Details Where Rejection_No IN (Select Rejection_No from Rejection_Hdr Where Rejection_Date > Convert(varchar,@maxDt,101) and Rejection_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Issues returns*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,Sum(Qty) As Issue_Returns,0,0,0 from Issue_Return_Details Where Issue_R_No IN(Select Issue_R_No from Issue_Return_Hdr Where Return_Date > Convert(varchar,@maxDt,101) and Return_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical Stock + */ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from Physical_Details Where Var_Qty>0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical -*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from Physical_Details Where Var_Qty<0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/ INSERT INTO Stock_Dump Select Product_code,Txn_Date, Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as Rejected,Sum(Issue_Returns) as Issue_returns, Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp Group By ProducT_Code,Txn_Date /* update closing stock*/ UPDATE Stock_Dump Set Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M)) Where Txn_Date=Convert(varchar,Getdate(),101) /* delete unwanted records */ DELETE From Stock_Dump Where Opening_Stock=0 and Issue_Qty=0 and Purchase=0 and Rejected=0 and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0
set @strReturn='Stock Table Update Successfully' return END
END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..
Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code
Code Snippet Dim constr As New SqlConnection(PVDBConn) Try 'Variable to hold the results Dim results As String = String.Empty cmdUpd = New SqlCommand("SelVis1Name", constr) cmdUpd.CommandType = CommandType.StoredProcedure constr.Open() 'Set results to the value returned from ExecuteScalar() results = CType(cmdUpd.ExecuteScalar(), String) constr.Close() 'Set our buttons text to that value Button1.Text = results Catch ex As Exception MsgBox(ex.Message.ToString) End Try
At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?
And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.
I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?
Hi All, I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?
Thanks to all for your time and advice in advance.
I'm looking for some help on how i should index this table.
current table has about 500k records in it. the fields in the table are: member_num (varchar(12), not null) first_name (varchar(20), null) last_name (varchar(20), null) ssn (varchar(50), null) address1 (nvarchar(200), null) address2 (nvarchar(200), null) city (nvarchar(200), null) state (nvarchar(200), null) zip (nvarchar(100), null) phone1 (nvarchar(50), null)
all of the fields are searchable through an asp.net webform.
my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.
What I have. I have a spreadsheet that is used in 4 or more locations on a daily basis by 1-3 ppl per locations. The spreadsheet is used to gather Quality Control information. So everyday there are a couple of spreadsheets from each system that is used to generate weekly and monthly reports. This is becoming to much work and I would like to automate the process.
What I have access to. I currently run a Sharepoint 2007 Server for all our collaboration and document needs. I also have the ability to setup any sql server.
What I want. I want the QC techs in each system to be able to upload the data at the end of each day and be done with it. This way they do not have email or do a weekly report. I would prefer to use Sharepoint and create reports weekly and monthly that can be pulled just by going to a site.
I'm knowledgeable in Sharepoint and Excel. I have some skills in VBA. I haven't dealt with SQL any, but willing to learn. Also I'm knowledgeable in Microsoft Access as well.
Any suggestions on how I could accomplish this would be appreciated.
Well, as a VB/VBA applications developer I'm not well prepared for this, but it looks like I will be riding herd on a production SQL Server.
TSQL I know well enough to get along, but where can I get a fast fix on all the logins, security, and process management info? Today we had a DTS package crash overnight and it took me forever to figure out that it had left half a dozen tables locked. (Note that the scripts for the DTS package are being re-written as we speak with use of transactions and NOLOCK.) Meanwhile tech support was handling a whole mess of grumpy users.
Are there any books you would recommend as resources/references? Is there a particular author who is good at writing the stuff you really need to know in English that can be read by a mere mortal like I? I am fond of the Microsoft resources/help files but I'd like to have somthing that holds highlighter and post-it flags a bit better. Not to mention something that focuses more on the beast as a whole rather than the minutia at length.
I have a set of tables with about the same structure
dataID, recordID, 15 other columns
dataID is unique but is never referenced in queries
recordID is one of the most referenced columns but only has a cardinality of about 30%
The current structure has a clustered PK on (dataID,recordID)
Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance any
After staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.
create a non-clustered PK on dataID create a non-unique clustered index on recordID
Let me know if any other information is needed. Thanks
I would be teaching an applied database course to buisness majorundergrads. I'm looking for a book that introduces database conceptsusing SQLServer as the database. I would really appreciate if you couldrecommend me a few such books.ThanksNemo
I'm trying to count the number of records in 'game_dates' where thecolumns home_team_id or away_team_id have the same value. E.g., iwant to know the number of records for each team_id where team_id ishome_team_id or away_team_id.I'm doing this in two separate select statements now. Example:SELECT count(home_team_id),home_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY home_team_idandSELECT count(away_team_id),away_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY away_team_idand then combining the results. Is there anyway to combine these toqueries into one query? ...and have a single result set returned withtwo columns (count,team_id)?Thanks,Glenn
Need some suggestions for senior management for DR Purposes:
Background:
WSS/MOSS2007 is being used as a Document Management solution.
17 Servers geographically dispersed around the UK. Each server runs WSS 3, SQL Server 2005 and IIS. Each server is linked into a PiP cloud via 2MB MPLS.
At each location; We are looking at 20 core databases; each pre-sized to 10GB. If I take one site as an example, the previous nights backup totalled 135GB.
The company has taken a centralised view on backup's, so SQL Server Data and Log files are replicated using Double-Take to a central location where by the files are taken onto tape daily (Full backup of all files).
As a precaution, I take a Full SQL Server backup daily and also Tran Logs every 4 hours locally and keep it there for 2 days; however if the site goes boom I loose those, so for this purpose; please forget they exist.
As I expect; when I restore the mdf and ldf files from tape, I will get errors when I attach those files into SQL Server for transactional inconsistencies which I'm well aware of.
Other options I've considered are:
1) DB Mirroring. Not a bad option, but still have to get the DB to the Mirror Server in the first place. Also DB Mirroring is not recommended for more than 10 mirrored databases.
2) Log Shipping. Same issue as above; Have to get the data here in the first place. Then once Log Shipping is setup; if I have a failure; I need to start the whole lot off again.
3) Transactional Replication. Issue is with the initial replication getting the data from A to B, then if I need to use it in a DR situation; I will get issues saying this table is being used for replication. This can be worked around, but it's a not a quick process...
4) 3rd Party Backup Compression. E.G. Litespeed; Redgate SQL Backup, etc. Good; Tests have shown a 42% compression for us, however if I refer to the earlier example of 135GB, this compresses to 81GB. Throw in the theoretical max for a 2MB link of 19GB / 24 Hours, this would take 4 Days to copy.
Other thoughts I've come up with are:
A) Split the tables into different file groups; not sure how easy this would be as the DB's and Tables already exist.
B) Full/Diff/Tran. Still have the issue of scheduling the full backup over the weekend and taking 4 days to get here.
C) Local Tape Backups. Issue is relying on someone to change the tape on a daily basis. It's not centrally managed and how do we restore in a DR situation ?
