Generate Format Files For All Tables In A Db

Sep 16, 2003

This script generates a format file for every table in a database.

It's set up for fixed width files, not too common in this world...but they can easily be modified.

Also the do not at this time handle text or image columns.

Any hints/advice here would be great.

I'll post it when I figure it out.

happy bcping..


SELECT FORMAT_CARD FROM (
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ char(9)+'"
"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping






Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

View 1 Replies


ADVERTISEMENT

Converting Csv Files From One Format To Another Format With Differing Columns

Dec 19, 2007

Hi,


I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.

View 1 Replies View Related

Generate Script Of All Indexes In A Particular Database In Format

Jul 15, 2015

I am in need to generate script of all the indexes in a particular database in the format "if not exist ....create index" format.I tried to google this I get for missing indexes, fragmentation level all that extra stuff, I need only in the format of "The script should be in "if not exist ....create index" format for a single database.

View 6 Replies View Related

Using MSS To Generate XML/HTML Files

Jun 15, 2005

Hello All,Looking for an opinion on the following scenario.Lets say you need to create a report for a 1000 stores. The report format is the same but the data changes for each store.Option 1:Write an asp.net page where the user selects the store, or a store parameter is pased, hit SQL Server with a SP and display the data.   - This option seem to have the advantage of flexibility and simplicity.Option 2:Write out a static XML/HTML report for each store and have the asp.net page load the static file.   - This options seems to have the advantage of speed on the client side but is less flexible and more complex on the backend.Any thoughts?For option 2 is it possible to write out the XML/HTML files using SQL Server or does something custom need to be created?Thanks, Gary

View 2 Replies View Related

Generate File Task With 2 Files

Sep 18, 2007

Hi Guys,

I've one Dafta flow task where I'm getting data from OleDb source and then doing some scripting using script component and then generating a file.
Now I would need to get the same data and apply some different things and generate another file.
Can I used this same task for doing the secondry work? If yes how woulld I put the thing in place, I would need to get the same data but I would need to use a seperate scripting and generate a seperate file?

TA
Gemma

View 1 Replies View Related

Bcp Unicode Files Using Format Files

Jul 20, 2005

I have a problem with bcp and format files.We changed our databases from varchar to nvarchar to support unicode. Noproblems so fare with that. It is working fine.But now I need a format file for the customer table and and it is notworking. It is working fine with the old DB with varchar, but withnvarchar I'm not able to copy the data. The biggest problem is, that Igot no error message. BCP starts copying to table and finished withouterror message.This is my table:CREATE TABLE [dbo].[Customer] ([ID] [int] NOT NULL ,[CreationTime] [datetime] NULL ,[ModificationTime] [datetime] NULL ,[DiscoveryTime] [datetime] NULL ,[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,[Class] [int] NULL ,[Subclass] [int] NULL ,[Capabilities] [int] NULL ,[SnapshotID] [int] NOT NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOTNULL ,[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AINOT NULL ,[LocationID] [int] NULL ,[MirrorID] [binary] (16) NULL ,[DeleteFlag] [bit] NULL ,[AdminStatus] [bit] NULL) ON [PRIMARY]GOand this is the format file:8.0131 SQLINT 1 12 "#~@~#" 1 ID ""2 SQLDATETIME 1 24 "#~@~#" 2 CreationTime ""3 SQLDATETIME 1 24 "#~@~#" 3 ModificationTime ""4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""5 SQLNCHAR 2 510 "#~@~#" 5 Name_SQL_Latin1_General_CP1_CI_AS6 SQLINT 1 12 "#~@~#" 6 Class ""7 SQLINT 1 12 "#~@~#" 7 Subclass ""8 SQLINT 1 12 "#~@~#" 8 Capabilities ""9 SQLINT 1 12 "#~@~#" 9 SnapshotID ""10 SQLNCHAR 2 510 "#~@~#" 10 CompanyNameSQL_Latin1_General_CP1_CI_AS11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountrySQL_Latin1_General_CP1_CI_AS12 SQLINT 1 12 "#~@~#" 12 LocationID ""13 SQLBINARY 1 33 "#~@~#

"13 MirrorID """#~@~#" is the field terminator. We have a lot of text files with allkind of charachers in it. So we think this is a set that will neveroccur in our files.Thanks for your help!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Programmatically Generate Rdl Files With Image File

Oct 1, 2007

Hello,

I would like programmatically generate rdl files with image.

I am using this XML Text Writer to create the rdl file.

But i have no idea how can i carryon on this task.

Can anyone help me in this topic?

Thanks.

View 4 Replies View Related

Using SSIS To Generate Multiple Excel Files

Apr 24, 2007



Hello all,



I'm new to SSIS and have a question about the best way to generate multiple Excel files with my current package design. I have a stored procedure that I run from an Execute SQL Task in a foreach loop container, and it generates results as appropriate for each of the parameters it loops through.



What's the best way to take the result set from each of those executions and generate an Excel file from each set? How do I map that result set variable to be the input for creating a new Excel file? Is this best done as a script task somehow?



Should I not be using SSIS for this task? I thought I would just create a package and schedule it to run daily with SQL Agent, and it would autogenerate the Excel files as needed.



Thanks for your advice!

View 6 Replies View Related

How To Dynamically Generate Excel Files For The SQL Query Input?

May 22, 2008

Hi,

There is one manual process done by my friend. The process is " In query analyser, she runs a sql query and the resultset of Sql query, she copies and pastes into a excel workbook".

I wanted to write a SSIS package to automate it and providing a UI, in which she can give connection details and paste the SQL query and the package will in turn generate excel workbook in the target path". I thought of trying ExecuteSQL task, but the output resultset of ExecuteSQL task cannot be mapped dynamically to excel source(correct me if I am wrong). If I use Dataflow task, then also whether dynamic mapping of SQL output to Excel destination input is possible?

Can you please tell me the best approach for achieving the above functionality?

Thanks,
Venkataraman R

View 5 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article

Oct 22, 2015

There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.

When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).

When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.

The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.

I have tried:

- dropping/re-adding the article in question.

- Setting up a new Snapshot Folder

- Validated all the settings and configurations

I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.

View 0 Replies View Related

Reporting Services :: SSRS - Auto Generate Multiple PDF Files And Save Into Window Folder?

Jul 15, 2015

We have the following requirement :

1. In daily basis auto loop through each item in the invoice table.

2. passing invoice number into a Summary SSRS report as parameter.

3. Auto download ALL generated PDF reports into a window folder with a special file name format i.e. <INVOICE_NO>_<DATE>.pdf

how to achieve this via SSRS, Store Procedure or Power Shell?

View 4 Replies View Related

Bcp Format Files Help?

Jun 3, 2004

I am using bcp to execute a query and would like to use the -f option for format_file so I am going thourgh the books on line and trying to make some sense out of it,no quite clear on it at the moment.

How do I find out the version of bcp utility and would somebody have an example with this options just to see how it works.

Thanks

View 4 Replies View Related

Structure Of Non-XML Format Files

Apr 10, 2007

Hi
I need to make changes to a bcp format file, but don't understand the thing.

Format file =
6.0
10
1 SQLCHAR 0 3 "" 1 Col1
2 SQLCHAR 0 9 "" 2 Col2
3 SQLCHAR 0 1 "" 3 Col3
4 SQLCHAR 0 1 "" 4 Col4
5 SQLCHAR 0 1 "" 5 Col5
6 SQLCHAR 0 1 "" 6 Col6
7 SQLCHAR 0 136 "" 7 Col7
8 SQLCHAR 0 50 "" 8 Col8
9 SQLCHAR 0 50 "" 9 Col9
10 SQLCHAR 0 50 "" 10 Col10



Example of the data extract file to be imported (.txt) =

AB 1 01A Surname1 Mr NameOne MiddleNameOne AddressLineOne Testing 1 AddressLineTwo Testing 2 AddressLineThree Testing 3
CD 1016 01A Surname901 Ms NameNineHundredAndOne MiddleNameNineHundredAndOne AddressLineOne Line 2 Testing 4 AddressLineTwo Line 2 Testing 5 AddressLineThree Line 2 Testing 6



I understand the first 6. Col1 has a lenght of 3, col2 has a length of 9 and so on.
When I get to field order 7 (col7), I get stuck. Field order 7 should be the Surname, title, name and middlename of the customer. But the host file data length is 136.
From what I understand that would mean that it would included the first part of the AddressLineOne as well isn't it? The thing is that it is working currently (not reading the address)
I'm obviously not understanding how this works.



Can anyone help with this?

View 3 Replies View Related

Generate Db Scrip Including Data In Tables

Oct 10, 2007

i'm not able to generate a sql script with the data in the tables! using sql express.
i need this script to ganerate the same database with its datas in a new sql server.
thanks in advance

View 2 Replies View Related

EDB On WM. Do Do Sort Orders Generate Index Tables?

Aug 6, 2007

Hi,

I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger?
In the MSDN documentation I can not find anything on this.

Thanks,
Giulio

View 4 Replies View Related

Convert SQL Trace Files Into .CSV Format

Mar 1, 2007

How to convert SQL trace files into excel files without doing any work on SQL Profiler / SQL server using any scripting code.
Consider that we only have SQL Trace files. What are the steps involved in converting into .CSV format using single "CLICK"

Thanks,
Rajaram.
Chennai- India.

View 2 Replies View Related

Format Files For Use With Bulk Insert

Aug 9, 2006

I used bcp to produce the apended format file.How can it be modified to recognize the quotes that surround the textfields and not insert the quotes along with the text? Invariably, thefirst four columns have text surrounded by quotes and are terminated bytabs. If the first column has "abc", only abc ought to be insertedinto that field in the table.ThanksTed==================format file========================<?xml version="1.0" ?>- <BCPFORMATxmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">- <RECORD><FIELD ID="1" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="6"COLLATION="Latin1_General_CI_AI" /><FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="7"COLLATION="Latin1_General_CI_AI" /><FIELD ID="3" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="48"COLLATION="Latin1_General_CI_AI" /><FIELD ID="4" xsi:type="NativeFixed" LENGTH="4" /><FIELD ID="5" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="8"COLLATION="Latin1_General_CI_AI" /><FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1" /></RECORD>- <ROW><COLUMN SOURCE="1" NAME="f_supplier_code" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="2" NAME="f_product_code" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="3" NAME="f_product_name" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="4" NAME="f_asset_classes_id" xsi:type="SQLINT" /><COLUMN SOURCE="5" NAME="f_size" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="6" NAME="f_dist_unit" xsi:type="SQLFLT8" /></ROW></BCPFORMAT>

View 6 Replies View Related

SQL 2012 :: Generate Triggers For Newly Created Tables

Sep 11, 2014

I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?

View 1 Replies View Related

How To Construct The Url Via Database Tables Info To Generate Report

Aug 17, 2007



Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform.
in the button click i am constructing the url :
**************************************************************************************************************
http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null
*************************************************************************************************************

I have a table would like to maintain the parameters required for the chosen report:
when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters:
UserID ContractID subcode

now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)

please any ideas will help me achieve the logic. please help thank you all very much.


View 4 Replies View Related

How To Manage Different Input (Excel Files) Format

Jun 21, 2006

Hi all,

I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).

I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.

I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.

Any help will be appreciated.

Kind regards,

Sébastien.

View 3 Replies View Related

Create, Insert And Format Into 1,000 Excel Files

Feb 24, 2008

Hello,

I need to create about 1,000 (literately) Excel files that each contain 5 tabs. The data being placed on the tabs will always be the same (meaning the columns are static).
I am fairly advanced at Excel VBA so I can write code that does all the following in Excel (looped 1,000 times):


Open an Excel template

Bring data in from the tables

Filter, then copy-paste the appropriate rows into each tab.

Save the new Excel file.

Email the file to appropriate individual (it is a Microsoft Exchange Server).
As I started this in VBA, I thought that I might be able to do it with SSIS. My concern is I need to have the rows formatted (font, border, etc.) and the number of rows change.

My questions are:
Is it possible to format Excel with SSIS?
Can I email the files even if it is not with an SMTP protocol?
Would SSIS process this data faster then Excel?
Does this approach even make sense? Am I better just doing it with VBA?


Thank you for the help.

-Gumbatman

View 4 Replies View Related

SQL Tools :: Load Data From One Database To Another Using Task / Generate Script / Tables

Jun 15, 2015

I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only).  So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.

Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.

View 2 Replies View Related

SQL 2012 :: Loading Raw Files Into Database - Datetime Format Conversion

May 23, 2014

I am using SSIS to load raw files into database. In my files I have columns Date which has format

1/1/2010 12:00:00 PM.

I want to load this column in format 1/1/2010 24:00:00. I mean in 24 hour format.

View 5 Replies View Related

VSAM Cobol Files (Array Format): Connector For SSIS

May 14, 2007

Hi ,

Just wondering what is the way to connect to Cobol VSAM files (data in array format) using SSIS ?



Where can I get the OLEDB/ODBC drivers for the same?



Thx,

Gaurav

View 3 Replies View Related

How To Load Several Excel Files In The Same Database (same Structure, But Column Format Different)

May 30, 2008

Hello,

Here's my need :
In one directory there's several excels file. Theses file have the same structure :
col : LOOKID, LOOKNAME, ASMPID, ASMPNAME, LOOKTYPE, LTYP_NAME, PAR_TYPE, PTYP_NAME, PARAMETER, VALUE.
The problem is that the cell format of the PARAMETER col. is different bewteen excel files. It could be date, numeric, ... The col destination in sqlservr database is Varchar(10).

I've created the ssis package with a ForEach Loop, and in the ForEach loop I've created a Data Flow Task.
In the data Flow Task I've created an excell source file (using excel file with col PARAMETER in date format) with an OLE DB destination.

When I launch the package on the same excel file as the one using to create Excel Source object it's OK, no errors, and data in the sql table are OK.
But when I launch the package on Excel file with col. PARAMETER in numeric format, there's no execution errors, but in the destination table the value of the PARAMETER col. is transform in date format.

I tried to change in Excel source object the datatype of the input PARAMETER col, but I've got some compilations errors. It seems that SSIS recognize automaticaly excel source data type col. But may be I did something wrong in the excel source settings ?
Is there a way to force the excel source datatype with varchar(10) ?

I've also tried to do the treatment with an script task but my vb.net knowledge isn't enought to do that.

If you have some suggestions, I'm listening.

Thanks

Luffy

View 1 Replies View Related

SQL 2012 :: Generate Scripts Result In Order Of Tables And Then Stored Procedures In Server

Sep 10, 2014

I have created one table and one stored procedure for to insert/delete/update the data in that table.

So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.

Generating Scripts:

Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts

The generated script output is in a order of stored procedure first and then table.

REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.

Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.

View 6 Replies View Related

SQL 2012 :: Generate Stored Procedures For Select / Insert / Update / Delete On Certain Tables?

Apr 3, 2015

Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?

View 4 Replies View Related

SQL 2012 :: Importing Xer Format Files Through Wizard Takes Longer Time?

Aug 9, 2015

We are importing xer formats through the wizard to sqlserver database and It takes upto 35-45 mins for each import (single project), any option to reduce the time.Is they any other import options - which can give us faster results.

View 0 Replies View Related

How To Create UNIX Format Files Using Flat File Connection Manager?

Dec 17, 2007

Hi All,

How to create a text file in UNIX format using Flat File connection manager. By default when we create a connection manager for flat files it is taking "CRLF" as the delimeter.

I beleive the format of the file will be decided based on the control line feed character at the end of each row. There are different control line feeds for different operating systems.

CR - Mac OS (Carraige return)
LF - UNIX (Line Feed)
CRLF - Windows. (Carriage return Line Feed)

Correct me if i am wrong.

Thanks in advance.

View 3 Replies View Related

Format Of Tables

Jul 20, 2005

Hello,I have the following problem; In my application I have certain items thathave properties.Item Property--------- -----------------------ToyBear Hairy, Soft, BrownToyCar Brown, Plastic, WheelsToyBall Round, Soft, Brown, PlasticAs you might expect I want to do queries on the properties; so propertyBrown should yield all items listed above and Plastic should yield onlyToyCar and ToyBall.The amount of properties is limitless, so making a separate field perproperty seems to be madness. My question is how can I make a (or more)tables that will enable me to search for items by applying one or moreproperties?? What structure do I need to accomplish this?Kind regards, Darius Blaszijk

View 2 Replies View Related

Need Help In Query - To Get Data From 2 Tables In A Particular Format To Display

Jul 14, 2007

Dear FriendsI need to get the data in the required format. Please help----------------------Table TripSheet----------------------Id,TSNo.,JourneyDate(ddmmyy),CustName,RateType,VehicleNo., BillId, journey hours, journey kmDATA
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------Table BillTripSheet-----------------------------Id,BillId,VehicleNo., TotalJourneyHours,TotalJourneyKM,SlabApplied, MinAmt, ExtAmtDATA
1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 502, 234, Vehi99,  13hrs, 250km, SlabA, 500, 50
I need to display data to be displayed in the following format in report(grouping based on vehicle no.)
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 50---------------------------------------------------------------------------------------------------------
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------2, 234, Vehi99,  13hrs, 250km, SlabA, 500, 50----------------------------------------------------------------------------------------------------------
The Calculation logic is lengthly and the number of records involved is huge.also, only the manager has authority to generate the Bill and rest of the team can view the bill report
So i cannot do the calculations while fetching the records, Bill has to be generated and stored. How do i write the query to fetch the data from the two tables "TripSheet" and "BillTripSheet" ? Please Help
ReagardsSara

View 6 Replies View Related

Generate A Separate Txt File For Each Account In A Table, Need To Join Tables To Get Details, And Specify Output File Name?

May 16, 2008

Hey,



I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?



I have table1 contain account numbers and output-filename for each account,

I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.



Here is my code using bcp (bulk copy)

DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)

SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath

WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END

View 7 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved