Hello all,
What is an easy way of scripting all the database tables and sprocs automatically to a separate file each one? I can't find this option in SQL Server Management Studio.
Or is any other way of uploading all objects to a Visual Source Safe project that does not require to copy and paste each script into a VSS sql script?
Thanks!
David
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
There is SCPTXFR.EXE script that has an option to create a script for all DB objects in one file or create separate files for each DB object - one for tables, one for SP-s, one for triggers, etc.
Is there a way to generate:
1. Separate script for each stored procedure (not from Enterprise Manager but automatically from command line or otherwise);
2. Script all DB objects excluding stored procedures in one file.
Thank you.
I'll be running SQL v7.0 with raid level 0. The DB will have approx. 250 tables and 1200 indexes.
Can you have the indexes located on a physically separate drive with SQL v7.0 and would this be the best practice?
We have a table that we are using for inserts for every 2mins and it is a very large table.
I am panning to add this table to a separate file and file-group, so that i can keep this file in separate drive.And it will restore files level.
Is there any disadvantage if we create this table in this way.
Getting the following error-message:
OS: Windows Server 2008 R2 Standard
64bit
Office 2010
Power Pivot Tables are fed by Power Query queries.
I was wondering if it is possible to use a generic list object to use as the datasource/data for a report? Right now we use these business objects to display information on our website and would be great it we could re-use those objects and create reports based on them. We are doing some calculations etc in the business layer to build these objects, so if we could re-use, that would be best.
Thanks!
How to create object type in sql server,
i.e in Oracle we can directly create an object TYPE and we can use it in other applications.
What's the equivalent of this object Type in SQL Server
I opened SSMSexpress and attached to my sql 2005 database and automatically created the scripts for creating all the object in a SQL 2000 DB. The objects seem to be created but I did get script messages. Are these messages harmless?thanksMilton
Msg 208, Level 16, State 1, Procedure Requestview, Line 3
Invalid object name 'dbo.requests'.
Msg 15135, Level 16, State 1, Procedure sp_validatepropertyinputs, Line 147
Object is invalid. Extended properties are not permitted on 'dbo.Requestview', or the object does not exist.
Msg 15135, Level 16, State 1, Procedure sp_validatepropertyinputs, Line 147
Object is invalid. Extended properties are not permitted on 'dbo.Requestview', or the object does not exist.
I would like to populate an array with an object. In other words, I would like for each element in the array to consist of an object that has several elements. Below is some code so you can see what I'm talking about:
Function buildObj() Dim countLimit As Integer = 5 Dim mArray() As myObject While reader.Read() For counter = 0 To countLimit - 1 mArray(counter).objVal1 = reader.GetValue(0) 'Error is here mArray(counter).objVal2 = reader.GetValue(1) mArray(counter).objVal3 = reader.GetValue(2) counter += 1 Next End While
Return mArray
End Function
Dim showValArr As Array = buildObj()Response.Write(showValArr(0).objVal1.ToString)
When I build my site, I get the following error:Object reference not set to an instance of an object.
It's pointing to the line that says "Error is here". Any ideas would be very helpful.Thank you!
I just completed creating a replication job between a server at my location, where I have SA privileges, to a subscription server at a location where I have db_owner rights (no SA). When replication runs, the objects get created on the subscriber as userid.object rather then dbo.object. The subscriber permissions were set by clicking on "Replication", right clicking on "Publication", clicking on "Configure Publishing, Subscribers and Distribution...", clicking on Subscribers tab, choosing the subscriber and selecting "Use SQL Server Authentication". How do I cause replication to create the objects as dbo.object if I connect to the subscriber using an id defined as db_owner?
Thanks, Dave
I need to create RDF files dynamically. As one example, I need to output a report that contains an OLAP table from an MDX query and will need to generate the RDL that represents the table on the fly. (Binding to a data source with any built-in control will not even come close to solving the problem. The actual requirements are extremely complex.)
In short... Rather than outputting the RDL XML the hard way is there an object model I can use to construct an RDL document with? Even if it's a bindhind-the-scenese, not-supported library?
Thanks,
Terry
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..
View 9 Replies View RelatedHi all,
I receive data via FTP to our webserver nightly as .txt files and .dic (if anybody is familiar with idx realtor websites, that's what this data is).
I've learned recently that I'm not going to be able to use Access to import or link to this data, so I'm trying to get my feet wet with SQL.
I have been practicing importing text files into SQL db, but I notice that the dts imports everything as varchar 8000, and that you can edit that. I've got a .dic file that accompanies every .txt file that contains definitions of each fieldname, fieldtype & length & I was wondering how to import that data as well, without having to manually retype everything.
I would be happy to email these text files to anybody willing to take a look.
Thanks,
Carrie
Hi,
I try to implement mirroring using certificate authentication.
When I do this query :
create certificate Principal_cert with subject = 'Principal certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_Princ state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication=certificate Principal_cert, encryption = disabled, role = all);
GO
I have this error :
Msg 1088, Level 15, State 1, Line 1
Cannot find the object "Principal_cert" because it does not exist or you do not have permissions.
I have also noticed that the error does not occured when I try this in the master database.
So, I have two questions:
1. Why SQL server does not find a certificate that just been created ?
2. Should the certificate be in the master database or in the database being mirrored ?
Thanks for any help.
CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.
What I want to do here is enable/create a "MODIFY" option on CLR-based objects in Object Explorer.
Option is only supported for assemblies who's source code has been previously loaded into DB via ALTER ASSEMBLY
Upon electing the option, source code gets displayed in editor.
Users alters managed source
User clicks the "Execute" button in the editor
onClick of "Execute" performs following
Alters managed source (not sure if this can be done in the system tables, may have to save .cs/.vb files out to file system first then reupload)
Builds updated source/.dll is built
Executes an ALTER ASSEMBLY statement to "refresh" the dll and it's associated source code file(s)
Now...how the heck do i do this lol! Anyone
I am trying to create an assembly object when a report is initialized and I am getting the error:
[rsCompilerErrorInExpression] The BackgroundColor expression for the textbox 'textbox2' contains an error: [BC30390] 'ReportExprHostImpl.CustomCodeProxy.X' is not accessible in this context because it is 'Private'
In the Code tab, I have:
Dim X As namespace.classname
Protected Overrides Sub OnInit()
X = new namespace.classname()
End Sub
In my color field, I have "=code.X.color"
If I replace the color field with "=namespace.classname.color" and have color be a shared property, then it works. However, I ultimately need the color to be based on individual user settings, so I cannot use the shared property approach.
Any ideas on how to get around this "because it is private" error?
FYI, I am trying to replicate the concept of themes using report formatting information pulled from a database.
I had a SSIS Package which was developed on Beta Version Of SSIS, Now We have Standard Edition Of SSIS(Sql2k5) Installed.
In the Package we have One Flat file Source, One Script Component and A Sql Server as Target, We are programaticaly creating the package, Now When we open this Package in the Designer and Try to Run We get this error
"The script component is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Component Editor by clicking Design Script button to cause binary code to be generated. "
Now when we just open the Script Designer and close it, The package runs.
I found the Error description at this link
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.hresults.dts_e_binarycodenotfound.aspx
but could not find any help on this.
Same thing use to work on Beta version now it is now working.
From my understanding the problem is that in beta version, the script use to run at runtime, Now what they have done is that they generate a compiled code out of script and run.
My problem is that since i am using C# code to generate the package, How Will I Create a Binary Code out of Script.
We are in the process of replacing our primary production server. In the process of determining how SQL server is going to be structured, it has been suggested that I place all current and new indexes on a separate file group. These filegroups would then reside on a separate shelf on the server. What are the pros and cons of doing this?
View 2 Replies View RelatedI have a flat text file with lots HTML tags and corresponding valuesÂ
For exampleÂ
<Near_Side> 5563 </Near_Side>
<Top_Down_Code> Xe345 <Top_Down_Code>Â
So, For example I can use the a functionÂ
ALTER function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as
begin
  declare @textXML xml
  declare @result varchar(max)
  set @textXML = REPLACE( @text, '&', '' );
[Code] ...
To which Select dbo.StripHTML('<Near_Side> 5563 </Near_Side>')  ValueÂ
I'll get 5563
However how would you get the values within the tag itself ? E.g.Â
Value NameÂ
5563 Near_side
Xe345 Â Â Â Top_Down_Code
I was thinking along the Charindex but cant seem to get it right.
hi, guys
How to move one table or a ResultSet to a separate database .sdf file in compact edition?
For example, I have handheld installed the compact edition to do some order works. After the job done, I want move the new table Orders to our server or other place. Of course I don't want to move everything in the .sdf file. So, what is the best way to move table Orders out to a sparate .sdf file? (In my case, I can not use RDA or replication. )
Thanks.
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies View RelatedHow can i format my query so that each piece of data appears on a new separate line? Is there a command for a new line feed? does not work.
thanks.
For example:
a: data
b: data
c: data
a: data
b: data
c: data
I have three tables in data base:
customer
product
sales
And i want to use SSIS package dynamically load data from database into three separate flat file, each table into each file.
I know i got to use for each loop task ADO.Net schema row set enumerator, with OLEDB connection manager, select table name or view name variable from access mode list, but the problem comes, as table name is dynamic then flat file connection is also dynamic, i am using visual studio 2013...
hi all,
how to generate txt file from sql table. I want text file with respective columns without any seperator between columns. and if for an example a column of int having 5 digits but i want it in text file with 10 digits with remaining all as blanks. guide me how to do this.
thnks in adv
This is my first time on sqlteam forum so a big hello to everyone!!
Firstly I am very noive user of sql to say the least, but i have be requested to create a .txt file
Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far, like i said i am a very novice user so try to help me and not condem me for my lack of knowledge!
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not to how to do that and how to generate it in a txt file?!
Keith
Hi everybody
EM has ability to Generate SQL Scripts to file(S)
I traced it with profiler but can not see how Sql server output objects to file(s)
I want to run DTS and sript all procedures to files every night
I don't want to use
xp_cmdshell 'osql -UTheUser myPass-P -Qsp_helptext 'Mystproc -oc:est.txt'
Could it be done another way ?
i want to generate a sql script file along with the data
how to generate it?
I am a Newbie to programming and databases and would like to create a simple program for doing full text searching on a SQL database.
I have downloaded and installed Visual Basic 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express. I have watched several hours of video tutorials and done numerous tutorials. I feel I am making progress and having a lot of fun. My machine is running XP home edition.
I have found the following resource that looks like a good place for me to start with creating a full text search program:
€œSQL Server 2005 Books Online
Item Finder Sample
http://msdn2.microsoft.com/en-us/library/ms160844.aspx
However I am stuck at the beginning with trying to €œgenerate a strong name key file.€? I don€™t know how to €œopen a command prompt€? (I did confess to being a Newbie!!). I searched the database for this forum and found a relevant thread stating that €œSQLCMD is the command-line utility for SQL Server and is included with SQL Server 2005 Express Edition.€? I found the SQLCMD program on my computer but when I opened it, it does appear to provide the options listed below in the tutorial (i.e., €œclick start,€? €œPoint to all programs,€? etc.).
Initially here is what I need based on the first section of the tutorial:
Building the Sample
If you have not already created a strong name key file, generate the key file using the following instructions.
To generate a strong name key file
Open a Microsoft Visual Studio 2005 command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
-- or --
Open a Microsoft .NET Framework command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.
Note:
To determine the folder where samples are located, click theStartbutton, point toAll Programs, point toMicrosoft SQL Server 2005, point toDocumentation and Tutorials, and then clickSamples Directory. If the default installation location was used, the samples are located in <system_drive>:Program FilesMicrosoft SQL Server90Samples.
At the command prompt, run the following command to generate the key file:
sn -k SampleKey.snk
Important:
For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
How do I complete this initial step of generating a strong name key file for the tutorial? I guess I am missing something simple and once I get past this I will be able to complete the tutorial. Any help will be greatly appreciated.
hi i was trying to generate a .xml file by using this command below
DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)
SET @Table ='Authors'
SELECT TOP 1 @Filename = au_fname FROM Authors
SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'
SELECT @FileName
DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)
SET @Table ='Authors'
SELECT TOP 1 @Filename = au_fname FROM Authors
SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'
SELECT @FileName
SET @SQL = 'EXEC master..xp_cmdshell '+ ''''+'bcp'+ ' "SELECT ''au_fname '' AS ''Name/First'', au_lname AS ''Name/Last '' FROM pubs.dbo.authors ORDER BY au_lname FOR XML PATH (''Books''), ROOT(''Authors''), TYPE, ELEMENTS " queryout '+ @FileName + ' -c -SlocalHost -Usa -P'+ ''' '
SELECT @SQL
EXEC(@SQL)
i got an error saying
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'au_fname'.
i could no tfigure it out my mind window is closed.can any one help me put to find a bug.
many many thanks
hi there.
i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?
generate xml and cache it and use the xml file rather going to sql server database?
has anybody have any help on this?
the steps invloved:
1) first generate a xml file from table something like this:
select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?
which one should i use and how do i access after i gnerate a xml file
thanks.
I want to create an excel file with .xls by using VB script.
Because i want to create a DTS package for our requirement. DTS accepts excel files which has .xls extension only.
I need to develop some crystal reports on some .NET ado datasets.This is easy to do if I actually had data to work with. It is mucheaser creating reports with you have data.Therefore, I would like to run the stored procedure that the .NET adodataset is built on and generate an XML file. I could run the projectand try to figure out where the developers are making the call to thestored procedure and insert a line to writetoxmlfile. I would rathernot have to mess with their code.Is there a way working with SQL Server (either query analyzer orenterprise manager, dts, or whatever) that I can generate an xml file.I see that I can run a stored procedure and get an xml style return inquery analyzer, but I don't know how to save that as an actual file.Thanks for the help.Tony
View 3 Replies View Related