Database

22-Jun-07 14:26

@@IDENTITY returns last generated auto increment number
with which you can get the last custid inserted into the table.

Regards
KP

Re: Plz solve this query

Colin Angus Mackay24-Jun-07 2:27

Colin Angus Mackay

24-Jun-07 2:27

Krish - KP wrote:
@@IDENTITY returns last generated auto increment number

Yes, but for what table? If you have a large multi user database handling many transactions at once there is no guarantee that it will return the identity of the last row inserted in the scope your running in. It might return the identity for some other code running on some other table that just happens to be running at the same time as your code.

You should use SCOPE_IDENTITY() to ensure that you get the identity of the last insert in your scope (and no one else's scope)

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Ready to Give up - Your help will be much appreciated.

My website

user sql server

WhiteGirl2322-Jun-07 1:10

WhiteGirl23

22-Jun-07 1:10

Hi,
I want to create an user in sql server.
My program create a database, then it create a user for that database .
The problem is that I can't see the tables from the database with the user.

please help.
thanks.

kubben22-Jun-07 1:56

kubben

22-Jun-07 1:56

Sounds like you need to give database reader role to your new user.

Ben

Mike Dimmick23-Jun-07 2:20

Mike Dimmick

23-Jun-07 2:20

SQL Server has security. In order to be able to SELECT from tables, the user account in the database has to be granted permissions. Use the GRANT statement to set the permissions.

I normally make ordinary users members of the public role and limit that role to only being allowed to do the minimal set of operations that the application requires. By default, public has no permissions to do anything.

Stability. What an interesting concept. -- Chris Maunder

Re: databas query question

WhiteGirl2324-Jun-07 19:48

WhiteGirl23

24-Jun-07 19:48

Thanks.
It works now.

databas query question

samtam21-Jun-07 23:09

samtam

21-Jun-07 23:09

Hi all!

There is a question. i am facing difficulting in writting a query for selecting record in database.i am explaining whole scenerio of current problem:
i have a table StockDelta having attributes as follows:

stockDeltaID(primary key),
productID(forighn key from product table(productID, productName, totalQuantity etc) ),
Delta(the change in quantity of stock of a product, if the quantity increases the value of delta will be positive and if the quantity decreases value of delta will be negative),
isStockAdded(bool value indicating if the changed amount was basically the added stock), isSecondarySales(bool value indicating if the changed amount was basically the sencondary sales),
isDamages(bool value indicating if the changed amount was basically the damaged amount),
isFreeGifts(bool value indicating if the changed amount was basically the amount of freeGift)

now i have to generate a Stock report(using crystal reports), the report will take a parameter in case of daily report and two parameters in case of monthly report. the information that we want to show in report is as follows:

The columns of report should be
"Opening Stock"(representing quantity at start of month/day). we will get this column by Sum(delta) where date < pdate-1 //pdate is parameter date

"Stock Added"(representing quantity purchased during month/day), we will get this column by Sum( Delta) where date = pdate and isStockAdded = true

"Secondry Sales"(representing the sale out of a produt on a specific month/day), we will get this column by Sum(delta) where date = pdate and isSecondarySales = true

"Closing Stock"(representing the net quantity of a product in stock on a specific month/day), we'll get this column by Sum (delta) where date < pdate

"Damages"(from 1st of a month till a specific day or in a whole month), we get this column by quering Sum(delta) where date between 1st of month till pdate and isDamages = true

"FreeGifts"(representing the quantity of freegifts from 1st till that day of a month or for whole month). we will get this column by quering Sum(quantity of Freegifts) where date between 1st of month till pdate and isFreeGift = true

hope you people have grasped what i said.. i have tried my best to figure out query for this report but maybe at this student level i am unable to come with the right solution. i am stuck on it and request you people to please consider the problem if you get some time.

Thanks in advance
shaz jazz

andyharman22-Jun-07 0:39

andyharman

22-Jun-07 0:39

Hi Shaz

I have done the SQL for the opening-balance and stock-added results:

SELECT SD.productId,
  SUM(CASE
      WHEN SD.deltaDate < @StartDate THEN SD.Delta
      ELSE 0
    END) AS OpeningStock,
  SUM(CASE
      WHEN SD.deltaDate >= @StartDate AND SD.isStockAdded = 1 THEN SD.Delta
      ELSE 0
    END) AS StockAdded
FROM StockDelta SD
WHERE deltaDate < @EndDate
GROUP BY productId
ORDER BY productId

I'll let you figure out how to do the remaining columns.

The @StartDate and @EndDate bits refer to the parameters that you need to supply. The above should work for SQL-Server and Oracle. If you are using MS-Access then you will need to use the IIF function. An alternative approach would be to use sub-queries.

Regards
Andy

user sql server

WhiteGirl2321-Jun-07 22:37

WhiteGirl23

21-Jun-07 22:37

Hi,
I have a problem with a user.

I have the connection string:

MsSQL_conn1.ConnectionString = "Server = " & frmMain.txtMyServer.Text<br />
 MsSQL_conn1.ConnectionString += "; User=RWIN" & "; Password=AVM" & "; Database =" & frmMain.txtMyDatabase.Text & ";Connect Timeout=30; Integrated Security = SSPI"

When I try to connect from my computer where I have installed the Sql Server it works.I can log on Sql Server with my user.

But when I try to connect with my program to another computer where is installed Sql Server I have the error:

Login failed for user 'LEXI-DEVELOP4\Guest'.

Please help.

Thanks

Removing Redundant enteries for a Table??

originSH21-Jun-07 22:57

originSH

21-Jun-07 22:57

SSPI will use your windows account to authenticate.

check your connection string here:
http://www.connectionstrings.com/[^]

granshah @ eVisionISF21-Jun-07 21:22

granshah @ eVisionISF

21-Jun-07 21:22

Can you please tell me about a query which I could use for the purpose of removing all the repetition(Repeated Records) in a table. I am trying with this one but its not working properly.

delete from EXCHANGE_STOCKS
where STOCK_ID not in (select distinct SYMBOL,EXCHANGE,(select top 1 STOCK_ID from EXCHANGE_STOCKS where SYMBOL = es.SYMBOL and EXCHANGE = es.EXCHANGE) as STOCK_ID
from EXCHANGE_STOCKS as es)

Re: Removing Redundant enteries for a Table??

Krish - KP21-Jun-07 21:44

SQL Server Authentication

21-Jun-07 21:44

Add an Identity column to the table. for ex. colA

then execute this query
SELECT * FROM TblA a WHERE ColA < (SELECT Max(ColA) FROM TblA b WHERE b.Stock_ID = a.Stock_ID) ORDER BY a.Stock_ID

this will fetch all records except one from the duplicates

Regards
KP

UNIQUE MUSKAN21-Jun-07 21:14

UNIQUE MUSKAN

21-Jun-07 21:14

How to authenticate the sql server

UNIQUE MUSKAN

Re: SQL Server Authentication

Sylvester george21-Jun-07 21:22

Sylvester george

21-Jun-07 21:22

what does it mean by authenticate the sql server?

Regards,
Sylvester G
sylvester_g_m@yahoo.com

Re: SQL Server Authentication

originSH21-Jun-07 23:10

originSH

21-Jun-07 23:10

You mean how do you authenticate a user using sql server authentication as aposed to windows authentication?

Or how do you authenticate against sql server?

Either way:

http://www.connectionstrings.com/[^]

query

mohantfor.net21-Jun-07 21:07

mohantfor.net

21-Jun-07 21:07

i want insert like this text.mohan's.how can i insert this in sqlserver.plz send me.its urgent

gauthee21-Jun-07 21:10

gauthee

21-Jun-07 21:10

use 'mohan'''s' and insert the value just play with apostrophee until the entire value becomes a single string!

Gautham

mohantfor.net21-Jun-07 21:13

mohantfor.net

21-Jun-07 21:13

thank u .but how can i concate in asp.net for insert

Krish - KP21-Jun-07 21:30

21-Jun-07 21:30

refer to message below with subject
"Problem with special characters in SqlParameter"
similary usage is explained as what you are looking for,
in different ways ...

Regards
KP

how to create stored procedure?

Blue_Boy21-Jun-07 21:51

Blue_Boy

21-Jun-07 21:51

if you are using asp.net with C# then try

TextBox1.Text.Replace("'","''");

I Love SQL

Care Career21-Jun-07 20:12

Care Career

21-Jun-07 20:12

Please help

I want to create stored procedure using two tables &
Want to commit data of first table & rollback .

Thnx
CARe CAREER

Re: how to create stored procedure?

Krish - KP21-Jun-07 20:22

Executing Stored Procedure

21-Jun-07 20:22

are the DML operations are done one after the other in batch or jumbled

Regards
KP

M. J. Jaya Chitra21-Jun-07 19:37

M. J. Jaya Chitra

21-Jun-07 19:37

I want to create a stored procedure in which while inserting or updating a record if a flag is set then i have to insert some other values in the parent table and based on the id which is auto generated by the parent table i have to insert that value into the child table How can i perform this.

I have got already two procedures for inserting and updating the data into the tables.

Best Regards,
M. J. Jaya Chitra

Re: Executing Stored Procedure

Krish - KP21-Jun-07 20:00