|
In SQL Server 2000 use the Enterprise manage. In the security tab for the server you can add the log in. In the users tab for the individual database you can then set up access to the database and the objects it contains.
In SQL Server 2005 there will be a similar mechanism in the SQL Server Management Studio.
|
|
|
|
|
Hi,
I need to check if a field exists in a certain table (in an SQL database), and if not then add that field to the table.
For Instance I have Table "A" that should contain field "a". So I want to check if field a is already defined for the table, and if not then add it.
How can I d o this? Is there an SQL statement to check if a field exists?
Thanks
|
|
|
|
|
SWDevil wrote: Is there an SQL statement to check if a field exists?
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName')
BEGIN
ALTER TABLE ....
END
|
|
|
|
|
If I need to add more than one fields can I write than one ALTER statements:
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName')
BEGIN
ALTER TABLE ....
ALTER TABLE ...
ALTER TABLE ...
ALTER TABLE ...
END
|
|
|
|
|
You can write as many ALTER TABLE statements as you like. Or you can put it all in one ALTER TABLE statement. It is up to you.
|
|
|
|
|
I have a single table with many phone numbers per person, i.e. person data is repeated for each phone number for that person. I while ago I normalised this using SQL updates, but I can't remember how I did it and maybe here someone can give me a better way anyway. The original table has no PK, and my first step is to get all unique person data, using distinct, into a new Person table. I then want to extract phone number columns from each record, and add a column to tell which phone record belongs to which Person record.
My problem is, using distinct to get unique Person data precludes including any ID unique to a Phone record, and I have to real columns that I can use to pair new Person records with Person records in the source table except comparing all columns, and some are null, so this is out. Any suggestions?
|
|
|
|
|
I would add a new column to the existing table and update it with the primary key you created based off it matching to your new table. So your primary key would be duplicated in the old table, but that will allow you to link properly when creating your phone table.
Hope that helps.
Ben
|
|
|
|
|
My problem is matching records in the new and old tables! I have to basically compare all fields from the old table to corresponding fields in the new table, and in T-SQL comparisons don't work with null values. So, I have to, just for one field, for example:
and (new.IDNumber = old.IDNumber) or (new.IDNumber is null and old.IDNumber is null)
etc.
etc.
<edit>
I've just been made privy to the trick of using isnull() on all the field comparisons.
"A little learning is a dangerous thing; drink deep, or taste not the Pierian spring: there shallow draughts intoxicate the brain, and drinking largely sobers us again.", by Alexander Pope
My Blog
|
|
|
|
|
Well, if you are able to write a distinct select statement to create the new table to create a primary key, then you can use those same columns to update the existing table with the primary key. I don't know if you have many other choices.
Ben
|
|
|
|
|
I didn't, so thanks. With your suggestion and Excel to generate the comparison lists, it wasn't too bad.
|
|
|
|
|
Hi
My Department is Using SQl for the Database, and i have run some selected column and data into the table, and i want to have it on a CD as Backup so i can give it to other Company that uses SQl , for our calculation based on our Business. i have a SQl Query, that is ready
How can i see to it that the data is copied to the Cd and will be views in SQl on the other Company
Thanks
Vuyiswa
|
|
|
|
|
Save it as a backup file, burn the file to CD and have the other company restore the file from the CD.
|
|
|
|
|
I need the Steps on how to do it
thanks
Vuyiswa
|
|
|
|
|
Step 1: Take a backup of your database
Step 2: Copy the .BAK file to the CD
Step 3: Send the CD to the appropriate person
Step 4: Get the appropriate person to RESTORE from the BAK file.
|
|
|
|
|
|
Hi all,
I have a table with 50 rows
I need to split the resoults of the select to pages.
page 1 will have the 1st 15 rows
page 2 will have the 2nd 15 rows
page 3 will have the 3rd 15 rows
page 4 will have the remaining 5 rows
is there a query that can make my life easy
somthing like
SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)
thanks
|
|
|
|
|
you can use a datagrid........or a grid like control...
yes it is possible to split your record in a stored procedure...
Tirtha
Do not go where the path may lead, go instead where there is no path and leave a trail.
Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist
|
|
|
|
|
SqlDataAdapter.Fill(DataSet, Start, TotalRows, TableName)
"The callee (server [not server application]) is not available and disappeared; all connections are invalid.
The call did not execute," said Internet Explorer, when I tried to access a deceased [window] object using JavaScript.
::..:.:..:: KiRtAN GoR ::..:.:..::
|
|
|
|
|
Thank you for your answer
I think SqlDataAdapter is in use by C# or VB
but I'm using Visual Studio to create reports,
so I need a query to do that.
I thought about somthing like that
DECLARE @page_num int<br />
DECLARE @rows_in_page int<br />
DECLARE @temp1 TABLE ( aaa nVarchar(100), bbb nVarchar(200), ccc float, ddd int)<br />
DECLARE @SQL nvarchar(4000)<br />
<br />
SET @page_num = 3<br />
SET @rows_in_page = 15<br />
INSERT INTO Temp1 = SELECT * FROM myTable<br />
[[[ now @temp has all records ]]]<br />
<br />
[--> some code to delete the first 30 records ((page_num-1)*rows_in_page) <--]<br />
[[[ now @temp1 has pages 3,4,5,6,.... ]]]<br />
<br />
SET @SQL = 'SELECT TOP ' + @rows_in_page + ' * from @temp1'<br />
[[[ now the variable @SQL = "SELECT TOP 15 * from @temp1" ]]]<br />
<br />
<br />
EXECUTE (@SQL)<br />
[[[ the EXECUTE will give me the first 15. That means only page 3 ]]]
Can anyone post the missing code ?
|
|
|
|
|
Hi
It can be done.
Calculate startpage and endpage using the parameters @rows & @currentpage.
You need to pass two values from the front-end that is current page and number of rows per page.
See below example with paging condition and I used temporary tables with identity column for filtering pages.
Hope you got it.
<br />
<br />
use northwind<br />
go<br />
<br />
--create temporary table to store all rows with identity column or serial number for page filter <br />
CREATE TABLE #OrdersPage ([Iden] [int] IDENTITY(1,1) NOT NULL, OrderId int,CustomerID nchar(10))<br />
<br />
declare @startpage int<br />
declare @endpage int<br />
declare @rows int<br />
declare @currentpage int<br />
<br />
set @rows = 10 -- number of rows per page from the front end<br />
set @currentpage = 1 -- pass current page from the front end<br />
<br />
set @endpage = (@rows * @currentpage) + 1<br />
set @startpage = @endpage - @rows<br />
<br />
print @startpage <br />
print @endpage <br />
<br />
insert into #OrdersPage <br />
select top 50 OrderID, CustomerID from Orders<br />
<br />
select * from #OrdersPage where Iden >= @startpage and Iden < @endpage <br />
-- set paging condition here using the column 'Iden' from the temporary table<br />
<br />
drop table #OrdersPage<br />
<br />
Harini
|
|
|
|
|
Thanks...
That helped me alot 
|
|
|
|
|
I am having a stored procedure returning a result set. I am using that stored procedure to fill a datatable. That stored procedure generates the rows dynamically from some tables using a cursor. There might be some invalid data in those tables and because of that invalid data some errors are thrown. When the error is generated while adding a row, that particular row is not added to the output (which is a temp table, by the way). But the execution will continue and new rows will be added even after the error is thrown. I also get the rows alongwith the error when I execute the stored procedure in the query analyzer. But when I execute the stored procedure using ADO.NET classes and try to fill a datatable, only that error is returned.
Is there any way to bypass that error and fill the datatable. I tried the SqlDataAdapter.FillError event, but that event is not getting fired  May be it doesnot get fired when the fill error occuring is occuring database side 
Any solutions ? ? ?
"The callee (server [not server application]) is not available and disappeared; all connections are invalid.
The call did not execute," said Internet Explorer, when I tried to access a deceased [window] object using JavaScript.
::..:.:..:: KiRtAN GoR ::..:.:..::
|
|
|
|
|
Does anybody know why this SQL statement returns duplicates even though I have included the DISTINCT keyword. There aren't duplicates in the database.
SELECT DISTINCT BLT_BILLM_TAX.BILLM_TAX_UNO, BLT_BILLM_TAX.BILL_TRAN_UNO, HBM_MATTER.MATTER_CODE, BLT_BILLM_TAX.JURISDIC_CODE,BLT_BILLM_TAX.TAX_CODE, BLT_BILLM_TAX.TAXABL_FEES_AMT, BLT_BILLM_TAX.TAXABL_HARD_AMT, BLT_BILLM_TAX.TAXABL_SOFT_AMT,
BLT_BILLM_TAX.TAX_ON_FEES_AMT, BLT_BILLM_TAX.TAX_ON_HARD_AMT, BLT_BILLM_TAX.TAX_ON_SOFT_AMT, BLT_BILLM_TAX.FEE_TAX_RATE,
BLT_BILLM_TAX.DISB_TAX_RATE, CDT_DISB.PERIOD, CDT_DISB.COST_CODE, BLT_BILL.BILL_NUM
FROM HBM_MATTER
INNER JOIN CDT_DISB ON HBM_MATTER.MATTER_UNO = CDT_DISB.MATTER_UNO
INNER JOIN BLT_BILLM_TAX ON CDT_DISB.BILL_TRAN_UNO = BLT_BILLM_TAX.BILL_TRAN_UNO
INNER JOIN BLT_BILL ON CDT_DISB.BILL_TRAN_UNO = BLT_BILL.TRAN_UNO
INNER JOIN APT_INVOICE ON CDT_DISB.SOURCE_TRAN_UNO = APT_INVOICE.TRAN_UNO
WHERE (APT_INVOICE.PERIOD BETWEEN '200601' AND '200601')
AND (APT_INVOICE.TRAN_UNO = 627167)
Any help would be appreciated
Thanks
-- modified at 6:00 Thursday 31st May, 2007
-- modified at 6:01 Thursday 31st May, 2007
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
Most likely it is a inner join issue. If there are multiple rows in the table you are joining to, but you are not including any columns from that join you will get duplicates. I would look at removing any unnecessary joins.
Hope that helps.
Ben
|
|
|
|
|
I don't know what it was but I messed about with it and swapped some stuff around and it seems to work now.
Thanks
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
