Heya, I'm stuck on this query, we are currently moving from mysql to mssql, and this one has stumped me.
I used to use CONCAT, and i thought i'd found the correct syntax for MSSQL but it fails when used.
Code:
$sql = " SELECT *
FROM ol_user_2_group ug
INNER JOIN ol_users_details ud
ON ug.user_id = ud.user_id
INNER JOIN ol_user_rank ur
ON ug.user_id = ur.user_id
WHERE ug.group_id= ".$group_id."
AND ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
Hi all just as the subject suggests I am getting the follwing problem not sure why as I have used the same method on a bunch of other servers but they were all on the same DOMAIN whereas this one isn't.
"Login failed for user ''. The user is not associated with a trusted SQL Server connection."
Using the following code:
Code Snippet
Str_connectionString = "server=OB-DEV6; database=WEBSERVER; Trusted_Connection=True" sqlCon = New SqlConnection(Str_connectionString)
But it doesn'y seem to like that at all. I have gone into:
SQL Management Studio > Properties > Security > SQL Server and Windows Authentication
Restarted the SQLServerAgent (MSSQLServer)Restarted the SQL Server (MSSQLServer)
I've asked this question before and I've read the answers before and I still cannot get it to work. My task is simple, I want to use the execute sql task container to grab a value from a database and put it in a variable. I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.
This is my sql command:
select output_location as output_location from script_master
Result Set is set up like this:
Result Name: output_location ; Variable Name: User::output_location
Here is the error I get:
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "output_location": "The type of the value being assigned to variable "User::output_location" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".
I don't know what I'm doing wrong, I've followed all the instructions exactly on how to populate a variable in this container. My variable is set up as a string, if I change it to object I can get it to work. I think this is because the object is allowing nulls. I really believe that the variable is not populating and that is why I'm getting errors.
Please help. If you could provide step by step example's that would really make my day.
Hello, I am trying to essentially create an inner join. However, the thing that makes my case unique is, I need to do an inner join "twice".
I have SomeTable: ID Name Item1ID Item2ID
ItemTable: ID Name Description
I need to do a query on SomeTable and retrieve a specific result based upon the ID. When I return that result, I want to return the two Item names that correspond with the result. I need this to act like a left join because the item id may not exist in the ItemTable. Does anyone know how to write a query like this?
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
Having problem completing this query. I have a list of items. Some items need to be grouped by a list, some by a range. I was thinking of useing two tables, one for the items, and one for the groups. The groups would have something like groupid, title, listnumbers, rangelow, and rangehigh. The tables are in sql database. For example: The list of items numbers are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20. In the group table, it would be listed like this: G1, Group1, 1,, G1, Group1, 6,, G2, Group2, ,2,5 G3, Group3,6,18,20 G3, Group3,7,15,17 G3, Group3,8,14,16 G3, Group3,9,11,13
In this example, Group1 is a list, group2 is a range, and group3 is a list of ranges. I can make a query that pulls all the items just in the groups: SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC FROM Group, ECC_ITEMS WHERE ECC_ITEMS.NBR Between Group.RangeLow And Group.RangeHigh Or ECC_ITEMS.NBR=Group.GroupItems Now, I am not sure how to put the rest of the items (the ones that aren't in a group) in that query. I was thinking on making a union and the second query being a unmatched query. Not sure how to make it were that query is "unmatched" with a table in the same query. And ideas on how to make the second part of the union query?
USE Billing INSERT INTO tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('test','1','1','9','6') where userId = id from tbl_account
and
INSERT INTO tblUserInfo (userNumber,userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('123','test','1','1','9','6') where userNumber = userNumber and userId = userId from tblUser
Im new when it comes to making SQL queries, so i need help badly.
Basically what i need the query to do is call from a DB "User" id where the id is in Binary data. from that i need it to insert into 2 other tables in the Billing DB.
the UserId needs to be the same in all areas, and when it inserts into tblUserInfo it has to pull from userNumber from tblUser after the rows are entered so that the userNumber in tblUser and tblUserInfo are the same.
Hopefully you can help. if any further information is needed please just ask and ill try to give as much as i know.
i am familiar with php mySql combo, but from now on the boss wants me on MS SQL. i have a little query sender that used mySql and i tried to switch it over to use mssql_functions like: php Code:
When i run my script i get a blank screen. I am already bent out of shape trying to switch from mySql to this. any help would be appreciated. here is my code...
php Code:
Original - php Code
<?php /*Program: mssql_send.php *Desc: PHP program that sends an SQL query to the * MS SQL server and displays the results. */ echo "<html> <head><title>MSSQL Query Sender</title></head> <body>"; if(ini_get("magic_quotes_gpc") == "1") { $_POST['query'] = stripslashes($_POST['query']); } $host="yourhost"; $user="you"; $password="example";
Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.
I have this database running (ignore that that was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the tbl_events table and the tbl_timekeeperDetails table I need to make a query that:
Lists the names of all timekeepers (whether they are booked for a meeting or not), and the meetings at which they are timekeeping.
The tricky part of this is getting the query to show the timekeepers who aren't assigned to an event.
I have two seperate querys so far, but I'm presuming there must be a way of merging them or something.
I have this code so far: select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast,eventID from timekeeperDetails,events where timekeeperDetails.timekeeperID = events.timekeeperID select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast from timekeeperDetails
If anyone has any ideas, please do post a reply or email me at paul [at] abscond [dot] org
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2
, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3 , IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
I have a mysql query in my php script like UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sessioncreated) as sessionspan . What is the equivalent of above query in mssql. I need the same query in mssql. Is there any function that does the same action in mssql2000.
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY ======= CREATE VIEW HISTORY AS SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION FROM LOCAUDIT
LASTHISTORY ========== CREATE VIEW LASTHISTORY AS SELECT CODE, Max(UpdateDateTime) AS LastUpdated FROM HISTORY GROUP BY CODE
UPDATE RETAIL SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END) FROM RETAIL AS t4 LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE) LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime) LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
I need to use mssql to create a ranking of some kind. This is the situation: I need to assign position to a list of students based on thier scores. e.g Student Score Position StudentA 56 4 StudentB 78 1 StudentC 66 2 StudentD 56 4
I need to create the positions based on the scores of the ctudents. I will appreciate any assistance. Thank you.
I got a big problem. I try to run a query on ms query analyzer or my .net-app, but in both cause, it wouldn't work. when i run the query in ms query analyzer it returns me this error-message:
Der aktuelle Zeilenwert der [verkauf_online]..[hs].[std_ftext].text-Spalte konnte nicht vom OLE DB-Provider 'MSDASQL' gelesen werden. [OLE/DB provider returned message: Die angeforderte Konvertierung wird nicht unterstützt.] OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
sorry, its in german, i know but the most important fact is, that a conversion failed. i tried to read a text-field from a sybase asa-8-database which is linked with a ms sql-server 2000. I tried to find out more about the error-messe an the error-code (x80040e1d) but i couldn't find anything helpful.
Is there a way to do a SELECT TOP # using a variable for the #? In other words I'm doing a SELECT TOP 50* FROM DATATABLE If I pass an @value for the number SELECT TOP @value* FROM DATATABLE doesn't work I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have. Thanks in advance.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
Here is our problem...We are doing a lot of selects against a table that has one large fieldin it.If we do a select against all the fields except for description, thequery comes back relatively quickly. If we add that last field (768chars) to the query, our query takes 10x longer (5 seconds vs 56seconds.)When we run the one without the description column, we can watchperfmon and see a very quick spike to physical disk. If we add in thedescription field we can see that the server becomes I/O bound - thedisk sits at 100% until the query is complete.We have tweaked the min query memory setting for the server but itseems to have had no effect no matter how high we set it. Is theresome point at which MSSQL decides it cannot perform the transaction inmemory? What would I increase to cure this problem?For example:TMZDIFF int410WRITETIMEcharno 16System_Namecharno 64Timestampcharno16Name charno32Mount_Pointcharno32Size intno4 10Space_Usedintno410Space_Availableintno410Inode_Sizeintno410Inodes_Usedintno410Inodes_Freeintno410Space_Used_Percentintno410Inodes_Used_Percentintno410FS_Type charno8Space_Available_Percentintno410Name_U ncharno32Descriptionncharno768
Hi all,is there any tool which is capable to convert query initially written formsaccess database to query for mssql server.I have tons of queries which contains iif, trim and similar functions whichshould be converted to case, ltrim(rtrim(, etc. etc.Does anyone know for tool which could do that automatically?Thanks in advance,Anabella
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
Hello all, I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005. I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's. In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:
100 query or transaction dump: mssql=3.7mb oracl10g=133kb
50,000 query or transaction dump: mssql=1.8gb oracl10g=0.6gb db2=0.6gb
as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.
So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState JOIN tblProduct ON tblProduct.id_Product = tblProductState.id_Product WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total, MAX(RecJobAds.PostedDt) as PostedDt, MAX(RecJobAds.JobTitle) as JobTitle, RecJobAds.JobId, MAX (AgencyCandidate.AgencyId) as agentID, MAX(RecJobAds.AdStatus) as status, MAX(RecJobAds.CompanyId) as CompanyId, MAX(RecJobAds.RecId) as RecId FROM RecJobAds LEFT JOIN AgencyCandidate ON RecJobAds.JobId = AgencyCandidate.JobId GROUP BY RecJobAds.JobId ORDER BY Total ASC
i have the above query. but i would like to join in a third table, where my query is
Code:
select * from RecruiterMA where Activated = '1'.
anyidea in which place i can put the second query in the first query? thank you very much!
I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
