How to Create Store Procedure

Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do?Okay, first off I am FAMOUS for writing the kludgiest code on the planet. This proc may or may not work for you (it does for me). It may or may not be very fast. It may or may not meet with best practices guidelines (it probably doesn't). But it should give you some things to work with (a stored procedure, parameters, working with results). You need to do a bit of research on your own. Google is your friend.

Supporting Tables

create table Users (Username varchar(255), [Password] varchar(255))

Supporting Data

insert Users (UserName, [Password]) values ('tscott','123')
go
insert Users (UserName, [Password]) values ('ascott','abc')
go
insert Users (UserName, [Password]) values ('bscott','Abc')
go

select * from users2

Stored Procedure

CREATE PROC spValidatePassword ( @.UserName varchar(255),
@.Password varchar(255)
)

/************************************************** *****
* spValidatePassword
* hmscott
*
* Validates a username/password against a table of known username/passwords
* Warning: data is not encrypted; these passwords are not secure. This
* sample is for demonstration only and is not intended for production
* use.
*
* Parameters:
* IN: UserName The usernmae provided by the user
* Password The password provided by the user
*
* Returns:
* Single-row recordset (ReturnCode):
* -1: UserName not found
* 0: Password incorrect for this user
* 1: Password correct for this user
************************************************** ****/

AS

DECLARE @.LocalPassword varchar(255), @.ReturnCode int

SELECT @.LocalPassword = [Password]
FROM dbo.Users2
WHERE UserName = @.UserName

IF @.LocalPassword IS NULL
BEGIN
SELECT @.ReturnCode = -1 -- User not valid
END
ELSE
BEGIN
IF @.LocalPassword = @.Password COLLATE Latin1_General_CS_AS
-- IF @.LocalPassword = @.Password -- Use this line instead of the above line if you want the passwords to be case insensitive
BEGIN
SELECT @.ReturnCode = 1 -- Password matches
END
ELSE
BEGIN
SELECT @.ReturnCode = 0 -- Password does not match
END
END

SELECT @.ReturnCode as ReturnCode

spValidatePassword 'nouser', 'Foo'

spValidatePassword 'bscott', 'Abc'

spValidatePassword 'bscott', 'abc'sql

How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJ
Try something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] =
'temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:

>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJTry something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] =
'temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:

>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

How to create schem

Hello all,
I've always tried to write my SQL script so that they could run
multiple times without error so when I was creating a new schema i get
an error
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
CREATE SCHEMA temp AUTHORIZATION dbo
--END IF
GO
this code works
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
--END IF
GO
and this code works
USE AdventureWorks
GO
CREATE SCHEMA temp AUTHORIZATION dbo
GO
can anyone shed some light on this.
Thanks in advance for your help.
Regards,
HJTry something like:
USE AdventureWorks
GO
IF NOT EXISTS(SELECT * FROM sys.schemas where [name] ='temp')
EXEC('CREATE SCHEMA temp AUTHORIZATION dbo')
-Sue
On 23 Mar 2006 09:26:13 -0800, hanklvr@.yahoo.com wrote:
>Hello all,
>I've always tried to write my SQL script so that they could run
>multiple times without error so when I was creating a new schema i get
>an error
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> CREATE SCHEMA temp AUTHORIZATION dbo
>--END IF
>GO
>this code works
>USE AdventureWorks
>GO
>IF NOT EXISTS(SELECT * FROM sys.schemas where [name] = 'temp')
> PRINT 'CREATE SCHEMA temp AUTHORIZATION dbo'
>--END IF
>GO
>and this code works
>USE AdventureWorks
>GO
>CREATE SCHEMA temp AUTHORIZATION dbo
>GO
>can anyone shed some light on this.
>Thanks in advance for your help.
>Regards,
>HJ

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thanks
well, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.

Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.
And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thanks
well, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.

Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.
And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.
sql

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thankswell, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.

Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working. |||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.
And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thanks
well, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.

Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.
And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thanks
well, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.

Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.
And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.

How to create apps that write code to retrieve data with foreign keys?

Hi Guys

Off late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?

Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?

Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?

Thanks

You use the Pk-FK relation to join the two tables and retrieve the columns from either of the two tables.

SELECT t1.col1, t2,col2, t2.col3

FROM Table1 t1

JOIN Table2 t2 ON t1.somecolumn = t2.somecolumn

Assuming the 'SomeColumn' here is the common column between the two tables, the above SELECT statement can be modified to retrieve columns from either of the tables. And I dont think it is cumbersome to retrieve info from another table. Your tables have to be properly normalized. This is the key. Joining too many tables in the query could also be detrimental. It depends on how well your tables are normalized.

>> Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?
It may not be a good tatabase technique to bend normalcy rules but from a practical/real world perpspective, sometimes, people do have redundant data. If you have enough justification (not just laziness or saving time or writing less code) then yes.

>>Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?
Sure, I did that above already.

|||

Thanks ndinakar for the reply. Code that I referred here is not the SQL query, that is not the cause of concern as I too know to retrieve the data from another table which has the Pk-Fk relationship another table with an SQL query. However, it often requires quite a lot of C# or VB code to retrieve the data from the other table when relationships are used.

Can you tell me if there are any in-built code that could be used to retrieve the data from the Pk table given the Fk table? I think I was not clear in my post and this is now clear.

Thanks

sql

How to create a torn page.

I want to write a magazine article about torn pages. Is there an easy way to create a torn page? Or does anybody have a backup of a sample database such as AdventureWorks that has a torn page?

I suppose you have to simulate a disk failure at the right moment. I don't think it is very easy to simulate tbh.

But do keep us informed if you find a way to do so.

|||

To simulate a torn page, you'll have to "corrupt" a database page on disk.

This can be done with a hex editor, or other tool.

Our pages are 8K in size, so 16 sectors of 512bytes.

We have 2 kinds of validation: checksum or torn page.

For the torn page, you'll want to corrupt bytes at 0 mod 512 in sectors 1..15 of a page. You could write 0x00 at offset 512, and 0xFF at offset 1024, for example.

Checksums are more robust, so you should be able to change any bit on the page to cause the corruption to be detected.

In order for this corruption to cause any problem, you'd have to pick a page that actually holds data, and then cause the engine to try to read that page.

Hope that gives you some ideas.

|||

Aah, think out of the box :-)

Great tip.

how to create a script to execute a package?

hello,

I am loading data from one DB to another. I wish to load them every day at night. I know to do that, I must write a script. But how to do it? I don't know. Could someone help me?

thanks in advance

Let se I got it right.

If you have a package or set of packages; you can use SQL Server agent to create a Job that rons on a scheduled basis. Why would you need an script?

|||

The easiest way to create a script is to use DTEXECUI.EXE to build the necessary command-line parameters for DTEXEC.EXE. You can then copy this into a batch file and schedule it using whatever tool you want.

With this said, Rafael is right - using SQL Server Agent is probably the simplest way to execute your packages on a schedule.

how to create a script to execute a package?

hello,

I am loading data from one DB to another. I wish to load them every day at night. I know to do that, I must write a script. But how to do it? I don't know. Could someone help me?

thanks in advance

Let se I got it right.

If you have a package or set of packages; you can use SQL Server agent to create a Job that rons on a scheduled basis. Why would you need an script?

|||

The easiest way to create a script is to use DTEXECUI.EXE to build the necessary command-line parameters for DTEXEC.EXE. You can then copy this into a batch file and schedule it using whatever tool you want.

With this said, Rafael is right - using SQL Server Agent is probably the simplest way to execute your packages on a schedule.

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Hi,

You can refer this link: http://www.sqlservercentral.com/columnists/mivica/creatingapdffromastoredprocedure.asp

I guess it solves your problem.

Thanks & Regards,

Kiran.Y

|||

Thank you for your replay. I' ve seen this article before but it did not really help. Exmple1 and 2 in the article are straight forward but you can’t see how to output the PDF file. Also it does not show you how to loop through the pdf table. I did run the stored procedure under The link SQL2PDF.TXT but it did not generate the PDF File.

Thank you

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Hi,

You can refer this link: http://www.sqlservercentral.com/columnists/mivica/creatingapdffromastoredprocedure.asp

I guess it solves your problem.

Thanks & Regards,

Kiran.Y

|||

Thank you for your replay. I' ve seen this article before but it did not really help. Exmple1 and 2 in the article are straight forward but you can’t see how to output the PDF file. Also it does not show you how to loop through the pdf table. I did run the stored procedure under The link SQL2PDF.TXT but it did not generate the PDF File.

Thank you

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name which has all these data is called dbo.TestTable.
I would like my stored procedure to output a PDF file with data from TestTable.
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Quote:

Originally Posted by goal2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name which has all these data is called dbo.TestTable.
I would like my stored procedure to output a PDF file with data from TestTable.
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

There's no way a stored procedure can directly output its results to a PDF file that I know of. I suggest that you make a .NET component that creates a database connection, executes your stored procedure retrieving a dataset, creates a PDF file and writes the output to it. You can leverage a free iTextSharp library http://itextsharp.sourceforge.net/ for making PDF files. It provides for basic formatting and may be just what you need. Then, you have a few options of setting up your component to run either as a SQL job or Windows service or even invoke it from inside the stored procedure itself (requires CLR integration). Hope this is helpful. Let me know if you need more details.|||

Quote:

Originally Posted by davef

There's no way a stored procedure can directly output its results to a PDF file that I know of. I suggest that you make a .NET component that creates a database connection, executes your stored procedure retrieving a dataset, creates a PDF file and writes the output to it. You can leverage a free iTextSharp library http://itextsharp.sourceforge.net/ for making PDF files. It provides for basic formatting and may be just what you need. Then, you have a few options of setting up your component to run either as a SQL job or Windows service or even invoke it from inside the stored procedure itself (requires CLR integration). Hope this is helpful. Let me know if you need more details.

Thank you for your replay. Here is what i am trying to do. I am trying to setup sql schedule which is going to execute a stored procedure that gets some data then put these data into a pdf file. After that it will send an e-mail to the manger with new created pdf file. How can i accomplish that?
Thank you|||

Quote:

Originally Posted by goal2007

Thank you for your replay. Here is what i am trying to do. I am trying to setup sql schedule which is going to execute a stored procedure that gets some data then put these data into a pdf file. After that it will send an e-mail to the manger with new created pdf file. How can i accomplish that?
Thank you

You can create a Windows service that establishes a connection to the database, polls the database table(s), creates a new PDF file with the data retrieved and sends an email to the manager with this attachment. In the service, you embed a timer component for scheduling tasks. The actual scheduling info can be placed in .config file for starters.

how to count with restrictions

Hello Gents,
need your help to write a formula for counting records with defined calue in the fileld, e.g. how many records with value "123456" in field {table.field}?
thank you very much for your help!U can.

Create a running total field. Add the field which you want to count. Give "Count" as your type of summary.

In Evaluate option,
select "Use a Formula" option. click X-2 box. Write your formula. Never reset this .

u can get the count what u want.

Hope this work.|||thanks a lot, but which will be formula in my case?|||Just simply enter

{table.fieldname} = "123456"|||thanks for prompt reply, but unfortunatelly it always returns zero...|||If it is string field, use trim function, records may be stored with spaces.

Otherwise it should work if the table contains records for that value|||Doesnt help... When i use simply count({fieldname}) - that returns total fine, but with this restriction it doesnt work...|||Sorry for asking this,

Do you have any records which meet ur condition?

I used lots of running total sums with various conditons. working fine.|||Sure, i have :-)
Actually, this field (string type) is a set of 5 kind of different words (total is ~700). I would like to know how many i have words "123456" for example.|||In addition: when i use running total with evaluation "For each record" - it returns 1. When i set type of summary to maximum - then returns only value, not the sum.|||Make sure

i) Included the exact field name in "Fields to Summarize" box
ii) Type of Summary is count

iii) Evaluate:

Check "Use a formula option" and
write the formula in formula workshop (by clicking X-2)
trim({table.fieldname}) = "123456"

iv) Reset:

check "Never" option|||Yes, all of these conditions is true.|||Ok, good

Now where have u placed your running total field?
(in which section)|||In ReportHeader.|||that's the problem.

It's running total. So will be calculated while printing records.

So place in Report Footer. So u'll get the count.|||Thats works! Thank you very much!!!

How to count across multiple tables in a DB?

Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:

Code Snippet

SELECT count(ulID)

FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....)

WHERE szF11 = ' '

Note: ulID is the name of a column that every table has and szF11 is also in every table.

Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.

I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.

Thanks,

Erik

You have a few options.

You could do use a derived table:

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
UNON ALL
SELECT uID, szF11
FROM F_APPEA_Data
UNION
etc....) AS Tbl
WHERE sF11 = ''

A variation on this could be that you include the WHERE clause for each table within Tbl

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
WHERE sF11 = ''
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
WHERE sF11 = ''
etc...) AS Tbl

Another option would be to include the tablename in the derived table so you can find out how many rows per table

Code Snippet

SELECT TableName, COUNT(uID)
FROM
(SELECT uID, 'F_ACCOU_Data' AS TableName, sF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, 'F_AGNCY_Data', sF11
FROM F_AGNCY_Data
etc...
) AS tbl
WHERE sF11 = ''
GROUP BY TableName

HTH!

How to copy the image field

Hi,
I have to write a procedure which copies some data within and between
tables, including image fields of unknown size. Those image fields contain
documents in various formats and can vary from couple of kilobytes to couple
of megabytes. How can I do it? My first instinct was to look at READTEXT,
WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
recommend them any more and is going to drop them altogether. Can anyone
direct me to some materials that would show how to do it?
Thanks,
MiroslawMPA (miroslaw_pa@.pf.pl) writes:
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields
> contain documents in various formats and can vary from couple of
> kilobytes to couple of megabytes. How can I do it? My first instinct was
> to look at READTEXT, WRITETEXT and UPDATETEXT functions, but it seems
> Microsoft does not recommend them any more and is going to drop them
> altogether. Can anyone direct me to some materials that would show how
> to do it?
You are correct that these operations are depreacated, but so is the
image data type.
The preferred data type for image data in SQL 2005 is varbinary(MAX). If
your data type is image, and you can change that, then you probably
have to use WRITETEXT and UPDATETEXT.
varbinary(MAX) works very much like the regular varbinary(n) data type,
but you can work with chunks with the .write mutator, and this way
update only a part of the column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||MPA wrote:
> Hi,
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields contai
n
> documents in various formats and can vary from couple of kilobytes to coup
le
> of megabytes. How can I do it? My first instinct was to look at READTEXT,
> WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
> recommend them any more and is going to drop them altogether. Can anyone
> direct me to some materials that would show how to do it?
> Thanks,
> Miroslaw
You can use the regular UPDATE statement to copy data between IMAGE
columns. However, in SQL Server 2005 the IMAGE type exists for
backwards compatibility only. Microsoft recommends that you use
VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
also deprecated.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The customers have different servers. The first one has SQL Server 2000, so
it looks like I have to use READTEXT and UPDATETEXT. I just wanted first to
make a test to copy the small piece of data from one row of the table to
another, but it does not seem to work. It runs like this:
DECLARE @.ptrval varbinary(16)
DECLARE @.ptrval2 varbinary(16)
SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
and doc_zkz_id=35)
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
doc_zkz_id=35
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
I was trying here to read 16 first bytes of DOC_BLOB column from the first
row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
After the operation the written 16 bytes are completely different than the
data in the first row. On the other hand I was able to update a second row
with the statement
like:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 'abcdef"
What I am missing?
Thanks,
Miroslaw
Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> napisal w
wiadomosci news:1147621152.612045.54660@.j55g2000cwa.googlegroups.com...
> MPA wrote:
contain
couple
READTEXT,
> You can use the regular UPDATE statement to copy data between IMAGE
> columns. However, in SQL Server 2005 the IMAGE type exists for
> backwards compatibility only. Microsoft recommends that you use
> VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
> also deprecated.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||MPA (miroslaw_pa@.pf.pl) writes:
> The customers have different servers. The first one has SQL Server 2000,
> so it looks like I have to use READTEXT and UPDATETEXT. I just wanted
> first to make a test to copy the small piece of data from one row of the
> table to another, but it does not seem to work. It runs like this:
> DECLARE @.ptrval varbinary(16)
> DECLARE @.ptrval2 varbinary(16)
> SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
> and doc_zkz_id=35)
> READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
> SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
> doc_zkz_id=35
> WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
> I was trying here to read 16 first bytes of DOC_BLOB column from the first
> row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
> After the operation the written 16 bytes are completely different than the
> data in the first row. On the other hand I was able to update a second row
> with the statement
The statement
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
does not assign @.ptrval. Rather @.ptrval is a pointer to the blob data
in the column. The 16 bytes gets returned to the client. So when you
say:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
you are just writing that pointer to the blob.
You can copy blob data with UPDATETEXT. But please don't ask me for an
example. I very rarely work with any of the text operations, and when I
do, I need to study Books Online carefully.
Now, you mentioned that the source and target tables were on different
servers. This is going to make it even more painful.
I would try a straight UPDATE across the linked server. If this breaks
down, I would try to use BCP to bring the source table over the target
server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx