Probem In Executing A Long Dynamic MDX From SQL Server
May 22, 2006
Hi ,
What i exacly want to do is
1. Connect to OLAP server from my Sql server using following query
string
'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; Initial
Catalog="MRS";
2. I want to execute my dynamically created MDX query . This query can
be greater than 8000 varchar limit.
When my query length exceeds 8000 length i break it up into 2 parts
..Here I have broken my query into 2 parts
@mdx1 and @mdx2
Now i execute the entire statement as
exec('SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; Initial
Catalog="MRS";'',' + @mdx1 + mdx2 ') as ' )
Still error comes that :
Unclosed quotation mark before the character string 'WITH MEMBER ..
('With member' is the starting statement of my MDX query)
Is there any other way to connect to OLAP server and execute an MDX
statement with a Length greater than 8000 chars
I have created a trigger on a table that get's too many updates so to analyze what records and what columns get updates I have coded a trigger that inserts into a table the record key and a flag on each column to let me know what is updates. Now that I am presenting the data to the developers I have been asked to provide the update / insert or delete statement
So on my trigger I have tried using dynamic management views but its returning the actual trigger code not the parent SQL Script that triggered it.
This is what I am using.
select@dDate as dDate, dest.[dbid], dest.[objectid], dest.[number], dest.[encrypted], case when sder.[statement_start_offset] > 0 --the start of the active command is not at the beginning of the full command text thencasesder.[statement_end_offset]
I was trying to install sql server 2005 in my pc (xp prof. sp 2). I downloaded this file from Microsoft site.
After Unpacking the content of the file and checking the integrity of that package it€™s showing it€™s finished the installation. Now if I press the finish, it€™s showing cannot open package it€™s may be corrupted.
I downloaded 3 times and still same thing. I tried in server 2003 too but no luck. I really don€™t understand this. Can anyone plz help me?
I am using the Dynamic Cross-Tab code supplied in an article from SQLServer Magazine (http://www.winnetmag.com/SQLServer/...608/15608.html).I modified the script to generate a temp table inside the storedprocedure, and then use this temp table as the source for thecross-tab. However, the problem seems to be that the dynamic SQLstring generated by the script is longer than what can be stored inthe @SQL variable. The Cross-tab works great, so long as the amount ofdata to be pivoted is small.Is there any way around this? E.g. a User defined type, or anotherdata type which can store more characters?Thanks,TimCREATE procedure CBN_CrossTab@StudyID varchar(100), --Model ID passed from web app - Only one modelcan be selected@Level int --The level to which the taxonomy should be rolled upAsDECLARE@Table as sysname, --Table to crosstab@OnRows as nvarchar(128), --Groupuing key values (on rows)@OnRowsAlias as sysname, --Alias for grouping cloumn@OnCols as nvarchar(128), --destination columns (on columns)@SumCol as sysname, --data cels@SQL AS varchar(8000), -- String to hold generated SQL String@NEWLINE as char(1) --Holds the New Line Character for the codeSET @OnRowsAlias = NullSET @SumCol = NullSET @NEWLINE = CHAR(10)-- Generate the Temp table for the taxa and countsCREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),fltCount float)INSERT INTO #RefOrganisms(sampleid, txtTaxa, fltCount)SELECT dbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ") AS Taxa,SUM(dbo.tblbenthic.[count] /dbo.tblsitedetail.numberofreps) AS SumCountFROM dbo.tblstudylist INNER JOINdbo.tblsite ON dbo.tblstudylist.studyid =dbo.tblsite.study_id INNER JOINdbo.tblsitedetail ON dbo.tblsite.siteid =dbo.tblsitedetail.site_id INNER JOINdbo.tblsampledata ONdbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_idINNER JOINdbo.tblbenthic ON dbo.tblsampledata.sampleid =dbo.tblbenthic.sample_id INNER JOINdbo.iter_intlist_to_table(@StudyID) i ONdbo.tblstudylist.studyid = i.number INNER JOINdbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =dbo.tblbenthictaxa.tsnWHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =2) AND (dbo.tblbenthictaxa.rank_id >= @Level)GROUP BYdbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ")-- Identify the Temp table info for the CrossTabSELECT @Table = '#RefOrganisms'SELECT @OnRows = 'sampleid'SELECT @OnCols = 'txtTaxa'SELECT @OnRowsAlias = NullSELECT @SumCol = 'fltCount'--STEP1 BEGININNING OF SQL STRINGSET @sql = 'SELECT'+ @newline +' '+ @onrows +CASEWHEN @ONROWSALIAS IS NOT NULL THEN ' AS ' + @ONROWSALIASELSE ''ENDCREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NOT NULL PRIMARY KEY)DECLARE @KEYSSQL AS VARCHAR (1000)SET @KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +@ONCOLS + 'AS NVARCHAR(100)) ' + 'FROM ' + @TABLEEXEC (@KEYSSQL)DECLARE @KEY AS NVARCHAR(100)SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHILE @KEY IS NOT NULLBEGINSET @SQL = @SQL + ' ,'+ @NEWLINE +' SUM(CASE CAST(' + @ONCOLS +' AS NVARCHAR(100))' + @NEWLINE +' WHEN N''' + @KEY +''' THEN '+ CASEWHEN @SUMCOL IS NULL THEN '1'ELSE @SUMCOLEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS [' + @KEY + ']'SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHERE KEYVALUE > @KEYENDSET @SQL = @SQL + @NEWLINE +'FROM ' + @TABLE + @NEWLINE +'GROUP BY ' + @ONROWS + @NEWLINE +'ORDER BY ' + @ONROWSPRINT @SQL --+ @NEWLINE --FOR DEBUGEXEC (@SQL)GO
I am facing a problem in a dynamic sql query. My query is
EXEC (' SELECT A.Atm_Model, CU.Credit_Union_Name, CASE WHEN LICENSED_SERVICES_NAME=''VISA'' THEN 1 ELSE 0 END AS "VISA", CASE WHEN LICENSED_SERVICES_NAME=''PLUS'' THEN 1 ELSE 0 END AS "PLUS", A.Atm_Make FROM ATM A LEFT JOIN Credit_Union CU ON CU.Credit_Union_ID=A.Credit_Union_ID LEFT JOIN Credit_Union_Licensed_Services CULS ON CU.Credit_Union_ID=CULS.Credit_Union_ID LEFT JOIN Licensed_Services LS ON LS.Licensed_Services_ID=CULS.Licensed_Services_ID ')
when i execute this query it works fine but when i try to execute the following query
SET @VAR=dbo.DYN() SET @VAR1=('SELECT A.Atm_Model, CU.Credit_Union_Name, '+@VAR+' A.Atm_Make FROM ATM A LEFT JOIN Credit_Union CU ON CU.Credit_Union_ID=A.Credit_Union_ID LEFT JOIN Credit_Union_Licensed_Services CULS ON CU.Credit_Union_ID=CULS.Credit_Union_ID LEFT JOIN Licensed_Services LS ON LS.Licensed_Services_ID=CULS.Licensed_Services_ID') EXEC (@VAR1)
it throws an error that "Incorrect syntax near VISA"
dbo.DYN is a function that writes string CASE WHEN LICENSED_SERVICES_NAME=''VISA'' THEN 1 ELSE 0 END AS "VISA",
CASE WHEN LICENSED_SERVICES_NAME=''PLUS'' THEN 1 ELSE 0 END AS "PLUS",
I am really in a great trouble. My requirement is quite complex, as I feel, it may be quite simple for some of you. Here is my problem - Actually I am doing a project, where client has a specific requirement. i.e. he want's to build a query on runtime for selecting particular record he wants, so that we have provided a user interface where he can select any datasource e.g. SQL, Oracle, Excel etc. He also specifies the database name. He is displayed all the tables and columns under those tables. He selects columns from those table boxes and write the query he wants, with where clause, if required. I am saving these query in a table, storing column names in another table where we map those oringinal column names with columns of another table, wehre we want to store actual result set of the prepared query by the user. for examaple .. if user preapare query like - 'SELECT CUST_ID, CUST_NAME FROM CUSTOMER WHERE CUST_ID = 10' In case of above query I will store CUST_ID in column COL1 of table TAB1 and CUST_NAME in COL2 of table TAB1, like that we have such fifty columns in that table. Now question is here every thing is dynamic data provider, database, tables, columns and where clause, then how can get the result set out of those queries and store that query output in the that storage table with columns col1, col2 and so on, upto 50 columns. Please help me on this, as it is so urgent. I will be very much thankful to you people.
Hi, In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:
Msg 16916, Level 16, State 1, Procedure SP2, Line 114 A cursor with the name 'C1' does not exist.
Yes, the cursor is of type GLOBAL. I am sure I miss something here ... Any help is highly appreciated !
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Trying to tackle this problem. It's a stored procedure that modifies data in two tables with a one-to-many-to-one relationship. I'm creating a C# front-end to change the data fields (Table1Data and Table2Data) in ASP.NET 2.0/VS 2005. The error I get is "Procedure or function Modify has too many arguments specified. " when I try to edit. Table1 has Table1ID (PK, int), Table1Data (varchar(50)) ; Table2 has Table2ID (PK, int), Table2Data (varchar(50)) ; Table1_2 has Table1_2ID (PK,int), Table1ID (FK,int), Table2ID(FK,int) There is a cascade upon UPDATE and DELETE one-to-many between Table1.Table1ID and Table1_2.Table1ID. Likewise, for Table2ID. Here's my (not working) stored procedure for changing Table1Data and Table2Data at the same time. Each of the SQL blocks work fine, independently: ALTER PROCEDURE [dbo].[Modify] ( @Table1ID int, @Table1Data varchar(50), @Table2Data varchar(50) ) AS BEGIN SET NOCOUNT ON;
UPDATE Table1 SET Table1Data = @Table1Data WHERE (Table1ID = @Table1ID)
UPDATE Table2 SET Table2Data = @Table2Data FROM Table2 INNER JOIN Table1_2 ON Table2.Table2ID = Table1_2.Table2ID INNER JOIN Table1 ON Table1_2.Table1ID = Table1.Table1ID WHERE (Table1.Table1ID = @Table1ID)
</connectionStrings> <membership><providers> <add connectionStringName="myDB" minRequiredNonalphanumericCharacters="0" enablePasswordReset="true" requiresQuestionAndAnswer="false"minRequiredPasswordLength="8" name="myMProvider" type="System.Web.Security.SqlMembershipProvider" /> </providers> </membership> I am not able to connect to database from my application.I getting the following error, Database is on my local system only. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I have a database with two publications. One publication is used to replicate data among 4 SQL Servers, while the other is used for the replication with SQL Server CE clients. The problem here is that the SQLCE receive a number of changes that it is not correct. Before establishing the publication for SQL Servers the SQL CE clients were working great, but now i have this problem.
Is there any patch or fix I should install on the server?, or do you know how can I solve this?
I am integrating RS2005 reports with asp.net page(VS 2005).the scenario is that i have created a
home page with a button.when i clicked on button it redirect to another page which contain report viewer control.This control is linked with report server report.Although report is showing when i directly execute the page but when i execute the home page and clicked on button it gives messege
" The permissions granted to user 'GGNHTEL866ASPNET' are insufficient for performing this operation. (rsAccessDenied) "
although i have given full control to ASPNET on app folder and report server.
I am trying to migrate from Sql server2000 to Sql server 2005 with the help of Copy Database Wizard of 2005. It is failing in the Last step. That is when executing the Sql Jobs.Could any one help me out ?
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
I need to find out how long my SQL Server has been running and I believe that there is a way of obtaining this info by querying a system table (or executing a procedure).
i have a table in sql server, i want to insert a long text for a field. that long text like a file's text.please tell me what is the data type for that field. i tired ntext and text data type but i cant insert long text values. reply me if u have answer
To anyone who knows how to use sybase. One of our guys has put in a sybase system and not told the rest of us about it and know their is a problem and it is in my lap. Surprise, surprise. Just asking does antyone know how to rebuild a sybase database after it has been recovered from previous data and is now twice the size that it was origonally. Please help if any one can>
Hi there,I've a table with 18 millions of recordes shaped like this :Code nvarchar(80) , State int , school int , class int , Term nvarchar(80)The following query takes too long to run ( more than 2 hours )select State , school , class , term , count (term) as freqGroup by state , school , class , termHow may I speed up the query?My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB of HDRegards,M.Mansoorizadeh
I use MS ACCESS and tables linked to MS SQL SERVER tables. There are indexes defined (they are visible in MS ACCESS too). Sometimes DLOOKUP functions are taking long time. Why?
Hi Guys,I have editing a SQL Server table field that have long text data. I amupdating some text in this field. How can I update this field insteadof re-write all text. With the Select command its gives me completetext in one line and it hard to read it. Any idea. Thanks in AdvanceAdnan
I'm an experienced SQL Server and .NET developer, but I wanted to expand the way I look at things and see how other developers approach the situation I'm going to outline in this post. I'm going to be engineering a large, new project soon and I want to examine how I approach this and see if there is a better way.
I work in a small development group with two developers (myself and another). We pretty much wear all the design, testing ,and development hats during the course of a system's development. I had a discussion today with the other developer about creation of stored procedures.
I like to create small specific stored procedures for whatever I'm doing. I will usually have at least 4 stored procedures for each table; Insert, Delete, Update, and Select. Frequently I'll have more Select procedures for special cases. I do this for several reason. One I can get Visual Studio to generate the basic procedures for me and utilize them in a typed dataset. Secondly I can keep all my SQL code server side, and in small maintainable chunks. It is also fairly obvious what my stored procedures do from the name. The main drawback is that the list of stored procedures gets huge.
The developer I work with likes to create a single stored procedure for Insert, Update, and Deletes. Based on the passed primary key, the procedure determines what it should do. For example:
Code Snippet
CREATE PROCEDURE udp_users_processing @key int output, @name varchar(200), @status int AS IF IsNull(@key,0)=0 BEGIN INSERT INTO ut_users(key, name, status) VALUES (@key, @name, @status) SET @key = SCOPE_IDENTITY() END ELSE IF KEY > 0 UPDATE ut_users SET key = @key, name = @name, status = @status ELSE BEGIN DELETE FROM ut_users WHERE key = @key END This has the advantage of being compact, but it has issues with VS.NET and designer support. Loss of designer support isn't a huge problem, but it can be handy to have. I'm also not certain how this approach would work when using typed dataset and the table adapter to do updates.
What is YOUR opinion? How would YOU approach this in your situations? Are there other alternatives that might work just as well?
Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque
I've managed to get my Instance connected to the internet and I can query it using PHP and SQL, I can also look at views with no problem.
I have it working as an "ADODB.Connection" and like I said it connects and I can query data and display results.
Now I have coded a Stored Proc "GetMonthDays" in Sql Server:
Which returns days 1 through xxx in a given month and also returns the Day name eg... Sat for each date
2014-01-01 Thurs 2014-01-02 Fri etc...
It works perfectly and very fast so All cool with that side BUT... I want to be able to query the Database through a Stored Proc, I've spent all day trying to find a way to get this to work and I've hit a wall
I am able to run the pacakge in command line but when i try running the package through agent . The it starts the pacakge it says that it has been started by me local user and fails in next step saying that it has been execute by administrator.
I understand that there is a miss match between the permissions. Cos the SQL server Agent uses my login in but the SQL server agent on the Server that i am trying to execute has the Administrator log in. How to solve the issue?
I have a C# assembly I've created that I wish to run inside SQL Server. Since SQL Server doesn't support SMO inside of it, does anyone have any suggestions on how I might execute a SQL Server job in my assembly?
Is there a way to execute a user-defined function as part of a select query on a linked server?
Here is the query that won't work because the function it calls is on a remote server (and the way I have called it is also incorrect I know, it's just to give an idea of what I'm trying to achieve).
SELECT a.[Member Number],
a.SchemeID,
b.Scheme,
Atlantis.ptr.dbo.upFUNMemberStatusOnADate(a.[Member Number],'01/10/2007 00:00:00.000') AS Category,
a.[Employer Number],
c.[Employer Name] FROM Atlantis.ptr.dbo.Member a
JOIN Atlantis.ptr.dbo.Scheme b
ON a.SchemeID = b.SchemeID
JOIN Atlantis.ptr.dbo.Employer c
ON a.[Employer Number] = c.[Employer Number]
Apologies for the formatting of the query. I have looked at the EXECUTE query in BOL but it doesn't seem to be able to work as part of a SELECT, I'll keep looking though but I'm hoping someone might know the answer straight away.
