|
When i get the script from sql 2008 to sql 2005 without any error it finish.
when i wanna to execute the script in sql2005 i get this error :
Msg 139, Level 15, State 1, Procedure Language_Update, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure Language_Update, Line 9
Must declare the scalar variable "@param".
this is the part of script :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Language_Update]
(@LanguageId SmallInt , @Language NVarChar(50))
AS
BEGIN
declare @param nvarchar(1000)= N'update [Common].[Language] set '
if @Language is not NULL
BEGIN
set @param = @param + '[Language]=''' + @Language + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where LanguageId=''' + cast( @LanguageId as nvarchar(5)) + ''' '
exec sp_executesql @param
END
GO
take attention that the script is about 77000 line and i have about 200 of this type error.<br />
<br />
Please Help !
|
|
|
|
|
When you declare variable @param, set default value after declared. Do this replace:
Old part of query
declare @param nvarchar(1000)= N'update [Common].[Language] set '
New part of query
declare @param nvarchar(1000)
set @param ='update [Common].[Language] set '
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks for Help!
But its about 200 to 500 of this items!
How can i do that ?<br />
<br />
and why doesn't the SQL create correct scripts.
|
|
|
|
|
How/where is SQL Server generating the script from?
Try doing a search and replace ') = N' with '): Set @Update =N'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
jojoba2011 wrote: and why doesn't the SQL create correct scripts
It did. You generated them on Sql2008, and thus it will use all the language-features available for 2008. If you wanted to be compatible with Sql2005, you'd generate them from there.
Put your database in compatibility mode (Google knows how) and try to script again.
Bastard Programmer from Hell 
|
|
|
|
|
Thanks!<br />
but this is not good trick!<br />
and no use in here cause i have different parameters;<br />
this was example @Param.
|
|
|
|
|
Hi
I posted a similar question to this here [^]
however after looking at the data in more detail, the query has to be changed a lot. Also in the previous post, I could not get it to work.
The select statement below is what i need except, this returns all the rows for each product, and all I need is the MAX date_despatched for EACH product
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
Basically the Sales Order Header (scheme_opheadm) contains the Date Despatched, Sales Order, ans Status (which must be 8 for despatched orders).
The Sales Order Detail (scheme_opdetm) has the Warehouse Product Net Price (There are also comments and other lines that I need to ignore in the Detail Table, hence the filters for Net Price > 0, Qty Despatched > 0, Warehouse = 06 (finished goods).
How can I get only the details of the LAST despatch for EACH item?
|
|
|
|
|
Create a sub query that uses Row_Number() and Partition based in the id field in scheme_opdetm, selecting data from your transaction table and numbered/ordered by the date field. Join that to your main query and filter it on row_number = 1.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Mycroft, but I have no idea how to do this.
First, I'm not sure how to put in the PARTITION clause,
Second, the Despatch date is in the header table (opheadm), with a Sales Order Num, but I want to Partition by Product not by Sales Order Num, and the product is in the Details Table (opdetm).
This what I have tried so far, but I get an error "The column 'order_no' was specified multiple times for ordlist
SELECT opdetm.warehouse, opdetm.product, opdetm.net_price, opdetm.despatched_qty, opheadm.date_despatched
FROM vektron.scheme.opdetm INNER JOIN vektron.scheme.opheadm ON opdetm.order_no = opheadm.order_no ,
(SELECT ROW_NUMBER() OVER (PARTITION BY opheadm.order_num ORDER BY date_entered DESC) , * FROM vektron.scheme.opheadm inner join vektron.scheme.opdetm on opdetm.order_no = opheadm.order_no) as ordlist
WHERE(((opheadm.status) = '8') And ((opdetm.warehouse) = '06') And ((opdetm.net_price) > 0) And ((opdetm.despatched_qty) > 0)) and ordlist.order_no = 1 and opdetm.product = ordlist.product
ORDER BY opdetm.product, opheadm.date_despatched DESC
|
|
|
|
|
Without the DB I'm not going to be able to build the query, but here is how I would go about it.
Create a query that returns you the minimum bits you need to do the partition job. That should be the ID, the date and the product fields. The ID to link back to your result set, the data to order by and the product to partition over.
Once you have that right you put it aside and create another query that had the ID field and all the bits you want in your end result.
Now join the 2 queries!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try something like this:
WITH original_query AS (
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
)
,last_despatched AS (
SELECT product,Max(date_despatched) AS date_despatched
FROM original_query
GROUP BY product
)
SELECT o.warehouse, o.product, o.net_price, o.despatched_qty, o.date_despatched
FROM original_query o,last_despatched l
WHERE o.product = l.product
AND o.date_despatched = l.date_despatched
I love CTEs.
|
|
|
|
|
Thanks so much Jorgen!
That worked brilliantly.
I did have to remove:
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
becaue it gave an error (MSSQL 2008 R2): "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
|
|
|
|
|
|
SELECT
b.RowNumber,
iSrno,
vUserCode,
dLoggedDate
FROM (
SELECT
iSrno,
vUserCode,
dLoggedDate,
ROW_NUMBER() OVER ( PARTITION BY vUserCode ORDER BY vUserCode DESC ) 'RowNumber'
FROM HOUserLog) b
WHERE RowNumber = 1
/*
Here HOUserLog is the table from where the Group By MAX needs to be pick.
ROw_Number() OVER ( PARTITION BY .... ) will generate the RowNumber according to the Group By
Column you provided in ORDER BY ..... DESC Clause.
Now pick the values for every RowNumber Column = 1 will get you the solution.
MARK AS ANSWER IF WORKS
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
After running a report in SSRS 2005 I am trying to export it into excel and I am getting an error
"Index is out of range. Must be non-negative and less than the size of the collection. Parameter name:index"
When I remove the "Page break at start" or the "Page break at end" grouping property, it fixes the problem, but I need this property on because that is how the report gets seperated onto seperate Excel worksheets by the groups I have set up. Any suggestions??? 
|
|
|
|
|
Hi All,
I have same table structure in Mysql and sqlserver.
If there is change in Mysql table, then sqlserver table should get updated.
Please suggest proper way to do.
We dont have SSIS we only use ssms2005 express.
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
Ramkumar_S wrote: Please suggest proper way to do.
Sql Express doesn't offer this functionality out of the box. You'd have several options;
- Add the MySql server as a linked server and sync using triggers
- Write an app to do the synchronization, using the Sync Framework
- Upgrade Sql Server
Bastard Programmer from Hell
|
|
|
|
|
I suspect the solution would require that you first analyze what happens if there is an error.
For example you update the MySQL table and at that moment the SQL Server database is down so of course the corresponding table cannot be updated then - so what do you expect to happen then?
Additionally how soon does the data need to be in the SQL Server database?
|
|
|
|
|
I have a requirement as in below
DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))
insert into @TEST values ('A','B'),('C','D'),('E','F'),('B','A'),('D','C'),('F','E')
SELECT A.* FROM @TEST A, @TEST B WHERE A.X = B.y
gives
X Y
A B
C D
E F
B A
D C
F E
But I have to get only the combination (AB or BA) ,(CD or DC),(EF or FE) in my select statement. In my sql we have some thing called DistinctRow.. What is it in SQL ..Pelase help ..urgent..
|
|
|
|
|
Try using the Distinct Keyword
SELECT Distinct(A.x), A.Y FROM @TEST A, @TEST B WHERE A.X = B.y
|
|
|
|
|
here it is
DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))
insert into @TEST values ('A','B')
insert into @TEST values ('C','D')
insert into @TEST values ('E','F')
insert into @TEST values ('B','A')
insert into @TEST values ('D','C')
insert into @TEST values ('F','E')
SELECT A.X
,(
select top 1 b.y from @test as b where b.y > a.x and b.x<a.y
) as subY
FROM @TEST A
where
(
select top 1 b.y from @test as b where b.y > a.x and b.x<a.y
) is not null
order by a.x
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Excellent .. thanks for the reply..
|
|
|
|
|
I was looking for some key word like distinctrow in MYSQL.. dont we have any thing like that in SQL.
|
|
|
|
|
Finally SELECT X,Y FROM @TEST WHERE X
|
|
|
|
|
There exists DISTINCT option to retrieve non-repeated values, but your case was different from using DISTINCT.
Glad to help you.
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
