Better Method To Count Records In Custom Paging For SQL Server 2005
Jul 24, 2006
heres my problem, since I migrated to SQL-Server 2005, I was able to use the Row_Number() Over Method to make my Custom Paging Stored Procedure better. But theres onte thing that is still bothering me, and its the fact the Im still using and old and classic Count instruction to find my total of Rows, which slow down a little my Stored Procedure. What I want to know is: Is there a way to use something more efficiant to count theBig Total of Rows without using the Count instruction??? heres my stored procedure:
SELECT RowNum, morerecords, Ad_Id FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum, morerecords = (Select Count(Ad_Id) From Ads) FROM Ads) as test
WHERE RowNum Between 11 AND 20
The green part is the problem, the fields morerecords is the one Im using to count all my records, but its a waste of performance to use that in a custom paging method (since it will check every records, normally, theres a ton of condition with a lot of inner join, but I simplified things in my exemple)...I hope I was clear enough in my explication, and that someone will be able to help me. Thank for your time.
View 1 Replies
ADVERTISEMENT
May 18, 2005
I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too).
I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong).
So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet:
SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ?
well thanks for taking the time to read this, any help woulb be appreciated.
View 17 Replies
View Related
Jan 24, 2007
I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
View 1 Replies
View Related
Jan 25, 2007
I did use query plans to find out more. ( Please see the thread BELOW)I have a question on this, if someone can help me with that it will begreat.In my SQL query that selects data from table, I have a where clausewhich states :where PermitID like @WorkTypeorder by WorkStart DESC@WorkType is a input parameter to the Stored proc and its value is'01%'When I use the above where clause, all the Sorts in the ESTIMATED QueryExecution plan show me a COST of 28%.However if I change the query manually to say:where PermitID like '01%'order by WorkStart DESCThe COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%and at the beginning of the PLAN, there is a Bookmark Lookup whichincludes the above where clause.Whereas with the FIRST example , the BookMark Lookup in the beginningdoesn't show that where condition.Can anyone help me better understand this anomaly?TIA=====================================I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
View 8 Replies
View Related
May 23, 2005
Im in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.
Oracle Example wrote:
Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;
View 12 Replies
View Related
Jan 17, 2005
I need to be able to specify which column to sort by, BUT SQL 2000 does not allow me to
SELECT * FROM #TempTable
WHERE ID > @FirstRec
AND
ID < @LastRec
AND
EmployerID = @EmployerID
AND
Job_no = @Job_no
ORDER BY @WHICHCOLUMN asc
You can see that @WHICHCOLUMN is can be Surname, Age ETC, I have tried to make it a variable but, it started complaining of @FIRSTREC not defined, what's going on pls help, However, how do you combine dynamic queries with parameters as the say
"Sql server does not accept variables as part of sql"
my yahoo is abujajob@yahoo.com
WORKING CODE without WHICHCOLUMN
CREATE PROCEDURE [GetApplicants]
@CurrentPage int,
@PageSize int,
@TotalRecords int output,
@EmployerID int,
@Job_no int,
@WhichColumn varchar,
@SortBy varchar
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
Job_no int,
EmployerID int,
JobseekersID int,
Email varchar (100)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable
(
Job_no, EmployerID,JobseekersID,Email
)
SELECT Job_no, EmployerID,JobseekersID,Email FROM ApplicantsManagement
--Create variable to identify the first and last record that should be selected
DECLARE @myStatement varchar(500)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT * FROM #TempTable
WHERE ID > @FirstRec
AND
ID < @LastRec
AND
EmployerID = @EmployerID
AND Job_no = @Job_no
ORDER BY surname asc
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM Customers
GO
View 1 Replies
View Related
May 23, 2007
Hi,I
am working on this select query for a report on website users. The
resulting rows will be displayed in a datagrid with custom paging. I
want to fetch 100 rows each time. This is the simplified query,
@currpage is passed as a parameter. ________________________________________________________________________________DECLARE @table TABLE (rowid INT IDENTITY(1,1), userid INT)INSERT INTO @table (userid) SELECT userid FROM UsersSELECT T.rowid, T.userid, ISNULL(O.userid, 0)FROM @table TLEFT OUTER JOIN ( SELECT DISTINCT(userid) FROM orders)AS OON O.userid = T.useridAND T.rowid > ((@currpage-1) * 100) AND T.rowid <= (@currpage * 100)ORDER BY T.rowid________________________________________________________________________________If
I run this query it returns all the rows, not just the 100 rows
corresponding to the @currpage value. What am I doing wrong? (The
second table with left outer join is there as I need one field to
indicate whether the user has placed an order with us or not. If the
value is 0, the user has not placed any orders) Thanks.
View 2 Replies
View Related
Oct 12, 2007
Hello, I receive this error "Incorrect syntax near 'GetGalleryPaged'." I'm trying to use custom paging on a stored procedure. ....... Dim mySqlConn As New SqlConnection(ConnStr) Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn) objDA.SelectCommand.Parameters.Add("@startRowIndex", SqlDbType.Int, 1) objDA.SelectCommand.Parameters.Add("@@maximumRows", SqlDbType.Int, 9) Dim objDS As New DataSet() Dim objPds As PagedDataSource = New PagedDataSource objDA.Fill(objDS, "Gallery") <<----error here mySqlConn.Close() objPds.DataSource = objDS.Tables(0).DefaultView objPds.AllowPaging = True....... ALTER PROCEDURE dbo.GetGalleryPaged ( @startRowIndex int, @maximumRows int)AS SELECT idgallery, g_picpath FROM ( SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank FROM Gallery ) AS GalleryWithRowNumber WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows) ORDER BY idgallery DESC cheers,imperialx
View 5 Replies
View Related
May 16, 2005
***the sql-instruction has been modified a lot, so whas was written here is now useless***
View 8 Replies
View Related
Apr 7, 2008
I am trying to implement custom paging. I want to get a subset from my Threads and Post tables by userID. But I can't make the stored proc work. Could somebody have a look at this and tell me what I am doing wrong or if there is a better way of doing this?
ALTER PROCEDURE [dbo].[syl_ThreadPost_GetSubsetSortedByUserID2]
@UserID uniqueidentifier,
@sortExpression nvarchar(64),
@startRowIndex int,
@maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
BEGIN TRY
IF LEN(@sortExpression) = 0
SET @sortExpression = 'PostID'
-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment
SET @startRowIndex = @startRowIndex + 1
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT t.[ThreadName],
p.PostID,
p.[PostTypeID],
p.[LanguageID],
p.[PostAccessID],
p.[UserID],
p.[ThreadID],
p.[PostParentID],
p.[VoteSummaryID],
p.[Subject],
p.[Body],
p.[PostAuthor],
p.[PostDate],
p.[IsApproved],
p.[TotalViews],
p.[FormattedBody],
p.[IPAddress],
p.[PostCount],
p.[ArticleCount],
p.[TrackbackCount],
p.[IsSticky],
p.[StickyDate]
FROM
(SELECT t.[ThreadName],
p.PostID,
p.[PostTypeID],
p.[LanguageID],
p.[PostAccessID],
p.[UserID],
p.[ThreadID],
p.[PostParentID],
p.[VoteSummaryID],
p.[Subject],
p.[Body],
p.[PostAuthor],
p.[PostDate],
p.[IsApproved],
p.[TotalViews],
p.[FormattedBody],
p.[IPAddress],
p.[PostCount],
p.[ArticleCount],
p.[TrackbackCount],
p.[IsSticky],
p.[StickyDate],
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') AS RowNum
FROM syl_Threads t RIGHT OUTER JOIN syl_Posts p
ON t.[ThreadID] = p.[ThreadID])
WHERE t.[UserID] = ' + CONVERT(nvarchar(16), @UserID) + ' )
AS syl_ThreadPostInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(16), @startRowIndex) + ' AND (' + CONVERT(nvarchar(16), @startRowIndex) + ' + ' + CONVERT(nvarchar(16), @maximumRows) + ') - 1'
-- Execute the SQL query
EXEC sp_executesql @sql
RETURN
END TRY
BEGIN CATCH
--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END
View 4 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Jun 25, 2007
I have a webpage that displays 4000 or more records in a GridView control powered by a SqlDataSource. It's very slow. I'm reading the following article on custom paging: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx. This article uses an ObjectDataSource, and some functionality new to Sql Server 2005 to implement custom paging.There is a stored procedure called GetEmployeesSubestByDepartmentIDSorted that looks like this:ALTER PROCEDURE dbo.GetEmployeesSubsetByDepartmentIDSorted( @DepartmentID int, @sortExpression nvarchar(50), @startRowIndex int, @maximumRows int)AS IF @DepartmentID IS NULL -- If @DepartmentID is null, then we want to get all employees EXEC dbo.GetEmployeesSubsetSorted @sortExpression, @startRowIndex, @maximumRows ELSE BEGIN -- Otherwise we want to get just those employees in the specified department IF LEN(@sortExpression) = 0 SET @sortExpression = 'EmployeeID' -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment SET @startRowIndex = @startRowIndex + 1 -- Issue query DECLARE @sql nvarchar(4000) SET @sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE e.DepartmentID = ' + CONVERT(nvarchar(10), @DepartmentID) + ' ) as EmpInfo WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' -- Execute the SQL query EXEC sp_executesql @sql ENDThe part that's bold is the part I don't understand. Can someone shed some light on this for me? What is this doing and why?Diane
View 4 Replies
View Related
Feb 7, 2008
Hi,
I'm using ComponentArt's Callback grids with Manual Paging.
The CA example grid uses Access:(http://www.componentart.com/webui/demos/demos_control-specific/grid/programming/manual_paging/WebForm1.aspx)
That SQL syntax produced is invalid in SQL Server 2005.
Example:
"SELECT TOP " & Grid1.PageSize & " * FROM (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " * FROM Posts ORDER BY " & sSortColumn & " " & sSortOrderRev & ", " & sKeyColumn & " " & sSortOrderRev & ") ORDER BY " & sSortColumn & " " & sSortOrder & ", " & sKeyColumn & " " & sSortOrder
So...This is what I have (simplified), and it appears return incorrect rows on the last few pages:
SELECT top 15 * FROM Posts where & sFilterString & " and Postid in (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " Postid FROM Posts where " & sFilterString & " ORDER BY " & sSortColumn & " " & sSortOrder & ") " & " ORDER BY " & sSortColumn & " " & sSortOrderRev
What other approaches has anyone used besides the "ID in (...)"?The examples I have included show the available variables: sort asc and desc, current page, number of rows on a page, etc.
View 2 Replies
View Related
May 7, 2015
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 10
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER,
* FROM summary
) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g. At 12:00pm, the result of page 1 (5 per page) is
R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button
Since records R21, R22, R23, R24, R25, R26 are inserted
page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
View 2 Replies
View Related
Jan 26, 2007
You are right, I did not include the exact query since it has a wholeof joins and many where clauses in it.I did not want to make the post very hard to read, hence I simplifiedit.In the Stored proc I am using a String variable @SQLStringvarchar(2000) to hold the entire select statement, and then executingthat SQL using EXEC (@SQLString).Thus for debugging, I used Query Analyzer, and within the Analyzer I amusing the Select statement.So in my test I do not use any stored proc.so one select statement says:Select * from ( Select Top 600 * from( Select Top 2000 * from( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,FromStreetName, ToStreetName, WorkStartDate as "WorkStart",tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,PermitType_ID as "Type",InspectionDistrict,PermitStatus,IssueDate
Quote:
View 2 Replies
View Related
Feb 7, 2008
we have a search string is used Server.URLEncode Method in sql 2000, but since we moved database from server2000 to 2005 then ,this search string doesn't work. does some know it?????
the code is :
Public Function categories(strType, recID)
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strRGConnection
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = objConn
If strType = "product" Then
strQ = "SELECT category FROM productsCategories where prodID = " & CInt(prodID)
End If
If strType = "service" Then
strQ = "SELECT category FROM ServicesCategories where svcID = " & CInt(svcID)
End If
objRs.Open strQ
categories = ""
tempCategories = ""
firstRec = True
While not objRS.EOF
If NOT firstRec Then
tempCategories = tempCategories & ", "
End If
tempCategories = tempCategories & "<a href='/resourceguide/resources/searchResults.asp?category=" & server.URLEncode(replace(objRS("category"),"'", "''")) & "&searchSource=Browse'>" & objRS("category") & "</a>"
firstRec = False
objRS.MoveNext
Wend
categories = tempCategories
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
End Function
View 1 Replies
View Related
Feb 13, 2015
Initially I had designed ETL using Dataflow task in SSIS , No I have converted into Store procedure using merge statement.
I am getting new records inserted, source records and deleted row count when I am running sqp manually.
In ssis simple I have used the rowcount transformation to capture the records.
below variable i have declared in SSIS Package.
User::etlArchiveLogId
User::sourcerecords
User::newrecords
User::changerecord
now I am incremental loading using Stored procedure below is the sp which is executing when task success and logs the records.
ALTER PROCEDURE [dbo].[usp_LogArchiveBBxEndTime]
@EtlArchiveLog_Id int,
@RowsSource int,
@RowsNew int,
@RowsChanged int,
@Sucessful bit,
@Description varchar(500)
[code]....
Now I want to log the variable records which I have declared in my stored procedure .how can I log it in table?
View 1 Replies
View Related
Jan 24, 2008
Scenario
Recently moved a SQL2000 database to SQL2005.
Collation on SQL 2000 database server was SQL_Latin1_General_CP1_CI_AS
Colaltion on SQL 2005 database server is Latin1_General_CI_AS
After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We have altered the database collation to Latin1_General_CI_AS. We understand this will work for all new objects created on the database but existing objects (prior to the collation change) will continue to have the 'SQL_Latin1_General_CP1_CI_AS' collation.
What is the best method to change the collation of the older columns to 'Latin1_General_CI_AS' -
1) Run ALTER TABLE ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>
on each varchar/char column
or
2) For each table
BCP out data
DROP TABLE
RECREATE TABLE (taking the new collation Latin1_General_CI_AS)
BCP in data
3) Other methods?
Thanks in advance
David.
View 4 Replies
View Related
Jan 13, 2008
Hi to all....
I'm new on this forum, but, I'm a experienced programmer.
I wanna solve this problem (if you can helpme....), recently, I'm trying to migrate an old SQL 7 to the new SQL 2005 server enterprise edition, I have no problem to import the data, users, stored procedures, etc. and all work fine, I can connect troght ODBC from the others computers, including some older pentium 1 mmx 200 mhz. with WIN98 (yes!).
My problem is an legacy VB6 application that work (well fine) with SQL7 server, but, when i change the odbc of the desktops systems to the new 2005 SQL server and run this legacy application hi recibe this error:
(english translation from spanish language)
[ODBC SQL Server Driver][SQL Server] The table to be modified is not included on this cursor or these can't be modified throught this cursor.
I have look on the source code of the legacy application, and someone of the segment's that causes error is:
------------------------------------------------------------------------
Public Function ConsultaCamara(sRutOP As String, _
sRut1 As String, _
strConsulta As String, _
Tipo As String, _
chk As CheckBox, _
Timer1 As Timer, _
LbAviso As Label) As Long
'----------------------------
Dim rsCACH, rsCACH_upd0, rsCACH_upd1, rsCACH_upd2 As ADODB.Recordset
Dim CmCACH As ADODB.Command
Dim lPendiente As Boolean
Dim lActivo As Boolean
Dim strSQL1, strSQL_upd0, strSQL_upd1, strSQL_upd2 As String
Dim StrWHE As String
Dim DameTiempo As Integer
Dim snombre As String
Dim sMsgEstado As String
Dim sAviso_Inicial As String
Dim cNum As String
Dim cDig As String
...
...
...
'Defino el string de consulta
strSQL1 = " SELECT RUTOPERADOR, " & _
" IDCAMARA, " & _
" RUTCONSULTADO, " & _
" ESTADO, " & _
" CANCON, " & _
" CADENACONSULTA, " & _
" FINGRESO, " & _
" FULCONSINT, " & _
" ULTRUTOP " & _
" FROM BOLETIN " & _
" WHERE RUTCONSULTADO = '" & sRut1 & "'" & _
" AND (ESTADO = 'PE' OR ESTADO = 'AC' )"
Set rsCACH = CreateObject("ADODB.Recordset")
rsCACH.ActiveConnection = cn
rsCACH.CursorType = adOpenKeyset
rsCACH.LockType = adLockOptimistic
rsCACH.Open strSQL1
If rsCACH.EOF Then 'No hay registro antiguo o en consulta, creo uno para consultar
sMsgEstado = "NO Existen datos ES REGISTRO NUEVO" & vbCr
rsCACH.AddNew
ConsultaCamara = GeneraID()
rsCACH.Fields("IDCAMARA") = ConsultaCamara
rsCACH.Fields("RUTOPERADOR") = sRutOP
rsCACH.Fields("RUTCONSULTADO") = sRut1
rsCACH.Fields("ESTADO") = "PE"
rsCACH.Fields("CANCON") = 1
rsCACH.Fields("CADENACONSULTA") = strConsulta
rsCACH.Fields("FINGRESO") = GetDateTime() 'Format(Date, "Short Date")
rsCACH.Fields("FULCONSINT") = GetDateTime() 'Format(Date, "Short Date")
rsCACH.Fields("ULTRUTOP") = sRutOP
rsCACH.Update <----- here's launches the error
...
...
...
--------------------------------------------------------
As you see, I'm not english spokeman, I hope that you understand me.
Thanks and best regards.
View 3 Replies
View Related
Jul 16, 2014
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
View 9 Replies
View Related
May 27, 2015
My data has 2 fields: Customer Telephone Number, Date of Visit.
Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.
Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3
As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.
View 3 Replies
View Related
Feb 6, 2008
I wanted to set up a mechanism that would transfer blocks of records (a few dozen to in rare cases a few thousand), with slight modification, from one database to another. It's a sort of custom partial archiving process that would be triggered from a web-based admin application. Records in the target db would be identical except:
-- the primary key in the source table, an identity field, would be just an integer in the target table
-- the target table has an extra field, an integer batch ID supplied by the web application that triggers the process
It's a simple, if not efficient matter to do it within the web application: query the source table, suck the records into memory, and insert them one by one into the target db. This will be an infrequent process which can be done at off-hours, so a bit of inefficiency is not the end of the world. But I wondered if there is a more sensible, orthodox approach:
-- Could this process be done, and done efficiently, as a stored procedure with the batch ID passed as a parameter?
-- Is there any way to do a bulk insert from a recordset or array in memory using ADO and SQL? And if so, is that better than inserting records one by one?
Advice on the best general approach would be appreciated, and I will try to figure out the details.
View 2 Replies
View Related
Nov 29, 2007
Hi.
We are going to upgrade one of our SQL Server 2000/Win200 instance to SQL Server 2005/Win2003. Currently, the SQL Server 2000 which contains about 30 database. This is my plan:
Install SQL Server 2005/Win2003 on a new instance
Backup SQL Server 2000 database instance (how do I back all 30 database at the same time?)
Then do a Restore from the SQL Server 2005 instanceIs this a good method or using the "Copy Database Wizard" is better method?
I'm new to this so please advice? Thanks!
View 14 Replies
View Related
Sep 13, 2004
This question is regarding a "helper app" I'm building to go along with my ASP.NET appplication. It will be inserting/updating records in the database as a nightly process. It is a Windows application built in VB.net.
I have a table which should always only have one of each type of record in it. This table on average will have between 100k and 500k records.
Which operation would be faster and less strain on the server?
a. Use a "if exists" and see if a record of this type already exists, if it does, update it, if not, insert the new one.
b. Unconditionally issue a delete for the record I'm about to insert, then insert the new one.
c. Create a trigger that will delete the old record if a new one is inserted?
Thanks!
View 1 Replies
View Related
Feb 7, 2008
I wanted to set up a mechanism that would transfer blocks of records (a few dozen to in rare cases a few thousand), with slight modification, from one database to another. It's a sort of custom partial archiving process that would be triggered from a web-based admin application in plain old ASP (not .net alas). Records in the target db would be identical except:
-- the primary key in the source table, an identity field, would be just an integer in the target table
-- the target table has an extra field, an integer batch ID supplied by the web application that triggers the process
It's a simple, if not efficient matter to do it within the web application: query the source table, suck the records into memory, and insert them one by one into the target db. This will be an infrequent process which can be done at off-hours, so a bit of inefficiency is not the end of the world. But I wondered if there is a more sensible, orthodox approach:
-- Could this process be done, and done efficiently, as a stored procedure with the batch ID passed as a parameter?
-- Is there any way to do a bulk insert from a recordset or array in memory using plain ASP, ADO and SQL? And if so, is that better than inserting records one by one?
I realize that the ASP.NET tableadapter and dataset features might provide a good solution, but in the short run I can't rewrite the whole application. Advice on the best general approach from an ASP-ADO platform would be appreciated, and I will try to figure out the details.
View 3 Replies
View Related
Oct 20, 2006
Hello to everyone, I've a question about UDTs and the way I can use them to access tables and columns where they are applied in a SQL Server 2005 DB.
I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here (this is the second post, maybe the last one was in the wrong Forum).
The scenario follows:
I've created a UDT called MyUDT that exposes 2 properties MyTable, MyColumn, here its the code:
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "MyUDT")]
public class MyUDT : INullable, IBinarySerialize
{
private string _myTable;
private string _myColumn;
....
/// <summary>
/// Set or Get the Table Name where the UDT is applied.
/// </summary>
public string MyTable {
get { return this._myTable; }
set { this._myTable = value; }
}
/// <summary>
/// Set or Get the Table's Column Name where the UDT is applied.
/// </summary>
public string MyColumn
{
get { return this._myColumn; }
set { this._myColumn = value; }
}
....
}
And here it's my question/s:
How can I expose the defined Properties (MyTable, MyColumn) in order to be directly used from
SQL Server Management Studio within the Column Properties Panel?
Pls take a look to the print screen placed below:
[img=http://img81.imageshack.us/img81/7193/untitledip1.th.jpg]
If it is not possible, is there a way for any UDT to get back from the sql server execution context
the table and the column where it is applied/used?
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used
and make some work on presented MetaData. Thanks in advance, every answer/help will be very much appreciated.
View 10 Replies
View Related
Apr 22, 2008
I got problem with using custom paging in sql 2005
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[searchperson_view_general]
@Search nvarchar(2000)
,@OrderBy nvarchar (2000)
,@PageSize int
,@PageIndex int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
--Default order by to first column
IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
SET @OrderBy = 'p.[person_id]'
END
-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT distinct'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex '
SET @SQL = @SQL + ', p.[person_id]'
SET @SQL = @SQL + ', p.[userType_id]'
SET @SQL = @SQL + ', p.[fullName]'
SET @SQL = @SQL + ', p.[gender_nm]'
SET @SQL = @SQL + ', p.[dateOfBirth] '
SET @SQL = @SQL + ', p.[positionTitle]'
SET @SQL = @SQL + ' FROM dbo.[person_view] p '
IF LEN(@Search) > 0
BEGIN
SET @SQL = @SQL + @Search
END
SET @SQL = @SQL + ' ) SELECT distinct'
SET @SQL = @SQL + ' p.person_id'
SET @SQL = @SQL + ', p.userType_id'
SET @SQL = @SQL + ', p.fullName'
SET @SQL = @SQL + ', p.gender_nm'
SET @SQL = @SQL + ', (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth] '
SET @SQL = @SQL + ', p.positionTitle'
SET @SQL = @SQL + ' FROM PageIndex p '
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
exec sp_executesql @SQL
I checked my store procedure with parameters
exec [hr2b_searchperson_view_general_load]
'LEFT OUTER JOIN qualification
ON p.person_id = qualification.person_id
WHERE qualification.institutionName like N''%ABC%'''
,' p.person_id asc ', 25 , 1
This is a actual query show :
WITH PageIndex AS
( SELECT distinct TOP 49 ROW_NUMBER() OVER
(ORDER BY p.person_id asc )
as RowIndex
, p.[person_id]
, p.[userType_id]
, p.[fullName]
, p.[gender_nm]
, p.[dateOfBirth]
, p.[positionTitle]
FROM person_view p
LEFT OUTER JOIN qualification
ON p.person_id = qualification.person_id
WHERE qualification.institutionName like N'%ABC%' )
SELECT distinct
p.person_id
, p.userType_id
, p.fullName
, p.gender_nm
, (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth]
, p.positionTitle
FROM PageIndex p
WHERE RowIndex > 25 AND RowIndex <= 49 ORDER BY p.person_id asc
If I used this query without using DISTINCT it will return extactly number of records which I expected but It is duplicated.
Then I tried to use DISTINCT in this query number of records return is less than 25 records . Because it was duplicated records when I used LEFT OUTER JOIN.But my query will be able to use more LEFT OUTER JOIN than this query. Please help me get exactly 25 records?
This is my tables
person_view(person_id, fullname, userType_id, gender_nm, dateOfBirth, positionTitle)
Qualification(qualification_id, qualification_nm,institutionName, person_id)
Thanks in advance.
View 3 Replies
View Related
Feb 13, 2008
Hi
Can any one tell me how to enable paging in ssrs 2005 reports, i am using "table" control for the report and when we print the report it gives every thing good, but when we desplay it the web it does not show page by page.
please help me...
thank you.............
View 5 Replies
View Related
Mar 11, 2008
I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
View 3 Replies
View Related
Mar 29, 2008
I designed dll file to get data from sql server, i am calling the dll from ssrs 2005
in preview , everything is ok but after deploying and put the dll file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin and add this to rssrvpolicy.config
<CodeGroup
class="SqlClientPermission"
version="1"
Name="MySecurityExtensionCodeGroup"
Description="Code Group for Don's test security extension"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:Program FilesMicrosoft SQL ServerMSSQLReporting
ServicesReportServerinMyAssembly.dll"
/>
</CodeGroup>
i still get #Error
i try everything , i don't know what i can do.
pls help
View 1 Replies
View Related
Nov 7, 2007
I want to integrate SQL Server 2005 setup into my Testproject Configurator setup. SQL Server 2005 install files should not be included in TestProject setup. If SQL Server 2005 is not installed on the target computer and install files are available then TestProject setup will install SQL Server 2005 with myProjectInstance instance name.
how the setup can install the custom instance name.?
Di i need to use install sheid for this ?
View 3 Replies
View Related
May 29, 2006
lets say we have more than 100 000 rows in Table1, and we want to view each 10 rows alone.... and by pressing on a NEXT button we will see the other 10 pages....
there is 2 buttons : NEXT and PREVIOUS
so can anyone tell me how to do that in SQL 2005, and what is correctly called.
I have found a code that does use ROW_NUMBER in order to view results between 2 numbers,
example: rows between 10 and 50....
but It is not what I want, so please I need some help, thank you
By Uncle Sam
View 10 Replies
View Related
Apr 23, 2008
Hi,
I've got some procedure which pages select query, the example is below:
Code Snippet
CREATEEND PROC GetCustomersByPage
@PageSize int, @PageNumber int
AS
Declare @RowStart int
Declare @RowEnd int
if @PageNumber > 0
Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
With Cust AS
( SELECT CustomerID, CompanyName, CompanyAddress,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end
How can I change this procedure in order to page the query OVER the column set as an argument?
In other words I would like to execute proc like:
- exec GetCustomersByPage 10, 1, 'CompanyName' which pages by CompanyName(...OVER (order by CompanyName)...)
- exec GetCustomersByPage 10, 1, 'CompanyAddress' which pages by ComanyAddress
Is it possible?
View 8 Replies
View Related
