Hello,
I have to filegroups like
database_data1 stored on d:
database_data2 stored on e:
How can I delete the database_data2 and tell SQL 2005 to copy all
data to database_data1?
Thanks for any help in advance!
A. KlemtOne way...
Assuming you have clustered indexes on all you tables is to drop and
recreate the index on the database_data1 filegroup this will also move
all the data over to database_data1
http://sqlservercode.blogspot.com/|||Hi
It is pretty much described in the BOL under "Moving database file"
"Andreas Klemt" <aklemt68@.hotmail.com> wrote in message
news:Oxkia5$JGHA.3200@.tk2msftngp13.phx.gbl...
> Hello,
> I have to filegroups like
> database_data1 stored on d:
> database_data2 stored on e:
> How can I delete the database_data2 and tell SQL 2005 to copy all
> data to database_data1?
> Thanks for any help in advance!
> A. Klemt
>
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
how to make this stored procedure more performant
ALTER PROCEDURE dbo.sp_AddProdutionPlanning
(
@.ProductionPlanning_Product uniqueidentifier,
@.ProductionPlanning_Date datetime,
@.ProductionPlanning_Quantity int
)
AS
Begin TRAN
Declare @.NewID uniqueidentifier;
Declare @.NewItemID uniqueidentifier;
Declare @.NewItemBatchID uniqueidentifier;
Declare @.Ingr uniqueidentifier;
Declare @.IngrQty int;
Declare @.Batch uniqueidentifier;
Declare @.BtchQty int;
Declare @.BtchConsumed int;
Declare @.MixQty int;
Declare @.QtyLeft int;
Set @.NewID= newid();
INSERT INTO ProductionPlanning
(ProductionPlanning_ID, ProductionPlanning_Product, ProductionPlanning_Date, ProductionPlanning_Quantity, ProductionPlanning_Created,
ProductionPlanning_PreviousUpdate, ProductionPlanning_LastUpdated, ProductionPlanning_Deleted)
VALUES (@.NewID,@.ProductionPlanning_Product,@.ProductionPlanning_Date,@.ProductionPlanning_Quantity,getdate(),getdate(),getdate(),0)
Select NULL mykey, ProductRecipe_ID, ProductRecipe_Ingredient, ProductRecipe_Quantity into #tblRecipe From ProductRecipe Where ((ProductRecipe_Product = @.ProductionPlanning_Product) And (ProductRecipe_Deleted = 0))
Set rowcount 1;
Update #tblRecipe Set mykey=1;
While @.@.Rowcount <>0
Begin
Set rowcount 1;
Select @.Ingr=ProductRecipe_Ingredient,@.IngrQty = ProductRecipe_Quantity From #tblRecipe;
Delete from #tblRecipe where mykey=1;
Set @.MixQty = (@.IngrQty * @.ProductionPlanning_Quantity);
Set @.NewItemID = newid();
INSERT INTO ProductionPlanningItem
(ProductionPlanningItem_ID, ProductionPlanningItem_Ingredient, ProductionPlanningItem_Planning, ProductionPlanningItem_Quantity)
VALUES (@.NewItemID,@.Ingr,@.NewID,@.MixQty)
Select NULL mykey,ProductIngredientBatch_ID, ProductIngredientBatch_Quantity, ProductIngredientBatch_Consumed Into #tblBatch From ProductIngredientBatch Where ProductIngredientBatch_Ingredient = @.Ingr And ProductIngredientBatch_Deleted = 0;
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Set rowcount 1;
Update #tblBatch set mykey=1;
While @.@.rowcount <>0
Begin
set rowcount 1;
Select @.Batch=ProductIngredientBatch_ID, @.BtchQty=ProductIngredientBatch_Quantity, @.BtchConsumed = ProductIngredientBatch_Consumed From #tblBatch
delete from #tblBatch where mykey=1
Set @.NewItemBatchID = newid();
Set @.QtyLeft = @.BtchQty - @.BtchConsumed;
if(@.QtyLeft > @.MixQty)
Begin
INSERT INTO ProductionPlanningItemBatch
(ProductionPlanningItemBatch_ID, ProductionPlanningItemBatch_Item, ProductionPlanningItemBatch_Batch, ProductionPlanningItemBatch_Quantity)
VALUES (@.NewItemBatchID,@.NewItemID,@.Batch,@.MixQty)
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Update ProductIngredientBatch Set ProductIngredientBatch_Consumed = (@.BtchConsumed + @.MixQty) Where ProductIngredientBatch_ID = @.Batch
if (@.@.ERROR <> 0) Goto ERR_HANDLER
End
else
Begin
INSERT INTO ProductionPlanningItemBatch
(ProductionPlanningItemBatch_ID, ProductionPlanningItemBatch_Item, ProductionPlanningItemBatch_Batch, ProductionPlanningItemBatch_Quantity)
VALUES (@.NewItemBatchID,@.NewItemID,@.Batch,@.QtyLeft)
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Update ProductIngredientBatch Set ProductIngredientBatch_Consumed = 0 Where ProductIngredientBatch_ID = @.Batch
if (@.@.ERROR <> 0) Goto ERR_HANDLER
End
if (@.@.ERROR <> 0) Goto ERR_HANDLER
Print @.Batch;
UPDATE ProductIngredient
SET ProductIngredient_Consumed = ProductIngredient_Consumed + @.MixQty
WHERE (ProductIngredient_ID = @.Ingr)
if (@.@.ERROR <> 0) Goto ERR_HANDLER
set rowcount 1;
update #tblBatch set mykey=1
End
Drop table #tblBatch;
Set rowcount 1;
update #tblRecipe set mykey=1
End
Commit Tran
RETURN 0
ERR_HANDLER:
Print 'An error occured';
ROLLBACK TRAN
RETURN 1
Given that nobody has taken a stab at this, I will give you some pointers. Yes, it is possible to rewrite the SP using set-based logic. Start with correlated subqueries that can maintain the running total. Using CTEs in SQL Server 2005 also is an option.
|||And perhaps you could use TRY / CATCH blocks to obtain a better error handlingWednesday, March 28, 2012
How to make tables & stored procedures in MSDE?
Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.
The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.
What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?
And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database.
My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?
Thanks a lot for your help! Have a good weekend!
Henry Li
There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net
http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994
|||Hello Ken,Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.
Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)
And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?
I hope my questions are not too many. Thanks a lot in advance for your help!
Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||
Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".
I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:
Build started: Project: test1, Configuration: Debug .NET
Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.
- Done -
Build: 0 succeeded, 1 failed, 0 skipped
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.
Thanks & Best regards,
Henry Li
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.
You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?
Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.
http://msdn.microsoft.com/vstudio/express/sql/
Here is a link to the free management studio for sql server 2005 express
http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--
I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.
By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.
Thanks a lot again!
Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.
Stevan
|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good!
Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!
Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window.
Now I don't use the VB.NET package anymore except if I need to create some windows exe files.
Thank you for your kindness!
Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!
Best wishes,
Henry|||
Hi,
You can generate tables with osql command, i just need to indicate the command as following:
osql -U user -S server\instancename
then
1> create database test
2> GO
1>use test
2>GO
then you're inside of test database and you are ready to generate your table according you're needed
How to make tables & stored procedures in MSDE?
Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.
The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.
What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?
And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database.
My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?
Thanks a lot for your help! Have a good weekend!
Henry Li
There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net
http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994
|||Hello Ken,Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.
Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)
And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?
I hope my questions are not too many. Thanks a lot in advance for your help!
Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||
Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".
I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:
Build started: Project: test1, Configuration: Debug .NET
Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.
- Done -
Build: 0 succeeded, 1 failed, 0 skipped
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.
Thanks & Best regards,
Henry Li
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.
You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?
Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.
http://msdn.microsoft.com/vstudio/express/sql/
Here is a link to the free management studio for sql server 2005 express
http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--
I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.
By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.
Thanks a lot again!
Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.
Stevan|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good!
Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!
Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window.
Now I don't use the VB.NET package anymore except if I need to create some windows exe files.
Thank you for your kindness!
Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!
Best wishes,
Henry|||
Hi,
You can generate tables with osql command, i just need to indicate the command as following:
osql -U user -S server\instancename
then
1> create database test
2> GO
1>use test
2>GO
then you're inside of test database and you are ready to generate your table according you're needed
How to make Stored Proc Changes
Hi,
Just moved to SQL2005 from SQL 2000. Thought I would drop some bad habits along the move. With SQL 2000, when I had stored proc changes to take to the client, I would open EnterpriseManager on my laptop while plugged into their network. I would open their database on their network, and the database on my laptop that had their changes, and cut and post the changes. Really want to stop that.
Script as > Alter to > File, and make a file of it. Or choose Create To if it is a new one.
Just moved to SQL2005 from SQL 2000. Thought I would drop some bad habits along the move. With SQL 2000, when I had stored proc changes to take to the client, I would open EnterpriseManager on my laptop while plugged into their network. I would open their database on their network, and the database on my laptop that had their changes, and cut and post the changes. Really want to stop that.
Now, with SQL2005, I see it wants me to modify the SP, then run it as a script.
What is the best way for me to take stored proc changes from my SQL2005 server in the office, and get them to the clients SQL2005 server?
Thanks
option 1. Script your procedures in the office with create and drop options
option 2. Edit the procedure and change the "Create Procedure" to "Alter Procedure" (Consistent since SQL 7, 6.5 ....)
Hope this helps
GDR
|||So for option 1., I would make the change in the office, then right click on the sp, chooseScript as > Alter to > File, and make a file of it. Or choose Create To if it is a new one.
Then what do I do when I get to the customers with my .sql files?
Thanks
|||..and, can I do the same thing with table changes?
|||You can either script the object one by one, or create a script right-clicking on the database and choosing script. After selecting the appropiate options which apply to you, you will get a Database script which can be run at the customer site to create a new database. if you just want to deploy changes to the customersite, you will have to only apply the procedures (which can be applied no matter if data is in the database or not) and the update scripts of the tables. YOu should not use the "Drop first" then "create" the table from scratch option, because data would be wiped out during the drop command. E.g. if you add another column in your database you would create an "update" script which only would apply the additional changes to the database like adding another column ALTER TABLE SomeTable Add SomeColumn VARCHAR(50).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
sql
How to make more than One Page Report in Sql Server Reporting Services 2005
i have a stored procedure that return a single row and more than 100 colums data. i want to show that columns more than one page.
how can i design my report when i preview the report it shows the data on 2 page
DO you want to display the pages next to each other or do you want to display the second page under the first page ? For the first one simply extend the page size to the double size. For the second choice place multiple detail rows on the page until all data is displayed.
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
Monday, March 26, 2012
how to make all Updates\ Inserts in the database Upper case?
I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
> I have a Sql server 2000 database which is already in production. The
client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application.
Does the client really care how it is STORED? You could easily create views
for all the relevant tables which perform UPPER() on varchar columns, and
this would be much easier than using triggers on all the tables to do the
convert on entry, IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)
|||You can do this in several places. Edit your client side code, so that it
converts all the input data to upper case, and then passes to the database.
Another option is to edit your stored procedures, so that they convert the
input data to upper case, before inserting/updating tables.
Yet another option is to have a trigger that converts the data to upper
case, but not really recommended, because of performance implications.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
|||thanks for your help Narayana .
I already did consider these 3 options but i am at a point where with the no
of the database table, stored procs and application, it would take me a long
time to do any kind of programming to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. Again please let me know if there
is any option or any other way other than the once you have metioned which
when i set would force all the data comming in to Upper case.
thanks, waiting for you reply
sameer
"Narayana Vyas Kondreddi" wrote:
> You can do this in several places. Edit your client side code, so that it
> converts all the input data to upper case, and then passes to the database.
> Another option is to edit your stored procedures, so that they convert the
> input data to upper case, before inserting/updating tables.
> Yet another option is to have a trigger that converts the data to upper
> case, but not really recommended, because of performance implications.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sameer" <sameer@.discussions.microsoft.com> wrote in message
> news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
> I have a Sql server 2000 database which is already in production. The client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application. Is there any
> system Database option in the database which when set to On would save all
> the data being stored to UPPER CASE.
> thanks
> Sameer
>
>
|||thanks for your help Aaron.
I already did consider this option but i am at a point where with the no of
the database table, stored procs and application, it would take me a long
time to do any kind of developement to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. again, please let me know if there
is any such switch in the database.
thanks
sameer
"Aaron [SQL Server MVP]" wrote:
> client
> Does the client really care how it is STORED? You could easily create views
> for all the relevant tables which perform UPPER() on varchar columns, and
> this would be much easier than using triggers on all the tables to do the
> convert on entry, IMHO.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||No, there are case-sensitive collations, but there is no global switch that
will force all data to upper case, sorry.
Tell the client if they really want this that there will be development time
involved.
http://www.aspfaq.com/
(Reverse address to reply.)
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:53744680-6679-4B86-A90B-51B80FEFA47F@.microsoft.com...
> thanks for your help Aaron.
> I already did consider this option but i am at a point where with the no
of
> the database table, stored procs and application, it would take me a long
> time to do any kind of developement to implement any of these 3 options.
And
> that is why i was curious if there is any Option in the database like the
> 'case-sesitive' Coalition which when set to ON would convert all the data
> being stored in the datase to Upper case. again, please let me know if
there[vbcol=seagreen]
> is any such switch in the database.
> thanks
> sameer
> "Aaron [SQL Server MVP]" wrote:
Upper[vbcol=seagreen]
views[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
> I have a Sql server 2000 database which is already in production. The
client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application.
Does the client really care how it is STORED? You could easily create views
for all the relevant tables which perform UPPER() on varchar columns, and
this would be much easier than using triggers on all the tables to do the
convert on entry, IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)
|||You can do this in several places. Edit your client side code, so that it
converts all the input data to upper case, and then passes to the database.
Another option is to edit your stored procedures, so that they convert the
input data to upper case, before inserting/updating tables.
Yet another option is to have a trigger that converts the data to upper
case, but not really recommended, because of performance implications.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
|||thanks for your help Narayana .
I already did consider these 3 options but i am at a point where with the no
of the database table, stored procs and application, it would take me a long
time to do any kind of programming to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. Again please let me know if there
is any option or any other way other than the once you have metioned which
when i set would force all the data comming in to Upper case.
thanks, waiting for you reply
sameer
"Narayana Vyas Kondreddi" wrote:
> You can do this in several places. Edit your client side code, so that it
> converts all the input data to upper case, and then passes to the database.
> Another option is to edit your stored procedures, so that they convert the
> input data to upper case, before inserting/updating tables.
> Yet another option is to have a trigger that converts the data to upper
> case, but not really recommended, because of performance implications.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sameer" <sameer@.discussions.microsoft.com> wrote in message
> news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
> I have a Sql server 2000 database which is already in production. The client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application. Is there any
> system Database option in the database which when set to On would save all
> the data being stored to UPPER CASE.
> thanks
> Sameer
>
>
|||thanks for your help Aaron.
I already did consider this option but i am at a point where with the no of
the database table, stored procs and application, it would take me a long
time to do any kind of developement to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. again, please let me know if there
is any such switch in the database.
thanks
sameer
"Aaron [SQL Server MVP]" wrote:
> client
> Does the client really care how it is STORED? You could easily create views
> for all the relevant tables which perform UPPER() on varchar columns, and
> this would be much easier than using triggers on all the tables to do the
> convert on entry, IMHO.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||No, there are case-sensitive collations, but there is no global switch that
will force all data to upper case, sorry.
Tell the client if they really want this that there will be development time
involved.
http://www.aspfaq.com/
(Reverse address to reply.)
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:53744680-6679-4B86-A90B-51B80FEFA47F@.microsoft.com...
> thanks for your help Aaron.
> I already did consider this option but i am at a point where with the no
of
> the database table, stored procs and application, it would take me a long
> time to do any kind of developement to implement any of these 3 options.
And
> that is why i was curious if there is any Option in the database like the
> 'case-sesitive' Coalition which when set to ON would convert all the data
> being stored in the datase to Upper case. again, please let me know if
there[vbcol=seagreen]
> is any such switch in the database.
> thanks
> sameer
> "Aaron [SQL Server MVP]" wrote:
Upper[vbcol=seagreen]
views[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
Friday, March 23, 2012
How to make a search engine to search my database
hi there,
i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out
can anyone help?
thanks
When you say "help", do you mean provide assistance with errors that you are encountering on something you have already started, or do you mean "do it for you"? In other words, what have you done so far?
Also, what database are you using? Which language do you use?
|||
If you are using SQL Server or SQLEXPRESS both have FullText Search indexing built in. If there is no native FullText search in some other RDBMS that you are using then try DotLucene.
|||i am using SQL Server 2005..
i used to study .asp and now i have to change to .aspx and i find tat everything is very different..
i am just starting to do the search engine thing..
and i need some guide... =/
|||The principals are the same in terms of the SQL. Generally, you will use LIKE and wildcard characters %.
Beyond that, drag a textbox and button onto a form, then a GridView and a SqlDataSource. The GridView needs to be bound to the datasource, which should be set up to connect to your database, and run the SQL that will fetch the search results. Follow some of the Quick Start tutorials or videos to get the hang of working with the new controls.http://www.asp.net/learn/
i manage to make everything and when i test query in the sql server it works..
but when i type in the keyword in the text box and i press submit it dint work.. i have done before the dropdown list where they have this autopostback thing.but for the text how do i code the submit button?
ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.ClickDim searchAsString = TextBox1.Text
SqlDataSource1.Select("search")GridView1.DataBind()
|||
try this, worked for me
<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"
SelectCommand="SELECT * FROM [table] WHERE ([field] LIKE '%' + @.field1 + '%')
<SelectParameters>
<asp:ControlParameterControlID="TextBox2"DefaultValue="%"Name="field1"PropertyName="Text"/></SelectParameters>
</asp:AccessDataSource>
Wednesday, March 21, 2012
How to loop through a table?
Hi,
I have a stored procedure, SPUpdateStatus, with a parameter,
InstanceID.
InstanceID is a column in a table, "BatchInstance". I'll need to loop
through table, "BatchInstance", and execute SPUpdateStatus on each
InstanceID. What's the syntax for the loop?
Thanks!On 21 Mar, 15:35, "Curious" <fir5tsi...@.yahoo.com> wrote:
> Hi,
> I have a stored procedure, SPUpdateStatus, with a parameter,
> InstanceID.
> InstanceID is a column in a table, "BatchInstance". I'll need to loop
> through table, "BatchInstance", and execute SPUpdateStatus on each
> InstanceID. What's the syntax for the loop?
> Thanks!
Why not just amend SPUpdateStatus or create a new proc so that it can
perform the same operation on all rows at once? That way you won't
need a slow, complex and cumbersome loop to do the job.
If you aren't willing or able to do that then lookup the DECLARE
CURSOR syntax in Books Online. There are lots of reasons why cursors
are almost always a bad idea for data manipulation operations. You
should carefully consider the alternatives first.
--
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
--
I have a stored procedure, SPUpdateStatus, with a parameter,
InstanceID.
InstanceID is a column in a table, "BatchInstance". I'll need to loop
through table, "BatchInstance", and execute SPUpdateStatus on each
InstanceID. What's the syntax for the loop?
Thanks!On 21 Mar, 15:35, "Curious" <fir5tsi...@.yahoo.com> wrote:
> Hi,
> I have a stored procedure, SPUpdateStatus, with a parameter,
> InstanceID.
> InstanceID is a column in a table, "BatchInstance". I'll need to loop
> through table, "BatchInstance", and execute SPUpdateStatus on each
> InstanceID. What's the syntax for the loop?
> Thanks!
Why not just amend SPUpdateStatus or create a new proc so that it can
perform the same operation on all rows at once? That way you won't
need a slow, complex and cumbersome loop to do the job.
If you aren't willing or able to do that then lookup the DECLARE
CURSOR syntax in Books Online. There are lots of reasons why cursors
are almost always a bad idea for data manipulation operations. You
should carefully consider the alternatives first.
--
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
--
How to loop through a table?
Hi,
I have a stored procedure, SPUpdateStatus, with a parameter,
InstanceID.
InstanceID is a column in a table, "BatchInstance". I'll need to loop
through table, "BatchInstance", and execute SPUpdateStatus on each
InstanceID. What's the syntax for the loop?
Thanks!On 21 Mar, 15:35, "Curious" <fir5tsi...@.yahoo.com> wrote:
> Hi,
> I have a stored procedure, SPUpdateStatus, with a parameter,
> InstanceID.
> InstanceID is a column in a table, "BatchInstance". I'll need to loop
> through table, "BatchInstance", and execute SPUpdateStatus on each
> InstanceID. What's the syntax for the loop?
> Thanks!
Why not just amend SPUpdateStatus or create a new proc so that it can
perform the same operation on all rows at once? That way you won't
need a slow, complex and cumbersome loop to do the job.
If you aren't willing or able to do that then lookup the DECLARE
CURSOR syntax in Books Online. There are lots of reasons why cursors
are almost always a bad idea for data manipulation operations. You
should carefully consider the alternatives first.
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
--sql
I have a stored procedure, SPUpdateStatus, with a parameter,
InstanceID.
InstanceID is a column in a table, "BatchInstance". I'll need to loop
through table, "BatchInstance", and execute SPUpdateStatus on each
InstanceID. What's the syntax for the loop?
Thanks!On 21 Mar, 15:35, "Curious" <fir5tsi...@.yahoo.com> wrote:
> Hi,
> I have a stored procedure, SPUpdateStatus, with a parameter,
> InstanceID.
> InstanceID is a column in a table, "BatchInstance". I'll need to loop
> through table, "BatchInstance", and execute SPUpdateStatus on each
> InstanceID. What's the syntax for the loop?
> Thanks!
Why not just amend SPUpdateStatus or create a new proc so that it can
perform the same operation on all rows at once? That way you won't
need a slow, complex and cumbersome loop to do the job.
If you aren't willing or able to do that then lookup the DECLARE
CURSOR syntax in Books Online. There are lots of reasons why cursors
are almost always a bad idea for data manipulation operations. You
should carefully consider the alternatives first.
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
--sql
Monday, March 19, 2012
How to locate the database folder in sql 2005
We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.
The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).
For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.
If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.
The code is t-sql so you will have to write your own C to do the same thing
|||We tried profiler but we didn't find something to help us.
Thank you Anyway.
|||Perhaps this will point you in the right direction:
SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'
A little string manipulation and you should be fine.
|||Thank you but this is for a database already attached.|||So..., if the database is not attached, then you can put it just about anywhere you wish.
There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?
All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.
|||I don't know the folder and the name of the database. That is why I want to locate it.|||Remove the WHERE clause and get a list of all databases attached to the server, and their locations.
SELECT
name,
physical_name
FROM sys.master_files
Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.
Did I say, really, really, lucky...
Perhaps adding the following WHERE clause:
WHERE name = db_name()
But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:
SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()
This last query should give you the logical database name, the filename, and the filepath.
|||
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
|||GeoB wrote:
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
That would have been GOOD information to have earlier.
Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.
|||When use SQL Server Management Studio UI you do know where the databases are stored ok?
I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png
|||
If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog
However there's no guarantee that all database and log files will be found at these locations.
Chris
How to locate the database folder in sql 2005
We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.
The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).
For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.
If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.
The code is t-sql so you will have to write your own C to do the same thing
|||We tried profiler but we didn't find something to help us.
Thank you Anyway.
|||Perhaps this will point you in the right direction:
SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'
A little string manipulation and you should be fine.
|||Thank you but this is for a database already attached.|||So..., if the database is not attached, then you can put it just about anywhere you wish.
There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?
All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.
|||I don't know the folder and the name of the database. That is why I want to locate it.|||Remove the WHERE clause and get a list of all databases attached to the server, and their locations.
SELECT
name,
physical_name
FROM sys.master_files
Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.
Did I say, really, really, lucky...
Perhaps adding the following WHERE clause:
WHERE name = db_name()
But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:
SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()
This last query should give you the logical database name, the filename, and the filepath.
|||
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
|||GeoB wrote:
Verry verry sorry..
I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.
That would have been GOOD information to have earlier.
Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.
|||When use SQL Server Management Studio UI you do know where the databases are stored ok?
I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png
|||
If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog
However there's no guarantee that all database and log files will be found at these locations.
Chris
Monday, March 12, 2012
How to list stored procs that use a given table
I'm sure there's an easy way to do this, if I knew my way around the data
dictionary. What is the best way to get a list of the stored procedures that
use a given table?
Also, is there a good reference for learning to use the data dictionary?
Much obliged,
Geoff.Geoff,
What you want is a list of objects (like stored procs) that are dependent on
a certain table. Don't count on SQL Server doing this for you very well. A
known "issue" with SQL Server is it's inability to maintain dependencies
accurately. Take a look at Largo SQL Tools. It does this for you without
relying on any dependency information stored by SQL Server. Take a look at
http://www.largosqltools.com/Dependency.asp.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Geoff Pennington" <penningtong@.stic2.com> wrote in message
news:OBNH2gqUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> I'm sure there's an easy way to do this, if I knew my way around the data
> dictionary. What is the best way to get a list of the stored procedures
that
> use a given table?
> Also, is there a good reference for learning to use the data dictionary?
> Much obliged,
> Geoff.
>|||Thank you. That should do very nicely.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O2qXJqqUDHA.1316@.TK2MSFTNGP12.phx.gbl...
> You can get this info with sp_depends:
> EXEC sp_depends 'MyProc'
> Note that dependency information is maintained correctly only when
> objects are created in dependency order. Consequently, the data will be
> incomplete if a dependent stored procedure was created before the table
> or if the table was recreated after the proc was created.
> You might also check out the INFORMATION_SCHEMA views. These are
> documented in the Books Online and contain a good deal of information
> about schema.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Geoff Pennington" <penningtong@.stic2.com> wrote in message
> news:OBNH2gqUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> > I'm sure there's an easy way to do this, if I knew my way around the
> data
> > dictionary. What is the best way to get a list of the stored
> procedures that
> > use a given table?
> >
> > Also, is there a good reference for learning to use the data
> dictionary?
> >
> > Much obliged,
> > Geoff.
> >
> >
>|||Or even easier:
DECLARE @.tablename SYSNAME
SET @.tablename = 'TSVFiles'
CREATE TABLE #procs
(
procname SYSNAME,
type VARCHAR(32)
)
INSERT #procs
EXEC sp_depends @.tablename
SELECT procname FROM #procs WHERE type='stored procedure'
-- if you want to include functions, leave out the where clause
DROP TABLE #procs
dictionary. What is the best way to get a list of the stored procedures that
use a given table?
Also, is there a good reference for learning to use the data dictionary?
Much obliged,
Geoff.Geoff,
What you want is a list of objects (like stored procs) that are dependent on
a certain table. Don't count on SQL Server doing this for you very well. A
known "issue" with SQL Server is it's inability to maintain dependencies
accurately. Take a look at Largo SQL Tools. It does this for you without
relying on any dependency information stored by SQL Server. Take a look at
http://www.largosqltools.com/Dependency.asp.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Geoff Pennington" <penningtong@.stic2.com> wrote in message
news:OBNH2gqUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> I'm sure there's an easy way to do this, if I knew my way around the data
> dictionary. What is the best way to get a list of the stored procedures
that
> use a given table?
> Also, is there a good reference for learning to use the data dictionary?
> Much obliged,
> Geoff.
>|||Thank you. That should do very nicely.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O2qXJqqUDHA.1316@.TK2MSFTNGP12.phx.gbl...
> You can get this info with sp_depends:
> EXEC sp_depends 'MyProc'
> Note that dependency information is maintained correctly only when
> objects are created in dependency order. Consequently, the data will be
> incomplete if a dependent stored procedure was created before the table
> or if the table was recreated after the proc was created.
> You might also check out the INFORMATION_SCHEMA views. These are
> documented in the Books Online and contain a good deal of information
> about schema.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Geoff Pennington" <penningtong@.stic2.com> wrote in message
> news:OBNH2gqUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> > I'm sure there's an easy way to do this, if I knew my way around the
> data
> > dictionary. What is the best way to get a list of the stored
> procedures that
> > use a given table?
> >
> > Also, is there a good reference for learning to use the data
> dictionary?
> >
> > Much obliged,
> > Geoff.
> >
> >
>|||Or even easier:
DECLARE @.tablename SYSNAME
SET @.tablename = 'TSVFiles'
CREATE TABLE #procs
(
procname SYSNAME,
type VARCHAR(32)
)
INSERT #procs
EXEC sp_depends @.tablename
SELECT procname FROM #procs WHERE type='stored procedure'
-- if you want to include functions, leave out the where clause
DROP TABLE #procs
How to list sp perms?
I query information_schema.table_privileges for permissions to tables
What do I query to get list of all permsissions grants on stored procedures
and udf's?
tia
chrisChris
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:3303F0F9-082F-48B1-AD48-0406FDB03761@.microsoft.com...
> I query information_schema.table_privileges for permissions to tables
> What do I query to get list of all permsissions grants on stored
> procedures
> and udf's?
> tia
> chris
What do I query to get list of all permsissions grants on stored procedures
and udf's?
tia
chrisChris
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:3303F0F9-082F-48B1-AD48-0406FDB03761@.microsoft.com...
> I query information_schema.table_privileges for permissions to tables
> What do I query to get list of all permsissions grants on stored
> procedures
> and udf's?
> tia
> chris
How to list atributes of all tables in Query Analyzer
Hi newsgroup
I need to know the properties of all tables in a given database on MSSQL
7.0. I know that there is a stored procedure with the name "sp_MShelpcolumns"
as well as another one called "sp_MSforeachtable". How can I combine these
two to get a result that looks like: tablename, columnname, datatype, size.
The result is something that I would like to copy and paste in Excel to send
it to a business analyst.
Any input is highly appreciated.
Kind regards
Gabriel
You want this ?
select table_name,column_name,Data_type,Character_maximum _length from
information_schema.columns
Thanks
"Gabriel Martin" <GabrielMartin@.discussions.microsoft.com> wrote in message
news:152308E6-5A4B-486E-9AF1-1735A1158881@.microsoft.com...
> Hi newsgroup
> I need to know the properties of all tables in a given database on MSSQL
> 7.0. I know that there is a stored procedure with the name
"sp_MShelpcolumns"
> as well as another one called "sp_MSforeachtable". How can I combine these
> two to get a result that looks like: tablename, columnname, datatype,
size.
> The result is something that I would like to copy and paste in Excel to
send
> it to a business analyst.
> Any input is highly appreciated.
> Kind regards
> Gabriel
I need to know the properties of all tables in a given database on MSSQL
7.0. I know that there is a stored procedure with the name "sp_MShelpcolumns"
as well as another one called "sp_MSforeachtable". How can I combine these
two to get a result that looks like: tablename, columnname, datatype, size.
The result is something that I would like to copy and paste in Excel to send
it to a business analyst.
Any input is highly appreciated.
Kind regards
Gabriel
You want this ?
select table_name,column_name,Data_type,Character_maximum _length from
information_schema.columns
Thanks
"Gabriel Martin" <GabrielMartin@.discussions.microsoft.com> wrote in message
news:152308E6-5A4B-486E-9AF1-1735A1158881@.microsoft.com...
> Hi newsgroup
> I need to know the properties of all tables in a given database on MSSQL
> 7.0. I know that there is a stored procedure with the name
"sp_MShelpcolumns"
> as well as another one called "sp_MSforeachtable". How can I combine these
> two to get a result that looks like: tablename, columnname, datatype,
size.
> The result is something that I would like to copy and paste in Excel to
send
> it to a business analyst.
> Any input is highly appreciated.
> Kind regards
> Gabriel
How to list all tables and their size
Is there a stored procedure to list all tables and their size, and / or
number of rows
Is there a stored procedure to list all indexes and their size
Check this link:
How do I get a list of SQL Server tables and their row counts?
http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
"Zack" wrote:
> Is there a stored procedure to list all tables and their size, and / or
> number of rows
> Is there a stored procedure to list all indexes and their size
>
>
number of rows
Is there a stored procedure to list all indexes and their size
Check this link:
How do I get a list of SQL Server tables and their row counts?
http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
"Zack" wrote:
> Is there a stored procedure to list all tables and their size, and / or
> number of rows
> Is there a stored procedure to list all indexes and their size
>
>
How to list all tables and their size
Is there a stored procedure to list all tables and their size, and / or
number of rows
Is there a stored procedure to list all indexes and their sizeCheck this link:
How do I get a list of SQL Server tables and their row counts?
http://sqlserver2000.databases.aspf...row-counts.html
"Zack" wrote:
> Is there a stored procedure to list all tables and their size, and / or
> number of rows
> Is there a stored procedure to list all indexes and their size
>
>|||The final solution in that article uses DBCC UPDATEUSAGE(0). This is fine,
but here are two further suggestions you might want to take into
consideration:
a) DBCC UPDATEUSAGE(0) updates sysindexes for all indexes on all tables in a
database. This can take a long time on a large production database & lead to
major performance degradation if used during peak processing periods. Just
make sure you schedule this for periods of low activity or keep a close eye
on the server whilst its running if users are using the database at the
time.
b) DBCC UPDATEUSAGE(0) alone doesn't update the rowcount for each table -
you have to add the additional "with count_rows" option if you want an
update of how many rows are in the table. Keep in mind that this figure will
always be slightly out of date on a busy server thoug. I generally don't use
it, but it's worth knowing all the same
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
< EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in message
news:57C92A87-D9DE-46AE-A45C-39D1DF9A272E@.microsoft.com...[vbcol=seagreen]
> Check this link:
> How do I get a list of SQL Server tables and their row counts?
> http://sqlserver2000.databases.aspf...row-counts.html
> "Zack" wrote:
>
number of rows
Is there a stored procedure to list all indexes and their sizeCheck this link:
How do I get a list of SQL Server tables and their row counts?
http://sqlserver2000.databases.aspf...row-counts.html
"Zack" wrote:
> Is there a stored procedure to list all tables and their size, and / or
> number of rows
> Is there a stored procedure to list all indexes and their size
>
>|||The final solution in that article uses DBCC UPDATEUSAGE(0). This is fine,
but here are two further suggestions you might want to take into
consideration:
a) DBCC UPDATEUSAGE(0) updates sysindexes for all indexes on all tables in a
database. This can take a long time on a large production database & lead to
major performance degradation if used during peak processing periods. Just
make sure you schedule this for periods of low activity or keep a close eye
on the server whilst its running if users are using the database at the
time.
b) DBCC UPDATEUSAGE(0) alone doesn't update the rowcount for each table -
you have to add the additional "with count_rows" option if you want an
update of how many rows are in the table. Keep in mind that this figure will
always be slightly out of date on a busy server thoug. I generally don't use
it, but it's worth knowing all the same
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
< EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in message
news:57C92A87-D9DE-46AE-A45C-39D1DF9A272E@.microsoft.com...[vbcol=seagreen]
> Check this link:
> How do I get a list of SQL Server tables and their row counts?
> http://sqlserver2000.databases.aspf...row-counts.html
> "Zack" wrote:
>
How to list all tables and their size
Is there a stored procedure to list all tables and their size, and / or
number of rows
Is there a stored procedure to list all indexes and their sizeCheck this link:
How do I get a list of SQL Server tables and their row counts?
http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
"Zack" wrote:
> Is there a stored procedure to list all tables and their size, and / or
> number of rows
> Is there a stored procedure to list all indexes and their size
>
>|||The final solution in that article uses DBCC UPDATEUSAGE(0). This is fine,
but here are two further suggestions you might want to take into
consideration:
a) DBCC UPDATEUSAGE(0) updates sysindexes for all indexes on all tables in a
database. This can take a long time on a large production database & lead to
major performance degradation if used during peak processing periods. Just
make sure you schedule this for periods of low activity or keep a close eye
on the server whilst its running if users are using the database at the
time.
b) DBCC UPDATEUSAGE(0) alone doesn't update the rowcount for each table -
you have to add the additional "with count_rows" option if you want an
update of how many rows are in the table. Keep in mind that this figure will
always be slightly out of date on a busy server thoug. I generally don't use
it, but it's worth knowing all the same
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:57C92A87-D9DE-46AE-A45C-39D1DF9A272E@.microsoft.com...
> Check this link:
> How do I get a list of SQL Server tables and their row counts?
> http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
> "Zack" wrote:
>> Is there a stored procedure to list all tables and their size, and / or
>> number of rows
>> Is there a stored procedure to list all indexes and their size
>>
number of rows
Is there a stored procedure to list all indexes and their sizeCheck this link:
How do I get a list of SQL Server tables and their row counts?
http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
"Zack" wrote:
> Is there a stored procedure to list all tables and their size, and / or
> number of rows
> Is there a stored procedure to list all indexes and their size
>
>|||The final solution in that article uses DBCC UPDATEUSAGE(0). This is fine,
but here are two further suggestions you might want to take into
consideration:
a) DBCC UPDATEUSAGE(0) updates sysindexes for all indexes on all tables in a
database. This can take a long time on a large production database & lead to
major performance degradation if used during peak processing periods. Just
make sure you schedule this for periods of low activity or keep a close eye
on the server whilst its running if users are using the database at the
time.
b) DBCC UPDATEUSAGE(0) alone doesn't update the rowcount for each table -
you have to add the additional "with count_rows" option if you want an
update of how many rows are in the table. Keep in mind that this figure will
always be slightly out of date on a busy server thoug. I generally don't use
it, but it's worth knowing all the same
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:57C92A87-D9DE-46AE-A45C-39D1DF9A272E@.microsoft.com...
> Check this link:
> How do I get a list of SQL Server tables and their row counts?
> http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
> "Zack" wrote:
>> Is there a stored procedure to list all tables and their size, and / or
>> number of rows
>> Is there a stored procedure to list all indexes and their size
>>
How to list (Export to txt) the Built-In and User Store Procedures
Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
Luis
Hi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
Luis
Hi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
How to list (Export to txt) the Built-In and User Store Procedures
Good afternoon,
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
LuisHi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
How can I export a list of Built-In and User Made Stored Procedures on an
SQL-2005 server ?
Regards,
LuisHi
How about querying sys.objects? If you want to do this manually save the
output from SSMS or use output option for SQLCMD, or use a query with BCP!
John
"gatox" wrote:
> Good afternoon,
> How can I export a list of Built-In and User Made Stored Procedures on an
> SQL-2005 server ?
> Regards,
> Luis
Subscribe to:
Posts (Atom)