Accessing (writing Into) Terdata By Ado.net And SSIS
Oct 13, 2006
Hello everybody,
my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had´nt seen so far?.
In the software documentation we found a tip "ADO.NET" as Data Destination is´nt imlemented for now - please wait until ....
I´m asking me if someone has had the same problem before and could help me out with a little tip.
thx in advance!
Christof
View 1 Replies
ADVERTISEMENT
Oct 13, 2006
Hello everybody,
my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had´nt seen so far?.
In the software documentation we found a tip "ADO.NET" as Data Destination is´nt imlemented for now - please wait until ....
I´m asking me if someone has had the same problem before and could help me out with a little tip.
thx in advance!
Christof
View 1 Replies
View Related
Aug 18, 2006
I am having a scenario where all the stored procedures are stored in a folder (one sql file per sproc). Stored procedure does not have 'IF Exists .... DROP Procedure' in the script so before creating them we have to drop all sproc manually.
Can anyone help me writing a script / SSIS process to loop through each file in folder and write "IF EXISTS ... DROP PROCEDURE" with the procedure name in it ??
I can create a package that loop through each file in FOR each Loop task but dont know how to write in file using .net
Thanks in advance
Furrukh baig
View 3 Replies
View Related
Nov 20, 2007
I have a Dataflow task with oledb source that is using SqlCommand to retrieve data and oledb destination to write the source output to a table. I have access to both the source and destination databases.
The problem is the destination component is not writing any rows to the destination table eventhough the Source component is returning rows (I can see them in the Preview and the source database table as well).
I'm using "Table/View Name from Variable" for destination.
The Package executes without any errors but there is no output.
Any ideas?
Thanks.
View 7 Replies
View Related
Aug 7, 2007
Was wondering which is better, writing the SQL code within the execute SQL task or calling a SP from within it with respect to performance? and what is the best practice? Also is there any way to incoporate the logic of a cursur within SSIS?
Thanks
View 3 Replies
View Related
Jul 17, 2015
I have an SSIS package that is creating an Excel worksheet and writing data to it. It works fine when i run it inside Visual Studio. But when it runs as a scheduled job it writes the header and no data. I turned on logging and the log even says it is writing the 10,456 rows that it should be.Â
But they are not showing up in the Excel document. The job is setup as 32 bit and writing to Excel 97-2003. The job ends normally and does not generate any type of messages that are out of the ordinary. This is running on SQL Server 2008r2.
View 4 Replies
View Related
Feb 12, 2008
Hi All,
I'm a bit lost on this...
I have an SSIS package which writes to a configured public MSMQ. When run directly through Visual Studio, file system or msdb it works fine (I guess these use me as the user).
However, when I schedule it through an SQL Agent Job it fails with the following:
Failed to write message ... to the queue.
This seems like a permissions thing to me, but I've tried giving full control to everyone in the MSMQ permissions, also explicitly giving full control to the user that the agent job executes as. Neither made a difference.
Any help would be greatly appreciated.
Cheers,
Tony Vaughan.
View 2 Replies
View Related
Mar 2, 2007
I am attempting to run an SSIS package from a web service. Right now both the service and package are on my local machine which is running XP. I have accessed the web service from a client application in debug mode. I am not sure if it is actually running under aspnet_wp.exe because it is XP and a development environment? (separate question)??? The package fails with a series of OnError messages similar to:
The result of the expression ""/c DEL /F /Q "" + @DeployFolder + "\catalog.diff.lz""" on property "Arguments" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
An initial supposition is that the permissions of the web service are inadequate for the package. I have the authentication as "Windows" and <identity impersonate="true" /> in the Web.Config file. When I break in the debugger the Environment.UserName and Environment.UserDomainName are mine and I am an Admin on the box.
the authorization is 'deny users="?".
The article that describes basic implementation of this in a Web Service states:
With its default settings for authentication and authorization, a Web
service generally does not have sufficient permissions to access SQL
Server or the file system to load and execute packages. You may have to
assign appropriate permissions to the Web service by configuring its
authentication and authorization settings in the web.config
file and assigning database and file system permissions as appropriate.
A complete discussion of Web, database, and file system permissions is
beyond the scope of this topic.
And how!
Note that the load is fine and that this is a run time error and that the package runs correctly when run manually from SQL Server using the 'run package' menu item in the Object Explorer tree of the SQL Server Management Console.
I need to know if this is an ASP.NET issue per se or XP or if this is even a security issue. And how to solve it! This is critical path so an expeditious reply with a solution would be greatly appreciated.
View 1 Replies
View Related
Jun 13, 2007
I have a variable defined as "Country". Based on the value, the header row printed needs to be different.
I've already created a 'HeaderRow' variable that I'm able to set using a script task. But how can you set the Header text value at run time from the variable? There is no expression defined for the Header with the Flat File Destination object, and when I attempt to reference the HeaderRow variable as the Header text, the variable name is printed as the header.
Another approach I tried was to write the Header Row separately through another data flow task, but the issue here is: what is the input source when all you have is a Country variable?
View 1 Replies
View Related
Nov 21, 2006
Hi
I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?
Please let me know if theres a way to access SSIS variables.
Thanks,
Vipul
View 10 Replies
View Related
Apr 23, 2015
I'm running windows 8.1 with SQL.Server.2012.
I've admin permissions on the PC but can't access SSIS because Access is denied like below.
However the solution proposed below is not appropriate for me as I can't find DCOM User Group Membership on my Windows 8.1 machine.
[URL]
View 2 Replies
View Related
Feb 12, 2007
After reading some threads on this forum about accessing OLAP data from SSIS via an OLE DB Source component (and trying it myself and getting that "pcrsstore.cpp line 325" error), I have worked around the problem by using a Script Task component.
This calls a .NET assembly I wrote that uses ADOMD.NET to get at the data using an MDX query, and then actually posts an event to a Notification Services instance via a stored proc.
Writing, deploying, and maintaining a .NET assembly is more than I wanted to do, but it was worth the effort since now I know a little about extending SSIS when I need to.
But what I really wanted to do was the following, all from within the core components of SSIS:
Import my OLTP data into my relational warehouse (already done using SSIS)
Process the cube (already done using SSIS)
Access the cube via MDX and post an event to Notification Services
It's this last step that needed some special work because of SSIS's apparent limitation accessing cube data (although posting the event to NS would be easy because SSIS can call a stored proc on the NS application database).
So I'm hoping that a future release or service pack of SSIS will give us some components to run MDX and get the OLAP data into the pipeline as if it were from any other source.
Thanks for reading, and if anyone has any suggestions on a better way to achieve what I need please let me know!
-Larry
View 2 Replies
View Related
Feb 6, 2008
Hi
I have a script component task that uses a column (which contains filenames) as input,
my problem is i cant access this in the script ? the column name is "Document"
and i have tried .
Test_var = Me.ProcessInput(186, "fulldoc").Value
View 1 Replies
View Related
Sep 26, 2007
Hi,
I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.
I follow the following steps to achieve this:
01. Created a new SSIS package
02. Create a connection string to "(local)/." server, "tempdb" database
03. Set the "RetainSameConnection" property value to "TRUE"
04. Set the "DelayValidation" to "TRUE", where ever I found this property
04. In Control Flow I added to items
a. Execute SQL Task
b. Data Flow Task
05. For "Execute SQL task" I set the connection to "tempdb"
06. I written the following query
Create table #transfer_CompaniesToProcess_tbl
(
companyID int not null
)
GO
07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination"
08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command"
09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl"
10. When I clicked on the "OK" button; I ended with following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
I gone through the following article and it seems I missed some thing.
http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
Can any one have any idea where I am doing wrong?
Thanks
Sreekanth
View 9 Replies
View Related
Feb 6, 2008
How do i access global variables in SSIS scripts ?
the Dts.variables("VarName").Value
should work but doesn't ?
do i need to reference the variable another way ?
View 3 Replies
View Related
Feb 6, 2008
Hi
I have a script component task that uses a column (which contains filenames) as input,
my problem is i cant access this in the script ? the column name is "Document"
and i've tried Me.ProcessInput("Document")
View 3 Replies
View Related
Sep 13, 2007
Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.
I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below
---------------------------------------------------------------
Message
Executed as user: FORTIESABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\Accfp1_data2_serverDATA2Arts&recAppsContactsCONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.
---------------------------------------------------------------
Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?
Can anyone help?
View 5 Replies
View Related
Jun 21, 2008
I have some experience with MS SQL mostly installation, configuration, maintaining, etc. But I am trying to teach myself some TSQL. I have bought a few beginner books and have some test machines. In advance, I appreciate any feedback!
I have a database with two tables. I want to calc the min salary for a class of employees. The "wage" is in one table and the "status" is in another.
USE Database
GO
SELECT MIN(Wage) AS "Min Salary", Status
FROM Employee, Job
WHERE Status = '1'
GROUP BY Wage, Status
GO
The result set brings back all not the "status" class that I want.
Again, I am relatively new so take it easy on me!
Thanks,
grinch
View 4 Replies
View Related
Mar 3, 2008
Hello,
I wrote a stored procedure in SQL 2K5, and I would like to write to the sql log without using a rasieerror function. I just would like to log the sucessfull steps without exiting the proc. I am not sure how to do this. Any help would be appreciated.
Thanks,
Dave
View 3 Replies
View Related
May 13, 2007
Hi,
I am new to this programming and SQL.
I am following the: Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database video series. I cannot get the following to write changes to the SQL Database:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
BindingSource1.EndEdit()
'CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
Dim rowsAffected As Integer = 0
rowsAffected = CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
MessageBox.Show(rowsAffected.ToString())
End Sub
End Class
I do get the message box to popup showing 1 row of changes when I make one change, however that change does not get written to the database. Is this enough info to have any idea why?
Thanks
View 1 Replies
View Related
May 17, 2007
I was hoping that someone might be able to help a SQL statement for this, if possible. I have two tables, InvoiceDetails and InvoiceLineItems. What I'd like to do is write a query that will return all invoices, but also return a value InvoiceTotal Based on SUM(InvoiceLineItems.qty * InvoiceLineItems.unitPrice). I've tried a few different methods of writing this, but haven't had much luck. Can anyone shed some light on this for me? Thanks.
View 7 Replies
View Related
Dec 20, 2007
Hi.I realize this may have been asked a thousand times before, but it's still not working for me. I would appreciate any help with it. First, I created a GridView, and from it I created a new SqlDataSource and let it point to the database in C:My ProjectApp_DataASPNETDB.MDF. This automatically created a connectionString in the Web.Config file, saying the following: "R1ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="C:My ProjectApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" /> Now when I try to programatically use that connection string, the program crashes. Below is my code: <script runat="server"> String sqlConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename="C:My ProjectApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True";
protected void Page_Load(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(sqlConnectionString)) { conn.Open(); const String selectQuery = "SELECT StatusName FROM Status ORDER BY StatusName"; Label1.Text = ""; using (SqlCommand cmd = new SqlCommand(selectQuery, conn)) { SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { Label1.Text += dr[0]; while (dr.Read()) { Label1.Text += ", "; Label1.Text += dr[0]; } Label1.Text += ". "; } else
Label1.Text = "None."; } if (conn != null) conn.Close(); } } Here's the error message I'm getting: Server Error in '/My Project' Application.
Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.ArgumentException: Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.
Source Error:
Line 15: protected void Page_Load(object sender, EventArgs e)Line 16: {Line 17: using (SqlConnection conn = new SqlConnection(sqlConnectionString))Line 18: {Line 19: conn.Open();
Source File: c:My Project est.aspx Line: 17
Stack Trace:
[ArgumentException: Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.] System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +417 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +99 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +25 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +141 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +38 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21 ASP.test_aspx.Page_Load(Object sender, EventArgs e) in c:My Project est.aspx:17 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745 My question is: How should I write the connection string? I have a feeling the problem is with the quotes ("), but I can't figure out how to write it otherwise.Thank you very much.
View 2 Replies
View Related
Jan 22, 2008
Hello, I have two tables: Customer Training Requests has say two columns.Customer - Class Requested a 1b 1c 1a 2b 2c 3a 3 I have another table called packages, which has packages of class.Package No. Class1 11 2 I want to write a query which will return only those customers who have requested exactly the classes in a particular package.so in above example if the package is 1 the query should return only customer b as only customer b has requested class 1 and 2. Any suggestions? Thanks,Paresh.
View 8 Replies
View Related
Feb 4, 2008
hi, I'm trying to write to a database from a web page. The code is throwing back no errors but it just isn't writing to the database. Am I missing something? void SubmitDetails(Object s, EventArgs e) { SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); SqlCommand objCmd; objCmd = new SqlCommand( "INSERT INTO Member_Information (Name, Address_Line_1, Address_Line_2, Address_Line_3, Email_Address, Contact_Number, " + "Password, Name_Of_Bank, Account_Number, Sort_Code, Bank_Address_Line_1, Bank_Address_Line_2, Bank_Address_Line_3) " + "VALUES (@Name, @Address_Line_1, @Address_Line_2, @Address_Line_3, @Email_Address, @Contact_Number, " + "@Password, @Name_Of_Bank, @Account_Number, @Sort_Code, @Bank_Address_Line_1, @Bank_Address_Line_2, @Bank_Address_Line_3)", objConn); objCmd.Parameters.AddWithValue("@Name", txtName.Text); objCmd.Parameters.AddWithValue("@Address_Line_1", txtAL1.Text); objCmd.Parameters.AddWithValue("@Address_Line_2", txtAL2.Text); objCmd.Parameters.AddWithValue("@Address_Line_3", txtAL3.Text); objCmd.Parameters.AddWithValue("@Email_Address", txtEmail.Text); objCmd.Parameters.AddWithValue("@Contact_Number", txtContact.Text); objCmd.Parameters.AddWithValue("@Password", txtPassword.Text); objCmd.Parameters.AddWithValue("Name_Of_Bank", txtBank.Text); objCmd.Parameters.AddWithValue("Account_Number", txtAN.Text); objCmd.Parameters.AddWithValue("@Sort_Code", txtSort.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_1", txtBA1.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_2", txtBA2.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_3", txtBA3.Text); objConn.Open(); objCmd.ExecuteNonQuery(); objConn.Close(); }
View 4 Replies
View Related
Feb 23, 2008
Hello, how can do the following using sql:
if exists(select * from siteindex where site_url like '%msn.com')ThenUpdate siteindex Set dmodified = GetDate(), isFullPage = 0, fp_flname = 'filename', fp_iWidth = 800, fp_iheight = 600
Where site_url = '%msn.com'
else
insert into siteindex (site_url, dcreated, dmodified, isFullPage, fp_flname, fp_iWidth, fp_iHeight)
Values ('msn.com', GetDate(), GetDate(), 0, 'filename', 800, 600)
I cant get the syntax right. Thanks!
View 1 Replies
View Related
Apr 28, 2008
Hi all,
I have a problem with retrieving data from lookup tables. Ok here is the issue I have a Tables ...
Tables | Schools StuAccount
Fields | Sch_Id (pk) Student_ID (pk)
School_Name AccountableSch_Id
DistrictName TestedSch_Id
ParticipatedSch_Id
Ok, so i have two tables Schools and StuAccount, each has one primary key and and the StuAccount has three foreign keys that associates to the Schools TABLE which has a the definition of school names wich i want to retrieve for all three foreign key in the stuaccount table. Here is what i want ....
Student_ID
AccountableSch_Id
Shool_Name( this the name of the AccountableSch_Id )
TestedSch_Id
Shool_Name( this the name of the TestedSch_Id)
ParticipatedSch_Id
Shool_Name( this the name of the ParticipatedSch_Id)
I just want to look up the id and put the school name in the final resulting join and i have no idea how to lookup the table School three time and put school name for all three foreign keys.??
Any help will be much appreciated.
Thanks
View 4 Replies
View Related
Jun 5, 2008
Hey everyone,
I'm making a site and I need to write some code that will do the following things:
Write a statement for the Page_Load event to pull the information from the db and load it into a textbox and a textbox with multiline attribute.
View 13 Replies
View Related
Nov 20, 2003
Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.
Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.
Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.
I'd really appreciate some help here. Thanks in advance for all your help.
---------------------------------------
Here's the script
---------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContractStatus]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblCompany] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblContractStatus] (
[StatusID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
[PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblDeals] (
[DealID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL ,
[DealDate] [smalldatetime] NOT NULL ,
[PhaseTypeID] [tinyint] NOT NULL ,
[CashAmount] [smallmoney] NOT NULL ,
[StatusID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblPhase] (
[PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[PhaseTypeID] [tinyint] NOT NULL ,
[PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblProduction] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[DealID] [int] NOT NULL ,
[PhaseID] [tinyint] NOT NULL ,
[TransactionTimeStamp] [smalldatetime] NOT NULL ,
[Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD
CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED
(
[StatusID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD
CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
(
[PhaseTypeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD
CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
(
[DealID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD
CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
(
[PhaseID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID]
GO
ALTER TABLE [dbo].[tblProduction] ADD
CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp]
GO
ALTER TABLE [dbo].[tblDeals] ADD
CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
(
[CompanyID]
) REFERENCES [dbo].[tblCompany] (
[CompanyID]
),
CONSTRAINT [FK_tblDeals_tblContractStatus] FOREIGN KEY
(
[StatusID]
) REFERENCES [dbo].[tblContractStatus] (
[StatusID]
),
CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
(
[PhaseTypeID]
) REFERENCES [dbo].[tblPhaseType] (
[PhaseTypeID]
)
GO
ALTER TABLE [dbo].[tblPhase] ADD
CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
(
[PhaseTypeID]
) REFERENCES [dbo].[tblPhaseType] (
[PhaseTypeID]
)
GO
ALTER TABLE [dbo].[tblProduction] ADD
CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
(
[DealID]
) REFERENCES [dbo].[tblDeals] (
[DealID]
),
CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
(
[PhaseID]
) REFERENCES [dbo].[tblPhase] (
[PhaseID]
)
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Description', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'Status'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO
---------------------------------------
And here's some data
---------------------------------------
INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed')
INSERT INTO [tblContractStatus] ([Status])VALUES('Hold')
INSERT INTO [tblContractStatus] ([Status])VALUES('Collections')
INSERT INTO [tblContractStatus] ([Status])VALUES('Legal')
INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')
View 3 Replies
View Related
Jun 16, 2004
i have a query and i would like to write the contents of the dataset out directly to a file and not bother with creating another temp table and then exporting it with a command with a dts to a csv file.
it is a type of reporting that i am trying to do
but i just need to export the raw data i retreive from the query
View 3 Replies
View Related
Oct 25, 1999
Is it possible to do the following without cursors or creating an identity column:
I have a table from legacy data with ~ 1 million records. I need to insert this into the new table which has a unique varchar(11) key. For the new system this key is generated by calling a SP that returns the next key in sequence. To put the legacy data records in the same table I want to first create a new column at end of legacy data table and populate this using SQL without going thru using cursor and calling the SP for each and every record to get a unique varchar(11) key.
In short here is what I want:
Field1 Field2 varchar(11)key
------ ------ -----------
jsdhf dsf99 1LEG
878jh whjhj 2LEG
8728jh whjhj 3LEG
8578jh whjhj 4LEG
3878jh whjhj 5LEG
6878jh whjhj 6LEG
8508jh whjhj 7LEG
...
...
...
4878jh whjhj 1000000LEG
How do I generate this key using an SQL stmt?
Thanks in advance,
Nishi
View 4 Replies
View Related
Oct 18, 1999
I have a table that has records that have multiple entries with only a slight varitation to accomodate client needs. I want to filter out the duplicates and call only one record row. How can I do this type of thing and make it work; example - SELECT * FROM clients WHERE client_id DISTINCT
I know that is not correct but that is what I need. I need to get all the columns but only one record for each client using the client_id field as the source of information.????
View 2 Replies
View Related
Apr 5, 2006
I have table Products
I have coloumn like ProductName:
ProductName Having two values like Colgate and pepsi
I want to change these coloumn values in the result set
Please write a query
Hi here is clear question?
Table Name is Products
in that One coloumn name is
ProductsName:
and the values for productsName are
colgate
pepsi
the result set should be like following way
ProductsName:
Mike
Johnson
so here I am replacing value Mike instead of colgate
and Johnson instead of pepsi
View 1 Replies
View Related
Apr 14, 2006
Please help with writing query
I have 2 tables:
Table1:
IDDescription
1name1
2name2
3name3...
Table2:
SiDID
1231
4562
789myname
852yourname
if description exisits in table1 get description from table1 in
all other cases get ID from table2
View 5 Replies
View Related
