Can you please help me on resolving this puzzle. I will appreciate your help.
At the botton is the data in one of the column (combined) in one of the table.
I need to split these data and insert into 4 different columns.
Structure of the data is this: eg: if we take the first value in the following description ie: n1^m1^d1^s1 contains 4 different values for the future insert eg: n1 goes to Name column, m1 goes to Title column, d1 goes to Date column and S1 goes to salary column (This is grate).
And this information is separated by ^. But This combined column can have multiple values for several employees such as in 2nd value in the following data ie: n2^m2^d2^s2~n3^m3^d3^s3 (this is grate too). here n2 is a employee Name where as n3 is another employee name. These set of data is differentiated by ~.
This combined column can have multiple no of employees information (separted by ~).
Note: The employee may have some of the information missing in its corosponding set of data such as if you look at the 4th set of data, ie: n5^^^. here we only have employeename information not other informations. another eg: if we take the last row ie: n7^^^~n8^^d8^~n9^m9^^~n10^^^s10 second set of data (n8^^d8^) has name(n8),null value for Title,d8 value for date,null value for salary.
So in a nutshell I need to split this data and load into 4 different columns in that table. which data goes which column is totally depend on the ^ and ~ separators.
For eg: 1) The output for the first row data should be like:: for name n1, for title m1,for date d1, for salary s1
2)The output for n6^m6^d6^ should be like : for name n6,for title m6, for date d6, for salary null
Thanks guys, This is making my head spin already and can you guys take a spin of this problem and help me.
I need to export the data directly using a query from sql server. This is just a temporary extract. Copy pasting the result in excel is giving mis-alignment.
Im using this query to select ,calculate and format data like Refer here for more understanding:-
Select DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime, Avg(Meter1) As Meter1, Avg(Meter2) As Meter2, Avg(Meter3) As Meter3 From TableName Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)
I want the output of the query to be written in the excel Sheet.
This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro. Please help me.
i want to automate the exporting of data from an excel database to sql using dts.i don't want this to do manually.can u help me out in solving this.what are the things i have to do for this ?expecting u'r earliest reply
hi everybody,iam soory maybe my question is easy, i heard about a product that cansolve such problems, but i forgetted.the problem:we have several foxpro6 systems, each one with seperated DB.Now we build a new complete DB for all systems using MS_SQL Server2000, we need to transfer/export old Data to new DB taking allsituations such as the data for one system (SALARY system) thatworking on employee table is not comaptible with other data from othersystem (emplyees system) that worked on the same table employee. Howwe can solve such issue using MS products or other products ifavailable.thanks inadvance for any advice.best regardseng thaarCNS
Hello, I have a report that I am creating in sql reporting services, I want my report to be exported in landscape PDF file format. I Went to the report properties then set the width = 11 in and height 8.5.
When i export my report to a PDF file it did not display it in landscape format!
Do i need to make the report go sideways? if yes how! for example
how would i write a textbox which has the label Time sideways?
Can anyone give me an idea where I can find the equivalent SQL scripts for the GUI process(DTS) for importing or exporting any DB from a given ODBC source. Thanks!
How can I Export Database with foreing Key and primary key.
Operation is that SQL2005 Management Studio/Database/Tasks/Export Data
Before Version is SQL2000 we can Selected Copy Object and data between server and then Use Default Options click checked and Select Copy Index, Copy Foreing Primary key vs vs
But this options is not found in the SQL2005 Management Studio/Database/Tasks/Export Data wizard or I can't found it.
How can I export foreing Key and primary key with SQL2005 Management Studio/Database/Tasks/Export Data wizard.
' File: Test.rss Dim format as string = "Excel" Dim fileName as String = "C:Export2.xls" Dim reportPath as String = "/TimeByEmployee"
Public Sub Main()
' Prepare Render arguments Dim historyID as string = Nothing Dim deviceInfo as string = Nothing Dim showHide as string = Nothing Dim results() as Byte Dim encoding as string Dim mimeType as string = "ms-excel" Dim warnings() AS Warning = Nothing Dim reportHistoryParameters() As ParameterValue = Nothing Dim streamIDs() as string = Nothing rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(3) As ParameterValue parameters(0) = New ParameterValue() parameters(0).Name = "Employee" parameters(0).Value = " " parameters(1) = New ParameterValue() parameters(1).Name = "Departments" parameters(1).Value = " " parameters(2) = New ParameterValue() parameters(2).Name = "Week Number" parameters(2).Value = WeekNumber results = rs.Render(reportPath, format, _ Nothing, Nothing, parameters, _ Nothing, Nothing, encoding, mimeType, _ reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report Dim stream As FileStream = File.OpenWrite(fileName) stream.Write(results, 0, results.Length) stream.Close()
' File: Test.rss Dim format as string = "Excel" Dim fileName as String = "C:Export2.xls" Dim reportPath as String = "/TimeByEmployee"
Public Sub Main()
' Prepare Render arguments Dim historyID as string = Nothing Dim deviceInfo as string = Nothing Dim showHide as string = Nothing Dim results() as Byte Dim encoding as string Dim mimeType as string = "ms-excel" Dim warnings() AS Warning = Nothing Dim reportHistoryParameters() As ParameterValue = Nothing Dim streamIDs() as string = Nothing rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(3) As ParameterValue parameters(0) = New ParameterValue() parameters(0).Name = "Employee" parameters(0).Value = " " parameters(1) = New ParameterValue() parameters(1).Name = "Departments" parameters(1).Value = " " parameters(2) = New ParameterValue() parameters(2).Name = "Week Number" parameters(2).Value = WeekNumber results = rs.Render(reportPath, format, _ Nothing, Nothing, parameters, _ Nothing, Nothing, encoding, mimeType, _ reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report Dim stream As FileStream = File.OpenWrite(fileName) stream.Write(results, 0, results.Length) stream.Close()
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Background: In my current company the business users maintain a huge quantity of master data using excel. Then a series of SSIS jobs are edited and manually executed.
Goal: the challenge is to replace this process using MDS. One of the requested features is the possibility for the users to edit or insert new master data using the Web UI or the Excel Add-in and when they are done perform a merge of the master data in the target, in this case in the reporting DB.
The perfect solution for me is something like trigger the execution of a SSIS package to export the data from the subscription views to the reporting DB after the business rules are apply to a specific entity.
I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,
Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
There are 2 tables: 1. ItemMaster Table: This table contains all the available Items (around 500,000). Fields are: (a) ItemId Integer (PK) (b) ItemDetails Varchar(100)
2. ItemImage Table: This item contains the location of picture of few items out of above table (around 10,000). Fields are: (a) ItemId Integer (PK) (b) ImageURL Varchar(50)
Requirement: I want to create a query or stored procedure, which would return me: ItemId, ItemDetails and a flag to indicate if image is available for that item or not.
Can anybody help me out in getting this flag which has to be build dynamically in the query as its not there in the tables. And what would be the query for acheiving the desired result.
Hi, I am a new SQL programmer. I need your help for writing a query. I am able to get the desired result set by looping through but it takes lots of time and resourses to run and crashes my machine. Is there any way to write a sql statement which performs batchwise? I really appreciate your help.
Following is a data from a view called ind_results.
last_name first_name course_code course_title score ------------------------------------------------------------------------- Walker Jay 1 Mid-Term Exam 71 Walker Jay 2 Final Part I Online Exam 64 Walker Jay 3 Final Exam Part II Online Exam NULL
Reed Steve 1 Mid-Term Exam 70 Reed Steve 2 Final Part I Online Exam 78 Reed Steve 3 Final Exam Part II Online Exam 80
I need help in writing a query, where
1) If Score is null for either course_code 2 or 3, display score = 'incomplete' for course_code 2 and 3 (for Jay Walker) 2) If Score is not null for either course_code 2 or 3, display score = average value of score for course_code 2 and 3 (For steve Reed)
i.e. the resultset should look like this.
last_name first_name course_code course_title score ------------------------------------------------------------------------- Walker Jay 1 Mid-Term Exam '71' Walker Jay 2 Final Part I Online Exam 'incomplete' Walker Jay 3 Final Part II Online Exam 'incomplete'
Reed Steve 1 Mid-Term Exam '70' Reed Steve 2 Final Part I Online Exam '79' Reed Steve 3 Final Exam Part II Online Exam '79'
I am trying to write a query where I get the following result. Basically I have to display latest part_number in the chain with status as 'current' for any part number that is superceded by another part_number.
I have written a code using cursor. But I just learnt that cursor should not be used. So I am looking for different syntax so that I can learn programming without cursor.
Hello I have the following query that is producing the following errors;
Code: declare @temp_request as varchar(8)
Select request as temp_request,BUSINESS_REQ_ID,TEST_CASE_SOURCE as SOURCE from TEAM3B_PULL_TOTAL_TST if (not exist (select * from TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST where REQUEST = temp_request)) begin
Insert into TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST(request,bu siness_req_id,source) select request,business_req_id,test_case_source from TEAM3B_PULL_TOTAL_TST where request=temp_request end
Else
begin Insert into TEST_REPORT.dbo.RDD_UPDATE_TST(request,business_re q_id,test_case_id,test_case_descr) select request,business_req_id,test_case_id,test_case_des cr from TEAM3B_PULL_TOTAL_TST where request=temp_request end
Errors:
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near ')'. Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'Else'.
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
I have a report which has a table and that table has 4 columns
I want to represent the Data like this.
Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor
Years of Service Vesting Years of service Vesting
1 40 1 50
I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes
Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.
I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:
WITH SET [FilteredAccountSet] AS 'order( [Acc€”BU_ Account_BCDsc].[Account],[Measures].[_MeasureBillingCode],ASC)' SET [FilteredBUList] AS 'order( [Acc€”BU_ Account_BCDsc].[Business Unit],[Measures].[_MeasureBillingCode],ASC)' SET [FilteredBillingCodeSet] AS 'order( [Acc€”BU_ Account_BCDsc].[Billing Code Description],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredEngagementGroupSet] AS '[Acc€”BCEngTypeGroup_BCDsc].[Billing Code Engagement Type Group]'
MEMBER [Measures].[AverageTeamSize] AS '[Measures].[Consultant Utilization]/(COUNT(GENERATE({[Date€”Year_Month_Day]. [Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[May]: [Date€”Year_Month_Day].[Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[July]}, DESCENDANTS([Date€”Year_Month_Day].CURRENTMEMBER,[Date€”Year_Month_Day].[Day]))))'
OPEN orabaan declare @batch int, @ttype char(3), @doc int, @ttdr float, @ttcr float, @diff float, @s1r int, @s2r int
WHILE @@FETCH_STATUS = 0
BEGIN FETCH NEXT FROM orabaan
INTO @batch, @ttype, @doc, @ttdr, @ttcr, @diff
if @diff < 0 update ttfgld102777 set t_amth_2 = (t_amth_2 + @diff) where exists ( select top 1 t_amth_2 from orabaan where t_dbcr = 1 ==> and t_btno = @batch and t_ttyp = @ttype and t_docn = @doc ) ==============================
I want to add a clause to auto update t_btno when value occur as "t_btno" is variable int
