Generalising Rownum For All The Databases
May 17, 2004
Hi
I have as issue with this rownum.This is supported in different forms in different databases like top in mssql ,limit in mysql and all..i want to genaralise this independent of the type of database because i want to run the query as i wont be knowing the type of the DB used by my user.
can anyone suggest a solution for this ? is there anyway of generating a series of numbers with the query by calling the function in that query repetitively ???
Regards
Sreenath
View 1 Replies
ADVERTISEMENT
Nov 10, 2004
Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3.....100 along with the each record position.
is it possible without using cursor?
Howdy!
View 4 Replies
View Related
Dec 1, 2005
Does SQL Server 2005 or SQL Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?please advice!To select records from row #10 to row #20Oracle: SELECT * FROM MyTable WHERE ROWNUM>9 AND ROWNUM<21MySQL: SELECT * FROM MyTable LIMIT 10,20SQL Server: ?
View 3 Replies
View Related
Aug 16, 2004
Hi,
Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?
select rownum from test_table;
Please advise,
Thanks
Sam
View 1 Replies
View Related
Jul 17, 2007
I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?
View 9 Replies
View Related
Nov 13, 2007
hai, for one of my batch requirement i would like to retrieve records set by set, meaning if there are 1000 records which satisfies the condtion, i want to take first 100 record process it, and then again go to database retireve another 100 record and so on...
how can i do this...do i need use the for loop logic or, is there any simple way to achieve this? help pls
View 3 Replies
View Related
Mar 19, 2008
Background: SQL Server 2000 transactional database
Key fact 1: I have one main fact table and several dimension tables. for each join between a dimension table and the fact table, I have a combined key that includes at least three fields one of which would be a date field.
Key fact 2: Sometimes fields in the dimension tables changes names over time e.g. a department that was called dept_1 a few years ago might have changed its name since and is now called dept_A today. The historic report should still use today's name of the department.
For example using three tables (keys are colour coded)
DEPT table and JOB table and Employee table
DEPT_table JOB_table EMPLOYEE_table
DEPTID DEPTID EMPLID
SETID DEPT_SETID EMPL_RCD
EFFDT EFFDT
DESCR_LONG EFFSEQ
DESCR_SHORT EMPLID
EMPL_RCD
Since departments change names over time, I intend to return just the most current department name for use in my reports but one way or another SOME records return more than one. I am using max(effdt) to extract the most recent from two tables like the ones above.
Code Snippet
DEPT_TBL.DEPTID=BO_JOB_VW.DEPTID and DEPT_TBL.SETID=BO_JOB_VW.SETID_DEPT and
DEPT_TBL.EFFDT=(select max(j.EFFDT) from DEPT_TBL j where
j.EFFDT<=BO_JOB_VW.EFFDT and
j.DEPTID=BO_JOB_VW.DEPTID and
j.SETID=BO_JOB_VW.SETID_DEPT)
and
Code Snippet
BO_JOB_VW.EMPLID = EMPLOYEE.EMPLID
BO_JOB_VW.EMPL_RCD = EMPLOYEE.EMPL_RCD
Although it is possible for an employee to belong to more than one department simultaneously, what I have notice is that for some employees that get multiple rows of departments, they have never belonged to more than one department or ever changed departments at all.
e.g
Code Snippet
DEPT
/*
DEPTID SETID EFFDT DESCR
DP1 GBR 01/01/1901
DP1a GBR 01/01/1965
DP2 GBR 01/01/1996
DP2a GBR 01/01/1998
DP2b GBR 01/01/2006
*/
DP1 and DP1a refers to the same department but reflect a change of names
DP2, DP2a and DP2b refer to one department but also reflect changes in the name
JOB
/*
DEPTID SETID EFFDT EMPLID EMPL_RCD EFFSEQ STD_HRS
DP1 GBR 01/01/1901 001 0 0 37.0
DP1a GBR 01/01/1999 002 0 0 37.5
DP2 GBR 01/01/1996 003 0 0 18.75
DP2a GBR 01/01/1998 003 0 1 22.2
DP2b GBR 01/01/2006 002 0 3 37.0
*/
EMPLOYEE
/*
EMPLID EMPL_RCD
001 0
002 0
003 0
003 0
002 0
*/
THe report should therefore look something like
EMPLID DEPTID STD_HRS EFFDT
001 DP1 37.0 01/01/1901
002 DP2b 37.5 01/01/1999
002 DP2b 37.0 01/01/2006
002 DP2a 18.75 01/01/1996
002 DP2a 22.2 01/01/1998
If you need any further details please let me know
code suggestions would be welcome and greatly appreciated
thanks
View 3 Replies
View Related
Jan 23, 2008
Hi. Is there a rownum, rowid, or autonumber in t-sql for SQL Server 2005?
Thanks!
View 10 Replies
View Related
Jul 30, 2004
Hi All....
I have to write one query where i have to display the ID as 1,2 3, 4 in oracle we used to ROWNUM to display ....in MS SQL server is there any property to show???
i have to display like
ID ProgramCount
1 4
2 6
3 5
4 1
5 2
Ashutosh
View 1 Replies
View Related
Oct 13, 2001
hello,
Do we have Rowid or Rownum in SQL Server, or any alternative.
Thanks,
venkat.
View 1 Replies
View Related
Nov 11, 2001
Hello,
How can this query be written in SQLSERVER 7 as it is written in Oracle.
SELECT rownum, column1 from XYZ;
This table returns two columns, first being the auto-generated sequence and second has the values in column1 of XYZ table.
I need to do similar in SQLserver 7 and I am new to it.
Please treat it as urgent..
Thanx
View 2 Replies
View Related
Aug 13, 2007
I apologize in advance, but this post might get somewhat lengthy.
I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:
The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:
INSERT INTO #RESULTS
SELECT PY.PaymentId
, PY.PayeeId
, PY.PartyAddressId
, PY.DistributionId
, PY.EntitlementId
, PY.DeliveryTypeEnumItemId
, PY.AccountPaymentId
, PY.ParentPaymentId
, PY.PaymentAmount
, PY.PaymentDate
, PY.PaymentStatusEnumItemId
, PY.PaymentStatusDate
, PY.ReleaseRunId
, PY.ReleaseDate
, PY.AccountTransactionLogId
, PY.AccountStatusEnumItemId
, PY.AccountStatusDate
, PY.AccountPaidAmount
, PY.ReconciledInd
, PY.UndeliverableInd
, PY.ReissueNote
, PY.CreateDate
, PY.CreateId
, PY.ModifiedDate
, PY.ModifiedId
, DS.Description
, AC.Description
, AC.AccountProvider
, AC.AccountId
, PT.Name
, PA.AddressLine1
, PA.AddressLine2
, PA.City
, PA.State
, PA.Zip5
, PA.Zip4
, PE.clm_no
, CM.clmnt_idno
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId,
ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.AccountPaymentId = PY.AccountPaymentId)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no)
WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
ORDER BY AccountPaymentID
--GET A COUNT OF THE ROWS SELECTED
SELECT @TotalRows = Count(*)
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.PaymentId,
ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.PaymentId = PY.PaymentId)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no)
WHERE
((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
SET @ORDERBY = ' ORDER BY ' + @SORT --END
--CASE WHEN @Sort IS NULL THEN '' ELSE
EXEC('SELECT * FROM #RESULTS ' + @ORDERBY)
--SET @TOPSQL = 'SELECT TOP ' + Convert(VarChar,@PageSize) + '* FROM #RESULTS ' + @ORDERBY
--SELECT @TOPSQL
--EXEC (@TOPSQL)
SET @PAGES = Round(@totalRows / @PageSize,0,1) + CASE WHEN @TotalRows % @PageSize = 0 THEN 0 ELSE 1 END
-- Return Total number of pages and Total number of Rows
SELECT @PAGES AS PageCount,
@TOTALROWS AS TotalRecords
I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.
I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)
What I would like to do is when I choose to sort on the column (ASC or DESC),
1.) The entire record set is esssentially retrieved again,
2.) The record set is resorted in the proper order
3.) The record set is redisplayed.
For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.
Hopefully this all made sense...!
If anyone has any advice or insight, please don't hesitate!
Thank You!!
View 2 Replies
View Related
Mar 31, 2006
do we have any equivalent of rownum and rowid in MS SQL
View 7 Replies
View Related
Aug 14, 2007
I apologize in advance, but this post might get somewhat lengthy.
I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:
The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:
Code Snippet
INSERT INTO #RESULTS
SELECT PY.PaymentId
, PY.PayeeId
, PY.PartyAddressId
, PY.DistributionId
, PY.EntitlementId
, PY.DeliveryTypeEnumItemId
, PY.AccountPaymentId
, PY.ParentPaymentId
, PY.PaymentAmount
, PY.PaymentDate
, PY.PaymentStatusEnumItemId
, PY.PaymentStatusDate
, PY.ReleaseRunId
, PY.ReleaseDate
, PY.AccountTransactionLogId
, PY.AccountStatusEnumItemId
, PY.AccountStatusDate
, PY.AccountPaidAmount
, PY.ReconciledInd
, PY.UndeliverableInd
, PY.ReissueNote
, PY.CreateDate
, PY.CreateId
, PY.ModifiedDate
, PY.ModifiedId
, DS.Description
, AC.Description
, AC.AccountProvider
, AC.AccountId
, PT.Name
, PA.AddressLine1
, PA.AddressLine2
, PA.City
, PA.State
, PA.Zip5
, PA.Zip4
, PE.clm_no
, CM.clmnt_idno
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId,
ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.AccountPaymentId = PY.AccountPaymentId)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no)
WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
ORDER BY AccountPaymentID
--GET A COUNT OF THE ROWS SELECTED
SELECT @TotalRows = Count(*)
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.PaymentId,
ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.PaymentId = PY.PaymentId)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no)
WHERE
((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
SET @ORDERBY = ' ORDER BY ' + @SORT --END
--CASE WHEN @Sort IS NULL THEN '' ELSE
EXEC('SELECT * FROM #RESULTS ' + @ORDERBY)
--SET @TOPSQL = 'SELECT TOP ' + Convert(VarChar,@PageSize) + '* FROM #RESULTS ' + @ORDERBY
--SELECT @TOPSQL
--EXEC (@TOPSQL)
SET @PAGES = Round(@totalRows / @PageSize,0,1) + CASE WHEN @TotalRows % @PageSize = 0 THEN 0 ELSE 1 END
-- Return Total number of pages and Total number of Rows
SELECT @PAGES AS PageCount,
@TOTALROWS AS TotalRecords
I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.
I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)
What I would like to do is when I choose to sort on the column (ASC or DESC),
1.) The entire record set is esssentially retrieved again,
2.) The record set is resorted in the proper order
3.) The record set is redisplayed.
For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.
Hopefully this all made sense...!
If anyone has any advice or insight, please don't hesitate!
Thank You!!
View 11 Replies
View Related
Jan 15, 2001
Is there an equivalent to Oracle's ROWNUM in SQL Server. ROWNUM, when added to a select statement as a column - the query would return an automatic counter, numbering each row returned.
View 1 Replies
View Related
Jun 10, 2000
Hi all
I am new to sqlserve
Is there any equivelant to rownum of oracle.
if not then please can some body gudie me how to give unique
number to each row
like
1 xxx
2 yyy
3 zzz
4 aaa
Thanks pratik
View 2 Replies
View Related
Sep 2, 2004
Hi,
Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.
Note that the Identity Property or TOP n will not solve my problem.
I want to asign a sequence no. to each row when its being fetched.
For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,
SELECT rownum , empno, empname FROM emp Where rownum < =3
I get the result like this
Rownum----Empno--------------Empname
------------------------------------------
1-----------2345---------------ABCD
2-----------3334---------------EFGH
3-----------4484---------------IJKL
I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'
Thanks
Asim Naveed
3
View 2 Replies
View Related
Feb 15, 2015
How can I reset the number of Row_Number() if I exceed the number of rows specified.
Example:
Code:
Col1 RowNum
----------------------
ID1 1
ID1 2
ID1 3
ID1 4
ID1 5
ID1 1
ID1 2
ID1 3
ID1 4
In this example, the Row_Number will reset for every 5 items.
View 1 Replies
View Related
Jul 23, 2005
Hello,I would like to know if the equivalent Oracle rownum exist inSQLServer. Here is a sample SQL code to explain what I want to do :selectjobs.name,jobs.job_id,jobs.description,hist.message,hist.step_name,hist.step_id,hist.run_status,hist.run_date,hist.run_time,hist.run_durationfrommsdb.dbo.sysjobs jobs,msdb.dbo.sysjobhistory histwherejobs.job_id=hist.job_idand hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'order byrun_date, run_timeI just want the first or second row returned by this query. In Oracle Ican simply add rownum=1 or rownum=2 in the where clause to obtain thedesired answer. I don't know how to do in SQLServer.Thank in advance,Pierig.
View 4 Replies
View Related
Sep 23, 2006
hi,
There is no direct equivalent to Oracle's rownum or row id in SQL Server
there is any possiblity are alternate thing is exist then please reply
regards
santosh
View 10 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Jan 24, 2006
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
View 3 Replies
View Related
Feb 8, 2000
hi from France !!!
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???
thanks to all, nico
View 1 Replies
View Related
Mar 13, 2007
This is the scenario. Is it possible to create views or something (like Oracle DB Links) in a database in Instance 1 which can show data from another database in 2nd Instance ?
I want to do this to create reports.
View 2 Replies
View Related
Sep 24, 2006
I have created a site which now has two databases. Firstly is the ASPNETDB.MDF which I beleives get set to manage the security, logon/Roles that I have on my site. The second dataabase "MyDatabase" is one that I have created for all my data withing the site. I would like to combine this to one Database for simplicity and cost. Now I can't see the ASPNETDB.MDF when I use SQL Manager 2005 and I can't see how to change the set up of ASP.Net Configuration if I was to just use mydatabase. Any suggestions
View 1 Replies
View Related
Sep 25, 2006
Hi, I have my site set up, but have had difficulty using the data gathered by the aspnet.mdf. I wanted to use the email and username columns from the membership table to send newsletters automatically from the database(but that's a whole other post)I also have my own databse, which will be populated by information gathered from the website, so is currently empty. I'd like to take this opportunity to say that i don't think there's enough information given on how to populate a blank database from a website application. Most tuorials etc are concentrated on retrieving and displaying data, which is all well and good, once you actually have data to play with!I don't need two databases, so can I add tables to the aspnet.mdf ? But then what about relationships etc? I won't damage the current relationships by adding new ones? On the other hand can i copy the aspnet.mdf into my own database, and then assign the relevant permissions to that db? Any help would be greatly appreciated-in language for newbies please! I am relatively new to .asp.net/Visual Basic/ and SQL. It's a steep learning curve!Also....I have used create user wizard, login, password recovery etc within my site, the data is inserted into the correct db tables-without using an sql datasource for some reason...? is that right?I have also created a wizard to collect some more data (because i couldn't work out how to insert the data from the extra steps in create user wizard, into my own database) but when i click submit the data is not inserting into the specified table - (yes the connection string is fine, insert and update has been selected from the advance tab in connections wizard). Would this/could this be because i have assigned a 'finishbutton url'? should i use a Response.Redirect("blahblah.aspx") instead?
View 4 Replies
View Related
Nov 16, 2004
I am very new to asp.net, but am having trouble trying to link the asp.net page that i have created to an sql database that has been set up on a local system. Am actually trying to use the sql control to extract info from the database. Would appreciate any ideas.
View 1 Replies
View Related
Jul 13, 2001
Dear friends,
There are two databases in SQL Server and I have to designed the view or store procedure joing tables from two databases. How ? It will be kind, if you give me answer with detail example.
Best regards!
manoj
View 1 Replies
View Related
Jul 16, 2000
Hello,
I’m using VB6 as the client and MS SQL Server 7 as the database. The company I’m consulting for has a proven track record for marketing and getting millions of customers. The parent company would like to create two new companies selling the same product; with 1 million customers per company. How many records can MS SQL Server 7 handle? I’ve been told it can handle 4 terabytes. What does 4 terabytes translate into records? I have already set up an application for company #1. Should I put company #2 in the same MS SQL Server database? Or should I put company #2 in a different database? My VB Application and SQL Server database were designed to handle multiple companies selling the same product. For performance sake; would it be better to separate the databases? Any suggestions? Is anyone currently using MS SQL Server with 2 or more million records?
Thanks,
Denise
View 2 Replies
View Related
Aug 3, 2000
I am presenting using MS SQL 6.5 and have used up all my (VDEVNO) device numbers (1-255). Will I have this problem when I migrate to SQL 7? I understand SQL 7 does not use a device when creating databases, so what
is the maximum number of databases I can create in SQL 7?
Thanks in advance.
View 4 Replies
View Related
Apr 9, 2005
Gusy im currently making and app that runs from a cd rom yet it uses databases made in sql and i already made the part for ataching the database from the cd but i haven't been able to do 2 things:
1. Can i actually attach my database from a cd without any need for the computer
2. does anyone has the code for detaching a database in Sql in c#
thanks ahead
View 6 Replies
View Related
Jul 30, 2004
How can you make two databases communicate with each other?? they both reside in sql server 2000. How would I go about doing that. Access 2000 is the interface
View 3 Replies
View Related
May 21, 2004
Hello,
Is it possible to restore a 6.5 database to a 7.0? I backup the entire 6.5 database to a BAK file then tried to restore it to SQL 7.0 but it didn't work. SQL 7.0 states that the disk device is not a valid Microsoft Tape Format backup set.
We have an application that was developped long long time ago by a old team and I don't want to upgrade the server. I tried to build a second 6.5 server (new hardware) but I couldn't restore the database as SQL 6.5 states that the server is in single user mode. I don't know how to switch to multi users mode. The database I want to restore is not in DBO or single user mode.
Any help will be appreciated.
Thanks.
Toan.
View 1 Replies
View Related
