Hi Everybody,
Is there any way to find the current position of a row in a table?. Like what we have in Oracle ROWID, Like that anything available in SQL Server.
I have seen NEWID(),uniqueidentifier & ROWGUIDCOL in BOL. Apart from that any easy method is available in SQL Server?
thanks in advance,
Vasu
There are about 500 tables in one particular datbase. There are
foreign keys set on most of the tables. I want to change the position
of the primary key column in all those tables. How can I do that
programatically?
Dear All,I want to get more than one table columns as a single column byalternative.For eg,Table name = employeecolumns = empid, empname, address1,address2Here, I want to interpolate the address2 column on address1 asmentioned below,empid empname address1address21 aaa First Layout,CA, US.Thanks in advance.Thanks and Regards,Ganapathi sundaram.G
View 3 Replies View RelatedHi,
Does anyone know if it is possible to add a column to a table at a particular position. The ALTER TABLE statement specifies that an ADD COLUMN puts this on the end of the table. Is there a way to insert into the middle of the table?
Alternatively, can it be done through SQL-DMO?
Obviously the fallback would be to drop and re-create the table with the correct column ordering, however this is not ideal for tables with large amounts of data.
It's possible to do this through Enterprise Manager (using Insert Column), so how does it do it - DMO, or by dropping and re-creating?
We're building a database schema upgrage tool and are trying to cover all possibilities.
Thanks,
Steve Jenkins.
Hy!
I'm trying to write a query which returns only some rows in my table...
For example :
I want all the rows included between 30 and 50 from table XYZ
(30 and 50 are not keys but really the numbers of records)
Is there someone out there who has a hint for me???
Thank you for your help and sorry for my english!
RadiFluide
Hello,
I have a report that prints cards for customers. The body of the report contains an address box, letter body, followed by a table that contains all the people to be printed on the cards. If the number exceeds 6 people, another page is to be printed, with no address or letter body, but the table with the remainder of the people on it.
I have been able to get everything to work except for the location of the table on the subsequent pages. They do not appear at the bottom as they do on the first page, but at the top of the page. I've tried using a rectangle as a container for the table, with no luck.
I seem to remember doing this in the past, but as projects get shelved for an extended period of time, the technical knowledge tends to fade.
Any suggestions would be greatly appreciated. Thanks
Hi
I am having a problem in auditing the column data in tables.My requirement is i have write a trigger which is capable of auditing the columns which are going to be added in the future also with out using dynamic SQL.is there any way to do so.
I feel if i can get the column data based on ordinal position then it is possible.
Can any body suggest.
My set Up is like this
I have a base_table to be audited.
I have a Audit_spec table which contains name of the table and columns to be audited.
And Audit table which actually captures the table name,column name ,old value and new value.
I have to audit only those columns in the Audit_spec spec.
If schema changes(Like new column added) happens to base_table and I want that column to be audited.with out any changes to my trigger code i should handle the newly added column ..
Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
hello all..
i want to update my table information with the value which is currently inserted/updated in another table dynamically..how to get the value of a currently inserted single cell in that table..?
table1 contains Refno,.....Refno is primarykey,identity
table 2 contains uploadID,....uploadID is primary key,identity
table3 contains RefNo,UploadID(both r foriegn keys corresponding to table1 & table2)
how to fill table3 with values(not manually) when i am inserting records into table1 n table2 ..( refno and uploadId are identity columns )..Any Idea..?
thanks in Advance
Anne
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
moe writes "Could anybody can tell me about having a current date automatically on my table I created by using enterprise manager. The field name I put it as date and the data type is datetime and the length is 8. So what I have to do to get the current date on date field automatically without showing time. I appreciate your help. Thanks!"
View 5 Replies View RelatedHi,I have a stored procedure that needs to know the name of the tablefrom which it is called. If you are familiar with a "this" pointer inJava or C++, that's very similar to what I need. I know I can usedb_name() to retrieve the database name, but how do retrieve the tablename?Thanks,--Michael
View 6 Replies View RelatedHi,I was wondering if you guys have any nth script to reads from tableand outputs into a temp table subset of records. There was a nth toolI used to use it was GROUP1 which was written in C and it used to bevery fast on nth -in a flat file. In this program we used to pass fewparamaeters. For example if I want 30,000 records from the file of500,000. The function seams to work something like this. you dividethe 30,000 records of 500,000 which will result with .090909090909.Now we would pass only the first 7 digit (0909090) as parameter thatwould nth the file down to 30,000 records. This function allwaysworked whichever number you use as long as the read file is largerthan output fileI like to use the similar concept in Sql Server and I was wondering ifanyone has any script to do this or how to go about this?Thank you. I appreciate your feedbackagron
View 1 Replies View RelatedGood Morning
Is There anyway to determine the table you are referencing in a trigger's inserted / deleted tables?
i.e would this be possible
print @Table_Name + " was Updated"
Where @Table_Name is the table on which the trigger is created
(bad example I know but you get the idea)
Any input will be appreciated.
Hi all...
Iam a newbie and i have one question want to ask experts ... .
I am current working on one App which have some user connect to database at the same time.
And i want to know: how many user connect or use (read , update) to one row in a table?
Could it be possible to know that??? If you have one solution to solve this problem , please let me know :) ...
Could it be done by Software , T-SQL or anything ...., iam happy to know.
Thanks all.
Just a quick easy question. If I alter a table (add a column to the table), will it take the table offline during the ALTER process? I am adding the column to the end of the table not in the middle. I know if I add it in the middle it will offline the table.
View 4 Replies View RelatedI've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
I have a table thats updated daily with monthly data totals:
Month, Total orders1, Total orders2, etc
12/01/2012, 5, 8, etc
11/01/2012, 6, 5, etc
How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results
I am running a script by the end of the day. What I need is the rows in my temp table get saved in a permanent table.
The name of the table should end with the current date at the end.
Declare @tab varchar(100)
set @tab = 'MPOG_Research..ACRC_427_' + CONVERT(CHAR(10), GETDATE(), 112 )
IF object_id(@tab ) IS NOT NULL
DROP TABLE '@tab';
Select * INTO @tab from #acrc427;
I have 2 stored procedure, insert and update.
They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.
Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;
Then I would expect the table difference to not matter as the table is being dropped...
I need to get previous month data in the table based on current date.
In case of execution of each month, the data for previous month should come with date as between
create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)
insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')
/*
if I execute in month July ( ie: today)
select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014
Result
5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000
if I execute in month June
select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014
Result
6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000
/*
I want to bookMark from one table to another in current report. Below are the 2 tables , when I select a
FromBrandName in the first table, it should take me to the second table row with the same
FromBrandName. Is this possible? Is there a function to access a specific running value other than First and Last?
Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!
Thanks!
Charles.
Somebody suggested I use the slowly changing dimension object to keep a table current between servers and databases.
I have two databases servers. One 2000 and the other 2005. I was told because security reasons I should not link them
The two tables are not identical. The destination table only has a handle of the columns and in some cases columns are not the same size.
Once a day. When new rows are introduced in the source table, I'd like to introduce those rows into the destination table. When rows are modified, I'd like to apply changes to the distination table.
I started playing with the slowly changing dimensions object, but am frankly confused connected the dots. Will I have an output oledb object for each command object?
Am i using the right the object for objective?
Are there any good tutorials or videos out there that will cover this?
Thanks for any help or information!
Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.
View 7 Replies View RelatedI have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
hello everyone,
we have a problem in my system that will say it now.
we have two departments can access to the same database , the first department fill all the fields in the application(web_enabled), the second department can fill some fields from whole the fields and the remaining fields are set as default values because they don't have access to the remaining items.
the problem here, when the first department go to fill the fields and then press save to save the filled information , will operation successful, but when the second one want to fill the fields then click save , will appear this error
" there is no row at position 0".
note that we can't to change any thing in the code , i hope to find the answer for our problem thru database.
with my regards
Hi,
I'm trying to get the position of a single value in the query results.
like this:
select * from tPerson order by ds_alias
results in
id_person | ds_name | ds_alias
15 | mark | AA
20 | john | AA
5 | mike | BB
8 | thomas | JK
2 | mike | MM
as you can see, I can have registries with the same name and with the same alias, there's no restriction
and I'm trying to find out how many registries there are before the second "mike", wich would be 3.. so it's the 4th element, get it?
I have no idea how to...
Thank in advance
Hi,
Is there an easy way to get the position (or number) of the week from a date? By position, I mean week number 1 if we are january 1st. For example:
Date | Week postion
=====================
2001-01-05 | 1
1996-12-28 | 52
1987-06-15 | 26
Thanks a lot,
Skip.
Hi, Nobody have idea to get a field in "select" statement by its position (without name then)?
My scope was to duplicate a row into a table with identity column getting all row except the identity one, that i want to provided by constant expression.
Bye and thanks...
:D :D
Is there a neat way to find an ordinal value from a table,for example the median or 95th percentile value in a column,without walking through the table in ascending or descendingorder?Thanks,Jim GeissmanCountrywide Home Loans
View 1 Replies View Related
When i use the "run package task" to run a package in the sqlserver storage,the package will run at the storage server or the server call this package?
Thanks!