|
Anne,
(I'm assuming SQL Server 2000.)
It looks like you're trying to insert and expand in the trigger. The body of the trigger has access to a table called "inserted" that contains the inserted values. Inserting is easy, but running your expansion is more difficult and poor form in a set-based language like SQL.
This does what you want:
<br />
create trigger mytrigger on tableone for insert as<br />
begin -- trigger<br />
<br />
declare<br />
@ptr int, -- your loop variable<br />
@code char -- what to insert each time<br />
<br />
-- probably can combine the next two lines if clever<br />
select @code = code from inserted<br />
select @ptr = numstart from inserted<br />
<br />
while @ptr <= (select numend from inserted)<br />
begin -- while<br />
<br />
-- consider a select .. into instead of next<br />
insert into tabletwo (num, code) values (@ptr, @code)<br />
select @ptr = @ptr + 1<br />
<br />
end -- while<br />
<br />
end -- trigger<br />
The motherhood-and-apple-pie stand on this, all else being equal, is that you should use neither a trigger nor the second table for this. It says that you should always avoid triggers unless there's no other solution (there is here), and you shouldn't store your data twice (table two simply repeats information you can find in table one). Instead, you should just store the start, end, and code, and craft a query that will give you a result set containing (1, A), (2, A), (3, A) that runs every time you want that result. I'm assuming you have a good business case for this though, like a performance enhancement or something.
Stephan
|
|
|
|
|
I have created a C# (.NET 2.0) database application working with SQL Server 2000. In my application I make use of the SQLDMO object of SQL Server. When i deploy my application, i have problems when it tries to load the SQLDMO object! And the application fails and also notifies me that the RPC service isn't available! With my application, i have also deployed ALL dependencies (including the Interop.SQLDMO.dll assembly)! Does anyone know what might be the problem here?
|
|
|
|
|
SQLDMO is ONLY loaded if you have installed the Sql Server tools on the computer.
In order to use SQLDMO on a desktop, you MUST have a license for it.
|
|
|
|
|
SQL Server is installed and licensed on the target machine! For that reason, i do not include the actual SQLDMO.dll file in the installation package. I only include the Interop.SQLDMO.dll assembly which is required to communicate with the SQLDMO Com object. And still, i'm having problems on the target machine!
|
|
|
|
|
I want to load a Datatable from a database in my application. While loading the data, i want to show
a progressbar.
How can i realize it ?
|
|
|
|
|
Hi,
I didn't tried this before, but I think one possible solution is to use the TableNewRow-event. This event will be fired every time a new row is inserted into the datatable. What you can do is to register for this event (at the datatable) and then within the handler you can update your progressbar.
Hope this helps.
Regards
Sebastian
|
|
|
|
|
Thank you for the Tip, but it doesn't work, because i created the database-application with the Designer and so, the datatable is private. I only have access the bindingsource- and the datatableadapter.
|
|
|
|
|
hi,
I am using SQLXML 3.0 SP3 to put the data from an XML file to Database tables.I have to create a document specifying the various bugs that can be reported while using SQLXML 3.0...I searched a lot on web but till now no proper information.
Could someone help me out in this regard..
thanx
punam
|
|
|
|
|
Hi ,
i have a key that migrate from a table reserver to an other table reservation
that mean i have a forieng key called N°Reser in the reservation table
well , now i want that this N°Reser displayed just once in the reservation table
i meant 1 reserver can do 1,1 reservation can you tell me how should i do that in sql ??
because the default relashion gave me 1,n
thank you very much
try to be good if you can't be the best
|
|
|
|
|
Mohammed Amine wrote: because the default relashion gave me 1,n
n can represent 0, 1 or many. If you want to force a one-to-one relationship then you can put a second relationship in place 1-to-many going in the opposite direction. If you have two tables with a one-to-many and a many-to-one between them they end up forcing a one-to-one. (Actually, not quite - You can have a zero on one side, i.e. one-to-zero)
I'm curious. Why do you need a foreign key? Surely the primary key of of both will be the same?
one-to-one relationships can also be symptomatic of two tables that ought to be one table. Have you explored this possibility?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
can you tell me please how can i attach a database to the server just with code ?? i use VS 2003
i have the two files Mdf & Ldf one & i want to attach them to the server just with code
thank you very much
try to be good if you can't be the best
|
|
|
|
|
same command which is used in T-SQL to attach DataBase use in ur application.
It's pretty easy
|
|
|
|
|
SQL Server Books Online
sp_attach_db
Attaches a database to a server.
Syntax
sp_attach_db [ @dbname = ] 'dbname'
, [ @filename1 = ] 'filename_n' [ ,...16 ]
Examples
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
well , thanks but i see that you used a stored procedure actually i didn't understand very well can you add some explanation please ??
thank you
try to be good if you can't be the best
|
|
|
|
|
using System.Data;
using System.Data.SqlClient;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=(local);Initial Catalog=master;Integrated Security=SSPI";
conn.Open();
SqlCommand myCommand = conn.CreateCommand();
myCommand.CommandText = "EXEC sp_attach_db @dbname = 'ATestDB',@filename1 = 'C:\\TestPath\\ATestDB_Data.MDF',@filename2 = 'C:\\TestPath\\ATestDB_Log.LDF'";
myCommand.ExecuteScalar();
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
thanks but what i don't understand is that how the vb.net will understand this ?? there is no procedure named like that in ma data base & he won't know it
can you explain from where you brought this procedure & how it works
thank you for your help but i want to understand but i can't because i have no procedure called like that
thank you for helping again 
try to be good if you can't be the best
|
|
|
|
|
sp_attach_db is a system stored procedure that is in the SQL Server master database.
If you really don't want to use it, you can use what is below (which is all the sp_attach_db does).
Also, I put this in vb.net - the last thing I posted was C#.
Imports System.Data
Imports System.Data.SqlClient
Dim conn As New SqlConnection
Dim myCommand As SqlCommand
conn.ConnectionString = "Data Source=(local);Initial Catalog=master;Integrated Security=SSPI"
conn.Open()
myCommand = conn.CreateCommand()
myCommand.CommandText = "CREATE DATABASE [ATestDB] ON (FILENAME = 'C:\TestPath\ATestDB_Data.MDF'), (FILENAME = 'C:\TestPath\ATestDB_Log.LDF') FOR ATTACH"
myCommand.ExecuteScalar()
Just change the name of ATestDB to whatever yours is, and the paths and filenames too.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
-- modified at 13:55 Thursday 1st June, 2006
|
|
|
|
|
Hi , thank you very very much this last one worked thank you i don't know without you what i would do thnx again
now if you permit i want to ask you some other things abt this
well , first when i create this database with the way you told me it work
but now if i already have the database attached i have to change ma code to :
conn.ConnectionString = "Data Source=(local);InitialCatalog=MyDataBase;Integrated Security=SSPI"
& i have to delete this 4 lines from code :
conn.Open()myCommand = conn.CreateCommand()myCommand.CommandText = "CREATE DATABASE [ATestDB] ON (FILENAME = 'C:\TestPath\ATestDB_Data.MDF'), (FILENAME = 'C:\TestPath\ATestDB_Log.LDF') FOR ATTACH"myCommand.ExecuteScalar()
like this i will just extract records from the database this is first
second thing i want to detect the name of the server automatically
like this :
conn.ConnectionString = "Data Source= <big>Here i have to write the name of the server but i don't know what is it name so how can i know without asking the user ?? </big>;InitialCatalog=MyDataBase;Integrated Security=SSPI"thank you very much for help
try to be good if you can't be the best
|
|
|
|
|
(local) will work if the server is on the local machine (the same machine the application is running on).
If it isn't local, there might be numerous instances of SQL Server running on the user's network. If you don't know the name of their server, how would you know which one to connect to without asking the user? You could present them with a dialog box that allows them to choose one of those available, then save what they chose somewhere (registry, file, whatever) so you don't have to ask them again. Or, make this a part of your installation process. There are various ways of handling this issue.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
hi
I need to know how can I code and decode password in Sql server 2005
(secure)
Thanks
dadax
|
|
|
|
|
Hi dadax!
Check my blog article at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. It describes how to do that using SQL Server's new encryption/decryption feature.
Regards,
Rainer.
Rainer Stropek
cubido business solutions gmbh
Email r.stropek@cubido.at
Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx
|
|
|
|
|
Can the Oralce ODP.Net driver be used with Reporting Services 2000? If so, how do you set up the connection? Thanks.
|
|
|
|
|
Hi buddies i have a relativly simple question
i whant to exececute a select statment in c++
but for the WHERE part of the statment i want to read it from a variable of type string
Let me be more precise.
string token;
token="SomeName"
pCommand->CommandText = "SELECT * FROM Table1 WHERE Name=?";//WHERE Name=token
Any ideas pls help
Thanks again
|
|
|
|
|
Like this
pCommand->CommandText = "SELECT * FROM Table1 WHERE Name='"
& token & "'" ;//WHERE Name=token
|
|
|
|
|
string strsql;
sprintf(strsql, "SELECT * FROM Table1 WHERE Name='%s'", token);
pCommand->CommandText =strsql;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
