I am new to jscript and trying to learn how to pull multiple entry's from a table. I know with php you can use a while loop but that doesn't seem to work with jscript. Here is what I have so far.
var sSql = "select nIndex,sDescription from StatisticalDiskIdentification " +
"JOIN PivotStatisticalMonitorTypeToDevice ON " +
"StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID = " +
"PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID " +
"where (PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID='2') and " +
"PivotStatisticalMonitorTypeToDevice.nDeviceID = " + nDeviceID;
while (oRs = oDb.Execute(sSql)){
if ( !oRs.EOF )
{
// Display various columns in the debug log (Event Viewer).
var sDisplay;
nIndex = "" + oRs("nIndex");
Context.LogMessage("nIndex=" + nIndex);
sDesc = "" + oRs("sDescription");
Context.LogMessage("Description =" + sDesc);
}
Context.SetResult( 0, " Ok");
oRs.MoveNext();
}
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
Pricing is a little confusing at my work. My works database from the point of sale has multiple places for price to be. Right now the tables in question are in a SQL database. I am trying to create a user defined function so I can get a list of prices for specific items and for the customer logged into the web page. The tables look like this:Inventory: ItemID, ItemCompany, Description, ListPrice, PublicPriceContractID: ContractNum, ItemID, ItemCompany, MinQty, ContractPriceCustomer: CustomerNumber, CustomerDepartment, Contract1 – Contract4ContractNum ‘99’ is a global contract to all customers. I was able to get the price for this generic contract price, but not for contracts customer may have. Here is what I have so far (with an example item already being pulled up). I am at a loss on how to get the customers contract price with the same function. SELECT Inventory.ItemNumber, Inventory.Company, Inventory.Description, Contracts.Quantity AS MinContractQty, COALESCE (Contracts.ContractPrice, Inventory.WhlCatalogPrice) AS Price, Inventory.WhlCatalogPrice AS ListPriceFROM Inventory LEFT OUTER JOIN Contracts ON Inventory.ItemNumber = Contracts.ItemNumber AND Inventory.Company = Contracts.Company AND '99' = Contracts.ContractNumberWHERE (Inventory.ItemNumber = N'444') AND (Inventory.Company = N'035')
iam new to MS SQL 7 server...i have two tables in my database say Table1 and Table2 having a comman field--- Name String(30). I want that dual data entry should be made for any single entry. That is if a name is entered in Table1, then same entry should be automatically entered in table2
is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?
I have a RS2005 report that was deployed to a ReportServer and has been running for over 1 month. I personally have not run this report in the past month, however, today when I tried to run it I got an error trying to select a date parameter. I can't paste samples of the windows displayed, so I'll do my best to explain. When I click on the report, it goes to the View window and displays 2 date parameter fields. When I click the icon next to one of the parameter fields, I get an error window that says A Runtime Error has occurred. Do you wish to Debug?
Line: 383 Error: 'resultfield.id' is null or not an object
I click Yes and get a VS Just-in-Time Debugger window, where I choose New instance of VS 2005. When VS2005 opens, then I get this error:
Microsoft JScript runtime error: 'resultfield.id' is null or not an object
The Continue option just keeps displaying this error, when I click Break it shows me the code below and has highlighted the line I show in BOLD:
function ClickDay(date) { var win = document.parentWindow; if (!win) win = document.contentWindow; if (!win) win = document.defaultView; if (!win) return true;
var ifrm=win.frameElement; if (ifrm==null) { return MoveToDate(date, false); } var eltValidator=win.parent.document.getElementById(ifrm.resultfield.id+g_strDatePickerRangeValidatorID); if (eltValidator !=null) { eltValidator.style.display="none"; } var OnSelectDateCallback=ifrm.OnSelectDateCallback; OnSelectDateCallback(ifrm.resultfield, date); var resultfunc=ifrm.resultfunc; resultfunc(ifrm.resultfield); return true; }
To make this more confusing, after I get this error a few times, eventually it lets me choose a date and run the report. Another quirk of my PC is that I often get runtime errors that ask me if I want to Debug when I'm in IExplorer.
Any idea what is going on and how to fix it? Thanks in advance!
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
Ok, to start, i have read SO many posts on this site, and othe sites, and i cannot get this to work.
What i want to do is: 1. Login 2. show an image from a SQL database, based on that login.
I have the image path stored in the database, and i can easily get it to display on the screen, but it doesnt show the correct image for the login, it shows them all. (all images based on that column).
I know i have to put a WHERE at the end of my SELECT statement, but everything i have tried doesnt work.
When i do the query builder, and i put in the value, it shows exactly what i want, based on the login's username.
Ok so every week I have to download a file containing in excess of 40000 products. I then have to change the category ids to suit my system. Is there a script or something that I could create so I just run it each week to do a search and replace and then loop until all category ids have been changed.
Table = ecommstore Column = section
I would need to change say from 84 to 1 then 86 to 2, 87 to 3 etc etc
I have had an unusual request and I thought to ask youreal smart folks out there for a hand. I am working with a non microsoft application that writes links to an html file. I need to extrapolate the link and pull it into a database table on our MS-SQL 2005 server. I believe that this woould best be done with a stored procedure.
I guess that I will get the files and save them on a given directory on the W2K3 Enterprise server and run the proceedure there.
Good Morning, I'm not quite sure this is the right place to post this, but i'll try anyway. I have a website where I allow people to create their own layouts in HTML, and in the HTML I have them placeing markers (eg. |1| ) where they would like to place something from a database. All the HTML is stored in a SQL Server DB, and when I pull it out, it's fine, except it won't allow me to Replace any of the markers with values from the DB. Here's and example line of HTML that's held in the DB:<table width='50%' border='1'><tr><td>|0|</td><td>|1|</td></tr> and when I do a strHtml.Replace("|0|", dReader["somefield"].ToString()) or even just a strHtml.Replace("|0|", "Test") it never changes it. Anyone have any ideas? Thanks, Deepthought
In my code behind file I have this function: 1 public void getAllSystems() 2 { 3 MTConnection con = MTConnection.CreateConnection(new Credential(new Context("ClearQuest - Boise", "Version 2.0", Micron.Application.Context.Environments.Production), "DSSPROD", Credential.DataSourceTypes.MSSQL, Credential.DataSourceProviders.Odbc)); 4 5 string sqltest = " select T1.dbid,T12.systemname AS 'ParentSystem',T1.systemname AS 'SupportSystem',T1.systemstate,T1.features,T3.name,T4.stakeholder," + 6 " T4.fldcolumn_1,T1.systemwebpage,T1.description from ( ( ( ( ( ( ( ( CQ_ADMIN.micronsystem T1 INNER JOIN" + 7 " CQ_ADMIN.microngroup T6 ON T1.primarysupportgroup = T6.dbid ) INNER JOIN CQ_ADMIN.securitygroups T7 ON T1.secuirtygroup" + 8 " = T7.dbid ) LEFT OUTER JOIN CQ_ADMIN.parent_child_links T12mm ON T1.dbid = T12mm.child_dbid and 16778862 =" + 9 " T12mm.child_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.micronsystem T12 ON T12mm.parent_dbid = T12.dbid )" + 10 " LEFT OUTER JOIN CQ_ADMIN.parent_child_links T3mm ON T1.dbid = T3mm.parent_dbid and 16804095 =" + 11 " T3mm.parent_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.feature_1 T3 ON T3mm.child_dbid = T3.dbid ) LEFT OUTER" + 12 " JOIN CQ_ADMIN.parent_child_links T4mm ON T3.dbid = T4mm.parent_dbid and 16804263 = T4mm.parent_fielddef_id )" + 13 " LEFT OUTER JOIN CQ_ADMIN.stakeholder T4 ON T4mm.child_dbid = T4.dbid ) where T1.dbid <> 0 and ((T6.abbreviation =" + 14 " 'Test ES Eng Software' and T1.systemstate = 'Active')) and ((T7.dbid in (select parent_dbid from" + 15 " CQ_ADMIN.parent_child_links where parent_fielddef_id = 16778767 and child_dbid in (select child_dbid from" + 16 " CQ_ADMIN.parent_child_links where parent_fielddef_id = 16777328 and parent_dbid = 33664106)and T12.systemname IS NOT" + 17 " NULL ))) order by T3.name ASC,T1.systemname ASC"; 18 19 System.Data.DataSet ds = con.ExecuteDataset(CommandType.Text, sqltest); 20 21 foreach (DataRow drCQ in ds.Tables[0].Rows) 22 { 23 string parent_system = drCQ["ParentSystem"].ToString(); 24 string child_system = drCQ["SupportSystem"].ToString(); 25 //Response.Write(child_system + "<B><I>'S PARENT IS</I></B> " + parent_system + "<BR />"); 26 } 27 } I would like to pull the values of parent_system and child_system into my main page but I don't know how to do that. Is it even possible?Thanks,Xana.
hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?
I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.
What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.
Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.
Any help would be greatly appreciated!! Thanks again!
Records in tblAPPRAISALS Table:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 A | 1/10/2003 | 5 A | 1/10/2002 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2004 | X B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
My current SQL:
SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating] FROM tblAPPRAISALS WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X" Order by 1, 2 DESC
Results from this Query:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5
Desired Results:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
using remote admin program and i need to pul the entire DB onto my local maachine to do some trial runs. I have a static IP so i thought I could just DTS the how system to my computer. Can any one verify that this is the best way of suggest another? Thanks Matt
FROM Edina_Class class LEFT JOIN Edina_Names names ON names.claimno = class.claimno INNER JOIN Edina_Address addr ON class.claimno = addr.claimno where addr.isactive = 1
What I want is to pull back the unique names, so, if the use only had 1 name, name2-4 would be blank, if the person had 3 names name4 would be blank.
Since "name1" is in the address table, I can pull that one back easy, but what is happening, is the code is taking the first name is finds in the name table and filling in names2 - names4 with it.
I have no idea how to fix the person besides recreating the name to have all of the names per user in 1 record opposed to individual records in the names table.
SQL Server 2000 - Backend Access 2000 Runtime - Front End Connecting via ODBC
I have read loads and loads of examples and looked at other sources of info for help on this but am struggleing big time.
I have multiple users working in the same database table. The problem is they often get an error message about the record has been changed and would they like to save the changes to clipboard etc etc.
I basicly want to implement pessimistic locking for my tables. So once a user has started to edit that record nobody else can get to it.
Code: SELECT P.ID, P.QTY, T_DATE FROM PARTS P INNER JOIN INVENTORY I ON P.ID = I.PART_ID WHERE P.QTY > 0 AND I.CODE = 'R' GROUP BY P.ID, P.QTY, I.T_DATE HAVING MAX(T_DATE) !> DATEADD(MONTH,-12,GETDATE())
[Code] ....
I am trying to make the sql only pull records where the highest date stored is older than a year ago.
So, based on today's date 12/21/2012:
if the highest (max) T_DATE is 3/12/2012 => don't pull that record if the highest (max) T_DATE is 11/30/2011 => pull that record into the recordset
the statement is off, it is pulling records that should not be included.
I am trying to pull subscription for a database but I always get the error message ... Error 15004: Name cannot be null What exactly have I done wrong Plaese help Thanks Nneka Echebe
Hello, I am new here and to sql so please bare with me.
I am trying to create 2 select statements from the same field. The field is a job_description field that sometimes contains PO#'s. One select statement needs to extract the PO# if it has been written in the field and the other needs to extract all data that is not the PO#
Here is some example of the data:
New shirts for 2007 Magazine cover for may edition Way to go postcards PO# 45687 PO#879
For the first select statement I need to capture just the PO#. the result for the above examples would be:
NULL NULL 45687 879
For the second select statement I need everything but the PO#. The result would be:
New shirts for 2007 Magazine cover for may edition Way to go postcards NULL
So far I have put together a basic idea for the PO# part:
(SELECT SUBSTRING (job_description , (SELECT patindex('%PO#%', job_description)+3),10)WHERE job_description LIKE '%po#%')
If i could figure out a way to only return numeric data from the above i think it will work.
As for the second select statement i am at a loss on how to select everything but the PO#.
I am having a problem trying to pull data that has apostrophe in them. How can I do this? I get this as an error
Msg 105, Level 15, State 1, Line 14 Unclosed quotation mark after the character string ''.
Select Name From Table Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)
I'm trying to pull data from about 30 progress databases using DTS andscheduling the jobs to run monthly. I'd like to pull data betweenspecific dates, but for some reason, I can't figure out how to filterthe data on the progress side.I want to run a query that will pull all data fromprior-month/8/current-yearandcurrent-month/15/current-yearI'll also have to account for when it's january, make it december ofthe year before.Any ideas?thanks,M@
I am the only DBA for a company of about 200 employees which makes about 100 million a year; I have 8 SQL servers some with over 70 databases on them that feed our web sites and educational web sites. I also have a few databases that are between 75 and 120 Gig that is for our circulation system. I am the Systems admin for all of the systems that run of my SQL server, so that makes our circ system, tradeshow system, accounting system, web sites there are about 90 of them or so. I also do programming for our IT department and some web site stuff as well as for our tradeshow dept. I am the answer man for our sales people and programmers, I also have the knowledge to build by own servers and run a network. So that€™s over 200 databases plus all the other *** that I do. Oh and by the way I'm supposed to help out and cover for our Tec support guys, witch there are 2 of them by the way. I can never take any more than 3 days off at a time; I have a month€™s vacation. I do all this for under $55,000 a year. I am never included in any decisions on software that runs on SQL nor am I consulted on any thing having to do with SQL. I work for a director who knows nothing about SQL server. There are 2 JR data guys and they do just data queries and deal with the day to day circ requests. These guys both have an office not to mention the tech guys have an office and guess what I am in a cube. I asked my boss for a laptop with a Verizon air card so when I€™m not at work if something happens that needs my attention I do have to drive to work or go home and remote in, here is the best part he looked at me and asked if I was high. I am on call 24/7 365. I have over 10 years experience working with SQL. I€™m I crazy for staying? I have been with this company for almost 8 years; I am the one who started them out on SQL server. I have converted every thing over to SQL 98.5% of all the company data I am in charge of. What would you do?
We are currently moving existing SSIS packages from one server to another. The former server is 32 bit and the new server is 64 bit clustered (not sure if that's relevant or not, though).
One package in particular is giving me a headache. It pulls from an access file to sql server 2005 table. We set up the security as don't save senstive and put the connection strings in a config file. We had to set the original job up to run the package step under a proxy account to get it to work.
If we right click on the package through the SSIS Store interface and execute it runs fine. However when we try to run it through the scheduled job, it fails. I very much appears to be a permissions issue on the proxy account. The person setting up the server is somewhat new to the area of SQL Servers, so they cannot provide much feedback. We're very much in a tweak until it works position, unfortunately.
Here are the errors that I am getting, one from the history of the job, one from the SQL Server logging. Does anyone have a suggestion, and if it is a permissions issue, where to look? I've been at this for days, so I've tried several different approaches. It seems like right now that I almost got it to work, but it seems like the connection to the access db is failing. But if that's the case, why does it run fine through the proxy on the original server (using same account)? I guess that very well could come down to the 64 bit problem (saw this on some other posts). I should mention that I set up the package as Run64BitRuntime = false. Do I need to rethink this as a batch file using DTExec? That was actually our original solution on the former server, but we could never get to work.
Job History Error:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-03-23 16:19:14.92 Code: 0xC0047017 Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2007-03-23 16:19:14.92 Source: Data Flow Task Validating: 100% complete End Progress Error: 2007-03-23 16:19:14.94 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-03-23 16:19:14.94 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation.... The package execution fa... The step failed.
sysdtslog90 error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
I seem to be having a problem in getting a string formatted correctly that I'm pulling from a SQL Datasource.I'm trying to format a Datalist, and more specifically, a Paypal "Add to Cart" button that's supposed to pass several values to another page. The problem I have is with passing the price amount. All the data is in a SQL Express database and the price field is in the database as "money". However it outputs the dollar amount with too many trailing zeros, for example $6.00 is formatted as 6.0000. I'm trying to pass it into the form variable with this code:<input type="hidden" name="amount" value="<%# DataBinder.eval(Container.dataitem, "Price", "{0}" %>">Unfortunately, that tends to make include the trailing zeros and mess up the ability to pass the variable. Can anyone help me with how to format this variable so there's no trailing zeros, just so that $6.00 comes out as 6.00 and not 6.0000?Thanks
Ok... Here is what I want to to: I have a number of tables, but this query concerns only two of them: Employee, Disposition, with a one to many relationship on the phone number.. Employee has personal info, and dispostion is a large table that keeps track of transactions. There is a unque_id for every transaction.
I want to pull some information. A date is assciated with each trans action on the disposition table. I want to be able to pull up all the unique employee phone numbers from a disposition table but only the latest date they made a transaction. My result should be only one phone nbr, one transaction number, and the date of the last transaction, per employee phne_nbr. But I can't seem to be able to pull this.
This query gets me the max date called per phne_nbr:
select 'Max'= max(dte_call), phne_nbr into TestTable from Disposition group by phne_nbr
The problem arises when I need to get the unique_id for that transaction, as I need it for other queries...The code below does not work...I get duplicate records and so forth.
select 'Max'= max(dte_call), phne_nbr, unique_id into TestTable from Disposition group by phne_nbr, unique_id
This shouldn't be that hard!! (((I tried a variation usin select distinct with the filed I wanted to be distinct in () otherfield, other field... but the distinct is not limited to the field in (), it gets the distinct record for the entire select ))) I must be missing something simple here. Can anyone help!!!
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Hi friends, I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO. I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Can any one tell me how to transfer bulk data from SQL Server6.5 to SQL Server2000.I have tried bcp out and bcp in to SQL Server2000 but it is taking very long time.Is there any way that I can pull the data into SQL Server2000 from SQL Server6.5.Can I use DTS feature of SQL Server2000 to pull the data from SQL Server6.5..
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...
I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?
E.G.
CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT ETC...........
