Generate Db Script In Plain Sql
Apr 26, 2007Hello. I am really new to sql server. I have a db in the server that I use for testing. i want to export it in plain sql so as to import it in another db system. How can I do it?
thank you a lot.
ADVERTISEMENT
Sp_password In Plain Text
Oct 1, 2007Greetings all.
I am currently working to improve the security on a legacy application we have at my company. The app was written in vb6 years ago. We now have the app running against a sql 2005 server. One of the function/screens in the application is used to administrate users. (each user has a sql user id) and one of the functions is to reset the password. The vb code uses a call to sp_password. Here is the problem. We setup a network sniffer and found the command being in plain text. While the user logon is encrypted ( SSL Fallback) the sp_password commands issued by the app are plan text. Anyone know of a way to make this encrypted?
Leif
Convert RTF To Plain Text
Jul 23, 2005I have a SQL Server 2000 table with a few fields of "text" data typethat contain rich text. I have to downstream this data and therecipient cannot handle rich text. I need to figure out a way toconvert it back to plain text. Any suggetions?TIA
View 4 Replies View RelatedPlain Text Mails
Apr 19, 2007How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.
View 5 Replies View RelatedPlain Text Rendering - Again!
Nov 29, 2007Hello List,
sorry to raise this again here. I have seen a lot of posts on this board about plain text rendering but never really a satisfactory answer. So my question is: Has anyone succeeded in developing a custom rendering extension which is able to render a report as plain text?
I am a Technical Lead in a project to convert and migrate reporting from Unix/C to Reporting Services 2005. One requirement is, to render the reports in plain text for archiving purposes. I know that there are a million options out there to archive in PDF and whatever. Well, we need plain text. No way around it! It is a governmental requirement!
I am about to pull the plug on SSRS 2005 just because we are not able to fulfill this requirement. And we are talking about a big project converting >700 reports!
Challenges we are facing:
Reporting components are rendered as they appear in a compnent list, one after each other. If you now have two multiline components side by side, i.e. two lists, the first list component in the array gets rendered and then the other. This makes it almost impossible to render the lines in those two components side by side. In the sample below, the Report Header would be rendered, then the Address block, then the Other Data block, then the Order History Table and then the Account Statement table.
Positioning of text boxes is almost impossible. We are able to find out how many centimeters/inches/millimeters/points a control is located from the left border. But how can we translate this into i.e. how many spaces will we need to add for padding?
The column width can't be determined. SSRS adds a line break if the cell text is longer than the column width.
We have reports which look something like this:
Code Block
Code Block
REPORT NAME
MANAGER NAME
ACCOUNT
REPORT DATE
First Name: XXXXXXXXXXXX Other Data: XXXXXXXXXXXXXX
Last Name: XXXXXXXXXXXX More Data: XXXXXXXXXXXXXX
Address: XXXXXXXXXXXX More Data: XXXXXXXXXXXXXX
City: XXXXXXXXXXXX
Order History Account Statement
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
I also got some advice that SSRS is probably the wrong tool. And I also read this in some of the "Plain Text" posts here. And I am actually surprised about this. People suggest to look for some other tool and not use i.e. email subscrptions, other rendering formats, SSRS security model, snapshot history, report models and all the other goodies only because we can't render in simple text?
If anyone has any information to share, I would very much appreciate it. We also looked at third party tools and plug ins but couldn't find any! If anyone knows of a company who would be able to help us, let me know!
I really would love to pull this project off with SSRS 2005 because I Iike the technology and SSRS provides a lot of functionality we want to leverage. I believe SSRS is the right tool for us and it covers 95% of our business requirements! But at this stage it doesn't look very rosy! Because of the Plain Text rendering!
Cheers
Peter
Copy Within A Table - Plain SQL
Aug 9, 2006Hi.
I'm quit new into SQL - have a problem - and hope someone here ca n help me.
I have a table TABLE1 with different fields. A part of it looks like this:
FIELD1 FIELD2 FIELD3
111111 121212 0
111111 121212 91
222222 232323 0
222222 232323 73
etc. etc.
I want to "copy"/write over '0' in FIELD3 with the number in FIELD3 from the line that has the same numbers (and unique for these two lines) in FIELD1 and FIELD2. The table will then look like this:
FIELD1 FIELD2 FIELD3
111111 121212 91
111111 121212 91
222222 232323 73
222222 232323 73
I can make a temporary table or a new field if necessary. I just don't know how to write the right SQL.
Thankful if anyone can help me.
T-SQL (SS2K8) :: CLR / RTF To Plain Text Not Working?
Sep 23, 2014Trying to troubleshoot an CLR/ RTF to Plain Text issue I am having.
I have 3 instances on one SQL server. Only one of the Instances is not working. Even tried deleting everything and resetting it up.
Here is the code I am testing with:
USE Test_Database
GO
DECLARE @RTF varchar(max)
SET @RTF = '{
tfansiansicpg1252uc1deff0deflang1033{fonttbl{f0 Calibri;}{f1 Arial;}}{colortbl
ed0green0blue0 ;
ed255green255blue255 ;}viewkind4paperw12240paperh15840margl1425margr1425margt1425margb1425sectdpgwsxn12240pghsxn15840
marglsxn1425margrsxn1425margtsxn1425margbsxn1425pardfs21sl276slmult1sa180{f1fs21
Test New Progress Notes - Praveen}par}';
SELECT dbo.clr_fn_ConvertRTF2PlainText(@RTF)
Here is what I am getting back:
The operation completed successfully
Not sure what to check as the other two instances are working fine.
I have CLR enabled in sp_Configure
clr enabled 0 1 11
Limit Joins (or Plain Improve Qry)
Jun 3, 2008Hi,
I got a qry that takes 10s. This is off course too long.
As I described in my earlier posts, I'm no sql hero, but I'm on a neverending quest to improve.
Currently I need a select statement over several (7) tables and return fields from almost all of them.
Furthermore I have optional restrictions on fields spread over all those tables, based on a set of parameters.
What I initially do is inner join all my tables and then have a big where clause that adds where-rules depending on the arguments given.
Like so:
select c.clr_id, n.clr_nm, co.long_nm, p.paint_cd, a.appl_desc, m.modl_nm, y.yr_num
from CLR c
inner join paint_cd p
on c.clr_id = p.clr_id
inner join clr_use_yr y
on y.clr_id = c.clr_id
inner join co
on co.co_id = c.co_id
inner join modl m
on y.modl_id = m.modl_id
inner join clr_appl ca
on c.clr_id = ca.clr_id
inner join appl a
on ca.appl_cd = a.appl_cd
inner join clr_nm n
on c.clr_id = n.clr_id
WHERE
(CASE @regn when '' THEN '' ELSE n.regn_cd END) like (CASE @regn when '' THEN '' ELSE @regn END)
AND (CASE @co when '' THEN '' ELSE co.long_nm END) like (CASE @co when '' THEN '' ELSE @co END)
AND (CASE @clrNm when '' THEN '' ELSE n.clr_nm END) like (CASE @clrNm when '' THEN '' ELSE @clrNm END)
AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END)
AND (CASE @applDc when '' THEN '' ELSE a.appl_desc END) like (CASE @applDc when '' THEN '' ELSE @applDc END)
AND (CASE @useYear when '' THEN '' ELSE cast(y.yr_num as varchar(4)) end) like (CASE @useYear when '' THEN '' else @useYear END)
AND (CASE @modlNm when '' THEN '' ELSE m.modl_Nm END) like (CASE @modlNm when '' THEN '' ELSE @modlNm END)
It appeared to me that by getting those where clauses in the join, I would limit the calculations. So the joins where similar to this:
inner join paint_cd p
on c.clr_id = p.clr_id
AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END)
The result though, was exactly the same.
So, where in my thinking am I wrong and what is the correct way to go about this?
SQL Queries - Kristen - In Plain English.
Oct 16, 2007Good Morning Kristen:
Thank You, You are the first person, that has given me a clear answer. I am going to explain in plain English What this SP is suppose to do.
I have to find any station_nbr that has more than one group_code and that the current date = (DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) The current date starts with the first day of the previous month.
Example today is October 10, 2007 I need September 01, 2007 for this query.
To insert the record it needs to be active meaning that the current date is in between Beg_eff_date and end_eff_date, and the end_eff_date is = ‘12/31/3000’
I need to write SP(cursor) that reads line by line this table and that insert the answer: INSERT INTO #TP_TIES_OVerlaping_Dates.
Example:
Current_date: 09/01/2007
Group_Code Station_nbr beg_eff_date end_eff_date
0000003 00371101 03/12/2006 12/31/3000
0000016 00371101 07/08/2007 12/31/3000
The above explanation is for the first type of Error.
The second Type of Error:
If the Group Code ends with "W" and the End_effective_date = '12/31/3000' and the W_Beg_date is Part of the Temp Table(I to create the W_Beg_Date Column) -- is GREATER than the End_eff_date. I want to Insert this Record into the Table. I need to set sometype of Record Control to make the different between one error to another one.
I thank you so much for your help.....I hope this is clear to understand for everybody.
End
SQL Function To Convert RTF To Plain Text
Jul 23, 2005Does anyone have the logic to convert RTF formatted data in a textcolumn into plain ascii text that I can use in a varchar variable orfield?We have an app that allows formatted comments/notes to be stored in aSQL 2000 text column. Ideally, I would like a trigger that would copyany inserted comments into a seperate table, varchar field so thatthese are viewable in other apps that can not display formatted RTF.I would really like the logic to be all SQL based and not have to use aclient app to read/convert/insert the data in a batch mode.
View 1 Replies View RelatedXp_smtp_sendmail Html+plain Text
Jul 20, 2005Hi all. Iv'e tryed out xp_smtp_sendmail, and I like what I can see sofar. The thing I wonder about is if the xp supports sending both htmlAND plain text in the same mail. I'm on a sql2000 sp3 and I have noproblem with the xp when i either send plain text or html, but asstated above I need to send with both formats in one mail......For use when one does not know if the receiver uses a mail-client thatsupports html or not. If not does anyone know of a good way to attackthis problem?thanks in advanceKarl B
View 1 Replies View RelatedCalling Stored Procedures Using Plain ADO In C#
Sep 7, 2006Hello All,
We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.
Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:
ADODB.Parameter param = new ADODB.Parameter();
param=cmd.CreateParameter("@StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);
cmd.Parameters.Append(param);
ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );
In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.
Sincerely,
Dwight Kulkarni
Microsoft Great Plain Integration Application And New Tables On Sql
Jul 28, 2006In dexterity iam using table_compare() function to check if tables in datasource and dictionary are same but its not picking up the primary key change but it does pick up the column change
any suggestion
thanks
Is It Possible To Import Data From Plain Text File In Integration Services
Feb 21, 2008
hi,
I am new to Integration services.I have one query ,Is it possible to import the data from text file in integration services.
I know that we can import the data from excel sheet and we can export it to table.But my question is whether we can do the same thing from the text file.If anyone come acroos the same thing send u r possible answers.Your help is much appreciated.
Thanks in advance.
Convert Plain Text To Reach Text
May 29, 2008Can some one help me convert plain text to reach text format using t-sql
I am on MSSQL 2005
Thanks
Error Message: Failed To Generate A User Instance Of SQL Server. Only An Integrated Connection Can Generate A User Instance
Mar 3, 2008Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies View RelatedGenerate .sql ?
Jan 9, 2007Hi!
When MS published starter kits there were files .sql in App_Data. This files contained some sample data for a DB. How to create such files when I have database with data ?
Jarod
Generate Xml
Oct 20, 2007how generate an xml file from data in sqlserver2005
and read it in sql again
Generate FKs
Nov 20, 2002One of my first tasks on my current project was to generate all of the SQL scripts needed to rebuild the database. Of course, one of the catches was that everything needed to be separated out (table columns in different script from PKs different from FKs different from DEFAULTs, etc., etc.). And we wanted our own comment block inserted. So, I couldn't just use the Generate SQL Script from Enterprise Manager and be done with it. I was going to need to do some more work. Well, here's one of the scripts I wrote to build the FK scripts. Execute this script, then copy & paste the results into a new window. Break apart into separate .sql files as desired.
NOTE: Be sure to show results in Text (not grid) and change the display options in QA to return more than just 256 characters.
/***************************************************************************************************
Author: Mark Caldwell
Date: 11/15/2002
Descrip: Generate scipt commands to ADD FOREIGN KEY CONSTRAINTS for constraints already in DB.
NOTE: Be sure to set your Tools/Options/Results/Maximum Characters per column to a
large enough number (such as 2000) to display the entire command.
***************************************************************************************************/
SELECT
'----------------------------------------------------------------------------------------------------
-- ' + so2.name + ' to ' + so3.name + '
----------------------------------------------------------------------------------------------------
IF OBJECTPROPERTY(OBJECT_ID(N''' + so1.name + '''), ''IsForeignKey'') = 1 BEGIN
ALTER TABLE ' + so2.name + ' DROP CONSTRAINT ' + so1.name + '
PRINT '' -- DRP - ' + so1.name + '''
END
G' + 'O
ALTER TABLE ' + so2.name + '
ADD CONSTRAINT ' + so1.name + '
FOREIGN KEY (' + ISC1.COLUMN_NAME + ')
REFERENCES ' + so3.name + '(' + ISC2.COLUMN_NAME + ')
PRINT '' -- ADD - ' + so1.name + '''
G' + 'O
'
FROM sysforeignkeys sfk
JOIN sysobjects so1 on sfk.constid = so1.id
JOIN sysobjects so2 on sfk.fkeyid = so2.id
JOIN sysobjects so3 on sfk.rkeyid = so3.id
JOIN INFORMATION_SCHEMA.COLUMNS ISC1 on so2.name = ISC1.TABLE_NAME AND sfk.fkey = ISC1.ORDINAL_POSITION
JOIN INFORMATION_SCHEMA.COLUMNS ISC2 on so3.name = ISC2.TABLE_NAME AND sfk.rkey = ISC2.ORDINAL_POSITION
ORDER BY so2.name
Edited by - AjarnMark on 11/20/2002 02:46:11
Generate Sql Script?
Oct 22, 2006The web site I am building is working fine locally, but I am hitting some problems with setting it up on a remote hosting server.First off, how can I generate the sql script to populate the SQL db on the remote server?I am using VS 2005 Standard. Do I need to d/l the SQL Server Express?Once I get that going, I should be able to figure out the rest...but I'll prolly have another question or two.Thanks
View 2 Replies View RelatedHow To Generate Userid?
Feb 15, 2008Hello everyone.
i need to auto generate the user id in id colunm in my sqldatabase table.i want it to generate in this fashion.(mycompanyname-todaydate-number.)eg (ibm-15thfeb-1) (ibm-15thfeb-2) (ibm-16thfeb-1)here i need this user id to be automatically displayed in my web form when doing registration of new user,then only after clicking the savebutton i want all the data along with user id to be inserted into the table in sqldatabase.thanksjack.
Generate AutoNumber
Jul 16, 2005Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ............ etc. I've the code below but it's not working in the right way, what's wrong with my code?
Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim ssql As String
Dim Itemid As Integer
Dim updcmd As SqlClient.SqlCommand
Itemid = 0
mysqladap = New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn)
Itemid = (Itemid) + 1
ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')"
updcmd = New SqlClient.SqlCommand(ssql, mySqlConn)
updcmd.ExecuteNonQuery()
lblmsg.Visible = True
End SubAnyone can help me? Thanks.
'Generate SQL Scripts'
Apr 2, 2001Are there any scripts available with anyone, for generating SQL scripts for all the objects in a server?
Thanks
Generate Script
Apr 2, 2001I want to transfer some procedures using a generated script. However, the procedures that create pre-existing tables will not be created. Is there a way to shut off the validation so that I can create these procedures without getting errors?
Thanks for the help
DTS - Generate SQL Script
Jun 7, 2002Is there a way to write a dts package that will Generate SQL Scripts for a particular database? I am just learning dts and would like to automate this process that I run on weekly basis.
Thanks,
Steve
Generate Excel From Sp
Nov 13, 2006can I generate excel from stored procedure?
View 2 Replies View RelatedHow To Generate Txt File
May 2, 2006hi 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
Generate A .txt File
Apr 28, 2008This 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
Generate SQL Script
Jul 29, 2005--This procedure will generate the Structure of a table
Create Procedure GenerateScript (
@tableName varchar(100))
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name=@tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')
set @sql=''
Select @sql=@sql
+case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end
+case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+
','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar)
end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end
+case when Data_Type in (Select datatype from @cols)then '' else ')' end
+case when Is_Nullable='No' then ' Null,' else ' Not null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName
select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
Madhivanan
Failing to plan is Planning to fail
Generate Script
Apr 22, 2007i recently moved from sql server 7 to sql 2005 -- in sql 7 if i wanted to generate an sql script then It would generate a whole script ---
in sql 2005 - when i do generate it seems to attach a file --
I want to generate a script that I can then modify and use to create a new db -- is this possible?
Custom Generate IDs
Jun 4, 2007Hi All!
I was thinking of creating a custom generated IDs for my table. I would like the ID to be something like "HR001" or "IT001", the two letter prefix would indicate the dept it belongs to. Initially I thought of having a table that will hold all the seed values for the IDs but I realize that this could have some concurrency problems if there will be a multiple number of users are creating a record at the same time. So now I have totally no idea on how to deal with the concurrency problem.
I will not be using this as a primary key because I already have the Identity field to be my primary key, though of course this field would definitely be unique. I will just be using this to display in the UI.
Any inputs would be greatly appreciated.
Thanks!
Generate SQL-Scripts
Jul 13, 2007Hi,
Do someone know if there is a SP_ or XP_ function for help me create a script of create table? I can do it "on-line" by right click a table in Enterprise-Manager (all properties;generate sql-script), but I want to "call a command" to do this.
Very glad if someone know!
//Lotta
Generate Inserts
Nov 24, 2007is there any easy way I can take a select statment
(such as select from payments where datetime>'20071122' and output a sql insert statment for these records?
I basically need to move a specific set of records from one sql server to another (both sql server 2005)
any suggestions for the best way to do this?