I have data as below:
columnA
D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0
How can I separate it to 5 additional columns?
columnB: D7 330 4/13/2014 0:0:0
columnC: KUL PVG 4/13/2014 18:35:0
columnD: 4/13/2014 19:15:0
columnE: 4/13/2014 23:45:0
columnF: 4/14/2014 0:45:0
Hi
I have an excel source which is a 41 column sheet. The excel filepath is stored in a table and captured into a variable. The excel source import is contained within a foreach loop and will loop through each file and continue until all the excel files are processed. It works fine until it gets to the last file. The import then fails with the following error:
The column "F42" needs to be added to the external metadata column collection.
The column "F43" needs to be added to the external metadata column collection.
The column "F44" needs to be added to the external metadata column collection.
The column "F45" needs to be added to the external metadata column collection.
The column "F46" needs to be added to the external metadata column collection.
The column "F47" needs to be added to the external metadata column collection.
Now when i open the excel sheet and hit CTRL+END the cursor goes to a column 6 to the right of the last column with data in it, effectively column 47 where column 41 is the end of my data.
I guess that the jet engine is trying to import these additional columns but because i am not expecting them there is no destination set up for them in the OLEDB destination and susequently the metadata needs to be added. I do not want to do this as these are excel files originating from the client and i cannot control how many additional columns they are going to "add".
Does anyone have any ideas as to how i can solve this? Is there a way of identifying the last column with data and only importing those columns?
Thanks in advance for any help or experience of this issue
In Transactional Replication -while Publisher, Distributer & Subscriber are on same instance- can I have additional columns in destination Table & set "Action if name is in use" for related Article to "Keep existing object unchanged"?
I do this but even Snapshot is not transfered to destination table!!
I'm somewhat new to report builder and have been trying to recreate a report previously created in an Excel Pivot Table. I'm encountering an issue arranging the data the way it's arranged in Excel.
Specifically, I would like the values column to precede an additional column.
Until I can post pictures I'll have to try and mock it:
COLUMNS
Values
Results (my data I want as a 2nd column)
I can't figure out how to get report builder to do it the same way. Whenever I add the 'Result' data as a column it always appears on top. I'm guessing what I need to do is somehow get result set as a child of the first Static group, but I'm unsure how to do that.
I have a value in a column '01-08-087-0101W5'. I would like to break this value down into 5 different columns. The column will be broken down at the dashes , so the 5 columns will have values like
01
08
087
101
5
I would also like to trim any leading 0's in the above values. i have been trying the following
SUBSTRING(column1,patindex('%[^0]%',column1) ,2) AS FROM_TWP
I have two columns "Project Name" and "Flag".
Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.
I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.
It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:
Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.
These errors occur regardless of what columns I am attempting to add to the lookup list.
I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.
Any ideas on what else could be causing this?
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
I have a address column in one of my databases that I need to split out by carriadge return but don't know what to start..This is my example query
SELECT
Address
FROM
PUB."Address"
Where "Id" = 123456
3 The StreetMayfairLondonEngland...I would like the results to show
Either
3 The Street
Mayfair
London
England
Or the same but horizontal
with varchars having a max of 8000 bytes and partly because of the coder before me, I'm stuck with using text. the text has a maximum of 30,000 characters, i've tried everything to try to separate the text into 4 different varchars to no avail.. none of the string functions (left, substring, right) works on a text field, and when i try to convert it to a varchar (i.e. convert(varchar(8000), @text)) it obviously only takes the first 8000 characters...
what can i do in this case? any help would be appreciated! thanks in advance!
i have written a query I was SO proud of but THEN was told to separate the values out of one column. simple way to do this without having to rewrite the whole query? Will I need to include a Case statement?
Basically the column contains several types of vacation codes which will need to be renamed to 2. So there is leave 1, leave 2, and leave 3 and sick. I will need to rename all leave 1, 2, and 3 to just vacation and leave sick as sick.
I have column with location for all reports, dashboards, images, pages in URL format from Sharepoint audit database. Like
sites/fm/finance/PowerPivot Gallery/FinancialStatement.rdlx
sites/ea/Mn_medical/Pages/Medical_DP/3 Site Compare.aspx
There are millions of rows like the above URLs. I need to separate all the strings from that ULR separated by "/" and make column for each so that I can create a hierarchy in tabular model. How do I write a sql for that. I could get first one and last ones with these kinds of substring:
SUBSTRING( b.DocLocation , LEN(b.DocLocation) - CHARINDEX('/',REVERSE(b.DocLocation)) + 2 , LEN(b.DocLocation) )
This gives me last piece of ULR. How I do I get all the middle pieces of URL?
I know of several methods to remove duplicate records but I recently encountered a unique situation where some duplicate records were actually acceptable.
Here is my situation:
I have a table that contains records of individuals who have children so 1 person can have 3 children with different birthdates; but there is also a field that has a specified language. The challenge arises when an individual may have only 1 child but has entered a single record twice. Once with a specifed language and again without which produces a default value of UNKNOWN.
I need to be able to remove this record without affecting records that may have a record entered twice as well; having 2 children for instance but they may also have a specified language in one record but a default value of UNKNOWN for their second record.
So I can't eliminate the unwanted duplicates by filtering out records that have UNKNOWN because I would also remove individuals that I need.
EX:
firstN | lastN | address | lang | childs birthdate
John Doe 210 Somewhere Ave ENG 1993-10-09
John Doe 210 Somewhere Ave UNK 1993-10-09
Jane Doe 210 Anywhere Ave ENG 1969-12-23
Jane Doe 210 Anywhere Ave UNK 1958-04-15
How could you remove the duplicate for John in this example without affecting Janes duplicate record which is actually ok because she apparently has 2 children with different birthdates whereas John's duplicate record is obviously created because it was entered twice; once without entering
a language and the second time specifying the language?
I have tried a number of things short of creating a cursor which isn't really the best way to resolve this issue since there are millions of rows.
Anyone out there have any input that be helpful? Or has anyone ever had this similar issue? I would be interested in knowing how you addressed the problem.
Any help is appreciated. :)
Below are the same data
with Sample(Size) as (
select '16.3 Oz.' union all
'1' as union all
'2 Tablespoons' union all
'46. Oz. Each' )
Iwant to separate number and alphabets. But i wanted to keep the dot to have decimal values. Expected output
select '16.3' as val1 'Oz' as val2 union all
'1' val1 as union all
'2' as val1 'Tablespoons' as val2 union all
'46' as val1 'Oz Each' as val2
Please note that i need to remove the extra dots at the end of the the val1 and no dots in val2
I rid some of the functions like dbo.fn_StripCharacters and dbo.fn_GetAlphabetsOnly ffound in the internet. evey with my own logic. i couldn't remove the dot which appear at the end of val1
I am trying to build a DIM table using a source table that has the following setup...
CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]....
What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table. I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...
CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]...
Hi folks. I am sure this is a common situation so there shouldn't be any problems ;-) I need to run a web server and a separate database server, but am having trouble working out what username I can use in my SQL database. Here is my setup:Web server: [Windows Server 2003, IIS6, ASP.NET 1.1] The anonymous web account for the website has been changed to one created by me, so I know the password - lets call it 'IUSR_ME'. Basides that it is all default. IIS_WPG has also been given access to all website files. Database Server: [Windows Server 2000, SQL Server Standard Edition] I already have a 'classic' ASP application running on this setup, and this was achieved by putting IUSR_ME as a user in the database. Does anyone know how to do it with ASP.NET? Many thanks in advance.George
View 1 Replies View RelatedI know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedHi Gurus,
I am new to SSIS. I am trying to install just the SSIS in one machine("SSIS Machine") and just the DB Engine ("SQL Server Machine") in another machine. What I am trying to do is, separating the SSIS service and packages from the Database Engine and trying to run in in another machine. I have few questions on this topic. I searched on this forum but I couldn't find a concrete answer to those questions. Forgive me if it already answered/asked multiple times.
1. When I install SSIS in "SSIS Machine", do I need to install client components also in the same machine?
2. I already established this setup (SSIS with client components in one machine and SQL Server in another) but when I tried to connect to the SSIS thro' Management Studio from Sql Server machine, I keep getting "Access Denied" error. Is it possible to connect to SSIS server from another machine (using Management studio)? I tried the DCOM security permission options I found in the internet(I don't have domain id so I gave "Everyone" full access) but still I get the same error. Any help would be appriciated.
3. Do I need 2 SQL Server Licenses (Enterprise) if I go with this environment?
4. Is it possible to configure SQL Job to run SSIS installed in another machine?
I would appriciate any help on this issue.
Thanks
Currently both of our servers have 1 gig of RAM. We are adding and additional 1gig of RAM to our local server but none to the remote server. Transactional replication is performed between both servers in both directions. SQL Server is configured to dynamically configure memory on both servers.
Are there any issues or concerns that I should be aware of?
Thanks
I have code on a website that does a search to an Access database based on what the person enters as the search criteria. My SQL code uses a like % statement for all the fields in the search criteria. The problem is that the first and third search fields do no return the search criteria that is asked for.....The 2nd search field (Description) returns whatever is asked for..... I did read up and found that the data must be character for the like statement. Anyone have any other clues or where I can read up on this???????HTML SEARCH CODE: <table ALIGN="CENTER" width="408" bgcolor="#FFFFFF" cellspacing="0" cellpadding="5"> <tr> <td width="400" bgcolor="#3366cc" colspan="2"> <form method="post" name="DaForm" action="Makeit.asp" align="center"> <div align="center"><center><table border="1" width="350" cellpadding="3" bgcolor="#3366cc"> <tr> <td align="left" width="189"><select name="TypeSearch" size="1"> <option selected value="PartNumber">Part Number</option> <option value="Description">Description </option> <option value="Mfg">Manufacturer</option> </select></td> <td width="310" align="right"><input type="text" size="30" name="DaInBox"></td> </tr> <tr> <td colspan="3" align="center" width="507"><div align="left"><p><input type="submit" name="B1" value="Search!"><input type="reset" name="B2" value="Clear"> </td> </tr> </table> </center></div> </form> </td> </tr></table>Makeit ASP COde:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Your Search Results!</title>
<meta name="Microsoft Border" content="lb, default">
</head>
<body>
<%
Dim MySql
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("parts")
MySql = "SELECT * FROM parts"
If Request.Form("TypeSearch") = "Part Number" Then
MySql = MySql & " WHERE PartNumber LIKE '%" &_
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "Description" Then
MySql = MySql & " WHERE Description LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
Dim rs
Set rs = new Server.CreateObject("ADODB.Recordset")
rs.Open MySql, cn, 0,1
%>
<% If rs.BOF and rs.EOF Then%>
<h2 align="center">We did not find a match!</h2>
<%Else%>
<%If Not rs.BOF Then%>
<h2>Here are the results of your search:</h2>
<table BORDER="0" width="100%" cellpadding="3">
<tr>
<th bgcolor="#0366cc"><font face="Arial" color="#000000">Part Number </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Description </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Manufacturer </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Qty </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Condition </font></th>
</tr>
<%
Do While Not rs.EOF
%>
<tr>
<td><%=rs("PartNumber")%>
<%=rs("Description")%>
</td>
</tr>
<% rs.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
<p> </p>
</body>
Hi,
What are all the performance parameters to be analyzed, in an existing production server, to accommodate an additional database.
Regards
Kalyan
I have two tables that I am pulling data from to insert into a third table.
Table 1:
Organization Address Date
Name 404 St. 12/31/1999
Table 2:
Organization Software Type Quantity
Name SW1 5
Name SW2 6
Name SW3 7
Name SW4 8
My query looks something like this:
INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity)
SELECT Table 1.Name, Table 1.Address, Table 1.Date,
(Select Table 2.Quantity Where Table 2.Software Type = 'SW1'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW2'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW3')
FROM Table 1, Table 2
WHERE Table 1.Organization = Table 2. Organization
Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.
Part of the code as follows:
SELECT
ARDoc."Cpnyid", ARDoc."Custid", ARDoc."CuryOrigDocAmt", ARDoc."DocBal", ARDoc."DocDate", ARDoc."Doctype", ARDoc."slsperid", ARDoc."Territory", ARDoc."RecordType", ARDoc."user7",
ARTran."CmmnPct", ARTran."CuryTranAmt", ARTran."DrCr", ARTran."ExtCost", ARTran."InvtId", ARTran."JrnlType", ARTran."Qty", ARTran."RefNbr", ARTran."Rlsed", ARTran."S4Future04", ARTran."S4Future05", ARTran."TranAmt", ARTran."TranClass", ARTran."TranDate", ARTran."TranType", ARTran."UnitDesc", ARTran."UnitPrice",
RptCompany."CpnyName", RptCompany."RI_ID",
Customer."Name",
Salesperson."CmmnPct", Salesperson."Name", Salesperson."SlsperId"
FROM
{ oj ((("SOLUSBS02APP"."dbo"."zARDoc_Comm" ARDoc INNER JOIN "SOLUSBS02APP"."dbo"."RptCompany" RptCompany ON
ARDoc."Cpnyid" = RptCompany."CpnyID")
INNER JOIN "SOLUSBS02APP"."dbo"."Customer" Customer ON
ARDoc."Custid" = Customer."CustId")
LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."Salesperson" Salesperson ON
ARDoc."slsperid" = Salesperson."SlsperId")
LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."ARTran" ARTran ON
ARDoc."Custid" = ARTran."CustId" AND
ARDoc."Refnbr" = ARTran."RefNbr" AND
ARDoc."Doctype" = ARTran."TranType"}
Currently, if a new rep takes over for an old ones invoices and accounts...he will also get credit on the report which this query is for. Instead I need to use a table SOShipHeader to be 'date sensitive'. SOShipHeader will have the correct 'SlsperID', but will still need to pull the name from Salesperson."Name"
My guess, would be that I need to wedge the SOShipHeader table between the ARDoc and Salesperson tables?
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales_HW| -20,000.00 | 01 | 2005
===================================================
1000| Sales_SW| -10,000.00 | 01 | 2005
===================================================
1001| Cost_HW | 5,000.00 | 01 | 2005
===================================================
1001| Cost_SW | 5,000.00 | 01 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Sales Category | Sales | Cost | Profit
=================================================
HW |-20,000.00 |5,000.00| -15,000.00
SW |-10,000.00 |5,000.00| -5,000.00
=================================================
Total |-30,000.00 |10,000.00|-20,000.00
The above report have 4 columns, with last column being a calculated field (Sales-Cost)
Guys, hope someone out there can help me with the sql command for the above report?
How do I create additional instances of any MSSQL 2005 service(specifically database and analysis) given that a default instance is present?
View 4 Replies View RelatedWe are
looking for a solution for a client that we will make software for. We are a ISV. After
completion of the software, our client then wants to sell this software to their
partners, people in that business, etc. We do not want to add any additional cost, by means of them(client(s)) purchasing
a server, server software or licensing. Is there any solution
that will make this work? A runtime or embedded database solution? If there is a
license we need to purchase, what would that be? SQL Express Edition work?
Hi
I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg
12234
122334343
1234234567
how can i get it to look like this adding an 0 to the front to make the row 10 digits
0000012234
0122334343
1234234567
Thanks
Hi All,
I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far.
Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end).
I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.
Thank you.
I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website. Is it possible to add additional data fields to this file? Where can I find the commands to do this?
View 2 Replies View RelatedHi, I use the FOR XML AUTO to retrive native XML from a database with:
SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')"
However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements:
XML is Stored:
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
Returns:
<databasename>
<tablename>
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
</tablename>
</databasename>
I have a stored procedure which is used to search records in the database based on the data sent from the web page. There are several search fields and all of them are in one table (Table1) except the "CallerName" field which is in a different table (Table2). Since I had to show CallerName also in the gridview apart from other columns, I did a LEFT JOIN (using field CallerNumber) to show all the required fields from Table1 and CallerName from Table2.
Now heres the problem. Since CallerName is a search criteria, its supposed to be in the WHERE clause after the JOIN or in the JOIN clause itself. The problem is, if I put it in WHERE clause, the result set doesn't show records from Table1 which do not have a matching CallerNumber in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber
WHERE T1.CallerNumber = 'some number' AND T2.CallerName = 'some name' If I put it in the JOIN condition, it works just like a LEFT JOIN is supposed to work, showing all the records in Table1 and also those which had CallerName in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber AND T2.CallerName = 'some name'
WHERE T1.CallerNumber = 'some number'
1st SQL won't work for me because it doesn't show all the records in Table1, even when no search criteria is specified.2nd SQL won't work for me because it shows more than required when just CallerName is sent from the web page as search criteria. It will show all the records where CallerName is "some name" and also all the additional records (since it is a left join).
Can I get the goodness of both in one or do I have to create two separate Stored Procedures?
Thanks all,Bullpit
I added one crummy column to my table. I updated the stored procedure and added the thing to the aspx page which is my form for adding an article. I have a strong feeling that something is foul over here...Why is it that Visual Studio will not allow me to capitalize the word get and when I delete the () after ShortDesc they immediatlye reappear and the get statement is set to get. Every other one of them, and there are 9 others use GET and there is no () after the public property variable name. Does anyone know the reason for this?
Public Property Author As System.String
GETReturn _Author
End Get Set(ByVal Value As System.String)
_Author= ValueEnd Set
End PropertyPublic Property ShortDesc() As System.String
GetReturn _ShortDesc
End GetSet(ByVal value As System.String)
End Set
End Property
I have a DataGrid, it will return a table in SQL2000 server.
the problem is:
no data in the datagird, only a record "null" in all field.
I find a sample is add a "sqlDataAdapter1.Fill".
but if I add the code:
sqlDataAdapter1.Fill(dataSet21);
I got the error:
---------------
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
-------------
I have search this problem fix many many time, many people has this problem, looks like no result I can find.
Please help, thanks very very much.
Thanks.