how to attach Access database to MSDE

Hello,
what is the procedure to attach a .MDB databse to MSDE?
I don't care using forms or report from the Access database, I just want to
have tables and queries on MSDE then I will analyze data and arrange comments
to them.
Thanks
Rocco
Hi,
You can not attach a Access database to SQL Server MSDE. This is due to
changes in architecture.Methods to transfer:-
1. SQL Server DTS to copy structure and data
2. Upsizing wizard (
http://www.mssqlcity.com/FAQ/General/MigFromAcc.htm )
Thanks
Hari
SQL Server MVP
"rocco" <rocco@.discussions.microsoft.com> wrote in message
news:0D42E485-DCCC-45E8-A2D4-5AE3A298E71A@.microsoft.com...
> Hello,
> what is the procedure to attach a .MDB databse to MSDE?
> I don't care using forms or report from the Access database, I just want
> to
> have tables and queries on MSDE then I will analyze data and arrange
> comments
> to them.
> Thanks
> Rocco
|||OK. ANy tutorial for the DTS? I have opened and since anything is labelled in
that sort of *MicrosoftCreeepingLanguage* I'm not able to use it.
Maybe is not the MicrosoftCreeping thing, but only that I'm not to good at
that!
"Hari Prasad" wrote:

> Hi,
> You can not attach a Access database to SQL Server MSDE. This is due to
> changes in architecture.Methods to transfer:-
> 1. SQL Server DTS to copy structure and data
> 2. Upsizing wizard (
> http://www.mssqlcity.com/FAQ/General/MigFromAcc.htm )
>
> Thanks
> Hari
> SQL Server MVP
>
> "rocco" <rocco@.discussions.microsoft.com> wrote in message
> news:0D42E485-DCCC-45E8-A2D4-5AE3A298E71A@.microsoft.com...
>
>
|||Hi,
This migration is very simple. Just open the Export and Import utility
wizard from SQL Server program group.
Select the source as Access and destination as SQL Server MSDE and select
dbname.
Now select the tables you need to copy. CLick next.
This wizard will automatically drags you till end and will copy the data to
SQL Server. So easy.
Thanks
Hari
SQL Server MVP
"rocco" <rocco@.discussions.microsoft.com> wrote in message
news:4093596D-D547-460D-B761-3F1373169432@.microsoft.com...[vbcol=seagreen]
> OK. ANy tutorial for the DTS? I have opened and since anything is labelled
> in
> that sort of *MicrosoftCreeepingLanguage* I'm not able to use it.
> Maybe is not the MicrosoftCreeping thing, but only that I'm not to good at
> that!
> "Hari Prasad" wrote:

How to attach a database using SQL Server 2005

have you try the sp_attach stored procedure?
"Angus" <nospam@.gmail.com> wrote in message
news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hello
> In the full version of SQL Server I can use enterprise manager to attach a
> database. I have an SQL Server 2005 database from another machine which I
> want to attach to a new computer with SQL Server 2005 Express. Is there a
> tool I can use or do I need to run a command?
> Angus
>
"Angus" <nospam@.gmail.com> wrote in
news:ecHo7S8kHHA.4188@.TK2MSFTNGP02.phx.gbl:

> Yes I have found the sp_attach syntax - but how do I run these
> commands? Can I run them from the command line? But if I navigate to
> prog files\microsoft SQL Server\MSSQL.1\MSSQL\Binn\ - I cannot run eg
> sp_attach? Is this not the right path?
> Or have I maybe not installed something?
> There is a Start menu item, Microsoft SQL Server 2005... Configuration
> Tools... SQL Server Configuration Manager - but can't see where I
> could type commands in there?
You can use the OSQL command-line utility to run stored procedures. See
BOL for how to use it.

> Angus
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:uGaU5N8kHHA.2272@.TK2MSFTNGP02.phx.gbl...
> a
> I
> a
>

How to assign values to variables in a procedure in with select s

Hi,
I have code like this
:
CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
OUTPUT,@.sex VARCHAR(1) OUTPUT
AS
SELECT NAME,SEX from table1 where .....
Now I want to assign the value of Name and sex from the select statement to
variables @.name and @.sex.
How do I do that. I just do not want to execute the select statement twice
as the selection criteria is big.
Thanks,
JSSELECT @.name = name, @.sex = sex
FROM table1
WHERE (something that guarantees exactly one row)
"JS" <JS@.discussions.microsoft.com> wrote in message
news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
> Hi,
> I have code like this
> :
> CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
> OUTPUT,@.sex VARCHAR(1) OUTPUT
> AS
> SELECT NAME,SEX from table1 where .....
> Now I want to assign the value of Name and sex from the select statement
> to
> variables @.name and @.sex.
> How do I do that. I just do not want to execute the select statement twice
> as the selection criteria is big.
> Thanks,
> JS
>sql

How to assign values to variables in a procedure in with sele

I tried that but get an error for syntax check
Error141 - A select statement that assigns a value to a variable must not be
combined with data retrieval operation.
I am using SQL Server 7 .
Any help will be greatly appreciated.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT @.name = name, @.sex = sex
> FROM table1
> WHERE (something that guarantees exactly one row)
>
> "JS" <JS@.discussions.microsoft.com> wrote in message
> news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
>
>> Error141 - A select statement that assigns a value to a variable must not
> be
> combined with data retrieval operation.
Then you didn't run exactly what I posted. Could you show exactly what you
tried to run?
You can't SELECT and assign in the same statement. So, if you are sure that
the WHERE clause will always limit to one row, *and* for some reason you
need to SELECT the data *and* return it in output parameters (why would you
need to do both?), you can do this:
-- assign variable values from table
SELECT @.name = name, @.sex = sex FROM table WHERE ...
-- return *variables* to client
SELECT name = @.name, sex = @.sex|||JS wrote:
> I tried that
I doubt that you tried exactly what he said. It would help if you showed us
the revised code, but I suspect your statement now looks like:
SELECT NAME,SEX,@.name = name, @.sex = sex
from table1 where .....
Right?

> but get an error for syntax check
> Error141 - A select statement that assigns a value to a variable must
> not be combined with data retrieval operation.
That's a pretty self-explanatory error message: in a single sql statement
you can either return data to the client or assign values to variables. You
cannot do both in a single statement.
I suspect what you are trying to to do is:
SELECT name = name, @.sex = sex
from table1 where .....
SELECT @.name, @.sex

> I am using SQL Server 7 .
>
Doesn't matter
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Here it is:
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Then you didn't run exactly what I posted. Could you show exactly what yo
u
> tried to run?
> You can't SELECT and assign in the same statement. So, if you are sure th
at
> the WHERE clause will always limit to one row, *and* for some reason you
> need to SELECT the data *and* return it in output parameters (why would yo
u
> need to do both?), you can do this:
> -- assign variable values from table
> SELECT @.name = name, @.sex = sex FROM table WHERE ...
> -- return *variables* to client
> SELECT name = @.name, sex = @.sex
>
>|||Here is my code
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
What I am trying to do is assign the values to the OUTPUT variables, so that
the client can see the values of the OUTPUT field.
Please help
"Bob Barrows [MVP]" wrote:

> JS wrote:
> I doubt that you tried exactly what he said. It would help if you showed u
s
> the revised code, but I suspect your statement now looks like:
> SELECT NAME,SEX,@.name = name, @.sex = sex
> from table1 where .....
> Right?
>
> That's a pretty self-explanatory error message: in a single sql statement
> you can either return data to the client or assign values to variables. Yo
u
> cannot do both in a single statement.
> I suspect what you are trying to to do is:
> SELECT name = name, @.sex = sex
> from table1 where .....
> SELECT @.name, @.sex
>
> Doesn't matter
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30)
> OUTPUT,@.sex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
(a) do you not have a primary key on SSN? If so, there is no need to set
rowcount 1, since there will only ever be a maximum of one match. If there
is no primary key, why not?
(b) sorry, but there is no way that the procedure above yields the error you
mentioned earlier. Either you transcribed it wrong or you are looking at
the wrong code.|||Also,
(a) can you come up with a more useless and generic name for your procedure
than GET_DATA? At least you use the dbo prefix...
(b) I recommend better formatting so your procedure is readable.
(c) I strongly recommend against these implicit, non-ANSI join syntaxes.
How about:
CREATE PROCEDURE dbo.GetNameSexData
@.ssn VARCHAR(9),
@.name VARCHAR(30) OUTPUT,
@.sex VARCHAR(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 1
SELECT
@.name = nt.Name,
@.sex = ot.Sex
FROM
Name_Table nt
INNER JOIN Other_Table ot
ON nt.ssn = ot.ssn
WHERE nt.ssn = @.ssn
END
GO
Now, if that produces an error when you *call* it, show the method you used
to *call* it.|||Got it I was declaring the variables but assigining all the values in the
select statement for the output type. Thanks very much for all the help
"JS" wrote:
> Here is my code
> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.s
ex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
> What I am trying to do is assign the values to the OUTPUT variables, so th
at
> the client can see the values of the OUTPUT field.
> Please help
> "Bob Barrows [MVP]" wrote:
>

How to assign string value to TEXT output parameter of a stored procedure?

Hello,

I am currently trying to assign some string to a TEXT output parameter
of a stored procedure.

The basic structure of the stored procedure looks like this:

-- 8< --
CREATE PROCEDURE owner.StoredProc
(
@.blob_data image,
@.clob_data text OUTPUT
)
AS
INSERT INTO Table (blob_data, clob_data) VALUES \
(@.blob_data, @.clob_data);
GO
-- 8< --

My previous attempts include using the convert function to convert a
string into a TEXT data type:
SET @.clob_data = CONVERT(text, 'This is a test');

Unfortunately, this leads to the following error: "Error 409: The
assignment operator operation cannot take a text data type as an argument."

Is there any alternative available to make an assignment to a TEXT
output parameter?

Regards,
ThiloIs there a reason you can't just do a select on it?

How to assign a value to a parameter?

Hello everyone,

i have the parameter in my stored procedure that i am using as a sqldatasource.

Now in one of the events, i need to assign a value to the parameter. How can i do that?

Microsoft is changing the syntax so often, all solutions i found on this forum just don't work anymore, like:

SqlDataSource1.SelectParameters[

"@.CompareInteger"].value="1"

OR

SqlDataSource1.SelectParameters["@.CompareInteger"].DefaultValue="1"

I guess the SelectParameter - became 'ReadOnly'..But how to assign value to a parameter now?!?

Thanks for any help

They aren't changing the syntax.. Hasn't ever changed, but those methods you mentioned above only work in certain circumstances, because they are "hacks".

What you want to do is capture the SqlDataSource1_Selecting event. From within that event, you have access to the underlying command (and parameters collection). Your code from within that event would looks something like:

e.Parameters["@.CompareInteger"].Value="1;

Or

e.parameters("@.compareinteger").value="1" if you are using VB.NET

|||

it does not work, Motley. It does not work.

for SqlDataSource1_Selecting event, e does not have such an option - e.parameters - check it for yourself...:(

So, the question still remains - HOW TO ASSIGN VALUE TO A PARAMETER?

|||IS there any way to assign the value to a parameter?!?!? in any event procedure?|||

If it's not e.parameters, then it's one of the following:

e.SelectCommand.Parameters

or

e.Command.Parameters

|||

thank you, Motley ! Will remember it now.

How to approach (Trigger-Stored procedure )

Hi All

I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate

I have a trigger on the master table upon deletion to archive. This is
triggered from a seperate routine from a vb app to delete a record
real time.

CREATE Trigger Archive_Proc On dbo.MASTER
For Delete
As
Declare @.iDate As DateTime
Set @.iDate = GetDate()

If @.@.RowCount = 0 Return
set Nocount on
Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select ProductID, ProductNo, @.iDate from deleted

My problem is that I have a temp table that gets filled from a
seperate transaction.It needs to be matched against the master table
then deleted at both master and temp. but the issue is that the temp
table contains its own SoldDate value that needs to be archived.

Q 1: if I use a stored proc. how do i pass the SoldDate value to the
trigger as Triggers dont use GVs.
Q 2: How do I set up the stored procedure to delete with multiple
tables. I can get it to UPDATE but not delete...

CREATE PROCEDURE COMPARESOLD
@.Pool Smallint
AS
Set NoCount on

Update MASTER
Set Released = 2
From TEMP, MASTER
Where TEMP.ProductNo = MASTER.ProductNo
AND TEMP.ProductID = MASTER.ProductID
AND INVENTORY.Released = 1

hopefully someone can lead me to the right direction...
ThanksHi

I have not picked up the exact relationship between temp and the Master
table. Posing correct DDL (using the scripting options in Enterprise Manager
or the QA Object browser) and example data as inserts statements go a long
way to remove this sort of ambiguity.

It seems that you should have a FK from the TEMP table to the Master that
cascades on delete.

It may also be possible to change the trigger so that it picks up the
SoldDate from TEMP such as:

Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select d.ProductID, d.ProductNo, ISNULL(t.SoldDate,@.iDate)
from deleted d LEFT JOIN Temp t on d.ProductID = t.ProductID

You may want something slightly different if there are multiple TEMP
records.

John

"Alvin" <josesievert@.earthlink.net> wrote in message
news:cc27243c.0311061338.45d42f52@.posting.google.c om...
> Hi All
> I need opinions on how to approach my task.
> I currently have 3 tables: the master table, the archive and a temp
> table.
> MASTER: has 3 fields ProductID and ProductNo and Released
> ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
> TEMP: ProductID, ProductNo, SoldDate
> I have a trigger on the master table upon deletion to archive. This is
> triggered from a seperate routine from a vb app to delete a record
> real time.
> CREATE Trigger Archive_Proc On dbo.MASTER
> For Delete
> As
> Declare @.iDate As DateTime
> Set @.iDate = GetDate()
> If @.@.RowCount = 0 Return
> set Nocount on
> Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
> Select ProductID, ProductNo, @.iDate from deleted
>
> My problem is that I have a temp table that gets filled from a
> seperate transaction.It needs to be matched against the master table
> then deleted at both master and temp. but the issue is that the temp
> table contains its own SoldDate value that needs to be archived.
> Q 1: if I use a stored proc. how do i pass the SoldDate value to the
> trigger as Triggers dont use GVs.
> Q 2: How do I set up the stored procedure to delete with multiple
> tables. I can get it to UPDATE but not delete...
> CREATE PROCEDURE COMPARESOLD
> @.Pool Smallint
> AS
> Set NoCount on
> Update MASTER
> Set Released = 2
> From TEMP, MASTER
> Where TEMP.ProductNo = MASTER.ProductNo
> AND TEMP.ProductID = MASTER.ProductID
> AND INVENTORY.Released = 1
>
> hopefully someone can lead me to the right direction...
> Thanks

how to append windows login name to table name

hi,
I want to append the windows login name to the table that i created in the stored procedure, Is there any command , can any one help.
Advance thanksIf I understand it correctly, you want to create a table using a stored procedure and the name of the table will contain the Windows login name of the use. Is this correct? Are you calling the stored procedure from TSQL or through a .NET application? Can you provide more details?|||Hi

declare @.sql nvarchar(100)
set @.sql= 'Create table [Test_' + SYSTEM_USER +']'
exec SP_executeSQL @.sql

this may work check it but it will give u the user name if only you are logged in with windows autintication|||HI ALL
I WANTED TO CREATE A TEMPOARARY TABLE UNIQUELY WHEN DIFFERENT CLIENTS HITS THE SERVER AND EXECUTE THE SAME PROCEDURE.FOR THAT I USED HOST_NAME FOR CREATING THE TEMPORARY TABLE.IT CREATED THE TABLE APPENDING WITH HOST_NAME
THANKS|||

I don't know the whole story but it sounds like you need to keep similar data unique to a specific user.

I've done this by creating one table that has a UserID field. Then you can easily manage 1 table by passing the UserID into the SP. This works well for inserts as well as selects.

For example:

Field1 Field2 UserID
111 222 JohnDoe
333 444 JohnDoe
555 666 JoeSchmoe
777 888 JoeSchmoe

THis has worked well for me even with a large number of users and each having a large number of "User-SPecific" records.

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming Lu
EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>
|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
to
> the
>
sql

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming LuEXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
to[vbcol=seagreen]
> the
>

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming LuEXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> > I am trying to write a script to change a column name (from "User_Name"
to
> > "Customer_Name") with the same datatype, etc.
> > Is there a stored procedure that I can use to do this? or I have to drop
> the
> > old column, adding the new column and copy over the data?
> >
> > Thanks.
> >
> > --C. Ming Lu
> >
> >
>

How to add if ~else to a exist Stored Procedure

here is my original Sql Code

I add a parameters @.CustId in line 13

when CustID not Null, I want to add this to the Where clause line 146 and 156

if CustId not null then where clause will add a reference like CustId = @.CustID

if CustId is Null then not reference @.custID

how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..

1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171

CustID=COALESCE(@.CustID,CustId)

COALESCE function returns the first non-null expression in its expression list.

Refer the below link for more information

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

how to add a where clause by parameter in a stored procedure

What i want is to add by parameter a Where clause and i can not find how to do it!
CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 Where fieldId = @.id /*and @.WhereClause */
GO

thx
What error did you get? That sproc defintion looks fine to me apart from the fact that you've missed the FROM clause from the parameterised SQL statement.

-Jamie|||thx for the reply.

You are right. I forgot the from clause.

CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 FROM Table1 Where fieldId = @.id /*and @.WhereClause */
GO
I get only a syntax error when checking the procedure syntax|||

SQL Server does not support "parameterizing" the WHERE clause or any syntactic construct.

You have to construct the SQL String using string concatenation operations and then execute the constructed string using the dynamic EXEC statement.

CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
BEGIN
DECLARE @.mdstring nvarchar(300);
set @.cmdstring = "SELECT field1, field2 FROM T WHERE fieldID = " + @.id +
" AND " + @.whereClause;
EXEC(@.cmdstring);
END
GO

|||Personally, I dislike the above mentioned method. Besides the fact that it opens you to all sorts of bad stuff.

What you can do is make the parameters nullable, and do a case statement on the where part of the clause:

create procedure pSomethingOrAnother
(
@.p_lID integer,
@.p_lWhereItem1 integer,
@.p_sWhereItem2 varchar(50)
)
as

set nocount on

select
t.Field1,
t.Field2
from
TableName t
where
t.FieldID = @.p_lID
and (case when ISNULL(@.p_lWhereItem1, 0) = 0 then 0 else t.FieldToCheck1 end) = ISNULL(@.p_lWhereItem1, 0)
and (case when ISNULL(@.p_lWhereItem2, '') = '' then '' else t.FieldToCheck2 end) = ISNULL(@.p_sWhereItem2, '')

set nocount off

No magic values, no string concatenation, no million if statements. The only time this won't work so well is when you are looking for the value NULL in a field, but you can separate those instances out with an IF statement for that field.

how to add a where clause by parameter in a stored procedure

What i want is to add by parameter a Where clause and i can not find how to do it!
CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 from table1 Where fieldId = @.id /*and @.WhereClause */
GO
any suggestion?What you're trying to do can only be done using dynamic SQL. Dynamic SQL is a pretty large topic, so here are a few links to get youstarted:
http://www.databasejournal.com/features/mssql/article.php/1438931
http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sommarskog.se/dynamic_sql.html

How to add a newline sequence

I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc

One of the paramteres passed to it is @.body which is the body text of the message. I want to be able to add a couple of blank lines and then some footer information. This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext.

I have tried \n but that literally adds the two characters \ and n

Can anyone advise how to generate newlien sequences in T-SQL.

Regards

Clive

Use the CHAR function e.g. CHAR(13) + CHAR(10)

Seehttp://doc.ddart.net/mssql/sql70/ca-co_4.htm

How to accomplish what I am trying to do?

Hello,

I need to write a package the does the following, and not sure the best approach:

The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.

That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.

Also, each time I create the new output file, I need to delete the old named file.

I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.

Any help appreciated!!

Thank you

You can use an expression on your flat file connection manager to change the file name at runtime. Just set the ConnectionString property. To replace the file, make sure your Flat File destination has Overwrite data enabled.

Do all your stored procs return the same resultset? If not, using a data flow will probably not work for you, so you wouldn't be able to use a Flat File destination.

|||

Hi John,

You say that if my procs do not return the same result set a data flow will probably not work?

What can I use instead?

I am having serious problems trying to get the output from my stored procedure loops to flat files.

That is, I added a data flow task to the foreach loop, so that each execsql task will run a stored proc then write the output to a file. But it seems this is not possible?

I find it hard to believe... but is there a workaround?

|||You could use a custom script task inside your loop to execute the procedures, read the results, and write them to a file.

How to access to an another database?

Hello,

I 'm making a procedure that needs to use a table that is in an other database and that database is in an other SQL Server Group. Did you know if is it possible. How can I make this?

Thank you very much for your help and time.

Jonathan.

yes u can do this...add the other server as a linked server to ur server..using sp_addlinkedserver...

then refer the table in other db as

servername.dbname.dbo.tablename

eg. select * from server1.mydb.dbo.table1

|||Thank you very much, Nithin Khurana!!!!

Jonathan

How to access tables from a dynamic

I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:

SELECT COUNT(id) FROM @.dbname..sysobjects WHERE type='U'

How can I access the tables while the database name is in a variable.

Thanx.I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:

SELECT COUNT(id) FROM @.dbname..sysobjects WHERE type='U'

How can I access the tables while the database name is in a variable.

Thanx.

You can do it with an exec call:

EXEC('SELECT COUNT(id) FROM ' + @.dbname + '..sysobjects WHERE type = ''U''')

The U value needs to be surrounded by 2 pairs of single quotes, because it is a string within a string.|||Hi, Thanx.

How to access Stored Procedure in Sybase from SQL 2000

I have Sybase 12.5.2 Database running in another domain.
I am currently working in a different domain under SQL 2000. I need to call
a stored procedure in Sybase by passing some input parameters and expect
some outptu parameters.
How to call a stored procedure in Sybase 12.5.2 from SQL 2000 Stored
Procedure?
With Regards,
SL Alagappan
SQL Server 2000 does not support the execution of remote stored procedures
against a non-SQL Server database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||First, get a copy of the Sybase ASE drivers. Next, try this:
Place this at the top of your class:
using Sybase.Data.AseClient;
Modify this code appropriately for your server and stored procedure:
// obtain the connection information specified
String host = textBoxHost.Text;
String port = textBoxPort.Text;
String user = textBoxUser.Text;
String pass = textBoxPass.Text;
// build a connect string
AseConnection conn = new AseConnection( "Data Source='" + host +
"';Port='" + port + "';UID='" + user + "';PWD='" + pass +
"';Database='oltp2_db';" );
AseCommand cmd = null;
AseDataReader reader = null;
try
{
conn.Open();
cmd = new AseCommand( "select adi_name from area_of_dominant_influence",
conn );
reader = cmd.ExecuteReader();
listAuthors.BeginUpdate();
while( reader.Read() )
{
listAuthors.Items.Add( reader.GetString( 0 ) );
}
listAuthors.EndUpdate();
}
catch( AseException ex )
{
MessageBox.Show( ex.Message );
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
if (cmd != null)
cmd.Dispose();
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}

How to access Stored Procedure in Sybase from SQL 2000

I have Sybase 12.5.2 Database running in another domain.
I am currently working in a different domain under SQL 2000. I need to call
a stored procedure in Sybase by passing some input parameters and expect
some outptu parameters.
How to call a stored procedure in Sybase 12.5.2 from SQL 2000 Stored
Procedure?
With Regards,
SL AlagappanSQL Server 2000 does not support the execution of remote stored procedures
against a non-SQL Server database.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||First, get a copy of the Sybase ASE drivers. Next, try this:
Place this at the top of your class:
using Sybase.Data.AseClient;
Modify this code appropriately for your server and stored procedure:
// obtain the connection information specified
String host = textBoxHost.Text;
String port = textBoxPort.Text;
String user = textBoxUser.Text;
String pass = textBoxPass.Text;
// build a connect string
AseConnection conn = new AseConnection( "Data Source='" + host +
"';Port='" + port + "';UID='" + user + "';PWD='" + pass +
"';Database='oltp2_db';" );
AseCommand cmd = null;
AseDataReader reader = null;
try
{
conn.Open();
cmd = new AseCommand( "select adi_name from area_of_dominant_influence",
conn );
reader = cmd.ExecuteReader();
listAuthors.BeginUpdate();
while( reader.Read() )
{
listAuthors.Items.Add( reader.GetString( 0 ) );
}
listAuthors.EndUpdate();
}
catch( AseException ex )
{
MessageBox.Show( ex.Message );
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
if (cmd != null)
cmd.Dispose();
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}