Normalizing Using Sql Commands
Aug 28, 2006
Hi
Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?
I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.
thanks in advance
View 11 Replies
ADVERTISEMENT
Oct 1, 2004
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.cmdUpdate = New System.Data.SqlClient.SqlCommand
Me.cmdGetAll = New System.Data.SqlClient.SqlCommand
Me.cmdSelect = New System.Data.SqlClient.SqlCommand
Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection
Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand
Me.OdbcSelect = New System.Data.Odbc.OdbcCommand
Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "this works fine"
'
'cmdUpdate
'
Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _
"City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _
"Holder)"
Me.cmdUpdate.Connection = Me.SqlConnection1
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
'
'cmdGetAll
'
Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData"
Me.cmdGetAll.Connection = Me.SqlConnection1
'
'cmdSelect
'
Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _
"HERE (CertHolder = @CertHolder)"
Me.cmdSelect.Connection = Me.SqlConnection1
Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder"))
'
'OdbcConnection1
'
Me.OdbcConnection1.ConnectionString = "This works fine"
'
'OdbcGetAll
'
Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData"
Me.OdbcGetAll.Connection = Me.OdbcConnection1
'
'OdbcSelect
'
Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _
"HERE CertHolder = @CertHolder"
Me.OdbcSelect.Connection = Me.OdbcConnection1
Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder"))
'
'OdbcUpdate
'
Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _
"City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder"
Me.OdbcUpdate.Connection = Me.OdbcConnection1
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub
Protected WithEvents btnSave As System.Web.UI.WebControls.Button
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList
Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox
Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList
Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand
Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
Protected WithEvents txtState As System.Web.UI.WebControls.TextBox
Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection
Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand
Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand
Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
View 2 Replies
View Related
Nov 8, 2006
Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help
View 6 Replies
View Related
Aug 10, 2005
So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?Here's the table: and here's the site that uses this table:http://waahp.byu.edu/links.aspThanks!~Cattrah~
View 3 Replies
View Related
Feb 24, 2004
I am a beginner, so please bare with me. I get very confused on how to normalize my database.
Firstly: The employees in the company I work for are in various departments and can have more then one title and work in more then one department.
Example: John Smith can work in the engineering department as a detailer and an engineer and at the same time work as a project manager for the management department.
How do I setup this table structure?
Employees Table
Login (PK) | First | Last | Extension.......
---------------------------------------------
jsmith | John | Smith | 280
Department Title Breakdown
Department | Title
--------------------------
Engineering | Detailer
Engineering | Engineer
Management | ProjectManager
Job Description
Login | Title
-------------------------
jsmith | Engineer
jsmith | Detailer
jsmith | ProjectManager
This is important to break this down because for each project the following is saved:
Project Listing
Project | Detailer | Estimator | Sales | Engineer |....... | Location
10001 | jsmith | jdoe | mslick | sjunk | ...... | Las Vegas
Or should the project be broken down as well
Project Listing
Project | Location
10001 | Las Vegas
Project Team
Project | Member | Activity
10001 | jsmith | Engineer
10001 | mstevens | Detailer
Any thoughts on how to normalize this?
Mike B
View 6 Replies
View Related
Jul 13, 2006
I have this table...CREATE TABLE #Test (ID char(1), Seq int, Ch char(1))INSERT #Test SELECT 'A',1,'A'INSERT #Test SELECT 'A',2,'B'INSERT #Test SELECT 'A',3,'C'INSERT #Test SELECT 'B',1,'D'INSERT #Test SELECT 'B',2,'E'INSERT #Test SELECT 'B',3,'F'INSERT #Test SELECT 'B',4,'G'....and am searching for this query....SELECT ID, Pattern=...?? FROM #Test....??....to give this result, where Pattern is the ordered concatenation ofCh for each ID:ID PatternA ABCB DEFGThanks for any help!Jim
View 2 Replies
View Related
Aug 24, 2006
I re-designed a predecessor's database so that it is more properlynormalized. Now, I must migrate the data from the legacy system intothe new one. The problem is that one of the tables is a CROSSTABTABLE. Yes, the actual table is laid out in a cross-tabular fashion.What is a good approach for moving that data into normalized tables?This is the original table:CREATE TABLE [dbo].[Sensitivities]([Lab ID#] [int] NULL,[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
View 5 Replies
View Related
Jun 9, 2006
Hi,
I have a table like this:
Col1 First_Year Last_Year
a 1990 1993
I want this data to be converted to
a 1990
a 1991
a 1992
a 1993
Is there any simple way to do it in SSIS without using Script Component?
Thx
View 3 Replies
View Related
Dec 27, 2003
THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"
The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:
[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix
The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:
[StreetSuffix_T]
SuffixID <-----this is the primary key
Name
For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.
THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?
View 6 Replies
View Related
Nov 7, 2005
I have a view with patient data. It looks like below
patid date pulmdc pulmstatus endodc endostatus
100 4/1/05 10 Good null null
100 5/1/05 10 Good 12 Poor
I want to create sql which by each patient, by date, by these four fields ,get
patid 4/1/05 pulmdc-10 pulmstatus-good
patid 5/1/05 pulmdc-1 pulmstatus-good
patid 5/1/05 endodc-12 endostatus-poor
View 1 Replies
View Related
Apr 27, 2006
Greetings all,
I have created an SSIS package that takes data from a very large table (301 columns) and puts it in a new database in smaller tables. I am using views to control what data goes to the new tables. I also specified that it drop the destination table and recreate it prior to copying the data. The reason for this is so that old data removed from the larger database will get removed from the normalized databases.
I have 2 things I am trying to figure out..
1. I would like to have the package set a specific row in each new table to be the primary key (this will allow us to use relationships when querying the data).
2. I decided I wanted to sort the data as it copies. I am using the BI Visual Studio for my editing. In the Data Flow view I cannot seem to disconnect the output from the Source block so I can connect it to the Sort block and then feed that to the output block. What am I missing here?
Thanks
View 7 Replies
View Related
Jan 23, 2006
Hi!
I have a table with the following columns:
account_nr, account_totaling_members, account_type
the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"
I need to normalize this so that i get records like:
"10", "1001", "sum"
"10", "1001", "sum"
"10", "1002", "sum"
..and so forth
Does anyone have any idea how to accomplish this?
View 3 Replies
View Related
Jun 21, 2006
I am copying data from one denormalized table to a COUPLE of normalized ones.
I am using multicast, following advices from the forum.
The problem I have is that the two destination tables (A and B) are sharing a foreign key relationship.Filling in A is no problem, but when I want to fill in B, I don't know how to populate its foreign key, since the multicast doesn't know the corresponding primary key in table A.
View 9 Replies
View Related
Aug 20, 2015
CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')
The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.
The results I need are:
CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40
I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?
View 1 Replies
View Related
Nov 6, 2007
I'm new to SSIS and have run into a problem I'm hoping someone can help me with.
Basically, I have a flat file that looks something like:
ID,Type,Description,Results
1,Test1,This is a test,5
2,Test1,This is also a 1 test,7
3,Test1,This is also a 1 test,13
4,Test2,This is a second test,14
5,Test2,This is also a second test,18
I'm trying to normalize the data by extracting out individual rows that have the same "Type" column value. So what I want is to extract each unique type and description into a separate table. This would give me two new rows, one for a type of Test1, and one for a type of Test2, with the descriptions. Does this make sense? Then I could relate the individual results to these test types. In my scenario, I don't care which description is used; I just want to take the first description that shows up with the associated "Type."
Does anyone have any idea of how I could go about doing this? I could pull out all unique "Types" from the rows with the Aggregate transformation, but I'm trying to figure out how to get the description that goes along with it.
Thanks,
Brian
View 1 Replies
View Related
May 22, 2015
I have a large data set with 10s of millions of rows of contact information. The data is in CSV format and contains 48 columns of information (First name, MI, last name, 4 part address, 10+ demographic points, etc.) and I'm struggling with how I should design the database and normalize this data, or if I should normalize this data.
My 2 thoughts for design were either:
Break the columns into logical categorical tables (i.e. BasicContactInfo, Demographics, Financials, Interests, etc.) Keep the entire row in one table, and pull out the "Objects" into another table (i.e. ContactInformation, States, ZIPCodes, EmployementStatus, EthnicityCodes, etc.)
The data will be immutable for the most part, and when I get new data, I'll just create a new database and replace the old one.
The reason I like option 1 is because it makes importing easier, since I can just insert the appropriate columns from each row into the appropriate tables. Option number 2 feels like it would be faster to get metrics on the data, like how many contacts live in which states, or what is the total number of unique occupations in the data set. Plus I'll be able to make relationships between the tables, like which state is tied to which zipcode, which city is tied with which county, etc. Importing that data might be more tricky, since I don't think SQL Bulk Copy will allow for inserting into normalized tables like that.
The primary use for this data is to allow our sales force to create custom lists of contact information based on a faceted search page. The sales person would create the filter, and then I will provide them with the resulting data so they can start making business contacts. Search performance needs to be good. Insert, update, and deletes won't happen once the data has been imported.
What should I look for in designing this database? Any good articles on designing tables around wide data sets like my contact information?
View 6 Replies
View Related
Oct 17, 2012
I need to normalise comma separated strings of tags (SQL Server 2008 R2).
E.g. (1, 'abc, DEF, xyzrpt') should become
(1, 'abc')
(1, 'DEF')
(1, 'xyzrpt')
I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.
Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').
If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.
View 2 Replies
View Related
May 28, 2015
I have a script that resolve's data into xml like this, ex:
<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS
View 0 Replies
View Related
Aug 21, 2015
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
View 3 Replies
View Related
Jan 7, 2008
Hi All,
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @cmd, no_output
SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @email, no_output
Thanks.
View 2 Replies
View Related
Jan 7, 2008
Hi All,
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @cmd, no_output
SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @email, no_output
Thanks.
View 2 Replies
View Related
Jan 13, 2004
How can I get all SQL commands in SqlServer? How can I trace this commands?
Thansk
View 1 Replies
View Related
Feb 8, 2006
Hello,
I finally got access to Northwind/pubs.
I would like to know when and why do we use the following set commands. The SQL Server BOL does not say why and when to use these commands.
Thanks in advance!!!
sqlnovice123
Option Default Setting
Set nocount OFF
Set rowcount 0
Set ansi_nulls ON
Set quoted_identifier ON
View 3 Replies
View Related
Nov 29, 2007
Hi guys,I wanna ask bout the problem with my web application. I'm doing a select a statement from table 1 and and with the query results i got, i need it to store the result on table 2. How will i do this? I need your tips and suggestions.
View 1 Replies
View Related
Jan 8, 2006
I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands? MainQuery = "declare @MinGrades as Table(GradeID Bigint) " & _ " INSERT @MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _ " FROM CreditGrades INNER JOIN" & _ " CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _ " WHERE (CreditGrades.x0x30 = - 1 OR " & _ " CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _ " CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _ " CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _ " CreditGrades.x3xNOD >= 0) " & _ " GROUP BY CreditGradeSplits.CreditGradeGroupID" & _ " Select * from @MinGrades "
View 2 Replies
View Related
Nov 7, 2001
I am experiencing a situation where I issue a lengthy SQL command to MS SQL Server 7.0 through MTS and it "disappears" - no errors or recordsets are returned. The command is "SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%'". When I issue this command from SQL Query Analyzer it takes 27 seconds to return 87 rows. When I issue this exact same command through MTS it does not return at all.
I've used the SQL Profiler to analyze the requests. It shows the commands from MTS starting but they never stop (or at least the profiler never reports them as stopping). The same commands coming from Query Analyzer are reported as starting and stopping without fail.
Here's a twist: I can issue less demanding commands (ie, one that doesn't take so long to process) through MTS and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.
PLEASE HELP!
View 1 Replies
View Related
Jul 26, 2004
Hi,
I am very new to sql server and I have been reading up on the dbcc commands. For instance, DBCC SHOWCONTIG where do I execute this command? I went into the cmd prompt and opened osql and typed:
1> DBCC SHOWCONTIG
2> GO
This gives me the information for what I assume is the master database. But, how do I use this command under the other databases?
Also, I have been trying to learn how to determine when to do index maintenance by using the index tuning wizard. But, I don't know what a workload is. Can someone point me in the right direction or give me some information about this topic.
Thanks,
Laura
View 5 Replies
View Related
Feb 29, 2012
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
View 6 Replies
View Related
Nov 4, 2007
hi all
when i set up a new subcription ,i notice in hte replication monitor thet this subscription 'uninitialize subscription' and the are 70 undelevier commands.
how can i fixed this problem,or how can i remove those undeleveirs commands?
thanx
View 1 Replies
View Related
Jul 23, 2005
Hi All,Is there way that commands executed in the query analyzer get loggedautomatically?TIAJoriz
View 3 Replies
View Related
May 19, 2006
Hi,
Suppose there are two tables employee and salary and a,b are two instances of tables employee and salary.There are 20 records in each table.Then what will be the result of the following query
select a.*, b.* from employee a, salary b.
regards
arun
View 3 Replies
View Related
Feb 6, 2007
Hi al,
Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)
View 8 Replies
View Related
Aug 7, 2007
Hi
Can we use
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0)
Command in Stored procedure. Which executes daily.
Thanks
Sridhar K
View 4 Replies
View Related
