Hello all,
Is there any way to force Autonum to generate a number before an entire record is created? Some of my forms will not work because it needs a number already listed in its index (which uses Autonum) and cannot add to the table until it is created.I really need it to have a number ready and waiting upon the last record's completion.
This is a pretty newbie question, but I have no experience with MSSQL. I have a table with the first column named "ID" and this column is a primary key. The data type for this column is "int" and the length is "4". The problem I'm having is that when I insert new data into the table I get an error because the "ID" column cannot be NULL. I thought since the "ID" column is a primary key it will automatically increment when new data is being inserted. I'm not sure what the best solution is, but I figure if I make the column "autonum" that would fix the problem. In the enterprise manager under the design table there doesn't seem to be an "autonum" selection from the data type drop down box. How can you set a column to autonum in MSSQL?
SB
Hi,
I have written the following StoredProcedure
Code:
create Procedure spCreateQuestion(
@QuestionName varchar(30)
)
as
declare @newAnswerId int
declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION Q1
--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions
--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
BEGIN TRANSACTION QA1
--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers
--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid
UPDATE Questions
set
AnswerId=@newAnswerId
where QuestionId=@newQuestionId
COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1
I think the second Transaction is not locking the table.so some how i should be
able to get the newly create AnswerId
i can't use the identity column in my tables
Can some one please have a look at it and suggest me how do we go about it..
How to create random number for the value in other colum. Please help and urgent
I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)
If Ori_quantity < 5000 then
Rand_quanty as qty = I want Random number within 100
Else If Ori_quantity = 0 or < 10 then
Rand_quanty as qty = I want Random number within 7
End if
From tblname where Location = 'DAS'
I have around 2500 field. So when I run the query I expect the result should be
like below
Ori_Quantity, Rand_Quantity
------------ -------------
4000 90
2986 57
2500 89
I don't want to pass any parameter and I am new. Please help
Hello,I am in the midst of converting an Access back end to SQL Server Express.The front end program (converted to Access 2003) uses DAO throughout. InAccess, when I use recordset.AddNew I can retrieve the autonum value for thenew record. This doesn't occur with SQL Server, which of course causes anerror (or at least in this code it does since there's an unhandled NULLvalue). Is there any way to retrieve this value when I add a new recordfrom SQL server or will I have to do it programmatically in VB?Any direction would be great.Thanks!
View 17 Replies View RelatedHere is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA
Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1
I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?
If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.
thoughts ?
Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC
Expected results as below :
------------------
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2
Thanks in advance
Hi all
i wants to generate linear sequence number like 1,2,3,.............1000000
,are there any function like NEWID() ( this return unique guide, i want to get integer)
i want to used this generated number inside the SQL query
thanks
IndikaD (Virtusa Cop)
Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express?
In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?
Thanks a lot.
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)
So, the SELECT would generate something that looks like this:
ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)
Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:
ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'
Is there an easy way to do this?
Hi
I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.
How do I generate 13 digit UNIQUE random in sql procedure?
Advance thanks
Hi
I have two below datacolumns
'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)
AS
Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'
from tablename
where ......
Advance thanks
I have a function which generate random number in range :
CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO
If you run this script you always get result between 1 and 4
SELECT dbo.Func_Rand(4, NEWID())
BUT, in this script sometimes have no result !!!!!!! why??
SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )
I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.
What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.
CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),
[code]....
Hi Experts,
In our production system, there are high number activities involving very huge tables ( around 250 million records ).
For performance benefits , we are using dynamic queries in the stored procedures. We are also using WITH clause to FORCE appropriate indexes.
Will forcing the indexes have any negative effects ? or Forcing the index would REALLY improve the performance.
Any inputs would be highly appreciated.
Thanks in advance.
Hariarul
I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.
When I run it in 32 bit mode using the GUI, it runs all the way through.
Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?
Thanks
Larry C
This is a really wide spread - more than a time discussed - on SQL CE MSDN Forums - Issue !!!
Is there any way i can commit changes which happens during runtime (when i am developing the application) such as inserts/updates and deletes to the .sdf DB on the machine ?????
Hi all,
As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.
the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.
Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.
So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.
Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.
the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?
greg
I have the following Case statement:
CASE
WHEN CAST(wo.start_date AS TIME) BETWEEN '00:00:00' AND '00:59:59' THEN 0
WHEN CAST(wo.start_date AS TIME) BETWEEN '01:00:00' AND '01:59:59' THEN 1
WHEN CAST(wo.start_date AS TIME) BETWEEN '02:00:00' AND '02:59:59' THEN 2
WHEN CAST(wo.start_date AS TIME) BETWEEN '03:00:00' AND '03:59:59' THEN 3
WHEN CAST(wo.start_date AS TIME) BETWEEN '04:00:00' AND '04:59:59' THEN 4
[code]....
The purpose is to take a row and set it to the hour of the day that it occurred in. This works fine, however I would like to force it to display every hour 0-23 regardless of whether or not it has a corresponding row.
So, if no row exists for 0, display 0 with null values for the rest of the columns.
In the following procedure i write the results to a temp table called #temp1I now want to count the results of #temp1, if the count of #temp1 = 0
I want to insert 'No Records Found' into #temp.ERRORMSG else return what is in the table
any idea on how to do this?
ALTER PROC [dbo].[SPU_RPT_Savings_AnomalyDispatches] 40,'04/01/07|06/30/07'
@PropertyID varchar(4000),
@DropDown varchar(50)
AS
SELECT Client.CLIENT, Client.CLIENTID, ErrorEmailLog.ID, ErrorEmailLog.SITEID, ErrorEmailLog.PROPID, ErrorEmailLog.DISTINCTERRORS,
ErrorEmailLog.ERRORMSG, ErrorEmailLog.ERRORDATETIME, ErrorEmailLog.EMAILRECIPIENTS, Property.PROPERTY, Property.STREET,
Property.CITY, Property.STATE, Property.ZIP, Property.PHONE
INTO #TEMP1
FROM ErrorEmailLog INNER JOIN
Property ON ErrorEmailLog.PROPID = Property.PROPID INNER JOIN
Client ON Property.CLIENTID = Client.CLIENTID
WHERE (ErrorEmailLog.ERRORDATETIME BETWEEN SUBSTRING(CONVERT(VARCHAR(12), @DropDown), 0, 9)
im testing an application change that should handle a timeout on a stored procedure being called from the application. thing is, the timeout that we experience in production that led to this fix is random. so is there some way for me to setup a test stored procedure or some way to call the SP so that i can test a timeout scenario?
im using MFC and the CDatabase::ExecuteSQL method to call this SP if you were wondering at all.
this app is running locally on the server that has an instance of SQL Server Express 2k5 on it. server is running win 2k3.
Hi Guys,
I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...
Is their a way to force 1 column to always be unique?
Heres the query:
SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC
The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.
Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:
ItemID
83630Mathew Smith
8 366Tony Wattage
Which is kinda annoying.... is there anyway i can prevent this?
And also apart from the Where Joins... is there a more efficient way of writing this?
thx for reading :-)
--Philkills
I am developing a simple DB-Library program in C calling SQL Server 2000 onwindows 2003 and NT 4. I have some T-SQL code that checks for the existenceof a table and want to abort the program if the table doesn't exist. I issuea raiserror if the table doesn't exist and then call RETURN.I construct the string using sprintf and pass it dbfcnd and dbsqlexec. Sincethe commands work, there is no error to halt the execution of the program.Is there an easy, clean way to force dbsqlexec to fail? Do I need a storedprocedure to return an error code and then deal with that?Thanks for any advice,-Gary
View 3 Replies View RelatedA stored procedure in the cache is automatically recompiled when a table it refers to has a table structure change. User defined functions are not. Here's a simplified code sample:
set nocount on
go
create table tmpTest (a int, b int, c int)
insert into tmpTest (a, b, c) values (1, 2, 3)
insert into tmpTest (a, b, c) values (2, 3, 4)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fTest]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fTest]
GO
CREATE FUNCTION dbo.fTest (@a int)
RETURNS TABLE
AS
RETURN (SELECT * from tmpTest where a = @a)
GO
select * from fTest(1)
CREATE TABLE dbo.Tmp_tmpTest
(
a int NULL,
b int NULL,
d int NULL,
c int NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.tmpTest)
EXEC('INSERT INTO dbo.Tmp_tmpTest (a, b, c)
SELECT a, b, c FROM dbo.tmpTest TABLOCKX')
DROP TABLE dbo.tmpTest
EXECUTE sp_rename N'dbo.Tmp_tmpTest', N'tmpTest', 'OBJECT'
select * from fTest(1)
drop table tmpTest
Running it, the output is:
a b c
----------- ----------- -----------
1 2 3
Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'tmpTest'.
a b c
----------- ----------- -----------
1 2 NULL
(I know that "select *" is bad, but it's a lot of legacy code that I'm working with here, and that's how it's written.)
The function doesn't detect that the table has changed in structure, or even that there is no longer a dependency on tmpTest. (Appending a column rather than inserting has the same effect, in that only the first 3 columns are returned.)
DBCC FREEPROCCACHE has no effect, not that I really expected it to, but you never know...
Is there any way, other than dropping and recreating, to force a recompilation of a particular function in memory, or perhaps all functions?
Thanks in anticipation.
Tom
Due to a lack of planning during an Active Directory migration last year, I'm now stuck with an immutable service master key on one of my production servers. Since I'm posting here, I guess it's obvious that we have no backup from which to restore. The account that all of the SQL services used to run under no longer exists, so the WITH OLD_ACCOUNT workaround is not viable. And REGENERATE fails, as expected, with Msg 15329, Level 16, State 2, Line 2, "The current master key cannot be decrypted..."
After some research, including several of Laurentiu's blog entries, it seems that my only path at this point is to use the FORCE option to REGENERATE. (And then to immediately backup the service master key at several geographically disparate locations!!
Considering that:
We aren't actively using any of SQL Server's encryption capabilities, the closest we come is that one of our legacy applications calls the old PWDENCRYPT() function to hash passwords
##MS_ServiceMasterKey## is the only record in master.sys.symmetric_keys, and every other database's sys.symmetric_keys table is empty
What, if anything, am I likely to lose if I ALTER SERVICE MASTER KEY FORCE REGENERATE? My understanding is that since we don't have any database master keys and aren't using encryption, there's no real potential for corruption or loss. However, I want to be a little more confident about this before I give it a go.
Pointers appreciated.
Hi,
Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?
I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.
=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value
Otherwise doesnt anyone know how to palce a row from the detail section into the footer?
Many thanks
Dave
In a decision tree algorithm, is there a known way to force a branch at a top level? For exmaple, I have 30 known decision patterns that are going to be completely different and I don't want them to intermingle. I wanted to force a branch at the top node on one of the 30 patterns so I wouldn't have to create 30 mining models per client.
Brian
Hi,
did anybody tri force password policy by modifing
sp_addlogin
and
sp_password
Raising custom error if password to be inserted or cnanged does not meet company policy.
Why microsoft did not add this condition in code?
Thank you
Alex
SQL Code:
Original
- SQL Code
SELECT acct.USERNAME,
SUM(trans.CHARGES) - SUM(trans.CREDITS) AS [Charges - Credits],
MAX(trans.ENDPERIOD) AS [Billed Through],
acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY,
bill.firstname, bill.lastname, bill.STREET1, bill.STREET2,
bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY,
acct.PHONE1, acct.PHONE2, bill.EMAIL,
acct.BILLPERIOD, acct.PLAN
FROM TRANS trans, ACCTS acct, BILLING bill
WHERE trans.ACCTNUM = acct.ACCTNUM
and bill.ACCTNUM = acct.ACCTNUM
and bill.ACCTNUM = trans.ACCTNUM
AND acct.CLOSED = 0
AND acct.SUSPENDED = 0
GROUP BY acct.USERNAME, acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY,
bill.firstname, bill.lastname, bill.STREET1, bill.STREET2,
bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY,
acct.PHONE1, acct.PHONE2, bill.EMAIL,
acct.BILLPERIOD, acct.PLAN
HAVING SUM(trans.CHARGES) - SUM(CREDITS) > 0
ORDER BY [Billed Through] DESC
SELECT acct.USERNAME, SUM(trans.CHARGES) - SUM(trans.CREDITS) AS [Charges - Credits], MAX(trans.ENDPERIOD) AS [Billed Through], acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLANFROM TRANS trans, ACCTS acct, BILLING billWHERE trans.ACCTNUM = acct.ACCTNUM AND bill.ACCTNUM = acct.ACCTNUM AND bill.ACCTNUM = trans.ACCTNUM AND acct.CLOSED = 0 AND acct.SUSPENDED = 0GROUP BY acct.USERNAME, acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLANHAVING SUM(trans.CHARGES) - SUM(CREDITS) > 0ORDER BY [Billed Through] DESC
Incorrect syntax near the keyword 'PLAN'.
If i take out SELECT & GROUP BY acct.plan, it works fine.
I've googled a bit and found 'EXPLAIN PLAN' command, I assume it's parsing the 'PLAN' as a command and screwing stuff up. I don't get why it'd take it for a command instead of a column. How does one select a keyword as a column name? Brackets & single quotes didn't do the trick.
Hello All,
Is it possible to force a delete of a table even when another program is using that DB, and still has some view data on that specific table.
I know that I can delete tables if another program is just have connection to the DB, but not using the specific table I'd like to delete. Can it be done also on a viewed table?
SQL 2005 Standard x64 Service Pack 2
Windows 2003 R2 X64 service pack 2
The principle, partner and witnesss have two NICs each (NIC1 and NIC2). I want them to communicate in NIC2 for sending logs and establishing quorum. This will happen in their own private network (say 192.168.1.0/24). The NIC1 in each server will be available for client communication. The domain and clients are in the network (say 10.1.1.0/24).
I am using the same domain account as SQL server service account in all three servers.
How can I do this?
Thanks
I have set the Interactive Height in my SQL report to 11 inches. I have also set a page break to occur after each table group in my report. When I run the report in SQL Reporting Services (or in Visual Studio 2005) the only page breaks that occur are the ones after each group. The Interactive Height setting is not causing page breaks. The first group in my report prints out as 4 pages but is showing up on the html screen as 1 long page. The first page break finally occurs at the end of the first group.
How can I get the Interactive Height to force page breaks within each of my groups? I am using SQL Reporting Services 2005.