Regarding XBRL File Creation

Sep 27, 2007

Hi ALL

I want create XBRL file from database table.Does any have help me about this??

T-sql File Creation

May 30, 2004

Hi all,

Can I create one xml file through transact-sql ( in a stored procedure )?
From where i will get the tutorials ? Please help me ...

I am using the MSSQL Server 8.0 .

thanks
Pinto

View 1 Replies View Related

Hi!

I have one package (Primary) that calls 4 Packages(1,2,3,4) throw an SQL Server Connection.
That 4 packages create 4 distinct files from a a Streamwriter.
The files area created in a Script component

I call the execution of the packages from an Web Page that calls the job an execute the package Primary.

The first two packages (1,2) create the file and everithing goes ok.

The third one(3) "blows up" with the exception:

System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

The next try that i made was to comment in the package Primary the execution of the packages 1,2 and run the package 3,4.

The package 3 run ok and the package 4 blows up with

"System.ArgumentOutOfRangeException: Index and length must refer to a location within the string. Parameter name: length at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)"

The next try that i made was to comment in the package Primary the execution of the packages 1,2,3 and run the package 4.

It run Ok.

When the package 3 and 4 blows up they create the file but they don't write.

In all the executions that i made i deleted the files that were created.

When I run the packages from the development solution and using file system connection everything runs.

Here is the code of the script component:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.IO

Public Class ScriptMain

Inherits UserComponent

Dim directorio As String

Private textwriter As StreamWriter

Private separador As String = ";"

Private cabecalho As Boolean = True

Dim linha As String = ""

Public Overrides Sub PreExecute()

directorio = Trim(Variables.Directory) & Trim(Variables.FileName)

textwriter = New StreamWriter(directorio, False)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If cabecalho Then

Dim DataProc As String = Row.var1

Dim DataInf As String = Row.var2
DataProc = DataProc.Substring(0, 4) _

& "/" & DataProc.Substring(4, 2) _

& "/" & DataProc.Substring(6, 2)

DataInf = DataInf.Substring(0, 4) _

& "/" & DataInf.Substring(4, 2) _

& "/" & DataInf.Substring(6, 2)

textwriter.WriteLine(Variables.var3 _

& separador & Trim(var1) _

& separador & Trim(var2)

cabecalho = False

End If

Dim var7 As String = ""

If Not Row.var7_IsNull Then

var7 = Row.var7.ToString

End If

With textwriter

linha = Trim(Row.var1) _

& separador & Trim(DataProc) _

& separador & Trim(DataInf ) _

& separador & Trim(var7)

.WriteLine(linha)

End With

End Sub

Public Overrides Sub PostExecute()

textwriter.Close()

End Sub

End Class

In the four Script Components the code is the same only change the value of the variables.

I'm sorry for my English and the variables here in the code were changed so it can be an error in the variables but forget that.

Can anyone help me!?

View 7 Replies View Related

FTP File Creation Date

Oct 3, 2007

I have a script that is utilizing an FTP task to retrieve files from a Windows FTP site without any issues.
I need to check the dates of the files on the FTP site prior to downloading them as I only need files that have been modified. Since these files are hundreds of MB in size, downloading them first and them checking the date is not an optimal approach.
Can somebody please help me figure out how to check the creation dates of the files on the remote FTP directory?
Thanks!

View 15 Replies View Related

Dynamic Flat File Creation!

May 14, 2008

I'm trying to read the folder which has flat files and transform it and put it another folder with the SAME original name.
I created a For Eachloop containter and assign it to variable infile. In the data flow I added some transform and flat file destination. currently its reading 3 files and output is only one file.
I would like to create dynamic flat file connection so that it'll create separate files (same name) in different folder.
Thanks,

View 5 Replies View Related

Dynamic Flat File Creation

Mar 19, 2008

Hi All,

I'm new to SSIS. I have a small problem. In our system we are trying to create flat files based on data from returned from different SQL stored procedures. The configuration settings for creating the flat files are stored in a SQL table.

For example, the config table will look like below :

Feed Id Feed Name Feed File Name Feed Stored Procedure Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 Biz C:Biz.txt dvx_GetBiz
2 PG C:PG.csv dvx_GetPG
3 NexT C:NexT.txt dvx_GetNexT
4 Amez C:Amez.csv dvx_GetAmez

The problem is dvx_GetBiz, dvx_GetPG,... all have different number and types of columns. So, I'm not able to create a dynamic Flat File destination depending on the columns returned from the corresponding stored procedure.

Is there a way to accomplish this ?

Pl. help.

Awaiting someones reply ASAP.

Thanks & Regards,
Vidya.

View 4 Replies View Related

SQL Alert Triggered By Text File Creation

Oct 22, 2001

I would like to know how to config SQL Server 2000 Alert to be triggered by new file creation (i.e. txt file).

Maybe by monitoring windows 2000 log file (not sure)??

Any help or suggestion will be highly appreciated.

View 2 Replies View Related

Error Redirection - How To Supress File Creation?

Oct 16, 2007

Hi all!

I�ve having some issues with a package that I�m currently building.
The package is pretty much finished and doing what it�s supposed to,
just doing some testing to ensure everything is in order.

The package is pretty basic:
Read a fixed with text file, do a lookup on destination database table,
conditional split to determine what to insert and what to update, and that�s it!

At the source of my data flow I have the error output set to redirect all rows,
counting those rows, and finally writing them to a dynamically created file.

However, I just now realized that this file seems to be created ALWAYS,
regardless of rows actually being redirected or not. Everytime I run the package
with no rows being passed through the error output, I still end up with a
dynamically named, zero sized text file.

So my question: Is it possible to supress the creation of this file,
or do I have to add a file system component to my control flow,
deleting this file if my number of errorrows = 0 ?

Hope you can help!

Regards
Daniel

View 1 Replies View Related

Need A Help For Executing Script File Having Creation Of Stored Procedure From C#

Jan 9, 2008

Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex() method reads the script file from starting to end and returns script text. My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime, @prmEndDate datetime,@prmEvent char(1)ASBEGIN SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) as EventDate from dbo.Parishioner P left outer join dbo.Sex S on P.SexId = S.SexId where (CASE @prmEvent WHEN 'B' THEN DateOfBirth WHEN 'D' THEN DateOfDeath WHEN 'M' THEN DateOfMarriage END ) between @prmStartDate and @prmEndDate END But when i executing script file having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm

View 1 Replies View Related

SQLExpress Database File Auto-creation Error:

Jun 12, 2006

i am using sql2005, the data inserted into the database by using createuserwizrd. after that anyone give me this error
anyone ? any thought? thanks please
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.

View 2 Replies View Related

SQL Server 2008 :: Creation Of XML File With Data In Table

Jun 1, 2015

I want to create a XML file with data in my table. I have a question about tags.

SELECT
-- Root element attributes
'http://tempuri.org/Form.xsd' AS 'xmlns',
'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsd',
(
SELECT
-- Creating a default element

[Code] ....

This is my query. When I use 'xmlns' namespace the result is below:

<Form xmlns="http://tempuri.org/Form.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri xmlns="">
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>

I dont want to see 'xmlns' in 'Veri' tag. When I use xmlna or another word instead of 'xmlns' the result is below:

<Form xmlns="http://tempuri.org/Form.xsd" xmlna:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri>
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>

How can I remove 'xmlns' from 'Veri' tag while using it in 'Form' tag

View 2 Replies View Related

Checkpoint File Falied During Creation - How To Figure This Error Out

Oct 12, 2007

hi folks,

i have this error when trying to run the package in development mode.
and i can not decipher this non-intuitive message:

Error: 0xC001604D at <package name>: Checkpoint file "" failed during creation due to error 0x80070003 "The system cannot find the path specified.".

please note that i have no checkpoints on package anymore; i had two but i deleted them but this message still appears.
i have searched the web but no luck in finding an answer.

maybe someone would be able to point out my obvious mistake here.

many thanks,

Nicolas

View 1 Replies View Related

Wrap Database Insert And System.IO File Creation In One Transaction?!

Mar 4, 2007

i am creating/uploading a new file on the webserver, and if it is successfully i want to insert a record in the database (with the filename).is there a way to create a transaction for this so that if either operation fails they both fail?

View 2 Replies View Related

SQLExpress Database File Auto-creation Erro...........Im Lost!

Mar 28, 2008

here is my error message wich can also be seen live at www.aspdoug.com/menu log in with user: doug pass: macromedia!
--------------------------------------------------------------------------------------------------------------------------------
Server Error in '/menu' Application.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +421
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +133
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68

[HttpException (0x80004005): Unable to connect to SQL Server database.]
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +124
System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +86
System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +25
System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +397

Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Below is my web.config file.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<?xml version="1.0"?><configuration>

<appSettings/>
<connectionStrings>
<add name="aspdougcomConnectionString" connectionString="Data Source=65.36.214.248;Initial Catalog=aspdougcom;Persist Security Info=True;User ID=*&^%$#@;Password=*&^%$#@"providerName="System.Data.SqlClient" /></connectionStrings>

<system.web>
<compilation debug="false" strict="false" explicit="true"></compilation>
<pages><namespaces>
<clear /><add namespace="System" />
<add namespace="System.Collections" /><add namespace="System.Collections.Generic" />
<add namespace="System.Collections.Specialized" /><add namespace="System.Configuration" />
<add namespace="System.Text" /><add namespace="System.Text.RegularExpressions" />
<add namespace="System.Web" /><add namespace="System.Web.Caching" />
<add namespace="System.Web.SessionState" /><add namespace="System.Web.Security" />
<add namespace="System.Web.Profile" /><add namespace="System.Web.UI" />
<add namespace="System.Web.UI.WebControls" /><add namespace="System.Web.UI.WebControls.WebParts" />
<add namespace="System.Web.UI.HtmlControls" /></namespaces>
</pages>

<authentication mode="Forms" />

</system.web>
</configuration>

-------------------------------------------------------------------------------------------------------------------------
MY host told me that they do not support sqlexpress and to change it MySql 2005 or something. im using hostmysite.com builder plan.

View 1 Replies View Related

Integration Services :: SSIS Flat File Processing And Creation

Nov 30, 2015

I am facing one process related issue.

I have one task in which i have to collect lots of .txt file having ## delimiter my requirement is to convert the delimiter from ## to comma and save the new file with .dat extension in different folder.

I have done all required process and run the application which should flow like collect source .txt file do Script component processing and create new .dat file with processed data in Data Flow task, but in my Task the Source and Destination start on same time and process start after words which cause empty file or some time a.txt file data stored in b.dat file where as a.dat file is completely empty.

The process should flow in sequence but behavior is totally against the process, i am using Foreach Loop Container for pick up each file.

View 6 Replies View Related

Integration Services :: How To Import Filenames And File Creation Date Into Table

Apr 20, 2015

I am importing Differennt Excels Files into table. my require ment is after importing completed I need to insert all these Filenames ,File creation date into table. (for Auditing).

View 9 Replies View Related

SQL 2012 :: Trace Creation Time Is Different From Time Check Creation

Oct 8, 2014

We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.

for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".

View 0 Replies View Related

SQL Job Creation

Oct 27, 2004

I know this is not SQL forums but still if some body has solutions..then plz help
My backend is SQL.
My Ms Access database is always open and getting updated continously.

I need to create Job for SQL. This job will run every 10-15 min may be.
This job should pick latest data from MS Access and put in my SQL server database
Is it possible to create such kind of Job>

OR

Can my application read this MSAccess database when its open by some other application and getting updated continously?----I tried this in vain!!!!

Regards,
Mani

View 2 Replies View Related

Creation Of An SQL Job

Oct 1, 2002

I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..

IS there a way? I need help.
Thanks

View 3 Replies View Related

Job Creation

Aug 12, 2003

Hello to all!
I have a procedure "rebuild_index" and I would like to create a job running that procedure.
May someone send me a script (template) how to create this job, the most important:
When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script)
Thank you very much for your help!

create proc p_rebuild_index as
declare @name varchar(100),
@string varchar(200)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch c1 into @name
while @@fetch_status = 0
begin
set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)'
execute (@string)
--print @string
fetch c1 into @name
end
close c1
deallocate c1

GO

View 5 Replies View Related

SDF Creation...

May 22, 2007

How can i create sdf files?
Is there any way to convert a mdb or a xml file to sdf?
The only way i was able to do this was with data port wizard from primeworks, but its not freeware...
Can i populate my sdf database from xml files by writing code...??
Does someone knows??
I have searched everywhere and didnt find anything...
I'm working in VS2005 vb.net and i have installed compact sql...
Thank you!

View 1 Replies View Related

Dabase Creation

Mar 16, 2007

Is it possible to create a database structure in MS-Access and somehow import it into Visual Web Developer 2005 Express or SQL Server Management Studio 2005 Express?

View 1 Replies View Related

Database Creation

Jan 11, 2008

Hi,
I am not sure is this a proper place for my question.
All we know that creating a database can be done as the following.
1) Create a project named DBExample
2) Click Add->New Item.
3) Select SQL Databse and type a name.(Ex: Database1.mdf) A database is added to the project
4) Then add table and input values.
5) Set the primary key....
My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?

Thanks

View 4 Replies View Related

SQL Server Creation

Feb 13, 2005

Hi, What should I type in as the server name if I would want to create a local database, I tried 'local' and 'localhost' but it didn't work.

View 1 Replies View Related

Database Creation

Aug 16, 2005

Hello...
I want to develop a web site having two features
1. Online Shopping2. Forums
Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once.
____________Thanks in advNauman Ahmed

View 1 Replies View Related

DTS Scheduling And Job Creation

Dec 13, 2000

I'm sorry to beat this to death but something odd is happening that I'm not quite sure I understand.

I have a number of DTS packages that when originally created with the wizard were scheduled and the jobs have run fine. Today, I created an import job that runs great but then crashes when I try to schedule it from the wizard. When I manually go in and try to schedule the DTS job, it accepts the input but does not create the job.

Since I've done this before, I'm confused about why I was able to do this previously, but not now. Permissions, etc. have not changed at all and I am the dbo for the database. The DTS package and attempted job scheduling are being done on Windows 2000 Server. SQL-Server version is 7.0.

Anybody have any ideas?

View 2 Replies View Related

Views Creation

Nov 19, 2003

can sum one help with this:

I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables

Thanks a lot.

View 1 Replies View Related

Procedure Creation

Nov 27, 2005

I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record

this is the cust table created
create table CUSTOMER (
CUSTOMER_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256));

I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...

any clues?

View 4 Replies View Related

Procedure Creation

Nov 27, 2005

i was asked to post this here..

_----------------------------------------

procedure creation
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record

this is the cust table created
create table CUSTOMER (
CUSTOMER_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256));

I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...

any clues?
i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.

create table CUSTOMER
(
CUSTOMER_ID int identity(1000,1),
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256)
)

But while writing insert statement didnt specify this column.

Like

Insert into CUSTOMER (NAME ,ADDRESS ,CITY ,STATE ,ZIP_CODE ,AREA_CODE ,PHONE_NUMBER,SALESPERSON_ID , CREDIT_LIMIT ,COMMENTS )
values('xoxo','planet','earth','jupiter','1234','2 13',31231,231,1231,'1231')

not working.....!!?!?!

View 2 Replies View Related

Script Creation

Sep 24, 2007

Hi

I am supposed to give scripts for an installtion at client site.

installing SQL server 2005 Developer edition
some databases already we have
tables
indexes
Stored procedures
jobs

Can u please guide me how to do this?

I am new to this.

Muralidaran r

View 4 Replies View Related

Creation Of Jobs...

Jan 20, 2004

I wanted to created a few jobs in my database.
How do i create it using the Batch files?????

Thanks,
Sandu_Bangalore

View 1 Replies View Related

DTS Creation Issue

Mar 17, 2004

Hi all,

Here is the problem : I can not create a DTS package in SQLServer.
=> Error description : access denied
Environment :
- Windows NT4 sp6 Server, french, logon on as "Administrator".
- SQLServer 7.0 sp4, french, logon as "sa" on the NT Server with Enterprise Manager.
- SQLServer Agent runs under an Administrator account

Why can't i create � single DTS package ? Strange, isn't it ?
Many thanks for your ideas

Bertrand

View 3 Replies View Related

User Creation

Apr 10, 2008

We normally map a single SQL user to all application users . From performance standpoint and from best practices perspective , what is the preferred method of mapping OS users to SQL users viz one to one OR many to one .

View 6 Replies View Related