question: get 10 gram of the gold from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error;
here my code:
--------test condition-------------
Create table Qt(id int identity primary key,Q int)
Go
insert into Qt(Q) values(8);
insert into Qt(Q) values(6);
insert into Qt(Q) values(6);
insert into Qt(Q) values(5);
insert into Qt(Q) values(3);
insert into Qt(Q) values(3);
insert into Qt(Q) values(1);
insert into Qt(Q) values(0);
Go
-------------------------------
Create procedure Test2( @m int) AS
declare @i int,@j int,@n int ,@id int ,@q int ,@last int
set @i=1;
set @j=1;
Create table #result(id int, Q int)
declare __cursor cursor for
select * from Qt where Qt.Q <=@m order by Qt.q desc for read only
Open _cursor;
select @n=@@cursor_rows;
fetch last from _cursor into @last
if @n > 0
-------------------
begin
Begin_this:
fetch absolute @i from __cursor into @id,@q
if @q=@m begin
insert into #result(id,Q) values(@id,@q);
GoTo End_this;
End
-------------------
else
-------------------------------
begin
------------------------
if @q=@last
begin
Set @j=@j+1;
------------
if @j>@n Goto End_this;
else
Begin
set @i=@j;
Delete from #result;
End
------------
End
else
begin
insert into #result(id,Q) values(@id,@q);
set @i=@i+1;
end
------------------------
GoTo Begin_this
End-----------------------------
End_this:
select id,Q from #result
--close __cursor
--deallocate __cursor
I used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?
I have started working on SSAS since last week, I need to perform some calculations on the data fetched from the cube based on the parameters. SSRS is used to display the output and SSAS is used as a data source.
How i could perform the operations on the data fetched from the cube in SSAS? Does SSAS provides the storage structure like temp table in stored procedure where we can perform the various operations before sending final data back to the client side tool(SSRS)?
OR Is there any alternative way to perform the operations based on the input provided through the parameters
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
com.microsoft.sqlserver.jdbc.SQLServer Exception: A server cursor cannot be opened on the given statement or statements . Use a default result set or client cursor.
If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.
SP :
create proc testProcedure @countrCode varchar(3) as
select countryname INTO #TMPCOU from country where countryCode = @countrCode SELECT COUNTRYNAME FROM #TMPCOU
Hello all,I have the following t-sql batch:create procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as beginset @p_date1 = isnull(@p_date1, <some expression>)set @p_date2 = isnull(@p_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @p_date1 anddate2 <= @p_date2 and(date1 >= @p_date1 ordate2 >= @p_date2)endgoexec stp_testThis gives a WRONG resultset.When I replace the variables with hardcoded values in the right format, thereturned result set is CORRECT, as followswheredate1 <= 'hard coded date value 1' anddate2 <= 'hard coded date value 2' and(date1 >= 'hard coded date value 1' ordate2 >= 'hard coded date value 2')When I elimate the derived table with a temporary table, the returned resultset is CORRECTWhen I store the parameters in a local variable, and use the local variable,the returned result set is CORRECT, as followscreate procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as begindeclare @l_date1 datetimedeclare @l_date2 datetimeset @l_date1 = @p_date1set @l_date2 = @p_date2set @l_date1 = isnull(@l_date1, <some expression>)set @l_date2 = isnull(@l_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @l_date1 anddate2 <= @l_date2 and(date1 >= @l_date1 ordate2 >= @l_date2)endgoWhen I put less columns in the select list, the returned result set isCORRECT, it doesnt make sense wich columns I remove from the select list.The tables are not small (500.000 rows) and also is the result set. I usethis construction elsewhere, on other table combinations, but dont haveproblems. So the content of the data makes difference.Seems to me as a bug.My question is: Can I say the derived table is instable in SQL server andcauses the problem of the wrong result set here?Peter
Hi all, As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!
99pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.
I have written this query but it returns all PersonnelID that fit into the date block
requrirements
PersonnelID select if DateStart falls within 2006 select if DateEnd falls within 2006
query i built:
Select * From tblResourceList Where PersonnelID=72 And tblResourceList.DateStart Between '01 January 2006' And '31 December 2006' Or tblResourceList.DateEnd Between '01 January 2006' And '31 December 2006' Or (tblResourceList.DateStart < '01 January 2006' And tblResourceList.DateEnd > '31 December 2006')"
I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint"NoLock" on all selects.One of my clients (OleDbConnection from C#) doesn't get the same Result Setas the others. The result Set should have 31 rows but this client only gets5!When I remove all the "NoLocks" everything works fine. How can that be?
EMPNO int Checked ENAME nchar(10) Checked JOB nchar(10) Checked MGR varchar(50) Checked HIREDATE nvarchar(50) Checked SAL int Checked COMM varchar(50) Checked DEPTNO int Unchecked Unchecked
Code Snippet
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800
20
7499 ALLEN SALEMAN 7698 20-Feb-81 1600 300 30
7521 WARD SALEMAN 7698 22-Feb-81 1250 500 30
7566 JONES MANAGER 7839 2-Apr-81 2975
20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2850
30
7782 CLARK MANAGER 7839 9-Dec-82 2450
10
7788 SCOTT ANALYST 7566 9-Dec-82 3000
20
7839 KING PRESIDENT
17-Nov-81 5000
10
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-83 1100
20
7900 JAMES CLERK 7698 3-Dec-81 950
30
7902 FORD ANALYST 7566 3-Dec-81 3000
20
7934 MILLER CLERK 7782 23-Jan-82 1300
10
After I execute the query
Code Snippet select * from emp where( deptno=10 or comm is not null or sal <= 2000 ) and deptno=20
I got
Code Snippet EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-Dec-80 800 20 7566 JONES MANAGER 7839 2-Apr-81 2975 20 7788 SCOTT ANALYST 7566 9-Dec-82 3000 20 7876 ADAMS CLERK 7788 12-Jan-83 1100 20 7902 FORD ANALYST 7566 3-Dec-81 3 000 20
I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.
But if i see same table through QA using sp_help tablename then it will show length of my nvarchar column just double.
when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.
i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.
I have one query that executes many times in a week.I created one Maintenances plan that Rebuild all index in my Database thathas been executed at 23:40 Saturday until stop finished at Sunday.However at middle of week (Wednesday or Thursday), that query don’t returnresult like that must be. The time exceeded and the result are total wrong.I compare the normal executed plan and the “crazy” one that SQL create tomount result.The normal is nested with index seek (very fast, the wrong is Merger withhash aggregate (very slow). After Index Rebuild, the executed plan bringresult that must be, but when the merge plan are executed with many updateson that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, theresult are total wrong, with many rows back.I recommended Index Seek force by coalesce function on one columnaggregate, but everyone here were very panic with that behavior of SQLServer.Please , anyone help me to explain that!Krisnamourt!P.S: Attachments :--Force Index Query with coalesceSELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL--Normal QuerySELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL--Message posted via http://www.sqlmonster.com
My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.
In my cube I have defined a role where thet user can only browse certain dimention value. BUt in grand Total the result showing is for all the diemnsion values.
for example The user restricted to browse only Australia and UK Country , But in grand total its showing the SUM of all the country.
SELECT * FROM ( SELECT TOP 15 * FROM (SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH, T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL, T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME, CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN (SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE T1.SERIAL=T2.SERIAL AND CMDS.SERIAL=T2.SERIAL AND T2.sd='20060821' AND CMDS.sd='20060821' AND T2.L_CUM < '1900' AND CMDS.sector >='1' AND CMDS.sector <='47') AND CMDS.SERIAL=T1.SERIAL AND CMDS.SERIAL=CMD7.SERIAL AND CMDS.sd='20060821' AND CMD7.sd='20060821' AND T1.sd='20060821' AND T1.L_CUM < '1900' AND CMDS.sector >='1' AND CMDS.sector <='47' ORDER BY T1.D_CUM desc) AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
Simple example:    declare @tTable(col1 int)    insert into @tTable(col1) values (1)    select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1]  and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.