Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts

Friday, March 30, 2012

How to authenticate users using user login and password stored in SQL database?

Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.

When you click the login button. Perform a select query to your database that gets the password for the given loginname.

Then compare the two passwords.

Good luck!

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
>

Wednesday, March 28, 2012

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.

Monday, March 26, 2012

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.

Friday, March 23, 2012

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 allow use to exec xp_sendmail

I have a stored proc that assigns a value to a field based on user input from an Access front end.

The last part of the stored proc sends an email if certain conditions are met.

It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?

The users are getting this message:

EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)You can grant a user to execute xp_sendmail by going to properties of xp_sendmail in master database and grant the user permission to execute.

Monday, March 19, 2012

how to add table and all its index for replication

If you're talking about code as in stored procedure
calls, the stored procedures you need are:
exec sp_addarticle
exec sp_addsubscription or exec sp_refreshsubscriptions
I can script mine out for you and post them up, but it
would be more useful for you to create a dummy
publication and add the new articles using enterprise
manager. After they're created, right click the
publication and get EM to script out the publications.
Selecting the relevant sp_addarticle sp_and
addsubscription bits will give you what you need.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I guess i can try it out.. Are indexes added thru sp_addarticle ? or is
there a parameter in there that states to includes indexes,
constraints,etc..?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0faf01c4f6f9$292fbae0$a501280a@.phx.gbl...
> If you're talking about code as in stored procedure
> calls, the stored procedures you need are:
> exec sp_addarticle
> exec sp_addsubscription or exec sp_refreshsubscriptions
> I can script mine out for you and post them up, but it
> would be more useful for you to create a dummy
> publication and add the new articles using enterprise
> manager. After they're created, right click the
> publication and get EM to script out the publications.
> Selecting the relevant sp_addarticle sp_and
> addsubscription bits will give you what you need.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Have a look at @.schema_option in sp_addarticle. If you
select teh elipsis button in EM and on the article
properties, snapshot tab, you'll see the corresponding
checkboxes. The easiest thing to do is to select what you
want graphically, and then have EM do the bitmask logic
to generate the correct parameter value.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Monday, March 12, 2012

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

Friday, March 9, 2012

how to add extra columns to a report that are not in select query or stored proc

Hi,

I am new to reporting.I have 10 columns coming from the sql query.I need to add 2 more columns to the same report.These 2 columns come from the values the user selected in the dropdownlist on a webpage(From where we are calling the Report).How to add these columns to the report from the webpage.Please guide me.

in the textbox use

=Parameters!parameterName.Value

|||

you create input parameters and supply the value when the rpt is launched from the web, then in the text box u place the i/p paramter inst of keeping the table field

Wednesday, March 7, 2012

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 add a image from database to page header

Hi,

I am having same issue, but the data is an image stored in database. actually its an company logo which i need to display on every page.

i have a doubt. if i user source as external and an URL in a image property, then my image will not appear if the Reporting Server and Application Server are on two different machines. if both are in one machine as my local machine(development machine) the image appears.

is this an known issue or how to solve this. due to this reason, we thought of putting images in database and pulling out, but it turned out to be an another problem.

You can follow the approach described in Books Online (scroll to "Adding a Databound Image to a Header or Footer"): http://msdn2.microsoft.com/en-us/library/ms159677.aspx

Note: if you are doing this on Reporting Services 2005, you should definitely first install SP1 (http://www.microsoft.com/sql/sp1.mspx) for both, the report server and the machines with BI Development Studio / Report Designer.

-- Robert

Friday, February 24, 2012

How to add a group header and footer for a report

I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.

please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.

i may get 100 records related to 10 contracts , 10 rows for each contract.

as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.

Please i am totally new to reporting and help would be appreciated. thank you all.

Here's what you can do:

Click anywhere on your table while in Layout.
Right click on the box next to your details row (it has 3 horizontal bold bars on it), and select 'Insert Group'.
On the 'General' tab, make sure 'Include group header' and 'Include group footer' are both checked.
In the 'Group on:' section, drop down the box and select your contract field. (Or you can type: =Fields!contract.Value)
On the 'Sorting' tab, again, pick or type your contract field as above.
On the 'Visibility' tab, make sure that the 'Visible' radio button is selected.
Hit 'OK' and now you can add your fields to the group header and footer.

Hope this helps.

Jarret

|||

Did this work for you Reddymade?

Jarret

Sunday, February 19, 2012

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 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();
}