Analysis :: Backend Operations To Run MDX Query
Oct 13, 2015Whenever we run the mdx query in SSMS, how it will fetch the data from cube.
Using XMLA script used to fetch the data or any thing else ..
ADVERTISEMENT
SQL Backend Access Adp Frontend - Stupid QUERY!!!!
Sep 26, 2005Ok, little frustrated hence the excessive exclamation points.
I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!
I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!
And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?
Is there something simple i'm missing or is it my table structure????
Thanks in advance!
Set Operations (EXCEPT/EXCEPT ALL)
Jul 20, 2005Hi all,I just start using SQL Server for my project. I have some questionsrelated to set operations. Suppose I have two tables, Table A andTable B, as following.TableA TableB======= =======--------------- ---------------| ID | DATA | | ID | DATA |--------------- ---------------| 1 | Val-01 | | 1 | Val-01 || 2 | Val-01 | | 2 | Val-02 || 3 | Val-02 | | 3 | Val-02 || 4 | Val-03 | | 4 | Val-03 || 5 | Val-04 | ---------------| 6 | Val-05 |---------------In DB2, I can write SQL statements as followingSQL 1:===========================SELECT DATA FROM TableAEXCEPTSELECT DATA FROM TableBAnd the result will beVal-04Val-05===========================SQL 2:===========================SELECT DATA FROM TableAEXCEPT ALLSELECT DATA FROM TableBAnd the result will beVal-01Val-04Val-05===========================1. How can I handle the EXCEPT (ALL) operator in SQL Server?2. Are there equivalent SQL queries for the above SQL queries?Thank youErwin Leonardi
View 4 Replies View RelatedMS SQL DB Operations
Mar 31, 2008I'm still a newbie with MS SQL DB. I was asked to perform some tasks within 3 days at maximum as follows:
1- Refine unique names from over than 80,000 students' names (Firstname, Middlename and Lastname) into a unique table so that we take all those name and translate them from local language to English through data entry employees (a dictionary).
2- Replace local names by English names into a new columns.
3- Creating a procedure that auto generates aliases based on Firstname, Middlename and Lastname as follows:
Assume the name John Edward Smith is found 5 times between 80,000 students, to create a unique ID do the following:
1- FirstnameLastnameint (JohnS).
2- FirstnameinLastname (JSmith).
3- FirstnameLastname (JohnSmith).
4- Firstnameint.Lastname (J.Smith).
5- Firstname.Middlenameint.Lastname (John.E.Smith).
Another precedure also needed:
We have 4 divisions at the establishment, needed to symoblize each division by a letter followed by ( - ) sign as follows:
1- If division = X , generate: x-ID
2- If division = Y, generate: y-ID
3- If divison = Z, generate: z-ID
4- If division =S, generate: s-ID
Can you please provide me with such a procedure to perform this in details?
Thank you
Analysis Services Query Log
Aug 10, 2004Hi,
Does anybody know how to control the Analysis Services query log properties?
I can clean it, extract its data to Excel however it's apparently being cleaned automatically every now and then (which I want to prevent) and I haven't found any clear documentation on the topic.
Thanks and kind regards.
7 Day Sales Analysis Query
Aug 31, 2014Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.
now the time is 22:00 & group by shop plus underline +24 hour format
Sun Mon Tue Wed Thur Fri Sat Sun Total
Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx
-------------------------------------------------------------------
Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx
Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $30 $30 $10 $4 $20 $0 $no sales $xxx
Analysis :: Projection In Mdx Query?
May 15, 2015I have a requirement where I need to project data point based on past data points. Like for 2015, I have data points for Jan,Feb and March. For April, I need to do Yeartodate and then divide by Count of nonempty data points. Like Jan is 25, Feb is 40 and mar is 50, then the projection is 115/3 for remaining 9 months of 2015. I need to count NONEMPTY Measure to get the count. Count has to be 3 for march. Since , there is no data after march, count for Apr to Dec should all have 3.So, the count should look like this:
1 2 3 3 3 3 3 3... till Dec.
Flash SQL Backend
Mar 13, 2007Hi there SQL TEAM
Can someone please point me in the right direction?
We have a customer who would like us to write an application in flash that has a SQL 2005 Backend
Can Flash integrate with Microsoft sql 2005?
And if so are there any tutorials?
Nonlogged Operations
Sep 28, 2000Hello,
I would like to know if there is a way to disable logging within SQL. (SQL7)
I set all of the obvious options (select into/bulk copy; truncate on checkpoint...) -- and not using SQL Servers conditions set in books online.
Here is the situation:
We have a VB app that updates about 14million records on a monthly basis. The commands are INSERT, UPDATE.. (no select into OR write text) There is no way to use this app that I know using BCP, or bulk insert since we are updating and not simply importing?
So now when this process runs, first the Tempdb fills(problem 1). I shut down SQL and re-initalized the TEmpdb. The data "update" should take ~3 days. Initial run shows the Tempdb filling, then the thread halts. Basically, the application copies records from a user defined "staging" database into a second user database. The second db's transaction log is growing considerable (say 6GB and growing after only a day) (problem2). I am concerned that after resolving the Tempdb from filling (unrestricted size set, and I know Tempdb automatically truncates on checkpoint..) but then we still face the issue to the db that the data is updated will fill out of control!
Ideally, I would like to set a db option that disables logging during this UPDATE statement, in other words operate with no integrety. We backup before so a restore should take care of things in the event. I think this will also aid in performance.
Thoughts on update nonlogged operations if any?
Thanks in advance.
Jason
Row By Row Operations In Sqlserver
Sep 15, 2006hello,
I need to push data from a temporary table into a master table in sql server database(Both tables are in the same database).
I need to follow these conditions.
tables used:
1. temporary table: Temp
2. master table: Master
for every record or row in Temp
check if exists(Temp.field1)in Master.Field1 then
update Master with this row.
else Insert into Master this row.
I performed a research through the net, and found various suggestions. like usage of cursors, usage of while loops etc.
I have to use nearly 50,000 to 60, 000 rows minimum or even more.
Time complexity is also to be considered
I request all who visit this thread to place any possible solutions/suggestions how shall i make this task.
I thank all in advance
File Operations
Apr 17, 2007I need to know, how to rename a file, how to delete a file. how to create a file, all programmatically in Vista.
Thanks in advance,
Frank
Only For Gurus -- Analysis MDX Query Help Needed
Mar 27, 2004I need a expression to get the name of the Member of a dimension.
For example .. if I have a Time Dimension ... and Child in hierarchy are Months.. I would like the result as
January
February
......
December
Any Ideas ???
Analysis :: How To Make Group By Query Using DAX
Jun 5, 2015I want to make a calculation to have the same return as the following SQL Query:
SELECT MAX(COLLECT_TOTAL)
FROM [dbo].[FACT_CAPACITY_COLLECT]
GROUP BY CI_NAME, FILESYSTEM_ID, SUBTYPE_ID, Month_DAY
How Can I do it?
Analysis :: MDX Query For Total Profit
Sep 3, 2015i am getting error for the mdx query fortotal profitis there any other way to write??????
View 2 Replies View RelatedAnalysis :: MDX Query Parameters In WHERE Clause?
Sep 25, 2015I need to filter my select statement with 2 parameters, each of which defaults to the "All" member. Some of the members may have spaces in the name.
So I need to handle something like these:
FROM MySalesCube
WHERE ( ... expression here ...)
The expression above needs to resolve to a set using both parameters
STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")
Running the query in SSMS, Im using something like this to test
WHERE ( [SalesPerson].[SalesPerson].&[17] , [Department].[Department].[All] )
I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.
[update]
Here is what I have now:
WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"] , [Department].[Department].[" + @pDept + "])") ) )
If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error
Analysis :: MDX-Query Throwing Error
Nov 18, 2015I am new to SSAS and learning mdx, I am running the following query but it is throwing error "Parser: The syntax for 1 is incorrect",
Select [Measures].[Order Quantity] on axis(0),
[DimProduct].[Product By Category].[ProductCategory].members on axis(1)
From [ResellerSales]
where
Except({[DimProduct].[Product By Category].[ProductCategory].members},
{[DimProduct].[Product By Category].[ProductCategory].&[1]})
Analysis :: All Member Not Working In MDX Query
May 28, 2015[DIMCustomerBuyer].[CustId].[CustId] is working.
[DIMCustomerBuyer].[CustId].
All members is not working any hint why this is not working.
Analysis :: Subset Function In MDX Query
May 18, 2015I need other function act like subset function using mdxquery.
In my mdxqueries,contain subset,order functions are available.
subset and order is getting poor query performance.
Analysis :: Less Than Date Value From Parameter For MDS Query
Jun 25, 2015I don't have much knowledge on MDX queries. Here, requirement is to get the data less than the date passed from the parameter(Date - IV Financial].[calendar YQMD].[Date]).
I am able to create the below query(through Query designer) which is giving the data EQUALS to operation. But, I need the same with LESS THAN.
SELECT NON EMPTY { [Measures].[InventTrans Quantity] } ON COLUMNS,
NON EMPTY { ([Items].[Item Id].[Item Id].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@DateIVFinancialCalendarYQMD, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( { [Inventory].[Is posted].&[Posted] } ) ON COLUMNS
[Code] ....
Linking To A SQl 7 Backend Using Access 97
Mar 22, 2001I created a SQl Server 7 database and used an Access 97 frontend on the same computer. The Access frontend uses linked tables to reach the SQl server. The security setup I have is based on the NT Domain Accounts and whatever group
I have them in (example: Mr. D is in Marketing therefore he is in a NT Domain
Group called "SQL_MARKETING"...the SQL Server recognizes that everyone in this
group has access to whatever role I have designated for it in the database").
I can access the linked tables fine on my computer at my desk or at the server. Another person with the exact same administration rights as me can
get to the server through the network neigborhood or by mapping..and they
can access the Access frontend with no problem from their computer. But
when they try to open a linked table they recieve an error message that
the ODBC connection has failed. We are all running Access 97 service pack
2 and another person and me have the exact same administration..but only
I am able to access the Access linked SQL tables but not him.I tried deleting
the ODBC system dsn on the server and the linked tables and then re-adding them and the ODBC link but to no avail. Does anyone know why this would
be occuring? Thank You in advance.
and the
Updating Backend Via Excel VBA
Dec 17, 2014I have create an Excel application which connects to an SQL Server backend.I have written an Excel vba procedure to update a table via ADO.But it couldn't---the error message says the provider (sqloledb) does not support updating database.
View 2 Replies View RelatedUpgrading Backend Access
Sep 10, 2013We are entertaining the idea of moving our data to a cloud and I need to know how to convert my Access backend DB to SQL. which version of SQL will I need to use to do the conversion and will also work in a cloud environment.
View 2 Replies View RelatedHow To Create SSIS Using CRM 3 As A Backend
Jan 8, 2008Hi,
I have some knowledge of SSIS and CRM. But i don't know how to create Integration Service using CRM 3.0 as my external data source. If anybody know about this, pls give steps to create Integration Service as soon as possible.
Thanx in advance.
Exe File With Sqlserver Backend
Jan 12, 2008I have an exe file which was generated using a visualbasic application and has a backend sqlserver database. the exe works fine on my computer which is also connected to sqlserver and the same exe when i run on the clients computer does not work and gives an error ...related to converstion of datetime datatype ...
View 2 Replies View RelatedSql Backend/access Frontend
Dec 2, 2006I have a database with a sql backend and an access frontend. I put it in a folder on the network for users to access. First, they were getting errors like not being able to access the database if someone else had it open and not being able to perform certain functionality. It was suggested that i put a mde on each of the users pc. i gave them all access to their own front ends on the folder because they use different pcs. this solved the problem. however, i need a way to perform updates without manually creating mdes for every user. i used to work for a co that there was one mde was put into a folder on the network with a config file. The users access the database through CITRIX. Whenever they accessed the shortcut on the server, it created the users their own front end. Whenever we need to provide updates, we simply made a new mde from the mdb and whenever the user accessed the database again, they would have the latest mde and it would created an update frontend for the user. Does anyone know of such an utility or know what I am referring too? I encounted several suggestions like a batch file; but they refer to putting the file and the mde on each users pc. This is not how i'm set up. Thanks
Table In Sql Does Not Appear In Access Backend!!!
May 6, 2008I add a table to sql studio express, but i cand see it in access backend in the other computer.
What is wrong?
Thanks,
Azi
Access With An SQL Server Backend.
Dec 12, 2006My question is this: "Does anyone know where to set up the connectivity for an access database to store data on an Sql Server?"
View 6 Replies View RelatedGenerate Ad Hoc Report In Backend
May 30, 2007Hi,
Currently I am developing a project which requires to generate time-consuming reports in ad-hoc real time basis. The mechanism is
(1) User submits report request with required parameters
(2) The request will be processed one by one (I guess a queue is required to monitor the number of requests and the status of each request)
(3) Reporting service will get the report request and generate report in real time.
(4) Once report is generated, send the report to user
So, is there any idea on how to handle this type of mechanism in reporting services? Any tool in MS reporting services that can help?
Thanks in advanced.
Datetime-Based Operations
Apr 26, 2007I'm new to SQL Server and relatively new to database design and I have a specific problem I'm trying to resolve. I have a collection of records in a table (let's call them 'tasks') which represent some list of things that needs to be completed. Each task has an associated datetime on which this action is to commence, and my applications is responsible for executing these tasks. My first instinct is to regularly poll the 'tasks' table to determine if there are any tasks which have not been processed and are past their schedule start datetime. But, I'm wondering if there is some sort of database feature that would recognize that a task's starttime has arrived, and could somehow communicate this to my application without my application having to constantly poll the database. Thanks.
View 5 Replies View RelatedBatch Operations Of SQL Command.
Jun 6, 2007Hi,Im trying to write a class that compiles a list of SQLCommands and then executes them all at once. Im trying to reduce the amount of calls to the database.Im also trying just to update the fields which have changed so I cannot use Stored Procedures as that would mean writing way too many stored procedures for every permutation on every table in my database.So Ive decided to build sql commands and then execute them all with one call to the database. When I print the commandtext property of the sqlcommand to the page whilst debugging It shows something like insert into XXX (f1,f2) values (@v1,@v2). Is there any way to see the final sql string, with the @v1 variables replaced by the actual values in the parameters I have added to the sql command?Im building the commands by creating a new sqlCommand. Then I set the commandtext to "insert into XXX (f1,f2) values (@v1,@v2)". Then I Add Parameters to the sqlCommand. My Parameters count is showing the correct value.I want to be sure now that when I go to send these commands to the database as part of one long string that it will work.Thanks,C
View 3 Replies View RelatedOperations Through Stored Procedures
Nov 20, 2007i write a following stored procedure
create proc prc_BUSINESS_MASTER(@chByMode Char(1), @intLIST_ID int=null,@intLISTBUSINESSID int=null,@dtmDATE datetime=null,@dtmEXPDATE datetime=null,@vchSTATUS varchar(10)=null,@vchOFFICE varchar(100)=null,@numCITY numeric =null,@numCOUNTY numeric=null,@numSTATE numeric=null,@numCOUNTRY numeric=null,@vchSICCODE varchar(50)=null,@numASKINGPRICE numeric =null,@numDOWNPRICE numeric=null,@bitOWNERFINANCE bit=null,@numOWNERFINANCE numeric=null,@numADJ_NETAMOUNT numeric=null,@numSALESAMOUNT numeric=null,@dtmYOE datetime=null,@dtmYRSOWNED datetime=null,@bitBITISACTIVE bit=null,@vchCREATEDBY varchar(50)=null,@dtmCREATEDDATE datetime=null,@vchUPDATEDBY varchar(50)=null,@dtmUPDATEDDATE datetime=null
)
as
set nocount ondeclare @byIntErrDescOut intdeclare @interrno intdeclare @intdupchk intbegin
IF @chByMode NOT IN ('I','U') BEGIN SET @byIntErrDescOut = 1 Return -1 END SET @byIntErrDescOut = 0 /* *************** Insertion Area *************** */
IF @chByMode = 'I' BEGIN
IF @vchSTATUS IS NULL OR @vchSTATUS = '' BEGIN SET @byIntErrDescOut = 1 Return -1 END
/***** Duplicate checking ****/
SELECT @intDupChk = COUNT(1) FROM BUSINESSMASTER WHERE vchLIST_STATUS=@vchSTATUS And vchLIST_OFFICE=@vchOFFICE IF @intDupChk <> 0 BEGIN SET @byintErrDescOut = 5 Return -1 END
insert into BUSINESSMASTER(
intLIST_ID,dtmLIST_DATE,dtmLIST_EXPDATE,vchLIST_STATUS,vchLIST_OFFICE,numLIST_CITY,numLIST_COUNTY,numLIST_STATE,numLIST_COUNTRY,vchLIST_SICCODE,numLIST_ASKINGPRICE,numLIST_DOWNPRICE,bitLIST_OWNERFINANCE,numLIST_OWNERFINACE,numLIST_ADJ_NETAMOUNT,numLIST_SALESAMOUNT,dtmLIST_YOE,dtmLIST_YRSOWNED,bitBITISACTIVE,vchCREATEDBY,dtmCREATEDDATE,vchUPDATEDBY,dtmUPDATEDDATE
)values(@intLIST_ID,@dtmDATE,@dtmEXPDATE,@vchSTATUS,@vchOFFICE,@numCITY,@numCOUNTY,@numSTATE,@numCOUNTRY,@vchSICCODE,@numASKINGPRICE,@numDOWNPRICE,@numOWNERFINANCE,@bitOWNERFINANCE,@numADJ_NETAMOUNT,@numSALESAMOUNT,@dtmYOE,@dtmYRSOWNED,@bitBITISACTIVE,@vchCREATEDBY,@dtmCREATEDDATE,@vchUPDATEDBY,@dtmUPDATEDDATE
)
SET @intErrNo = @@Error IF (@intErrNo <> 0) BEGIN SET @byIntErrDescOut = 2 Return -1 END
/*Insertion Area End*/
/*-------Updation----------*/
/* IF @chbyMode = 'U' BEGIN --IF @intBuyerId IS NULL BEGIN SET @byIntErrDescOut = 1 Return -1 END
*/
IF (EXISTS (SELECT * FROM BUSINESSMASTER WHERE intLISTBUSINESSID = @intLISTBUSINESSID )) BEGIN
/***** Duplicate checking ****/
SELECT @intDupChk = COUNT(1) FROM BUSINESSMASTER WHERE intLISTBUSINESSID = @intLISTBUSINESSID and vchLIST_STATUS=@vchSTATUS IF @intDupChk <> 0 BEGIN SET @byintErrDescOut = 5 Return -1 END
UPDATE BUSINESSMASTER SET intLIST_ID=@intLIST_ID,dtmLIST_DATE=@dtmDATE,dtmLIST_EXPDATE=@dtmEXPDATE, vchLIST_STATUS=@vchSTATUS,vchLIST_OFFICE=@vchOFFICE,numLIST_CITY=@numCITY,numLIST_COUNTY=@numCOUNTY, numLIST_STATE=@numSTATE,numLIST_COUNTRY=@numCOUNTRY,vchLIST_SICCODE=@vchSICCODE,numLIST_ASKINGPRICE=@numASKINGPRICE,numLIST_DOWNPRICE=@numDOWNPRICE,bitLIST_OWNERFINANCE=@bitOWNERFINANCE,numLIST_OWNERFINACE=@numOWNERFINANCE,numLIST_ADJ_NETAMOUNT=@numADJ_NETAMOUNT,numLIST_SALESAMOUNT=@numSALESAMOUNT,dtmLIST_YOE=@dtmYOE,dtmLIST_YRSOWNED=@dtmYRSOWNED,bitBITISACTIVE=@bitBITISACTIVE,vchCREATEDBY=@vchCREATEDBY,dtmCREATEDDATE=@dtmCREATEDDATE,vchUPDATEDBY=@vchUPDATEDBY,dtmUPDATEDDATE=@dtmUPDATEDDATEWHERE intLISTBUSINESSID = @intLISTBUSINESSID -- AND WhenUpdated = @ptmsUpdated SET @intErrNo = @@Error IF (@intErrNo <> 0) BEGIN SET @byIntErrDescOut = 4 Return -1 END END ELSE BEGIN SET @byIntErrDescOut = 4 Return -1 END END End
How can i check this procedure by performing insert ,update operations?
ie., how can i passing and updating informations through stored procedures?
Explain me in detail....
Status On Set Based Operations?
Aug 26, 2004Every time I mention cursors, I hear a lot of people pointing out how you shouldn't use cursors and how set based operations are much faster.
However, procedural code offers the big advantage that I can embed status/progress type commands into a cursor loop and get accurate data on progress and ETA.
If I execute a single huge UPDATE/INSERT, I am at the whim and mercy of SQL Server. Seemingly simple statements often run for 50 hours+ and I have no clue how far along it is or anything remotely close to an ETA. Often, that is absolutely inacceptable to have mission criticial hardware to be churning through a process for DAYS without the slightest idea if it is actually making progress or not.
Is there any way to get progress/status on such an UPDATE/INSERT command?
Cross Database Operations
May 6, 2008Hi,
I'm trying to get data from several databses of different kinds (Sql Server and Oracle) and from different servers - is that possible ?
If not - is there any way to copy some data from one database to another - like tables and their content (or partial content) ?
I tried to use "select name from <database name>..sysobjects where xtype = 'U'" but I get security error.
thanks,
Naama