How to Script Muplitple SPROCS/VIEWS at one time

In Enterprise Manager one can select several SPROCS/VIEWS using the
CONTROL key and then Right-Click to script out those objects.
Alternativly, pressing CONTROL-C copies, to the clipboard, the T-SQL to
create the selected objects.
SQL Management Studio seems to only allow you to script one object at a
time.
Is there a way in SQL Management Studio to select multiple objects and
generate create or modify scripts?Hi, naviagte to the database node and script the objects using the
wizard, select only the procedures you want to script.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

How to Schedule and Run a SSIS package

Hi

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

sql

How to Schedule and Run a SSIS package

Hi

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

How to schedule a Stored Procedure to run nightly in SQL Server

Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.
Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:

> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.
sql

How to schedule a Stored Procedure to run nightly in SQL Server

Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:
> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.|||Thanks very much. It is so simple I feel silly.
"Alejandro Mesa" wrote:
> Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
> use EM to do this. Open the server group, locate the server, open it, go to
> management, right click on jobs and select "New job".
>
> AMB
> "Dave Pylatuk" wrote:
> > Hello.
> >
> > What is the easiest way to schedule a stored procedure to run nightly at a
> > specified time ?
> >
> > Thanks in advance.

How to schedule a Stored Procedure to run nightly in SQL Server

Hello.
What is the easiest way to schedule a stored procedure to run nightly at a
specified time ?
Thanks in advance.Creating a job (sp_add_job) and scheduling it (sp_add_jobschedule). You can
use EM to do this. Open the server group, locate the server, open it, go to
management, right click on jobs and select "New job".
AMB
"Dave Pylatuk" wrote:

> Hello.
> What is the easiest way to schedule a stored procedure to run nightly at a
> specified time ?
> Thanks in advance.

how to save listbox multiple select values

Hi,

I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.

I grab the values for the selection as follow:

foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}

but I'm not sure on what to do next and could use some help.

Thanks
Germanoshouldn't lbAttendees have .SelectedItems?

Next, you should be passing the values to the database through sql or other dataaccess means.|||>>Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there.

I would strongly suggest option 2 (Normalize).

>>I'm not sure how to loop through each value and write it to the database table.

Well you have some options.

i) Loop through your items on the client and perform inserts (one row at a time).

ii) Package up the values as an xml chunk and use openxml to insert (set based method, less chatty)

ii) Package up the values as a delimited string and perform parsing and insert at the server.

And there are others (using OO: collections, persistance frameworks mechanisms, etc..)sql

How to save DTS package as an application

Hi,

I want create stand alone application from DTS package. I remember I
did one time ago, I couldnt recall how I did it.

Thanks.If you want to run DTS as an executable then the two main options are
to use the DTSRUN utility (see Books Online) or to invoke the package
using DTS's COM object model (see "DTS Programming" in Books Online)

--
David Portas
SQL Server MVP
--|||more importantly..

you can save a dts package as a vb bas module and then compile it into
a vb6 app right?

hth

aaron|||Yes, I forgot abvout that. Select Package, Save As and select "Visual
Basic File" from the location dropdown.

--
David Portas
SQL Server MVP
--|||that, single-handedly is the reason i haven't gotten into this .NET
crap yet

you can't save it as VB.net can you?

-aaron

How to run SQL-Server on a Webserver!

Hi!

Today I decided to finally install MS SQL 2005 Express on my VPS. For a long time I tried to work with MS Access what definitely brings a lot of complications.

I already installed SQL05Exp on my local machine and everything works great here. Because I'm quite new to these things I simply installed SQL05Exp on the Server the same way like locally. I just clicked through the installation with the predefined settings and everything installed without troubles.

When I uploaded my first Application and tried to run it, I got the following error-message:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

I then read somewhere that I could change the web.config connectionstring to "User Instance=False" what leads to the following:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I read a lot of documents about how to install, etc. but it couldn't really use it for me, surely for a lake of basics. When I understand the problem correctly, the user running the app on the server and trying to attach the mdf-database to the sql-server has not the rights to do that. However I don't know what to do against that.

What I would really love is a simple solution that allows me to connect to the sql-server from whatever domain on the server, just by copying the mdf. It also would be nice to use some kind of password in the connectionstring to assure that just my apps can connect to my databases.

I would be very thankful if someone could give me some good basic explanation what to do and maybe also give me a reference to some nice and understandable informations on this.

Best regards,

Markus

In most cases, the error is exactly what the error message says, but most people ignore what it's saying, so I'll break it down:

Error Description
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed.

Possible causes
1)database with the same name exists
2)specified file cannot be opened,
3)or it is located on UNC share

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

|||

Hi Motley, thanks for your help.

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

There is not even one database installed until now, however I tried to change the name but everything is the same.

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

It was running as Network-Service, I also tried as Local Service and as Local System, always the same result.

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

It is located within the App_Data folder, everything is on the same machine.

What else could it be?

Markus

|||

Did you check the second half of Fix#2? Most likely you need to make sure the user(Network Service) has write/read permissions to the folder (or to the database directly, if you assign permission database by database).

If you use window authentication, you may need assign window login user with write/read permission to this folder too.

|||

Now I tried to assign the user everyone with all possible rights to both the master database located in the installation folder of sql server and I did the same on both databases of my app.

Everything stays the same. What else could cause this errors?

Markus

|||

I didn't mean the user everyone. Try this one (yourMachinesName\theLoginUser) and Network Service (win2k3 IIS 6.0).

to App_Data with write/read permissions.

Here is a link you can check the basics.

Tell you the truth, I went through this one alittle while ago. What I can remember is the security settings. Since you mention this problem, I am curious that how did you access your application? through VWD on the server or through a virtual directory under IIS 6.0? It may help us to understand why the problem didn't go away.

If you are using IIS to access your application, there are some other settings to adjust like Application should run on asp.NET 2.0 , connction string in your web.config modification ...

Hope we can get the application run soon.

|||

Dear limno, thanks for your help.

Yesterday I assigned the user everyone because what I know this is like setting access for all the existing users. It is a quick way to test if it has something to do with rights, after that I can search for the right user which to give access to.

However, I did exactly like you told me, I gave the network service user and the user administrator which i us to log on full rights but the problem stays the same.

Not sure if I understand your second question. Locally I'm using VWD with SQL Express and it works fine. But on my VPS it's not working. Means if I call my website through the browser I get this error-message.

Markus

|||Change the anonymous user accout for the application to you. You can't log in as any of the accounts you mentioned, so they won't have a user directory (C:\Documents and Settings\{User Name}).|||

The Anonymous user of the application is gsp-peru and it also has a user directory. I tried to change it to administrator which changed nothing. Hope I understood your idea.

Markus

|||

If there are any more hints or ideas I would be happy to hear about them.

Limno, you wrote above about a link which seems that you have not set, maybe you could post it again.

Markus

|||

Hello:

Here is the link from the tutorial.

http://www.asp.net/QuickStart/aspnet/doc/data/default.aspx

Please read the Deploy your local database section.

You mentioned you had problem to access the site from the browser.

Which one IIS or the built-in web browser? what is your url looks like?

Also, I think the user we are talking about isyourmachinename\ausername.

If you cannot figure it out, I will start over on my machine to figure it out.

Good luck(sorry for the delay, I was locked out for a while on this site.)

|||

This thread has an answer.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

|||

Hi limno,

Many thanks for all your replies. I finally got it running and the problem was where we searched. In IIS I chose the application-pool of my domain and assigned as identity "local system". After that I also had to change sql-server to run under local system. Now it is working, however I'm not quite sure if I will use that because of Security reasons. If I understand correctly, it now would be possible to get access to the whole file-system through script and probably more. Beside all users/domains will be able to access all databases within the instance of sql server.

So the next big step would be to get sql server running under the standard IWPD user of the domain and also to include some username/password security through the connection-string.

Is that possible?

Markus

|||

Hello:

FYI:

<connectionStrings>

<add name="conString1" connectionString="Data Source=yourServer;Integrated Security=false;Initial Catalog=YourDB;User ID=yourDBUser;Password=thePassword" providerName="System.Data.SqlClient" />

<add name="conString2" connectionString="Data Source=yourServer;Initial Catalog=yourDB;Integrated Security=True" providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer" />

</connectionStrings>

<roleManager enabled="true"
defaultProvider="CustomizedRoleProvider">
<providers>
<clear />
<add name="CustomizedRoleProvider"
type="System.Web.Security.SqlRoleProvider"
connectionStringName="conString1"
applicationName="/"
/>
</providers>
</roleManager>

<membership defaultProvider="CustomizedMembershipProvider">
<providers>
<clear />
<add name="CustomizedMembershipProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="conString2"
applicationName="/"
requiresUniqueEmail="false"
minRequiredPasswordLength="1"
minRequiredNonalphanumericCharacters="0"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"

/>
</providers>
</membership>

you need create your database user in your own database first.

Assume you merged the ASPNETDB.MDF into yourDB and you are using customized prociders which point back to the default ones for membership and roles.

The applicationName is important. You can type your application name in it. You can search for explainations.

Hope this can save you a little bit time.

how to run queries??

Hi,

I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.

i want to write my own querywithout help ofsqlDataSource control from start something like

sqldatasource con=new sqldatasource;

con.connection String=""

then what all things will come...... ?

and please give me some poitners to some articles which help one to do the requested.

Regards,

Please checkheresql

How to run multiple jobs in parallel

I have five SQL jobs that I want to run at the same time to save time.
Running them in serialization costs me lot of time so running in parallel is
choice I have. However, they all need to run after 5 DBs are restored. So, I
created one job that does all the restore for these 5 DBs one after another
on nightly basis. So far so good. Now I want to run all these 5 jobs in
parallel so that they all start at the same time to save me lot of time.
Example: I have total of 6 jobs.
Restore All DBs
Step 1: Restore DBCA
Step 2: Restore DBNY
Step 3: Restore DBNJ
Step 4: Restore DBTX
Step 5: Restore DBHI
I have other 5 jobs like this:
1. ProcessDBCA
2. ProcessDBNY
3. ProcessDBNJ
4. ProcessDBTX
5. ProcessDBHI
I need to start running these 5 jobs in parallel as soon as all 5 steps from
job 1 are finished.
Any suggestions.
Thanks in advance.DTS Package can be used to run activities in parallel. So, you can create a
DTS Package, and execute it from the SQL Job.
"Mark" wrote:

> I have five SQL jobs that I want to run at the same time to save time.
> Running them in serialization costs me lot of time so running in parallel
is
> choice I have. However, they all need to run after 5 DBs are restored. So,
I
> created one job that does all the restore for these 5 DBs one after anothe
r
> on nightly basis. So far so good. Now I want to run all these 5 jobs in
> parallel so that they all start at the same time to save me lot of time.
> Example: I have total of 6 jobs.
> Restore All DBs
> Step 1: Restore DBCA
> Step 2: Restore DBNY
> Step 3: Restore DBNJ
> Step 4: Restore DBTX
> Step 5: Restore DBHI
> I have other 5 jobs like this:
> 1. ProcessDBCA
> 2. ProcessDBNY
> 3. ProcessDBNJ
> 4. ProcessDBTX
> 5. ProcessDBHI
> I need to start running these 5 jobs in parallel as soon as all 5 steps fr
om
> job 1 are finished.
> Any suggestions.
> Thanks in advance.|||Thanks for the response.
Any sample code or direction to a whitepaper will be really helpful
"Absar Ahmad" wrote:
> DTS Package can be used to run activities in parallel. So, you can create
a
> DTS Package, and execute it from the SQL Job.
> "Mark" wrote:
>|||There is plenty of information in BOL. Please search for following topics:
DTS, overview
Creating DTS Package Objects and Connections
Creating DTS Package Workflow and Tasks
You can also look for the sub-titles under the following titles in the BOL:
DTS packages
DTS Designer
Best Wishes,
Absar
"Mark" wrote:
> Thanks for the response.
> Any sample code or direction to a whitepaper will be really helpful
> "Absar Ahmad" wrote:
>

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guys
really understand my problem. if u have any doubt reply me. thanks in advance.
-suresh
No. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas

"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-suresh

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guys
really understand my problem. if u have any doubt reply me. thanks in advance.
-sureshNo. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas
"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-sureshsql

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guy
s
really understand my problem. if u have any doubt reply me. thanks in advanc
e.
-sureshNo. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas
"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-suresh

How to return time & number format that has set in the regional setting using stored proce

How to return time & number format that has set in the regional setting using stored procedure.

Following is my sp for getting current date format from Sql Server.

if exists

(select*fromsysobjectswhereid =object_id(N'[HSP_GetDateFormat]')andOBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure[HSP_GetDateFormat]

create procedure

HSP_GetDateFormat

(

@.strDateFormat nvarchar(64) out,

@.iErr

intout

)

as

begin

set nocount on

set@.strDateFormat = (selectdateformat frommaster..syslanguageswherelangid = (selectvaluefrommaster..sysconfigureswherecomment = 'default language'))

set@.iErr = @.@.Error

set nocount off

end

Now, I want to know what would I write if I want to get currenttime &numberformat from Sql Server.

Hi,

From your description, it seems that you want to get the current date time in stored procedure, right?

TSQL has provided several built-in functions which is used for datetime and datetiem calculating. You can get the current date and time by using GetDate() function, also, you can use such functions like DATEADD,DATEDIFF to calculate the date and time. See the following codes:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))

Besides, if you want to output the value, just create a datetime typed variable and set it as OUTPUT, and then create a parameter in .NET side, which shares the same type, set the direction as OUTPUT, then you can receive the datetime from your stored procedure. As for the number type, you can use INT,FLOAT,DOUBLE and etc in your stored procedure.

Thanks.

how to retun results from Stored Proc

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

Quote:

Originally Posted by Talghagin

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

try a function instead|||

Quote:

Originally Posted by Talghagin

Hi to all

I would need some help with mySQL 2005 Stored Proc. This is the first time that i'm using them so sry if i seem a bit noobish.
I would need to return a set of rows by using an sql statment.
Can some1 tell me one i need to change in my code in order to return the rows?

Thanks

this is the code ...

USE [MPS_TEST2]
GO
/****** Object: StoredProcedure [dbo].[spMachine_Get_By_Model_ID] Script Date: 07/19/2007 14:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spMachine_Get_By_Model_ID]

(@.Model_ID bigint)

AS
SELECT Machine_ID
FROM tblMachine

WHERE(tblMachine.Machine_Model_ID = @.Model_ID)

Return

I think if you take out the Return at the end then stored procedure will return the result of the query.

How To Retrive Data If There Are Large No. Of Datas In Table(by Minimum Time Conusum]

I Have One Module , In Which I Have To Find Perticular Person's Data .
It Is Easily To Get But Here Large No. Of Data I Have, I Know That It Is Possible But It Is Very Much Time Conusuming, So, Any Other Way To Retrive Value By Minimum Time Consuming

Reply As Soon As Possible
Thanks,
Rahul

Rahul_nadiad@.yahoo.com

Quote:

Originally Posted by RAHULPRAJAPATI

I Have One Module , In Which I Have To Find Perticular Person's Data .
It Is Easily To Get But Here Large No. Of Data I Have, I Know That It Is Possible But It Is Very Much Time Conusuming, So, Any Other Way To Retrive Value By Minimum Time Consuming

Reply As Soon As Possible
Thanks,
Rahul

Rahul_nadiad@.yahoo.com

Hi Rahul,
What is the module? How is the data stored ie what is the structure?|||

Quote:

Originally Posted by RAHULPRAJAPATI

I Have One Module , In Which I Have To Find Perticular Person's Data .
It Is Easily To Get But Here Large No. Of Data I Have, I Know That It Is Possible But It Is Very Much Time Conusuming, So, Any Other Way To Retrive Value By Minimum Time Consuming

Reply As Soon As Possible
Thanks,
Rahul

Rahul_nadiad@.yahoo.com

Hi there,

Please give a simple & clearer problem statement as it would ease in providing solution. Take care.

How To Retrive Data If There Are Large No. Of Datas In Table(by Minimum Time Conusum

MY PROJECT IS HOSPITAL MANAGEMENT SYSTEM
AND THERE ARE ABOUT 700 PATIENT DAILY VISIT THE HOSPITAL/ ADDING NEW PATIENT(LARGE HOSPITAL).
SO, LARGE NUMBER OF PATIENTS ARE THERE. NOW SUPPOSE AFTER FEW YEARS THERE ARE ABOUT 70000000 PATIENTS RECORDS IN DATABASE , THEN TO FETCHNIG SOME PATIENT'S DETAILS IS VERY TIME CONSUMING [ BY SELECT * FROM PATIENT]
THEN WHAT SHOULD I DO? ANY ONE HAVE ANY IDEA?

AND ONE MORE THING I WOULD LIKE TO KNOW THAT HOW THE IMAGES WILL STORE IN DATABASE ACCESS?
PLS REPLY SOON

RAHUL PRAJAPATI
rahul_nadiad@.yahoo.com

Quote:

Originally Posted by RAHULPRAJAPATI

MY PROJECT IS HOSPITAL MANAGEMENT SYSTEM
AND THERE ARE ABOUT 700 PATIENT DAILY VISIT THE HOSPITAL/ ADDING NEW PATIENT(LARGE HOSPITAL).
SO, LARGE NUMBER OF PATIENTS ARE THERE. NOW SUPPOSE AFTER FEW YEARS THERE ARE ABOUT 70000000 PATIENTS RECORDS IN DATABASE , THEN TO FETCHNIG SOME PATIENT'S DETAILS IS VERY TIME CONSUMING [ BY SELECT * FROM PATIENT]
THEN WHAT SHOULD I DO? ANY ONE HAVE ANY IDEA?

AND ONE MORE THING I WOULD LIKE TO KNOW THAT HOW THE IMAGES WILL STORE IN DATABASE ACCESS?
PLS REPLY SOON

RAHUL PRAJAPATI
rahul_nadiad@.yahoo.com

Hi there,

The structure of the database isteslf can play a major role is enabling fast access, heard of database normalization? Why not use database normalization to restructure your current database.

In relational database theory, normalization is the process of restructuring the logical data model of a database to eliminate redundancy, organize data efficiently, reduce repeating data and to reduce the potential for anomalies during data operations. Data normalization also may improve data consistency and simplify future extension of the logical data model. The formal classifications used for describing a relational database's level of normalization are called normal forms

Kindly refer to below link for further reading & understanding, hope it helps.

http://en.wikipedia.org/wiki/Database_normalization
You can use Binary large object (BLOB technique in order to be able to safe graphics and other related material in database.

Good luck & Take care.|||Hi Sashi,
I am also a newbie to these things.
My understanding is that, data normalization eases the adding the data and managing the constraints.
But normalisation results in more number of tables than without (or lower level of) normalisation.
So doesnt it mean that it slows the data access as it requires to join more number of tables.

Please correct me if I am wrong.

-Subhash.

Quote:

Originally Posted by sashi

Hi there,

The structure of the database isteslf can play a major role is enabling fast access, heard of database normalization? Why not use database normalization to restructure your current database.

In relational database theory, normalization is the process of restructuring the logical data model of a database to eliminate redundancy, organize data efficiently, reduce repeating data and to reduce the potential for anomalies during data operations. Data normalization also may improve data consistency and simplify future extension of the logical data model. The formal classifications used for describing a relational database's level of normalization are called normal forms

Kindly refer to below link for further reading & understanding, hope it helps.

http://en.wikipedia.org/wiki/Database_normalization
You can use Binary large object (BLOB technique in order to be able to safe graphics and other related material in database.

Good luck & Take care.

how to retrieve values from four tables at a time

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

Quote:

Originally Posted by sudhashekhar30

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar|||

Quote:

Originally Posted by sanbala

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.|||

Quote:

Originally Posted by sudhashekhar30

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.

Hi sudha
i coundn't get you.what you trying to asking.
specify clearly...

by
sankar.b
bsankarit@.gmail.com

how to retrieve random records from table

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

Quote:

Originally Posted by pritisarode

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

select top 10 * from table order by newid(), mydate