I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?
View 5 RepliesI have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.
Do anyone know the syntax??
Thanks, Vic
Hello everybody,
I have the following problem.
I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.
I would like to make a query that retreives al send emails, its recipients and message_size.
SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles
Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55
But i'd like to get;
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55
Can anyone help me with this query?
Best Regards,
Sidney
Hi
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
Thanks
I need to combine my first 3 columns into 1.
SELECT [Category]
,[Sub Category - I]
,[Sub Category - II]
,[MSC #]
,[Price]
FROM [Pricing Analysis].[dbo].[Table1]
Hey everyone,
I have a windows app with a particular report that has 2 date columns in it. I need to combine these two columns based on a choice that the user makes on a previous form. So the user enters two dates (beginning and ending) and then makes a selection. So if my query brings back
Column1 Column2
1/1/2000 1/1/2004
2/2/2002 2/2/2007
3/3/2005 3/3/2008
.... .....
If the user selects option 1 column1 is displayed on the report, option 2 then column2, and if they select option 3 then columns 1 and 2 must be combined together based on other criteria.
I'm passing a parameter with the selection to my report, is there any way to combine these columns strictly in the report, or would it be easiest to do this in my sql query, and if so how? Keep in mind that I must keep each column separate because after the user makes the selection I have to make certain comparisons to each column before combining them. If any more information is needed then just let me know and I will post it as well.
Thanks in advance,
Aaron
Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:
order# Part# Addon1 Addon2 Addon3
What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:
Old Table: addon1 Addon2 Addon3
New Table:
Addon1
Addon2
Addon3
Of course I would also provide a link between the part and the applicable addons.
Thanks
I have a query (SELECT * FROM Pricing) that produces the following results
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23
I want to convert this to the following :
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23
We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Many thanks.
Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement:
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName
FROM dbo.Item INNER JOIN
dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN
dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)
As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:
UPC ….. CategoryName DepartmentName
12345….. MerlotWine
34556….. ChadonnayWine
Etc…
What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.
UPC ….. CategoryName DepartmentName Combined
12345….. MerlotWine Wine~Merlot
34556….. ChardonnayWineWine~Chardonnay
Etc…
I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.
Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully
View 5 Replies View RelatedI have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC
View 10 Replies View RelatedHow do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).
Thank you,
Andrew
Hello,
I'm running a webapplication which uses msde 2000 as database server.
All webpages query the database.
If I refresh a lot of time a webpage then I get a time-out error.
With the netstat command I found a lot of ms-sql-s connections.
Is there a way to disconenct these connections?
Is there a way to repair this problem?
Thanks!
how many connections can be established to sql server at atime?
is the transactional log be readble in the ?
will it contain all data
John AlmedaSystem AdmIt seems that I have done something really stupid or something wentboonkers. I was restoring a DB whn I got a message that the log filecould not be restored because it was XXXXXX????. This left my DB greyedout and with a (loading) sign. I tried to detach and then re-attech theDB and all hell broke loose. Now I cannot connect to any of my DB's. Themessage I get is "a connection could not be established on sqlsrvr .Cannot open users default db. Login failed."I cannot see any of db under that regitration and the connectioncontinues to fail. I am drawing a total blank as to why and how tocorrect with the exception of unistalling MSSQL and re-installing theserver and then trying to establish a connection with my db's again.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI'm setting up SQL 2005 Reporting Services on a Windows 2003 R2 SP2 machine and trying to configure the Database Setup to a SQL 2005 Virtual Server Instance running on a x64 2-node a/p Cluster. The virtual server is registered in DNS but when I attempt the 'connect' using Windows Integrated Authentication. I get the following error:
"A connection could not be established with the information provided. Click OK to change your connection settings.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provide: Named Pipes Provider, error: 40 - could not open connection to SQL Server) "
The SQL cluster is configured to allow Local or Remote connections TCP or Named Pipes.
Any suggestions?
Hi, I received this message today when I tried to log on to my SQL Server:
**********************
A connection could not be established to <server>
Reason: SQL Server does not exist or Access denied
ConnectionOpen(Connect())..
Please verify SQL Server is running and check your SQL Registration properties**********************
My SQL server wasn't running, however I am not able to start it (right click->start doesn't work).
Any thoughts?
thanks
Table 1
vEmail_Id
vFirst_Name
vLast_ Name
Table 2
vEmail_Id
iCell No
How to relate Table1 with Table2 by 1 to 1 relation in sql express ?
When trying to connect to my SQL Server I'm getting the following error:
******************************************
A connection could not be established to (local).
Reason: Logon failed for user.
Please vertify SQL Server is running and check your SQL Server registration properties (by right-clicking on the (local) node) and try again.
******************************************
SQL Server is definately running, but I'm unable to connect.
I've tried changing the ODBC settings but I can't get past the "MIcrosoft SQL Server DNS Configuration" window. I choose "With Windows NT authentication using the network lognin ID" and click "Conect to SQL Server to obtain default setting for the additional congiruation options" and click "Next" but I get stopped by the following error:
connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user machine1Administrator'
Does anyone have any ideas on what I need to do to fix this? If it helps, this all seems to have started occuring since I changed the Administrator password
Hello
I am getting this err msg when trying to logon to my website on the web - on my machine there is no prob,
An error occurred while established a connection to the server.
When connection to Sql.S 2005, this failure may be caused by the fact that
under the default settings sql.s doesn't allow remote connection.
(provider ql.S Network interface, error:26- Error Location Server/Interface Specified)
(the files path is like in the web host s)
now this msg is related to the ASPNETDB.MDF (dont use sql.s db but this file)
thanks
Hi,
I am a SQL DBA involved in accomplishing various activities using Enterprise Manager by regestering servers of various countries.
Now as the NT Team has gone for auditing implementation in all the servers in OS lever, they find Access violations from the workstation from where I connect to the server. This does not happens if I unregester the Servers from Enterprise manager and use only query analyzer.
Note: We have our own users in OS as wel as in SQL SERVER .
Its Urgent!!! Please help!!!
After a trust with exchange server established the xp_SendMailgives the following error:xp_sendmail: failed with mail error 0x80070005Prior to the trust everything worked fine.I have tried the following:1)Checking SQL and SQLAgents accounts all match up with default mailprofile.2)Confirmed I could send from outlook logged in under the account.3)xp_stopmail and xp_startmail4)restarted the SQL Agent5)Confirmed outlook settings as defined in:kb153159 -http://www.sqlnewsgroups.net/link.a...b/153159/EN-US/I also tried a pop3 account just to test, but no luck.I think what is happening is now that the trust is established when SQLmakes a mail contection it thinks it is user admin@<otherdomain> ratherthan admin@<normaldomain> and therefore thinks that agent account isdifferent than the mail account.Please help if you can.Thanks.
View 2 Replies View RelatedHi,
I am having such a problem. I have SQL Express installed on the windows 2003 server and originally I installed instance1 of SQL Server 2005. When I installed Instance1, I remember having problems with remotely connecting to that instance1, but somehow I fixed it. Now I installed instance2 and I started having remote connection issues again with that instance2. I am able to connect to instance1 but not instance2. I checked settings for both instances and they look identical.
I turned firewall off for now. I checked TCP settings using surface area configuration (allowing both local and remote connections). I turned off VIA protocol. I specified TCP properties to use dynamic port for that instance2.
In the sql native client configuration I have the following order#: 1. Shared memory, 2. tcp, 3. np, 4. VIA(Disabled). TCP is configured to use 1433.
I have no clue about what is going on. Sometimes I am able to connect remotely and sometimes I get the following error:
"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider)."
When I connect locally - everything connects fine. File sharing is also checked, it works etc...
Thanks for help!!!
GK
I just noticed that I was able to connect from different computer on the different network.
I get the following error when trying to view my page.
Can someone please help me with this error. What does it mean and what is the solution to it? Here is the complete error message. -
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
I have just installed MS SQL Server 2005 Express edition along with the SSMSE. I am using windows authentication. When I open SSMSE it connects me to a 'local instance' of the server. From there I right click on Database and select to add a new Database and I get the error above. I think I only need to allow local connections to the engine but I have tried allowing remote conections with all varations of named pipes and TCP/IP. I am running on Windows XP SP2 (home).
View 1 Replies View Related
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
I got this message from sql
"Server was unable to process request. ---> A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) "
Does anyone know what happen?
Thanks
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
use [master]Go
-- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO
-- Switching to our databaseuse [DatabaseName]GO
CREATE SCHEMA schemaname AUTHORIZATION usernameGO
ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO
/* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
Dear Developres,
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.
at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)???
Thanks in advance.
Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ?
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM dbo.Orders
WHERE (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 1 Results
EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM dbo.Orders
WHERE (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 2 results
EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19
Table 3 the desired result:
EmployeeID CountShipVia1 CountShipVia2
1
82 44
2
71 36
3
81 45
4
116 70
5
29 15
6
48 25
7
44 24
8
75 48
9
29 19
thanksrobby