________________________________________________
When i use above querey and want to select any one of Attribute then it works right for @ITEM = 1 or 2 but for Item 3 it not shows the 'Factor' and show DateValues on option 3 which is wrong.
Also when i Change selection and select Code instead of Startdate then it works for all three Options.
so i guess that Startdate create a problem, but i dont know why it creating problem and how to resolve it
Plz give me some sugessions and solutions to resolve it.
Hi!I have a following problem: I need to select some attributes fromtable, i.e.first, third, fifth...Can anyone give me the hint how to do it. Is there some method to saysomthing like this:SELECT $1, $3, $5 which will refer to the 1st, 3rd and 5th attribute?Thanks!Mario.
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
I'm looking for some online resources here. Specifically, I'm interested in finding some case/project examples to learn more. I'm looking for any and all kinds in all areas...ADO, Security, Maintenance, etc. I've worn out google, but the most I seem to find is articles. I'm looking for actual Cases, like one you'd find in a text.
I have a text book from a couple of courses I took in school. Unfortunately it doesn't delve much into said areas. Any resources you could point me I will greatly appreciate it. I'd even be interested in some actual books if there's any that any of you have experience with that you think would help me out. Thanks for reading.
I recently added a nested table to a model that I had been using for a while. I noticed that after I added the nested table that the ClusterDistance() function returned 0 for every case. I went ahead and changed some of the keys for the nested table records so that the values would show up as missing and now the cases with a missing value have a non-zero ClusterDistance() value. Can anyone help me understand why this may be happening?
I have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4' then 4, else ..... then 3, else ....... then 1, end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks Ludwig
CREATE TABLE #QTEMP( [JobQueueID][int], [JobScheduleID][int], [JobID][int], [JobName][varchar](50), [JobDesc][varchar](50), [JobStepID][int], [JobStepName][varchar](50), [JobStepDesc][varchar](50), [JobStepExecutable][varchar](100), [JobQueueStatus_ID][int], [JobQueueStatusDesc][varchar](100), [NextRunDateTime][datetime], [LastRunDateTime][datetime], [ProcessID][int] )ON[PRIMARY] Declare @sql nvarchar(4000) Set @sql='INSERT INTO #QTEMP SELECT [JobQueueID], [JobScheduleID], [JobID], [JobName], [JobDesc], [JobStepID], [JobStepName], [JobStepDesc], [JobStepExecutable], [JobQueueStatus_ID], [JobQueueStatusDesc], [NextRunDateTime], [LastRunDateTime], [ProcessID] FROM [FlexPort].[dbo].[FlexPortJobQueueView] WHERE [JobID] IS NOT NULL
' IF ISNull(@JobScheduleID,'')<>'' Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%''' IF ISNull(@JobID,'')<>'' Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%''' IF ISNull(@JOBName,'')<>'' Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%''' IF ISNull(@Status,'')<>'' Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%''' If IsNull(@LastRunDateTime, '') <>'' Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
Exec master.dbo.sp_ExecuteSql @sqlI have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4' then 4, else ..... then 3, else ....... then 1, end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks Ludwig
CREATE TABLE #QTEMP( [JobQueueID][int], [JobScheduleID][int], [JobID][int], [JobName][varchar](50), [JobDesc][varchar](50), [JobStepID][int], [JobStepName][varchar](50), [JobStepDesc][varchar](50), [JobStepExecutable][varchar](100), [JobQueueStatus_ID][int], [JobQueueStatusDesc][varchar](100), [NextRunDateTime][datetime], [LastRunDateTime][datetime], [ProcessID][int] )ON[PRIMARY] Declare @sql nvarchar(4000) Set @sql='INSERT INTO #QTEMP SELECT [JobQueueID], [JobScheduleID], [JobID], [JobName], [JobDesc], [JobStepID], [JobStepName], [JobStepDesc], [JobStepExecutable], [JobQueueStatus_ID], [JobQueueStatusDesc], [NextRunDateTime], [LastRunDateTime], [ProcessID] FROM [FlexPort].[dbo].[FlexPortJobQueueView] WHERE [JobID] IS NOT NULL
' IF ISNull(@JobScheduleID,'')<>'' Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%''' IF ISNull(@JobID,'')<>'' Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%''' IF ISNull(@JOBName,'')<>'' Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%''' IF ISNull(@Status,'')<>'' Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%''' If IsNull(@LastRunDateTime, '') <>'' Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
I need to add some cases to the select statment for cpeorderstatus: Here is my Select statement: "SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD') AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER, OI.ITEM AS CPEORDERTYPE, OI.QTY, O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST, C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE, 'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC" I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
I need to add some cases to the select statment for cpeorderstatus:
Here is my Select statement:
"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD') AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER, OI.ITEM AS CPEORDERTYPE, OI.QTY, O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST, C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE, 'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"
I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.
A parameter in my stored procedure "@intUserType" may contain values 0-3
If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records
The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.
This is what I am working with:
Code: SELECT * FROM tblEmployees WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END) OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END) OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))
Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.
do you know if I can do the following task through a single query
TableA(LocID,LocNAME) TableB(ID,LocID,Amount)
What i need to do is to add sum amount having same locID from TableB and get LocIDs name through TableA.LocName. In the query there should be one thing more, if amount is less than zero put it into credit column, while if positive, puts in debit column
Thus Result(LocId, LocName, Debit, Credit) is the requied structure. Can anyone help me out. I m not getting how to get the LocName if gets the sum by Groupby LocID also applying condition is confusing me:s
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.
I have this: DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp] JOIN (select emailaddress, account_id, max(contact_id_tmp) max_cid from [acctID_emailAddress_tmp] group by emailaddress, account_id) AS tempImportTable ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress] WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid] AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];
but it doesn't work since it's keeping the subset of the dupe row(s).
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table CaseID, CaseNumber, CaseName
CaseChild Table CaseID, FK to CaseInfo ChildPartyID, FK to PartyID in Party table ProgramClosureDate
Party Table ID, PartyID, Firstname, LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate FROM CaseInfo ci JOIN CaseChild cc ON ci.CaseID = cc.CaseID JOIN Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
I haven't been able to find a DMX query which will spit out the cases which support a particular association rule. I was hoping it would work sort of like drillthrough but show only the cases supporting a particular rule. Am I missing something?
What I ended up doing was extracting the itemsets of the rule from the model's content then running a SQL query to retrieve the cases that contain both the left-hand and right-hand itemset of the rule. I'm hoping there's a better way.
I am testing a set of SSIS packages, In order to test my SSIS packages for errors I have two negative test cases
1) I didn't provide checkpoint file for the checkpoint enabled package.
2) I provide a wrong configuration file
Even though I am using a script task in my "on error" event of my SSIS package. It is not executed. (Perhaps because the package doesn't even execute).
My problem is that SSIS itself puts just a simple one liner in windows event log "Package Failure Error". It does not provide which package failed, why it failed etc. Therefore the admin who gets the ticket to resolve the issue has no clue of what is going wrong and where!
Since my custom logger doesn't even run, I don't know how can I put more details into the windows event log.
Configuration: MS SQL server 2005 SP2, and MS jdbc driver version: 1.1 The sendStringParameterAsUnicode has been set to false for performance reasons. However, when inserting unicode data, we would like to override the setting and send the data encoded in unicode, instead of defaulting the whole app to unicode=true and take a performance hit.
Any suggestions? We have tried the cast(? as nvarchar) function, but that did not help.
Sample code/output: String text = "u0143u0144"; sendStringParametersAsUnicode=false insert into unitable (_ntext) values (?) Inserting into databse: 143 144 (printed hex values) Read from database: 3f 3f (printed hex values)
I have created a custom CLR user define aggregate function based on the example that i found at http://msdn2.microsoft.com/en-us/library/ms131056(SQL.90).aspx.
It works great until i discovered that it will failed if i try to do either one of the following:
query a large records e.g: more than 4k records has IS NULL in my where clause e.g: WHERE myConcatenatedFld IS NULL
Other than these two, it works perfectly fine.
Here is the error that i got:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have attached the Concatenate class that i used in my UDAGG.
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
How to write a Query for multiple legal names that have the same CARE Number (same address) with difference of one Legal Name having a period in the name versus the other legal name that doesn't.
For example: Looking for cases of two of the same legal name one set off by period
All Season Equipment Ltd. All Season Equipment Ltd
West End Housing, Inc. West End Housing, Inc
Wellings, Norman L. Wellings, Norman L
North Texas Boats, LLC North Texas Boats, L.L.C.
Oktibbeha County Cooperative (A.A.L.) Oktibbeha County Cooperative (AAL)
S & R Turf & Irrigation Equipment, L.L.C S & R Turf & Irrigation Equipment, L.L.C.
Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc. Burke Equipment Company Burke Equipment-Seaford, Inc.
Pleasant Valley Outdoor Power, L.L.C. Pleasant Valley Outdoor Power, LLC
J & D Lawn and Tractor Sales, Inc. J&D Lawn & Tractor Sales, Inc"
Actor train nested table: ID MovieID Gender 1 1 F 2 1 M 3 1 F 4 1 F 5 2 M 6 2 M 7 2 F 8 3 F 9 3 F 10 4 M 11 4 M 12 4 F 13 4 F 14 5 F 15 5 M
We want to build a classifier model in order to predict the Class of a Movie based on the Gender of movie's actors. To deal with the nested table Analysis Services maps each record of the nested table to an attribute of the case table. These attributes are named Actor(n).Gender with n = 1..15, and so they are dependent on the nested table record numbers. Both Microsoft Decision Trees and Microsoft Naive Bayes algorihms use these attributes without any modification.
We are implementing a Relational Naive Bayes algorithm and we are planning to aggregate such attributes in order to make them independent of the nested table record numbers.
Next step we tried to predict some unseen cases and here we face with a very huge problem.
Lets take more two tables of unseen cases:
Movie test table: ID Class 6 + 7 NULL 8 NULL
Actor test nested table: ID MovieID Gender 1 6 F 2 6 M 3 6 F 4 6 F 16 7 F 17 7 M 18 7 F 19 7 F 20 7 F 21 8 M 22 8 M 23 8 F
Predicting the movie 6 Class is not a problem since the movie actors were included in the training dataset and when the records are mapped to attributes because they already exist in the model. But when you try to predict movies (7 an 8) with unseen actors all new attributes are simply ignored in the ALGORITHM:redict call (in_ulCaseValues is zero!) because they do not exist in the model!
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000 System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000 System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000 System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000 System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000 System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
INSERT INTO mytable (mydataitem,mydatefrom,mydateto,myopcode,lastupdat e) VALUES ('SAMPLEDATA','01/01/2003','18/05/2003',1,'05/Mar/2004')
THE ERROR RETURNED IN MY CLIENT APP IS [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
AND THAT IN QA IS
Server: Msg 296, Level 16, State 3, Line 1 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. The statement has been terminated.
can ne 1 point out the mistake in my sql statement ?
I have an Execute SQL Task that pulls the max date from a sql table.
SELECT max(date_Idx) FROM dbo.FactDailyInventorySnapshot
This field is defined as a smalldatetime. I want to store this max date in a variable in SSIS called LastDate.
Then in a data flow, in an OLE DB Source, I want to use it as a parm in a sql command to compare to a smalldatetime field.
My question is, what type of variable do I declare it in in SSIS, and what is the correct parameter mapping inside of my sql query? I thought I had exhausted all combinations of variable types and parameter types. I have even tried casting it as different data types when I do the exec sql task of getting the max date. Any help would be appreciated. Thanks!
I need to convert a datetime field to smalldatetime. This particular field we only care about the time portion (an example would be '1899-12-30 13:15:00.000') For now I created another field say 'newTime' that is smalldatetime, in which I want to "update" to the smalldatetime version of the data. I know this will truncate the ms, but I don't care about that. Also the min date that can be used with smalldatetime is Jan 1 1900. Not sure how to go about doing this.
Hi guys! I need to convert datetime data type to smalldatetime on production server with hundreds transactions per minute. In this case do I need to restrict users access to the table or put table in the single user mode? Or it doesn't have any impact on productivity and I just can open Design table window in SQL Enterprise Manager and edit it? Thank you in advance, Igor
