Attach db without transaction log file

Hi!
Is it possible attach db without transaction log file?
With store procedure like sp_attach_db and sp_attach_single_file_db it isn't go. Server still looking for transaction log file.
Thanks your helpHi,
You can use sp_attach_single_file_db only on databases that have a single
log file.
Instead try to update the database status to emergency mode and create a new
database and use DTS to transafer all objects to new database.
Steps:
1. Update the database to Emergency mode
update sysdatabases set status=32768 where name=<name of the database>
2. Craete a new database
3. Use DTS to transfer all objects
Thanks
Hari
MCDBA
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||Try this:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly
SQL Server MVP
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||You must first: sp_detach_db
then: delete the ..._log.sfd
then: sp_attach_single_file_db
Bye
Uwe

Asynchronous operation

What I am looking to do is have a stored procedure begin a dialog with my request service.
With that dialog established my stored procedure sends 50 request messages, one for each of the 50 of the United States. I want these to be processed asynchronously by a procedure that is called on activation for the request queue. In that activation procedure the request is processed against the respective state and a response message is sent to the response service (to the response queue). I want to be able to tie these request messages and response messages together with some type of shared identifier. These requests don't need to be processed in any specific order and don't need any fancy locking mechanism via conversation group since these requests require to be processed asynchronously. What is the best approach? Do I need to create 50 seperate queues and open dialogs with each? If this is the route to take, would this be a performance hit?

My goal is to have all 50 states process all at once, each finishing with a response message sent to the response queue. The initiating procedure, after sending these 50 requests, would then spin and wait for all 50 to complete, be it a response, error, or timeout. When all 50 have returned, the procedure would then merge the results and return. So as you can see in my scenario, I dont care when a state is complete as they do not affect the outcome, nor do they access any of the same resources when being processed.Interesting. Are you running into a problem that the 50 messages are processing synchronously, rather than asynchronously? If so, I believe it is because they are part of the same conv. group, and an activation proc. will only fire more activation instances for different conversation groups. Does that make sense?
Tim|||Yes, that is exactly what is happening. All of the messages are part of the same conversation group, therefore, they are processing synchronously as you've mentioned. With that in mind, do I then open new conversations for each of the 50 states? Will opening so many conversations have an adverse effect on performance? I still need to group these messages together somehow. In a way, the only possible solution I see is creating 50 individual request queues and sending messages to each within the same conversation group... Does this sound reasonable?|||Would it be possible for you to create two conversation groups, and send 25 through each group?

Asynchronous ADO procedure invocation?

Is there any way to send several ADO Command.Execute asynchronously at once?
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeIn the following example, an event handler has been implemented to print to
the Debug window when the command has completed:
Dim WithEvents conn As ADODB.Connection
Sub Form_Load()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql70server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set cmd.ActiveConnection = conn
cmd.Execute "select * from authors", , adAsyncExecute
Debug.Print "Command Execution Started."
End Sub
Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)
Debug.Print "Completed Executing the Command."
End Sub
Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
> Is there any way to send several ADO Command.Execute asynchronously at
> once?
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Thanks for your answer.
However, I found that the secondary asynchronous Execute fails while the
first asynchronous Execute is not completed yet. Is this phenomenon
ordinary? Or is there any way to enable more overlapped Execute be allowed?
Thanks.
Regards,
Hyun-jik Bae
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In the following example, an event handler has been implemented to print
> to
> the Debug window when the command has completed:
> Dim WithEvents conn As ADODB.Connection
> Sub Form_Load()
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Set conn = New ADODB.Connection
> conn.ConnectionString = _
> "Provider=SQLOLEDB;Data Source=sql70server;" _
> & "User ID=sa;Password='';Initial Catalog=pubs"
> conn.Open
> Set cmd.ActiveConnection = conn
> cmd.Execute "select * from authors", , adAsyncExecute
> Debug.Print "Command Execution Started."
> End Sub
> Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
> pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
> pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
> ByVal pConnection As ADODB.Connection)
> Debug.Print "Completed Executing the Command."
> End Sub
> Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
> news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
>|||There should be the possibility to do this unless you will not use the same
connection for it.
Jens Suessmeyer.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:uCgnSElRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Thanks for your answer.
> However, I found that the secondary asynchronous Execute fails while the
> first asynchronous Execute is not completed yet. Is this phenomenon
> ordinary? Or is there any way to enable more overlapped Execute be
> allowed?
> Thanks.
> Regards,
> Hyun-jik Bae
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>

Asynchronous ADO Command execute

I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,
ThanksIt also happens inconsistently. Sometimes, it just runs fine and sometimes, it just gives a Program Error and everything blows out.|||Originally posted by archnam
I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,

Thanks

Have you looked in the SQL Server error log in enterprise manager?|||Where exactly are error logs in the SQL Server under Enterprise Manager?
Also, if I put a breakpoint in my program, it seems to run fine and when I just run it, it bombs, do you think may be I should put some delay in it. The Command.Execute might be taking some time to run.

Asynch call DTS or Stored procedure 1.1

Hi,

I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT

I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.

Those values are not needed immediately. The calculation takes between 20 or 30 minutes.

Do you have any idea how to do it ?

Thanks

The easiest way I can think of to do it is to have a daemon sitting on the server. Tickle the daemon (either via a port, web service, or table on the DB that the daemon watches), and it'll fire off the stored procedure and keep the connection open until the SP completes.|||In this case I have to put a connection timeout to more than 5 minutes ... It's not a good solution neither|||

Valvert:

I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT

I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.

Hello, i'm not a DBA expert and have never tried this, but i guess you could define a job to be started manually; then start the job withsp_start_job and check for termination withsp_help_job.

HTH. -LV

AsyncExecute problem

Hi there!
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:

mdbConnection->BeginTrans();
_CommandPtr cmd;
cmd.CreateInstance( __uuidof( Command ) );
cmd->ActiveConnection = mdbConnection;
cmd->CommandType = adCmdStoredProc;
cmd->CommandText = _bstr_t( SP_DDPOM );
cmd->Parameters->Append( cmd->CreateParameter( "@.0", adVarChar,
adParamInput, 10, _bstr_t( dateod ) ) );
cmd->Parameters->Append( cmd->CreateParameter( "@.1", adVarChar,
adParamInput, 10, _bstr_t( datedo ) ) );
cmd->Execute( &vtEmpty, &vtEmpty2, adAsyncExecute );
int state;
while((state=cmd->State) & adStateExecuting) {
TRACE("State = %ld\n", state );
// if cancel then mdb->RollbackTrans(); return;
}
mdbConnection->CommitTrans();

The thing is that the state is adStateExecuting for about 16 seconds
(30days interval), and afterwards drops to 0 (adStateClosed), while the
sp is still executing (which usually takes about a minute). The process
waits at the CommitTrans() until the sp is done. There aren't any errors
reported or exceptions thrown.

BUT: NO DATA IS FILLED IN THE TABLE.

I've tested that begin-commit works by adding a call to another sp, and
it works fine.

The weird thing is that the sp works if the execution takes less then
this 16 or so second limit. E.g. for 3days interval, it works fine.

The weirdest thing is that the whole thing worked at first, i.e. did not
produce erroneous state and filled the table, and that for 5 minutes
execution and more. Then I changed something in the sp (commented out a
single line!!), and ever since then it doesn't work anymore. I've got
the database backup from when it worked, but doesn't anymore.

The sp works fine when executed from Query Analyzer.

Has anyone got any ideas? Am I missing something?
Thanks,
Juhu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!juhu (juhu@.bruhu.com) writes:
> The thing is that the state is adStateExecuting for about 16 seconds
> (30days interval), and afterwards drops to 0 (adStateClosed), while the
> sp is still executing (which usually takes about a minute). The process
> waits at the CommitTrans() until the sp is done. There aren't any errors
> reported or exceptions thrown.

I don't have any experience of asynchronous queries. But it could be
that the command timeout strikes. This timeout is by default 30 seconds.
Set it to 0 to get rid of it:

cmd->CommandTimeout = 0;

My first thought was that you have a pseudo-recordset in form of a
rowcount from an INSERT into a temp table. But since it works with
smaller amount of data it may not be that. Nevertheless, add a SET
NOCOUNT ON in the stored procedure if you don't have one.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

ASYNC_NETWORK_IO issue

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.

Thanks
-Ashis

Hi, Ashis,

Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.

Thanks,

Zhiqiang Feng

ASYNC_NETWORK_IO

Hello, When I execute a SQL SERVER 2005 stored procedure from Visual Basic 6 Application, I get my process locked with ASYNC_NETWORK_IO wait type.

When I Execute the same procedure from sql server console, It doesn't occur...

This is the visual basic code:

With objcommand

.ActiveConnection = CnnConexionPpalDb
.CommandTimeout = 0
.CommandText = "EJECUTIN"
.CommandType = adCmdStoredProc

'- Parametros del Command

'.Parameters("@.StrOrdenEstablecido").Value = StrOrdenEstablecido

End With
Set gTbl.VisOrdenamientoDatosFinalCiudades = objcommand.Execute

With gTbl.VisOrdenamientoDatosFinalCiudades

This is SQL SERVER CODE:

ALTER PROCEDURE [dbo].PCN_CONSULTAR_VISORDENAMIENTODATOSFINALCIUDADES_ORDENADO] @.StrOrdenEstablecido varchar(200)
as
BEGIN TRAN
DECLARE @.lvaSentencia NVARCHAR(4000)

SELECT dbo.Tbl_DatosSubidos.Direccion, dbo.Tbl_DatosSubidos.Ciudad, dbo.Tbl_DatosSubidos.Departamento,
dbo.Tbl_DatosSubidos.Inserto, dbo.Tbl_DatosSubidos.IndiceOLlaveUnica, dbo.Tbl_DatosSubidos.Identificacion,
dbo.Tbl_DetalleCiudadesBuenas.IdArchivo, dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes,
dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytesFinalDato, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Dep_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dep_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.DatoIngresado, dbo.Tbl_DetalleCiudadesBuenas.Dis_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dis_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.Doc_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Doc_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Cli_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Cli_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.DCB_Novedad,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Georreferenciada, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Prioridad,
dbo.Tbl_DetalleCiudadesBuenas.Geo_CodigoZona, dbo.Tbl_DetalleCiudadesBuenas.DatoCiudadCompleta,
dbo.Tbl_DetalleCiudadesBuenas.Tipo_Distribucion, dbo.Tbl_DatosSubidos.Factura, dbo.Tbl_DatosSubidos.Nombre,
dbo.Tbl_ArchivosSubidos.Ars_NombreArchivoSubido AS NombreArchivo
FROM dbo.Tbl_DatosSubidos WITH (NOLOCK) INNER JOIN
dbo.Tbl_ArchivosSubidos WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_ArchivosSubidos.Ars_Codigo INNER JOIN
dbo.Tbl_DetalleCiudadesBuenas WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_DetalleCiudadesBuenas.IdArchivo AND
dbo.Tbl_DatosSubidos.PosicionEnBytes = dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes

ORDER BY 1
COMMIT

This wait type means the server is busy sending rows to the client fo processing. The best way to fix it is to reduce the number of rows returned. Alternatively, client-side processing should be accelerated. Or both.

-Ryan / Kardax

Associating a Windows account with a user

Hi,
I need to associate an existing Windows account with an existing SQL Server
2000 user, but have not found the stored procedure for this.
Can somebody guide me on this please?
Thanks in advance,
Juan Dent, M.Sc.Is this the case of an orphanned user, due to moving the db from one domain
to another or something? There is a procedure called sp_change_users_login,
but that will not work with Windows accounts.
Can you simply grant access to that Windows login to SQL Server using
sp_grantlogin, and add associated user in the database using
sp_grantdbaccess? You'll have to apply the permissions manually though.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Hi,
> I need to associate an existing Windows account with an existing SQL
Server 2000 user, but have not found the stored procedure for this.
> Can somebody guide me on this please?
> --
> Thanks in advance,
> Juan Dent, M.Sc.|||Actually what I have is a database with three users but only one of them is
associated with a Windows account.
Looking in SQLSErver Enterprise Manager, in the Users folder of the database
, it looks something like this:
dbo DENTDEVELOPMENT\JuanDent
isadmin
isuser
See? What I want is to associate the login above with isadmin as well as dbo
.
Is that possible?
Thanks in advance,
Juan Dent, M.Sc.
"Narayana Vyas Kondreddi" wrote:

> Is this the case of an orphanned user, due to moving the db from one domai
n
> to another or something? There is a procedure called sp_change_users_login
,
> but that will not work with Windows accounts.
> Can you simply grant access to that Windows login to SQL Server using
> sp_grantlogin, and add associated user in the database using
> sp_grantdbaccess? You'll have to apply the permissions manually though.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
> news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Server 2000 user, but have not found the stored procedure for this.
>
>|||No actually what I have, as seen in SQLSERVER Enterprise Manager in the User
's folder is:
dbo DENTDEVELOPMENT\JuanDent
isadmin
isuser
And I want to associate the above login with both isadmin and dbo users.
Is that possible?
Thanks in advance,
Juan Dent, M.Sc.
"Narayana Vyas Kondreddi" wrote:

> Is this the case of an orphanned user, due to moving the db from one domai
n
> to another or something? There is a procedure called sp_change_users_login
,
> but that will not work with Windows accounts.
> Can you simply grant access to that Windows login to SQL Server using
> sp_grantlogin, and add associated user in the database using
> sp_grantdbaccess? You'll have to apply the permissions manually though.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
> news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Server 2000 user, but have not found the stored procedure for this.
>
>

Assisted Editor

I have a book that refers to the "SQL Server Assisted Editor" in management
Studio. but when I right click on the Stored Procedure node or anything
else I can't get the Assisted Editor. Where is it?
Regards,
Gary Blakely
GaryDean (GaryDean@.newsgroups.nospam) writes:
> I have a book that refers to the "SQL Server Assisted Editor" in
> management Studio. but when I right click on the Stored Procedure node
> or anything else I can't get the Assisted Editor. Where is it?
It was in the betas of Mgmt Studio, but it was dropped along the way. In
my opinion, it was just as well, since it did really add that much. One
shortcoming as I recall was that you could only add a new parameter at
the end of the parameter lsit.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:eEoY8jMFHHA.3776@.TK2MSFTNGP02.phx.gbl...
>I have a book that refers to the "SQL Server Assisted Editor" in management
>Studio. but when I right click on the Stored Procedure node or anything
>else I can't get the Assisted Editor. Where is it?
> --
> Regards,
> Gary Blakely
>
|||Hi Gary,
Please notice that I'v replied to the same issue in
microsoft.public.sqlserver.server newsgroup. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Assistance with Stored Procedure and ASPX Page Needed

Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind?

Stored Procedure

CREATE PROCEDURE InsertNearMiss @.Branch Int, @.Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@.Branch)

SET @.Identity = SCOPE_IDENTITY()

GO

ASPX Page

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NearMissConnectionString%>" InsertCommand="InsertRecord"InsertCommandType="StoredProcedure"SelectCommand="InsertRecord" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameterControlID="TextBox1"Name="Branch"PropertyName="Text"Type="Int32"/> <asp:ControlParameterControlID="TextBox2"Direction="InputOutput"Name="Identity"PropertyName="Text"Type="Int32"/> </SelectParameters> <InsertParameters> <asp:ParameterName="Branch"Type="Int32"/> <asp:ParameterDirection="InputOutput"Name="Identity"Type="Int32"/> </InsertParameters> </asp:SqlDataSource> <asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

Here's all about getting the value of the most recently added record:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

SqlDataSource options are about 3/4 of the way down.

|||

It appears you are only doing an insert, so you can get rid of the whole <selectParameters> collection. In this case, I would create a handler for onInserted and have the handler insert the output value from the procedure into the textbox 2 box. To fire the event, put a button that has an onclick event handler that calls SqlDataSource1.Insert(). This fires the insert command of the datasource. Below is an example page and codebehind

12 id="testSource"3runat="server"4InsertCommand="usp_ins_test"5InsertCommandType="StoredProcedure"6ConnectionString='<%$ ConnectionStrings:test %>'7OnInserted="testSource_Inserted">89"TextBox1" Name="prm_b" Direction="Input" Type="int32" PropertyName="Text">10"prm_r" Direction="Output" Type="Int32">111213branch: "TextBox1" runat="server">
14returnedvalue: "TextBox2" runat="server">15"btnSubmit" runat="server" Text="Add New Branch" OnClick="btnSubmit_Click"/>

 Code Behind:

 
protected void btnSubmit_Click(object sender, EventArgs e) {if (Page.IsValid) { testSource.Insert(); } }protected void testSource_Inserted(object sender, SqlDataSourceStatusEventArgs e) {if (e.Exception !=null)//display an error message to the screen e.ExceptionHandled =true;else TextBox2.Text = e.Command.Parameters["@.prm_r"].Value.ToString(); }
 

That should do it.

--D

Assistance with Stored Procedure

I am running SQL Server 7.0 and using a web interface. I would like
for a user to be able to input multiple values into a single field
with some sort of delimiter (such as a comma). I want to pass this
field into a Stored Procedure and have the stored procedure use the
data to generate the resutls.

Example:

Web page would ask for ID number into a field called IDNum. User
could input one or many ID numbers separated by a comma or some other
delemiter - could even be just a space (113, 114, 145).

SQL statement in Stored Procedure is something like this:

Select * from tblEmployess where IDNumber = @.IDNum

I need the SQL statement to somehow use an "or" or a "loop" to get all
of the numbers passed and use the delimiter to distinguish when the
"loop" stops.

I obtained a module from a friend that allows me to do this in access,
but have recently converted everything to SQL server and web
interface. Now, everyone in the office expects to be able to
accomplish the same results via the web.

Any help is appreciated. If you need any additional information to
provide me some assistance, please email me at
tod.thames@.nc.ngb.army.mil.

Thanks in advance.

TodTake a look at http://www.algonet.se/~sommar/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod|||Everytime I try to get to the website you refrenced, I get TCP_ERROR.
Some sort of communication problem. Is there any other sites that have
the same sort of information?

Thanks for the response,

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know if the content is mirrored elsewhere. The author, Erland
Sommarskog, frequents this newsgroup so maybe he'll jump in.

BTW, I don't have any problems accessing the site.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:3f803253$0$195$75868355@.news.frii.net...
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that
have
> the same sort of information?
> Thanks for the response,
> Tod
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||[posted and mailed]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that have
> the same sort of information?

Too bad. If you have the complete error message, I'm interested. I'm
inclined to suspect that this might be some firewall problem at your
side, but I might get carried away of the .mil in your address.

Anyway, here is an excerpt of the part which is most relevant to
you. If you want to read the entire article, just drop me a line.

An Extravagant List-of-integers Procedure

The technique in the previous section can of course be applied to a list
of integers as well, so what comes here is not a true port of the
iter_intlist_to_table function, but a version that goes head over heels
to validate that the list items are valid numbers to avoid a conversion
error. And to be extra ambitious, the procedure permits for signed
numbers such as +98 or -83. If a list item is not a legal number, the
procedure produces a warning. The procedure fills in a temp table that
has a listpos column; this column will show a gap if there is an illegal
item in the input.

CREATE PROCEDURE intlist_to_table_sp @.list ntext AS

DECLARE @.pos int,
@.textpos int,
@.listpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @.textpos = 1, @.listpos = 1, @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SELECT @.chunklen = 4000 - datalength(@.leftover) / 2
SELECT @.tmpstr = ltrim(@.leftover + substring(@.list, @.textpos, @.chunklen))
SELECT @.textpos = @.textpos + @.chunklen

SELECT @.pos = charindex(' ', @.tmpstr)
WHILE @.pos > 0
BEGIN
SELECT @.str = rtrim(ltrim(substring(@.tmpstr, 1, @.pos - 1)))
EXEC insert_str_to_number @.str, @.listpos
SELECT @.listpos = @.listpos + 1
SELECT @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SELECT @.pos = charindex(' ', @.tmpstr)
END

SELECT @.leftover = @.tmpstr
END

IF ltrim(rtrim(@.leftover)) <> ''
EXEC insert_str_to_number @.leftover, @.listpos
go

-- This is a sub-procedure to intlist_to_table_sp
CREATE PROCEDURE insert_str_to_number @.str nvarchar(200),
@.listpos int AS

DECLARE @.number int,
@.orgstr nvarchar(200),
@.sign smallint,
@.decimal decimal(10, 0)

SELECT @.orgstr = @.str

IF substring(@.str, 1, 1) IN ('-', '+')
BEGIN
SELECT @.sign = CASE substring(@.str, 1, 1)
WHEN '-' THEN -1
WHEN '+' THEN 1
END
SELECT @.str = substring(@.str, 2, len(@.str))
END
ELSE
SELECT @.sign = 1

IF @.str LIKE '%[0-9]%' AND @.str NOT LIKE '%[^0-9]%'
BEGIN
IF len(@.str) <= 9
SELECT @.number = convert(int, @.str)
ELSE IF len(@.str) = 10
BEGIN
SELECT @.decimal = convert(decimal(10, 0), @.str)
IF @.decimal <= convert(int, 0x7FFFFFFF)
SELECT @.number = @.decimal
END
END

IF @.number IS NOT NULL
INSERT #numbers (listpos, number) VALUES (@.listpos, @.sign * @.number)
ELSE
RAISERROR('Warning: at position %d, the string "%s" is not an legal integer',
10, -1, @.listpos, @.orgstr)
go

Here is how you would use it:

CREATE PROCEDURE get_product_names_iterproc @.ids varchar(50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_table_sp @.ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_names_iterproc '9 12 27 37'

The validation of the list item is in the sub-procedure
insert_str_to_number. For many purposes it would be sufficient to have
the test

@.str NOT LIKE '%[^0-9]%' AND len(@.str) BETWEEN 1 AND 9

which checks that @.str only contain digits and is at most nine digits
long (that is, you disapprove ten-digit numbers as well as signed
numbers).

You might guess that there is a performance cost for this extravaganza,
and indeed the procedure needs about 50% more time than the corresponding
function. Still, for many situations, the execution time is acceptable.

One note about the warning produced with RAISERROR: with ADO, this
warning may be difficult or impossible to detect on client level. If you
change the severity from 10 to 11, it will be an error, and raise an
error in your client code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thanks for the response - it's a little above my abilities, but I plan
on studying it and trying to make it work for me project.

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See if the following link helps..
http://tinyurl.com/6iil

--
-- Anith|||Tod,

Try this:

create procedure ListEmployees
@.IDNum char(1024)
as begin
set @.IDNum = ' ' + replace(@.IDNum, ',', ' ') + ' '
select *
from tblEmployess
where @.IDNum like ('% ' + ltrim(str(IDNumber)) + ' %')
end

Shervin

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod

Assistance with Stored Procedure

I currently have a sql statement that works great. I want to convert it
to a stored procedure so I can generate results from a webpage. Below
is the stored procedure that is working fine.

select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
[tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
SMOSC=(case [tblSTAP Info].[SMOS Considered]
when "1" then "Yes"
else "No"
end),
FIRSTSGTC =(case [tblSTAP Info].[1SG]
when "1" then "Yes"
else "No"
end),
CSMC=(case [tblSTAP Info].[CSM]
when "1" then "Yes"
else "No"
end),
tblPersonnel.*, [tblSTAP Info].*
FROM SIDPERS_PERS_UNIT_TBL
INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
(tblPersonnel.PAY_GR = 'E5')
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PAY_GR
when "E4" then 350
when "E5" then 400
when "E6" then 450
when "E7" then 500
when "E8" then 600
else 0
end))
AND [tblSTAP Info].NotConsidered = 0
ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NAME_IND;

I would like the 3 items under the where clause to recieve a variable
from the website:

(SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))

(tblPersonnel.PAY_GR = 'E5')

(SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))

Everytime I try to make this a stored procedure and try to pass multiple
values in the PMOS field, I get an error stating too many variables.

If anyone can tell me what the Stored Procedure should look like AND
what the ASP should look like to pass the variables, I would be much
obliged.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it![posted and mailed, please reply in news]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> I currently have a sql statement that works great. I want to convert it
> to a stored procedure so I can generate results from a webpage. Below
> is the stored procedure that is working fine.
> select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
> SIDPERS_PERS_UNIT_TBL.UNAME,
> SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
> [tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
> SMOSC=(case [tblSTAP Info].[SMOS Considered]
> when "1" then "Yes"
> else "No"
> end),
> FIRSTSGTC =(case [tblSTAP Info].[1SG]
> when "1" then "Yes"
> else "No"
> end),
> CSMC=(case [tblSTAP Info].[CSM]
> when "1" then "Yes"
> else "No"
> end),
> tblPersonnel.*, [tblSTAP Info].*
> FROM SIDPERS_PERS_UNIT_TBL
> INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
> tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
> ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
> WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
> (tblPersonnel.PAY_GR = 'E5')
> and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
> and ([tblSTAP Info].TotalPoints >=
> (case tblPersonnel.PAY_GR
> when "E4" then 350
> when "E5" then 400
> when "E6" then 450
> when "E7" then 500
> when "E8" then 600
> else 0
> end))
> AND [tblSTAP Info].NotConsidered = 0
> ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
> tblPersonnel.NAME_IND;
> I would like the 3 items under the where clause to recieve a variable
> from the website:
> (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))
> (tblPersonnel.PAY_GR = 'E5')
> (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
>
> Everytime I try to make this a stored procedure and try to pass multiple
> values in the PMOS field, I get an error stating too many variables.
> If anyone can tell me what the Stored Procedure should look like AND
> what the ASP should look like to pass the variables, I would be much
> obliged.

The SP would look like this:

CREATE PROCEDURE TodTahems @.rpt_seq_code_pattern varchar(25),
@.pay_gr char(2),
@.pmos text
select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
...
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
JOIN iter_charlist_to_table(@.pmos) AS pmos ON
SUBSTRING (tblPersonnel.PMOS,1,3) = pmos.str
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE @.rpt_seq_code) and
(tblPersonnel.PAY_GR = @.paygr)
...

The function iter_charlist_to_table unpacks a comma-separated list
into a table. You find the code here:
http://www.sommarskog.se/arrays-in-...list-of-strings

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I need a little more assistance. I did a copy and paste of the
"char_to_table_sp" to create the procedure in my DB. I followed the
examples in you email.

I have everything working to push the variables from the asp page to the
stored procedure. The pages work fine when I only put in one value,
however it doesn't work when I input more than one value.

The information below is provided.

standinglist2_test 'AAA_', 'E5', '71L, 75H'

doesn't return any values.

standinglist2_test 'AAA_', 'E5', '71L'

returns several rows.

Here is the SP I created.

CREATE procedure standinglist2_test
@.rsc varchar(4),
@.paygr varchar(3),
@.mos varchar (5)
as
CREATE TABLE #strings (str nchar (20) NOT NULL)
EXEC charlist_to_table_sp @.mos

select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
[tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
SMOSC=(case [tblSTAP Info].[SMOS Considered]
when "1" then "Yes"
else "No"
end),
FIRSTSGTC =(case [tblSTAP Info].[1SG]
when "1" then "Yes"
else "No"
end),
CSMC=(case [tblSTAP Info].[CSM]
when "1" then "Yes"
else "No"
end),
tblPersonnel.*, [tblSTAP Info].*
FROM
#strings s INNER JOIN
SIDPERS_PERS_UNIT_TBL INNER JOIN
tblPersonnel INNER JOIN
[tblSTAP Info] ON
tblPersonnel.SSN_SM = [tblSTAP Info].SSN
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
ON (SUBSTRING(tblPersonnel.PMOS,1,3) = s.str)
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE (@.rsc)) and
(tblPersonnel.PAY_GR = @.paygr)
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@.mos))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PAY_GR
when "E4" then 350
when "E5" then 400
when "E6" then 450
when "E7" then 500
when "E8" then 600
else 0
end))
AND [tblSTAP Info].NotConsidered = 0
ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NAME_IND;

Your help is really appreciated. If you need any other information to
assist, please let me know.

I am unable to access the website you reference in your first response
from my office. I had to wait until i got home to try it. Must be a
firewall issue.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Further information below:

I am using SQL 7, so I went to the SQL Server 7 link on your site. I
used the List-of-string Procedure to try and make it work as opposed to
information below.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> The information below is provided.
> standinglist2_test 'AAA_', 'E5', '71L, 75H'
> doesn't return any values.

There is a very simple explanation:

> CREATE procedure standinglist2_test
> @.rsc varchar(4),
> @.paygr varchar(3),
> @.mos varchar (5) <------

Change the declaration of @.mos to varchar(8000) or to text, to avoid
truncation issues.

> I am unable to access the website you reference in your first response
> from my office. I had to wait until i got home to try it. Must be a
> firewall issue.

I registered the domain in the beginning of December, so it could be
slow propagation somewhere. You could also try with
http://www.algonet.se/~sommar, which is the same site, but a less
pretty URL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried changing this:

> @.mos varchar (5) <------

to

@.mos varchar (8000)

I had the same problem. When one variable is sent, it works fine, but
when several are sent, it returns no rows.

So, I tried changing it to:

@.mos text

and received this error:

Server: Msg 8114, Level 16, State 1, Line 1
Error converting data type text to ntext.
Server: Msg 306, Level 16, State 1, Procedure standinglist2_test, Line 9
The text, ntext, and image data types cannot be used in the WHERE,
HAVING, or ON clause, except with the LIKE or IS NULL predicates.

I think I am very close to getting this resolved. Does anyone else have
any ideas?

I tried the link you provided in your last post and still couldn't get
to the site. I think it must be the firewall here.

Tod Thames

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (anonymous@.devdex.com) writes:
> I had the same problem. When one variable is sent, it works fine, but
> when several are sent, it returns no rows.

I went back to the stored procedure, and there are more problems:

and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@.mos))

You need to remove this condition.

If there are further problems, I would recommend that you do some
debugging on your own. First thing is to add a "SELECT * FROM #strings"
to see that the table is correct. Next is to remove condition, until
rows starts to pop up. That's probably a more effective way than asking
for help and wait for someone to come by in the newsgroups.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks so much for the assistance. It worked after I took that last
statement out of the SP. I actually tried some debugging, but I am not
very proficient at it. I did the "select * from #strings", but received
this message.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#stings'.

I couldn't figure out how to get the results from a temporary table.
Since I couldn't get the results from the table that is populated, I
didn't really know where to go from there.

Anyway, it is working now and I thank you very much. That sp you wrote
amazes me.

Tod Thames

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (anonymous@.devdex.com) writes:
> Thanks so much for the assistance. It worked after I took that last
> statement out of the SP. I actually tried some debugging, but I am not
> very proficient at it. I did the "select * from #strings", but received
> this message.
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#stings'.

Judging from the error message, you mispelled the table name. But that
may of course been a type when you posted.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

assigning Select results to local vars in SP

Hi. I'd like to assign the results of a select statement to a local
variables in my stored procedure. My intent is something like this:
SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
only, some how I'd like to get the field2,field3 into variables. Can
this be done?
Thanks in advanceFirst of all, do not use TOP without using ORDER BY, unless selecting
somewhat random results is required (which I gues is not).
Other than that, this is the way to go:
select @.variable_name = owner.table.colum
from owner.table
where (owner.table.another_column = @.parameter)
Don't forget to look up using local variables in Books Online.
ML|||Johnny,
Something like this:
USE Pubs
GO
CREATE PROC TESTPROC
@.AID varchar(11)
AS
DECLARE @.FName varchar(30)
DECLARE @.LName varchar(30)
SELECT @.FName = au_fname, @.LName = au_lname
FROM authors
WHERE au_id = @.AID
PRINT @.FName + ' ' + @.LName
GO
EXEC TESTPROC '172-32-1176'
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127950731.353336.297780@.g43g2000cwa.googlegroups.com...
> Hi. I'd like to assign the results of a select statement to a local
> variables in my stored procedure. My intent is something like this:
> SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
> only, some how I'd like to get the field2,field3 into variables. Can
> this be done?
> Thanks in advance
>|||Thanks Jerry, I'll try this out!|||Just be careful! If the SELECT returns more than 1 rows, you will *not* get
an error. The
variable(s) will contain the value for an unspecified row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127953579.251983.177820@.o13g2000cwo.googlegroups.com...
> Thanks Jerry, I'll try this out!
>

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assigning permissions to a table

I have a table that is being dropped and then recreated by "SELECT INTO"
through a scheduled stored procedure. So, therefore, all permissions are
being dropped as well.
I need to re-establish SELECT privileges for this table to a role containing
a user, or if it can't, a user alone. I have a role assigned to a specific
set of users who would need to SELECT this table. The stored procedure takes
care of all UPDATES/INSERTs so therefore doesn't need this.
JulianDo:
GRANT SELECT ON tbl TO <role>
Anith|||Looku GRANT in BOL
GRANT SELECT
ON authors
TO public
GO
GRANT SELECT
ON authors
TO Mary, John, Tom
GO
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Assigning multiple values to a parameter in stored procedure

Hi:
How do you write a SQL statement in a stored procedure so
that it will allow you to assign multiple values to a
parameter?
For instance in this example below, depending on what the
users select on the front-end application, the values
assign can be one customer id value or multiple customer
id values:
Create procedure dbo.SP_Test
As @.CustID varchar(3)
Select * from tblCust where customerid = @.CustID
Please help!This is probably not the best solution but it should work
create procedure sp_test
@.cust_id varchar(50)
as
set nocount on
exec ('select * from tblcust where customerid in (' + @.cust_id +')')
go
This procedure would be called as sp_test '50' for one cust_id or sp_test
'50, 55, 100' for several cust_id's

Assigning DATEADD results to variable

I am working on a stored procedure that requires saving the results of
several DATEADD calculations to variables. The problem I am having is
illustrated by the following code:
----
declare @.LastOfRecMo datetime
declare @.FirstOfNextMo datetime
declare @.LastOfNextMo datetime
declare @.AprilFirst datetime
select @.AprilFirst = '4/1/2005'
select @.LastOfRecMo = '3/31/2005'
select @.FirstOfNextMo = DATEADD(d, 1, @.LastOfRecMo)
select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.AprilFirst))
select LastOfRecMo = @.LastOfRecMo,
FirstOfNextMo = @.FirstOfNextMo,
FirstOfNextMo2 = DATEADD(d, 1, @.LastOfRecMo),
LastOfNextMo = @.LastOfNextMo
----
I would expect the @.FirstOfNextMo variable to be set to '4/1/2005', but as
you can see by running the above code, the result, returned as FirstOfNextMo
,
is always NULL, although FirstOfNextMo2 returns the correct date using the
identical calculation. Interestingly, @.LastOfNextMo works correctly with a
much more complex DATEADD calculation.
What's going on here?Sheldon Penner wrote:
> I am working on a stored procedure that requires saving the results of
> several DATEADD calculations to variables. The problem I am having is
> illustrated by the following code:
> ----
> declare @.LastOfRecMo datetime
> declare @.FirstOfNextMo datetime
> declare @.LastOfNextMo datetime
> declare @.AprilFirst datetime
> select @.AprilFirst = '4/1/2005'
> select @.LastOfRecMo = '3/31/2005'
> select @.FirstOfNextMo = DATEADD(d, 1, @.LastOfRecMo)
> select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.AprilFirst))
> select LastOfRecMo = @.LastOfRecMo,
> FirstOfNextMo = @.FirstOfNextMo,
> FirstOfNextMo2 = DATEADD(d, 1, @.LastOfRecMo),
> LastOfNextMo = @.LastOfNextMo
> ----
> I would expect the @.FirstOfNextMo variable to be set to '4/1/2005',
> but as you can see by running the above code, the result, returned as
> FirstOfNextMo, is always NULL, although FirstOfNextMo2 returns the
> correct date using the identical calculation. Interestingly,
> @.LastOfNextMo works correctly with a much more complex DATEADD
> calculation.
> What's going on here?
First thing is the date format you are using is not portable. The only
portable formats are:
yyyymmdd
yyyy-mm-ddThh:mm:ss.mmm(no spaces)
But your code works fine for me with the bad date format. Try changing
the date format and see what you get.
David Gugick
Imceda Software
www.imceda.com|||I'm baffled! I worked on this problem for hours yesterday, restarted the SQ
L
server, rebooted the computer, and could not get the code to run properly.
This morning, after reading your post, I tried again and the problem has gon
e
away. Thank you for your response.
"David Gugick" wrote:

> Sheldon Penner wrote:
> First thing is the date format you are using is not portable. The only
> portable formats are:
> yyyymmdd
> yyyy-mm-ddThh:mm:ss.mmm(no spaces)
> But your code works fine for me with the bad date format. Try changing
> the date format and see what you get.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I thought the problem was solved, but it's not. DATEADD behaves erratically
when given a variable as an argument. The code I sent you yesterday
inexplicably runs correctly now, but the following example, closer to what I
am actually using, does not:
----
declare @.ReceiverDate datetime
declare @.FirstOfRecMo datetime
declare @.LastOfRecMo datetime
declare @.FirstOfNextMo datetime
declare @.LastOfNextMo datetime
-- Receiver Date is 3/27/2005
set @.ReceiverDate = '20050327'
-- First Day of Receiver Month
select @.FirstOfRecMo = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0'
+ cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
select myVar = @.FirstOfRecMo,
calc = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0' +
cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
-- First Day of Month after Receiver Month
select @.FirstOfNextMo = DATEADD(m, 1, @.FirstOfRecMo)
select myVar = @.FirstOfNextMo,
calc = DATEADD(m, 1, @.FirstOfRecMo)
-- First Day of Receiver Month
select @.LastOfRecMo = DATEADD(d, -1, @.FirstOfNextMo)
select myVar = @.LastOfRecMo,
calc = DATEADD(d, -1, @.FirstOfNextMo)
-- Last Day of Month after Receiver Month
select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
select myVar = @.LastOfNextMo,
calc = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
----
I entered the Receiver Date in the format you recommended, although in the
actual application, the date is pulled from the database.
The first calculation correctly returns '3/1/2005' in both the myVar and
calc columns, indicating that '3/1/2005' was successfully assigned to the
variable @.FirstOfRecMo.
The second calculation, however, fails to assign the results of the DATEADD
function to @.FirstOfNextMo. It returns NULL in the myVar column, although
the calc column correctly returns '4/1/2005'.
The other two calculations return NULL in both columns.
Is the problem with my SQL Server, perhaps? Does the code run correctly on
your computer?
Your help is very much appreciated.
"David Gugick" wrote:

> Sheldon Penner wrote:
> First thing is the date format you are using is not portable. The only
> portable formats are:
> yyyymmdd
> yyyy-mm-ddThh:mm:ss.mmm(no spaces)
> But your code works fine for me with the bad date format. Try changing
> the date format and see what you get.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||This is very creepy!
When I type the code into Query Analyzer, it produces the errors I described
in my previous messages. However, if I copy the code to this newsgroup, pos
t
it, then copy the code from the post and paste it into Query Analyzer, it
runs properly.
Evidently, SQL Server is messing with my code. Any thoughts?
"Sheldon Penner" wrote:
> I thought the problem was solved, but it's not. DATEADD behaves erratical
ly
> when given a variable as an argument. The code I sent you yesterday
> inexplicably runs correctly now, but the following example, closer to what
I
> am actually using, does not:
> ----
> declare @.ReceiverDate datetime
> declare @.FirstOfRecMo datetime
> declare @.LastOfRecMo datetime
> declare @.FirstOfNextMo datetime
> declare @.LastOfNextMo datetime
> -- Receiver Date is 3/27/2005
> set @.ReceiverDate = '20050327'
> -- First Day of Receiver Month
> select @.FirstOfRecMo = cast(cast(Year(@.ReceiverDate) as char(4)) + right('
0'
> + cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
> select myVar = @.FirstOfRecMo,
> calc = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0' +
> cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
> -- First Day of Month after Receiver Month
> select @.FirstOfNextMo = DATEADD(m, 1, @.FirstOfRecMo)
> select myVar = @.FirstOfNextMo,
> calc = DATEADD(m, 1, @.FirstOfRecMo)
> -- First Day of Receiver Month
> select @.LastOfRecMo = DATEADD(d, -1, @.FirstOfNextMo)
> select myVar = @.LastOfRecMo,
> calc = DATEADD(d, -1, @.FirstOfNextMo)
> -- Last Day of Month after Receiver Month
> select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
> select myVar = @.LastOfNextMo,
> calc = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
> ----
> I entered the Receiver Date in the format you recommended, although in the
> actual application, the date is pulled from the database.
> The first calculation correctly returns '3/1/2005' in both the myVar and
> calc columns, indicating that '3/1/2005' was successfully assigned to the
> variable @.FirstOfRecMo.
> The second calculation, however, fails to assign the results of the DATEAD
D
> function to @.FirstOfNextMo. It returns NULL in the myVar column, although
> the calc column correctly returns '4/1/2005'.
> The other two calculations return NULL in both columns.
> Is the problem with my SQL Server, perhaps? Does the code run correctly o
n
> your computer?
> Your help is very much appreciated.
> "David Gugick" wrote:
>

Assigning a variable a table value

Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.

So, I declare a variable:
DECLARE @.variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.

I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.

Thanks in advance for any help

You need to use Select. You can assign multiple variables at the same time, but you cannot mix assignments with result sets. You can use either Set or Select to assign literal or constant values to a variable. Set only allows one variable at a time.

You can also use variables for values in a select statement.

Here is a full example:

Declare @.variable int,

@.pkey int

Set @.pkey = 42

Select @.variable = col1

From myTable

Where pkCol = @.pkey|||Thanks for your time and help it worked perfect.