Put them into a textbox and separate with a , (comma)
-------------------------------------------------------------------------------------
| mail1@email.com, mail2@email.com, mail3@email.com |
-------------------------------------------------------------------------------------
I have a table that has some comma separated values and then have another table with the lookup values. I"m not sure why it was created this way and have not had to do this. This is a vendor database so I can't go around and changing things.Â
Table AÂ IDStageIDs 188 288,86,87 388,87
Table B (Lookup table) IDName 86test1 87test2 88test3
I have a stored procedure that is passed two values. Both are strings representations of GUID values, the first is a single value, the second is a comma delimited string of values.In the stored procedure I call a split function to separate the comma delimited values into a table and this is used in my WHERE clause to filter my select results.This is an example:
Code: SELECT item.uiGUID as ItemGUID, stores.strStoreName as Store, location.strLocationName as Location FROM tblItems as item INNER JOIN tblStoreLocations as location ON item.uiLocationGUID = location.uiGUID INNER JOIN tblStores as stores ON location.uiStoreGUID = stores.uiGUID WHERE CAST(item.uiGUID as varchar(36)) IN (SELECT Value FROM dbo.Split(',',@ItemGUIDList))
When I run this query in the management studio, passing a list of 5 values in the second parameter, my results include one item for each of the 5 values. However, when I pass the parameters from my ASP project, (I've verified the values I think are being passed are indeed being passed), I only get one item.I believe the error is in my split function. Both split functions return the same results in the SQL management studio, but only one returns the correct results in the the ASP project.
When I use this version of the function it returns the correct table values to the calling application, but it chokes when the item list does not have a trailing comma. I figure that to be a bug in the SQL function.
I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS (SELECT sys.fn_PhysLocFormatter (%%physloc%%) col1, RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID], DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006
and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas, Thanks in advance, Aldo.
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name. Example David Michael Carol Anne Is there a way in a query to look for the blank space and separate the names?
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
On the time of installation SQL Server asking me where I wont to locate the DATA files and the PROGRAM files. It’s giving to me choice to put database AND log files on one disk and program files on separate. But what about to separate LOG and DATA files. I have RAID1 especially created on F: drive for LOG files and RAID 5 on E: for DATABASE files. When I have to separate that if not on the time of installation? How I can do that?
Ok, I have three tables worth of data. They basically hold data that is keyed into a system for potential clients and include stuff like names, weights, classes of weight, addresses etc.
The main table that holds data on a record by record basis is set up like this:
This table contains the batches (or collections of the data above). Basically a collection of bills is called a batch, so if a batch has 18 bills in it, those 18 bills are keyed and assigned the prospect_batch_id above.
This is the table that contains the various weights and classes for the individual records in the first table (tbl_prospect):
Tbl_prospect_clwt Prospect_clwt_id (pk, unique) Prospect_id(unique) Class weight
Generally without worrying about the multiple classes and weights it's easy to just join all three tables and pull the fields I need, but now that I have to get multiple classes and weights I have no clue how to actually grab them and delimit them with a comma.
I think I'm only going to be working on tbl_prospect and tbl_prospect_clwt, and if I put a filter in place for the prospect_batch_id I can see the individual record in tbl_prospect as well as the multiple classes and weights in the clwt table, as seen here:
So record 18 of that batch is comprised of two shipments, one that is class 100/weight 1623 and one that is class 70/weight 438, just not sure how to actually grab both of those for each record and delimit them.
I have a table which has fields called defid, datavalue, pid etc. I have to create a new table which should have fields: CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.
COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows: Thank you in advance.
SELECT DISTINCT c.CustID, d.datavalue, f.name FROM dbo.Customers c INNER JOIN dbo.Date7 d ON d.pid = c.root INNER JOIN dbo.fielddisc f ON f.defid = d.defid WHERE c.separatedate BETWEEN @StartDate AND @EndDate AND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472 ORDER BY c.root
This is probably for most of you a very basic question. My goal is to define in a seperate instance from application all infos related to DataSets and DataAdapters that are needed when connecting to a specific database. Why?
First, I would like to be able to have in a single instance everything needed when hooking to a SQL Server Express database and in an other everything needed when hooking to a SQL Server CE database. Then, from application, I would like to be able following a few basic configuration steps to choose from one or the other, or even both in specific cases.
Second, I want to be able to seperate Database related stuff from application so that I can re-use everything from any other application when needed.
I've read several books, articles on the subject and would like to have your opinion as several approaches were illustrated through them. What would you use for this:
hi alli have a web application on a web server.and another windows application on another computer.there are databases on each of them.how can i send and receive data between these two applications?please reply me as soon as possibleregards
I'm getting ready to install SQL Server 2005 Enterprise for the first time and I have a question about the directory location of the log files and the data files. I have 3 RAID arrays on my server, 1 for the OS, 1 for SQL Logs, and 1 for SQL Data.
Here's my issue. I want to install the logs on the Log array and the SQL data on the SQL data array, however, during the installation I can't find anything that allows me to select certain directories!
I receive a file that will have hyphens between data items such as 123-aed-edr-45r-ui9 1-ed3-45r-rrr-98u I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status] @Salesmen nvarchar(50), @QuoteStatus nvarchar(50) AS BEGIN SET NOCOUNT ON;
I am using Reporting Services in the following way. The application implements a three tier Architecture viz Web Server, Application Server and Database Server. Reporting Services 2005 is installed on the Database Server.
The application uses ReportViewer Control which is part of an ASPX page deployed on the Web Server. For ReportViewer Control to fetch data from Reporting Services using RS API, we have to set ReportServerUrl and Report Path. In this scenario, the web server cannot communicate to the database server directly. The web server is in the DMZ and it can only communicate with the Application Server which is in a separate Network.
How can I use Report Viewer Control to fetch data from Reporting Services which is on the Database Server.
I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.
I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.
I have 1 table that is just a list of feeds. A, B, C, D etc (15 rows in total) and each feed has other information attached to it such as Full name, location etc etc. I am only interested in the Feed column.
Each feed then has a corresponding data table which contains a list of records. Eg Feed A data is contained in TableA, Feed B data is contains in TableB and so on.
Basically what I need is a combined table that shows the list of Feeds in the 1st Column ( So A, B, C, D…..) and then a second column which counts the records from each separate data table corresponding to that feed.
So the end result would look something like this:
Feed------No of Records A----------4 (from TableA) B----------7 (from TableB) C----------8 (from TableC) D----------1 (from TableD)
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
LEFT JOIN Status As S on S.LoanID = L.LoanID LEFT JOIN Borrower B on B.LoanID = L.LoanID LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID where S.PrimStat = '1' and B.Deceased = '0'
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
