|
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Bs_Proc_Serial'
AND type = 'P')
Drop Proc Bs_Proc_Serial
GO
Create Proc Bs_Proc_Serial
@TableName Char(25) ,
@ColumnSearch Char(25)
AS
/* G e t S e r i a l I d D y n a m i c */
Declare @Ex NVarChar(500)
Declare @Par NVarChar(100)
Set @Par= N'@Serial bigint'
Set @Ex = N'Select @Serial = Count(*) From ' + @TableName
Set @Ex = @Ex + N' while (@Serial < 100000000 )'
Set @Ex = @Ex + N' begin '
Set @Ex = @Ex + N' if Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= RTRIM(@Serial)) '
Set @Ex = @Ex + N' begin '
Set @Ex = @Ex + N' If (@serial < 10 ) '
Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) '
Set @Ex = @Ex + N' Break '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' If (@Serial < 100 ) '
Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) '
Set @Ex = @Ex + N' Break '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' If (@Serial < 1000) '
Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) '
Set @Ex = @Ex + N' Break '
Set @Ex = @Ex + N' End '
Set @Ex = @Ex + N' Set @Serial = @Serial + 1 '
Set @Ex = @Ex + N' End '
Set @Ex = @Ex + N' if (@serial < 10 ) '
Set @Ex = @Ex + N' Select ''00'' + Rtrim(@Serial) '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' if (@Serial < 100 ) '
Set @Ex = @Ex + N' Select ''0'' + Rtrim(@Serial) '
Set @Ex = @Ex + N' Else '
Set @Ex = @Ex + N' Select Rtrim(@Serial) '
-- Set @Ex = N'Select Count(*) From '
-- Set @Ex = @Ex + @TableName
Execute sp_executesql @Ex,@Par
Go
Exec Bs_Proc_Serial 'Ic_Category' ,'CategoryId'
sql server msg:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@Serial'.
what's problem?
123
|
|
|
|
|
Try adding:
PRINT @Ex
so that you can be sure of the SQL string that you are building up.
Regards
Andy
|
|
|
|
|
I think you should launch sp_executesql by providing a value for @serial
Ex :
Execute sp_executesql @Ex,@Par,@serial=0
But I don't get the point of your SP, maybe should @serial be an OUTPUT param ?
|
|
|
|
|
i am providing the code below
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds, "offensive_issue");
da.Fill(ds, "crime_mgmt");
|
|
|
|
|
JOIN the tables in your database query.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hai all,
i had one column Emp_ID,location,.... in view(vw_Employee) and
one colmn Qurry_ID,location,Emp_ID in table(tbl_querries)
i just want to know,
how to find the count(Emp_ID) and count(Qurry_ID) by using innerjoin depending on distinct location
thanks in advance
Thanks
Subbu.
|
|
|
|
|
This is the syntax for doing a count of distinct records from a table:
SELECT COUNT(DISTINCT column(s)) FROM table
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
The background
I'm planning on setting up a secondary database at work which will act as a view only platform for our accounting system.
The reason behind this is that we have 32 licenses for our account system and by using replication I can create another database allowing view only permissions - hence doubling our licenses. In effect I will be creating a new accounting system which will be a replication of our live system(updated every hour).
I've done a fair bit of reading from both Microsoftt, Wrox "Professional SQL Server 2000 Programming" book,idiots guide to sql server etc...
However I still want something based a bit more on experience before I start setting up replication on my business server.
Replication tends to get presented in such a dry and theoretical manner!
Can anyone recommend a good link/article on the internet for me?
Or is the answer just to dive in and learn from practice?
Regards
Guy
-- modified at 4:44 Saturday 17th November, 2007
You always pass failure on the way to success.
|
|
|
|
|
|
Thank you for the link. 
My plan is to have the Publisher/Distributor and Subscriber on the same server.
We are doing things on the cheap here.
In your experience will this cause a marked slowdown in the the machine?
Thanks again
Guy
You always pass failure on the way to success.
|
|
|
|
|
i want to know how can i delete (or disable) all users (dbo, guest, etc.) from my database and set one user for my database.
in fact i don't want other users could to connect to mydatabase in any sql server.
|
|
|
|
|
hi
Use enter prise Manager To remove Users ..Follow these steps
1)To remove a user account from a database role
2)Expand a server group, and then expand a server.
3)Expand Databases, and then expand the database in which the role exists.
4)Click Roles.
5)In the details pane, right-click the role to which the user account belongs, and then click Properties.
6)Select the user to remove, and then click Remove.
|
|
|
|
|
thank you,
so i will remove all users (dbo, ..) from my database and set one user to it and detach it from sql server, now i have a mdf file that just one user could connect to it. let me know in any machine with sql server express just my user could connect to it and others (dbo, ...) could not connect to my database?
in fact i don't want any user from any machine could connect or view my database.
|
|
|
|
|
hi all,
if u create view from single table it will allow updating view
,but if u create view from two tables it is not allowing to
update view .... can any one explain this .....?
suman
|
|
|
|
|
This makes sense for many reasons:
1-What if the view does not contain the PKs of one or both of the tables?
2-What if the join returns the same record more than once (as when joining master and detail tables)?
For more information check: Modifying Data Through a View[^]
|
|
|
|
|
Hi,
can anyboby tell how to write triggers as my req is if a row is inserted in table1 the same row has to be inserted on table2..
regards
padma
padma
|
|
|
|
|
Have you read the documentation? What code have you written so far?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi
till now i wrote
CREATE TRIGGER trginsert
AFTER INSERT ON table1
FOR EACH ROW
INSERT INTO table2(unit,id) values('888','999');
i will get data through a device whenever a record is inserted into the master table the same record has to be added into another table
this is my req hope i was clear
padma
|
|
|
|
|
When a trigger fires for an insert/update, the new data can be accessed through a logical table named inserted.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
This KB article[^] has given me a good starting point except it is ADO.NET with managed C++. Would anyone know of an article that is ADO with native C++ and would connect to SQL Server 2005 Express Edition. Thanks.
|
|
|
|
|
hi all, the Bolow Query find me Records that have Duplicates,
select Property_ID,Lis_key from Property_Private
group by Property_ID,Lis_key
Having count(Property_ID) = 2
order by Property_ID .
Now Property_ID is not a Primary_key, in this table, i have created another Field and named it "Primary_key", that will be an identity Column and a Primary key. So from the above Query i want to delete only the record that have been found by this query.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
|
Hi,
What would be the best way of making a transaction from a aspx webpage to the SQL database for example,
where a customer orders a product, hits the order button and then this transaction goes through to the database where it automatically updates the records. Any ideas?
cheers
|
|
|
|
|
Your button handler will be responsible for reading the data from the page. A separate layer will contain code which will map to an insert into a database (this insert will be done through a stored procedure). In the case of an update, use an update statement instead of an insert.
|
|
|
|
|
hi , first i would thank you for help
i have a column in table contains a code for students
the code format is 11-1-07-0001
what i want , i want to replace the first tow digits from 11 to 01
without any efection to the other characters
MD_NADA
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
