ok...this (going on eight years now) sql server user has a seemingly
very simple problem with his new developers edition of sql server 2005.
I'm trying to script multiple tables and indexes in the mmc...and using
the usual control/shift keys to select multiple objects isn't working.
Is this a bug in the mmc, or am I losing my mind or is there something
I haven't yet seen in bol?
thanks in advance...
As part of a simple maintenance package I am writing, the boss wants it to script out all objects from the database to a text file nightly (or better yet one file per object but that is not a requirement only a "nice to have"). I've been having a surprisingly difficult time determining how to do this.. its simple enough in SSMS but you can't schedule that :) Googling turns up a lot of people asking the same question with no real answers.. SSIS seems geared towards copying all objects to another DB - not just scripting them.
Any ideas?
What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database??
I would like a separate file for each object (table, view, or stored procedure).
Could someone point me in the right direction??
Thanks,
Paul
Hi
I have recently upgraded several of our SQL2005 servers to SP2a :) version 9.0.3050
and find that I can no longer script the Service Broker objects from a remote connection. The error I am getting is:
Script failed for MessageType 'MyMessageType' (Microsoft.SqlServer.Smo)
Additional Information
Either the object or one of its properties is not supported on the target server version
(Microsoft.SqlServer.Smo)
Now I know that this worked pre SP2 using exactly the same setup. Has anyone else experienced this or know how to fix it?
I've checked this site but was unable to find what i needed. Perhaps there is a very simple solutio to this.
I have several databases (development, testing1, testing2, production).
I have recently modified and added some tables and stored procedures in the development database. I would like to push the changes to testing1 database. In SQL Management Studio, it's possible to right-click the a table and create EITHER an alter or create or drop script. The same is for stored procedures. I need to be able to create scripts that checks if the object (table, or stored procedure exists) and either 1) create it, if the object exist or 2) alter it, if the object exists.
Is there a tool that does this? or How can I do this via Management Studio?
Thanks,
Psion
Hi,
I need to transfer data from my test server to the deployment server, is there any way or tool to achieve that. Any help will be much appreciated.
Thanks.
Kabir
Hi All,I am looking to store a DataSet into a Table in SQL Server 2005, or any object for that matter. I cannot find any code to perform. Can anyone help?Many Thanks,Peppa.
View 2 Replies View RelatedHi guys,I am pretty happy with the new system views in SQL 2005 but there isone thing I could not figure out: What is the view that let you seewhat object belongs to what DB? Sorry for the simple question, Cheers,G.
View 3 Replies View Relatedhi
I need to knwo if its possible to easily store / retrieve c# objects in SQL Server 2005 as user defined data types or some other type. We need to be able to store collections of simple objects as a single value and be able to and recreate the c# object. For example one type would be a hashtable of objects that just have 2 public members Date and Value
Any help apprecitaed
Steve
I just upgraded to SQLServer 2005 Standard. I do development in adatabase on one server. When a solution is completely tested, I moveit to the operational database on a different server. With SQLServer2000, I could just open the .adp file and do File->Get ExternalData->Import to move the objects. That does not work with 2005.What is the recommended method for moving objects from one database toanother? Obviously, the objects that changed will not be the sameevery time.Thanks!Jer
View 2 Replies View RelatedWe are trying to use source control to store our database objects andqueries in Perforce. The general idea is to produce scripts whichcreate all objects in the DB and then store the SQL in source control.Has anyone done this before and can suggest some advice? Alternativelyif anyone has other methods for using source control on a database,could you please let me know?Also, is there any automated way of extracting individual scripts forobject creation from the database?
View 2 Replies View RelatedSometimes at the end of a project you'll end up with unused Tables,Stored Procs, Functions, and Views.Since there is not something like a 'SELECT' trigger ... what is thebest way for telling what is not being used by your solution? To turnon some kind of tracing?Any ideas?Thanks,Kelly GreerJoin Bytes!change nospam to yahoo
View 4 Replies View Relatedpassing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
Hi
Can anyone help me out in migrating Oracle stored procedures & Functions which contains collection types (user-defined datatypes like VARRAYS) into SQL Server 2005. How to achieve this taks? This is very important for me and it's very urgent. Please someone help me.
Thanks & Regards
K P Kumar
Hello,
I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.
Can anyone out there help me?
Thanks, Bill
Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
How can I add a reference to the Microsoft excel 2003 in the script tool in SSIS 2005? I need to automate Excel for formatting.
Thank you.
Dan
I'll try to explain this in detail so that it's easily understood. I
sure could use some other eyes on this folks. I need help figuring this
one out. Here is the project...
We have your basic Windows 2003
server environment with XP Pro on the workstations. I'm using a program
called Tag. Yes I know this site did not develop tag but I'm hoping
that this post will get you thinking and hopefully some of you might
like the challenge of helping a fellow developer figure out a technical
problem.
We have mp3's that all have some specific tags based on
date and title for example. They would need to change dynamically on
the fly without human intervention. Here is a generic list of tags we
typically use.
--artist "Company Name Here"
--title "My Test Title"
--year "2006"
--genre "News or whatever here"
--comment "This is a test comment"
The
TITLE, GENRE AND COMMENT would need to change dynamically. The rest are
fixed and generic and dont need to change. I have different folders.
For example:
C:AudioTaggedFolder One
C:AudioTaggedFolder Two
C:AudioTaggedFolder Three
I have the tag.exe and batch files in C:Program FilesTag folder. Here are the batch files I use.
test1.bat
for /f "tokens=1-4 delims=/.- " %%A in ('date /t') do (set Dow=%%A&set MM=%%B&set DD=%%C&set YYYY=%%D)
tag
C:AudioTagged*.mp3 --artist "Company Name Here" --year %YYYY%
--genre "News" --title "Some kind of title here" --comment "This is a
test comment"
mp3tags.txt (flat file that contains the tags)
--artist "Company Name Here"
--title "My Test Title"
--year "2006"
--genre "News or whatever here"
--comment "This is a test comment"
http://www.flickr.com/photos/ericowens_photography/ (when you get here look for a screen shot of an MP3 Tag toward the bottom.
So
if I want this program to run (without human intervention) and put the
tags that pertain only to those mp3's that need them how would I do
this?
Example: C:AudioTaggedFolder One est1.mp3 needs...
--artist "Company Name Here"
--title "Specific Title of it's own here"
--year "2006"
--genre "Specific genre of it's own here"
--comment "This is a test comment"
Can
this be done with VB Script and if so I need help writing it out. Can
SQL Server be used to store the data but then call the script which
then executes the Tag.exe program?
Please anyone's thoughts on this would be greatly appreciated.
Thanks in advance
Hi guys , can I know is that any way to set up some authentication for user access all the objects within the database after login successfully. For example, after access login into the database server and would like to click on certain database (eg : A) , then pop up the user access login page again. Thx
View 7 Replies View Relatedplease help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View RelatedHi all,
I'm going to be getting involved with the creation and reconfiguration of DTS jobs, however I've noticed that they'll require VB Script, which I'm not familiar with.
Without me reading an 1000pp book on learning all uses of VB Script, are there any articles/resources that focus exclusively or distinctly on the most common uses of vb script in databasing?
On a separate note, are there any ready-made sample applications I could download and connect to a SQL database for study purposes?
Thanks in advance,
JB.
I am having immense difficulties trying to transfer tables, sprocs, udfs, etc, from a production SqlServer2000 server to a testing SqlServer2005 server.
Under SS2000 this was easy, using the DTS wizard. Apparently that's no longer an option.
I've tried the Transfer Database, Transfer Sql Server Objects tasks and the wizard in BIDS, and they all fail (for different reasons).
Help!!!!
- Mark
p.s. -- As a side question, why in the world did you guys get rid of the one workhorse tool in Enterprise Manager that made synchronizing databases easy?
The problem is this: In SQL 2000 DTS there was an option for "Copy
Objects and Data Between SQL Servers". However, this option has been
removed in SQL 2005 SSIS. Apparently the only way to do this in SQL
2005 is to create a .DTSX package in SQL Server Business Intelligence
Developement Studio or VS 2005. You do this by creating a new
Integration Service Project and using the Transfer SQL Objects Task.
Within the properties of this task you can select any of the options
that were available in the SQL 2000 DTS export wizard. I have set up a
test package that will copy a stored procedure from one db to another
but I am unable to get it to work. It runs fine but the result is that
the SP is not copied.
I am new to Visual Studio and I think I probably just need help in
know ing how to run a package in SQL Server Management Studio. I was
able to import the package into SSIS in the Management Studio and run
it without errors not with the expected result (the copy of an SP from
one db to another). I'm sure there are people besides me who would like
to have the ability to easily perform ad hoc copies of objects between
SQL servers. If anyone has any experience with using a SSIS package to
do this please help. Thanks!
Anyone who has successfully done this. Do reply. Thanks
View 3 Replies View RelatedHello,
I am sure there will be a simple answer to this but it has got me stumped.
Having to move over to Vista with my new machine so I am having to switch to 2005 version for my development but still upload to a 2000 server.
I have had a look at 2005, like the new Management Studio, however I ahve a couple of problems which I can not find the answer to.
Firstly, the SQL Query Builder, where has it gone? I often have to import/export data from Excel files and used to use the SQL query builder to create my queries. If I want to copy all columns it is fine but if I want to import select columns I find it easier to view a list and then just add the ones I want.
Am I missing something here?
Secondly, copying stored procedures, before when running DTS ther were three options, Copy Tables/Views, Data Using Query and Copy Objects.
I used the copy opbjects a lot as it was a very quick way of transfering a group of tables and stored prcoedures that I had created. This appears to have now been replaced with Copy Database, which copies everthing, can can not be used to copy from SQL2005 to SQL2000.
If I want to copy multiple stored procedures from SQL2005 to SQL2000 how is it done now? I have tried finding out but have not been sucessful.
Any help would be greatly appreciated,
Regards,
Lee
creating a simple package and cant get it to run below is the error. when i check the source 2k db its there see below...
i am clue free of the issus...
SSIS error message
Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=0 description='FK_MoreBDRInformation_dbo_BatchDataReports' is not a constraint. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
source Server DB sysobjects table record
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
FK_MoreBDRInformation_dbo_BatchDataReports 1618573300 F 1 0 0 0 0 86043838 2006-01-31 12:35:41.530 0 0 0 F 0 11 0 2006-01-31 12:35:41.530 0 0 0 0 0 0 0
This is the database structure I setup:
--Main Table
CREATE TABLE [dbo].[tbl_RF_Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[Item] [nvarchar](256) NOT NULL,
)
--Mapping Table
CREATE TABLE [dbo].[tbl_RF_Tags_Map_Items](
[TagID] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_tbl_RF_Tags_Item_Map] PRIMARY KEY CLUSTERED
[Code] ....
This a result set of the Items Map table so far:
TagIDItemID
1284838
1291475
1291480
8284838
8291475
8291480
10284838
10291480
62291475
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
While attempting to script out some multi-server jobs I am receiving this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Script failed for Job 'my job name'. (Microsoft.SqlServer.Smo)
For help, click: <link removed to keep message width to a normal size :)>
------------------------------
ADDITIONAL INFORMATION:
Unable to cast object of type 'System.DBNull' to type 'System.String'. (Microsoft.SqlServer.Smo)
------------------------------
BUTTONS:
OK
------------------------------
This happens on any multi-server job. The reason behind needing to script this is the current database server that acts as our master server for SQL Server jobs is being replaced. I figured it would be easy enough to script out the job on the old server and run it (with minimal modifications) on the new server. Now, if I create any of these jobs manually on the new server and try to script it, it works just fine. Any ideas what might be the issue here?
I've tried doing it via SSMS from multiple servers and the issue persists.
Is there a way in Reporting Services to filter based on a parameter, but get it to use distinct values?
Currently I have a dataset that is grouped on a Branch value and a Team value.
The end-user wants to be able to filter out data based on the branch values, however when I set it up to that field it comes back with:
Branch 1
Branch 1
Branch 1
Branch 2
Branch 2
Branch 3
Branch 3
and the same goes with Team, which is a sub-group of branch. Is there any way I could set this up so that the parameter drop-down will only display distinct values? Also, is it possible to do the same with another parameter based upon the branch parameter.
Ex.
Branch 1 contains Team 01, Team 02, and Team 03
Branch 2 contains Team 04, Team 05
User selects to filter by Branch 1, the Team dropdown should only contain options for further filtering on Team 01, Team 02, and Team 03 not all of them.
Any help is much appreciated.
Does anyone know how to copying database objects and data from Oracle 8 to SQL 2005 ?
View 1 Replies View RelatedHi, I've just been given the task of finding out how to implement a backup procedure for our SQL server databases. Most are running 2000, some 2005.
I'm a programmer, and I'm used to having a DBA to help me! I've seen a few methods on the web involving a stored procedure and running the task from task manager.
I need to backup and restore all the databases in SQL Server 2000 and work out a way of displaying whether or not it was successful.
Can anybody please point me in the right direction as I've no idea how to do any of this really. I guess if I could setup a sproc to loop through the databases that would help, but I'm not sure where to start.
Thanks in advance.
Is there a way to (automatically) remove/disable the first statements like SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON which are generated by modify sp via mms 2014 interface?
--
SET ANSI_NULLS ON
--
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[sp_SendMail] @test INT = 0
AS
begin
--blabla
end
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry