Is SSIS The Solution To Our Problem???

Mar 12, 2007

Hi All,

A small part of our project needs to be customizable for each of our clients. Each of our clients have the concept of a "JOB"; The details of which are held in their own database. When our product is installed it must gather Job information from their database and match the appropriate fields to our generic Jobs table. Once this has been done, we can generate a stored procedure that will periodically poll the clients database for new/updated jobs and transfer the data to our database.

What we really need is to come up with something that has a generic UI that will allow us to connect to any data source, enumerate the databases and their tables so it will allow someone to perform the operations above.

My question is can SSIS assist us in achieving this goal??

Any help on this would be greatly appreciated.

Paul.

View 5 Replies


ADVERTISEMENT

Solution In SSIS

Jun 1, 2006

Hi,

When I create a new Project, I am not getting a solution name in the Solution Explorer. It is starting from the Project Name only.

So when I create a Package Deployment Utility, I am not getting a DTSInstall.exe file in the deployment folder.

Pls guide me.

Thanks & Regards,
Prakash Srinivasan

View 3 Replies View Related

SSIS Solution Question

Jun 20, 2007

I have been asked to write a script to hit about 50 SQLServer databases to check the individual status of those dbs. and i need help to write this script.

what i need to do is.

1. hit sqlserver#1 and hit all the databases from that box and see whether the sqlserver dbs are
ok or are suspect. if they are anything else other then OK then I send an email via sendfile.
2. hit sqlserver#2 and continue doing this.

is this possible? do anyone of you know how i can do this programatically via an ssis job or have a script that I can use?


I have both SQL2k and SQL2k5

thanks

View 4 Replies View Related

SSIS: Error Opening Solution

Jan 12, 2006

I am trying to open the solution and it is giving me error

"The application for the project \.... est.dtproj is not installed

Make sure application for the project type(.dtproj) is installed."

I tried to open any SSIS solution and it is giving me same error.

Anybody has any ideas how to resolve this problem?

View 7 Replies View Related

Help With Converting Access VBA To Appropriate SSIS Solution

Mar 20, 2007

Hi

I have created a package which imports a unit of measure table. I now want to populate more rows in the same table from itself. In Access VBA the below code extract does the first part of the job I need to do. Can anyone point me in the right direction to convert this into an efficient SSIS solution.
SQLstr =€?SELECT [UOM conv].[Short Item No], [UOM conv].UOM, Count([UOM conv].[UOM 2]) AS [CountOfUOM 2] €œ & _
€œFROM [UOM conv] €œ & _
€œGROUP BY [UOM conv].[Short Item No], [UOM conv].UOM €œ & _
€œHAVING (((Count([UOM conv].[UOM 2]))>1)); €œ

Set RsMoreThanOne = db.OpenRecordset(SQLstr)
With RsMoreThanOne
While Not .EOF
SQLstr = "SELECT [UOM conv].* FROM [UOM conv] WHERE ((([UOM conv].[Short Item No])=" & ![Short item No] & ") AND (([UOM conv].UOM)='" & !UOM & "'));"

Set RsSql = db.OpenRecordset(SQLstr, dbOpenSnapshot)
RsSql.MoveLast
x = RsSql.RecordCount
Set rs = db.OpenRecordset("UOM Conv")
rs.Index = "PrimaryKey"
RsSql.MoveFirst
Item = RsSql![Short item No]
For y = 1 To x
Um1(y) = RsSql![UOM]
Um2(y) = RsSql![uom 2]
Fct(y) = RsSql!factor
RsSql.MoveNext
Next y
For y = 1 To x - 1
For k = 1+y To x
rs.Seek "=", Item, Um2(y), Um2(k)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(y)
rs![uom 2] = Um2(k)
rs!factor = Fct(k) / Fct(y)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(k) / Fct(y)
rs.Update
End If
End If
rs.Seek "=", Item, Um2(k), Um2(y)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(k)
rs![uom 2] = Um2(y)
rs!factor = Fct(y) / Fct(k)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(y) / Fct(k)
rs.Update
End If
End If
Next k
Next y

RsSql.Close
.MoveNext
rs.Close
Wend
.Close
End With
db.Close

Regards

ADG

View 3 Replies View Related

General SSIS Solution Question

Jun 20, 2007

I need to load Excel files, which have 200+ columns. There is a check for every column, some are simple type and range checking, some checking involve a little bit complex logic that depends on other columns. The legacy problem use a VB code check each column one by one, row by row, and color the problematic cells.

- What's the best practice to do it?
- How to generate a human readable error log in SSIS? the output error log is so hard to read.

Thanks,

View 1 Replies View Related

Understanding SSIS Solution Directory Structure

Apr 8, 2008

Say I do File -> New -> Project and make a new project called RedApple under C:. For simplicy, I chose root, but I would designate location for all my SSIS projects. I also check the box "Create directory for solution".

I see that this creates C:RedApple

Under this, I have another RedApple directory with a .sln and .suo files.
In this 2dn RedApple directory, I have .database, .dtproj, and .dtproj.user files.

I deleted the default package1, so I don't have any .dtsx files yet.

As far as the above files are concerned (.sln, .suo, .database, .dtproj, .dtproj.user), I understand these are some kind of system or meta data files and I shouldn't be messing around with them. And that's as far as I need to concern myself on these files.

As I add packages to my project, I see that it is creating .dtsx files under the 2nd RedApple directory. So, I'm thinking these are the package files, one for each package I see in my solution explorer, correct?

MY QUESTION:
Now, I am looking at another SSIS directory tree for a project that I created before I started to pay attention to all this. In this other directory tree, I see that there is also a bin directory, which I do not yet have in C:RedAppleRedApple. In this bin directory I see more .dtsx files, the same ones as I see in the directory above it, but also other .dtsx files that have the names of other packages that I onoce created and deleted along the development of this particular project. What are these .dtsx files?

View 3 Replies View Related

Migrating From SQLServer2000 To SQLServer2005 SSIS Solution

Mar 3, 2006

Hi,

We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.

Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following

a. Import all Look Tables.

b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)

c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)



My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.

Thanks

Jayadev



View 4 Replies View Related

What Is The SSIS Solution To Matching Columns When Using The Lookup Transformation

Jan 9, 2008

How would you do the following in SSIS?

SELECT a.TestID,
a.TestCode
FROM TableA a
WHERE UPPER(RTRIM(a.TestCode)) IN SELECT (SELECT UPPER(RTRIM(b.TestCode)) FROM TableB b)

Of course the above query is missing a few things but with ETL the where clause UPPER(RTRIM does not appear to be something that has an object or property that I can use in the Lookup.

Please correct and educate me.

View 4 Replies View Related

Integration Services :: Alternate Solution To DTC In SSIS Transactions

Jun 2, 2015

I would like to have my SSIS tasks to be transacted, but due to infrastructure issues in our network, cannot utilize DTC, because our SQL Boxes are situated where the DTC cannot communicate. I know we can use execute SQL task with Bein Trans, but I dont think dataflow and all will not be in scope of this and cannot be rollback in case of any error in the underlying tasks.

View 2 Replies View Related

Unable To Create A SSIS Deployment Utility For A Solution Which Are Having 2 Packages

Dec 28, 2007

Hi All,

I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.

For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.

Am I missing something here?

Thanks.
Venkat.

View 8 Replies View Related

Opening Solution Creates Extraneous Xx.database Files In SSIS Project

Feb 9, 2007

I have a solution with a couple of ssis projects in it.  Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file.  The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.

What causes this and how can I prevent this from occurring?

The files contain this:

<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="d3ce9653-3ac5-4ee5-85c3-7d60b2e5f109" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ID>Publish</ID>
  <Name>Publish</Name>
  <CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>
  <LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>
  <LastProcessed>0001-01-01T00:00:00Z</LastProcessed>
  <State>Unprocessed</State>
  <LastUpdate>0001-01-01T00:00:00Z</LastUpdate>
  <DataSourceImpersonationInfo>
    <ImpersonationMode>Default</ImpersonationMode>
    <ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>
  </DataSourceImpersonationInfo>
</Database>
 

There are 2 of us working on the solution and the other fellow does not see this behavior.

The files do not show up in the VS2005 solution explorer.  If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.

Thanks!

View 3 Replies View Related

SOLUTION! - VB.NET 2005 Express And SQL Server 2005 - NOT Saving Updates To DB - SOLUTION!

Nov 30, 2006

VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!

-----------------------------------

The following article is bogus and confusing:

How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

Do not copy








The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

-----------------------------------

The above article is bogus and confusing.

This is rediculous!

This is the most vague and convoluted bunch of nonsince I've ever come accross!

Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.

--------------------------

I've tried it and it doesn't work for me.

Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.



If you have a step-by-step procedure that can be reproduced this properly please PM me.

I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.

Many thanks.



This is the exact procedure I have come up with:

NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.

NOTE 2: DO NOT hand copy the database to a folder/directory in your project.

Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.

Create a folder for your data following NOTE 2.

Copy your data to that folder. * mine was C:mydatamyData.mdf



Create a NEW project.

Remove any Data Connections. ( no matter what)

Save it.

Data | View Data Sources

Add New Data Source

select NEW CONNECTION ( No Matter what, do it!

Select the database. * again mine was C:mydatamyData.mdf

Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.



To Test ----------

From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.

The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.

Save it.

1) Run the app.

Add one database record to the database by pressing the Add(+) icon

Just add some quick junk data that you don't mind getting lost if it doesn't save.

YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Now exit the application.

2) Run the app again.

And verify there is one record already there.

Now add a second database record to the database by pressing the Add (+) icon.

NOW add some quick junk data that you WILL intentionally loose.

*** DO NOT *** press the save icon.

Just Exit the app.

3) Again, Run the app.

Verify that the first record is still there.

Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.

Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.

As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.

Whewh!

I hope this saves me some time the next time I want to start a new database project.

Oh, and uh, for anyone else reading this post.

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

View 17 Replies View Related

SQL 2012 :: Moving SSIS Solution To Another User - Reset User Encryption Key

Oct 22, 2014

I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.

I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?

View 1 Replies View Related

Error On Build Solution, If SSIS Project Is Part Of Build

Mar 22, 2006

Hello,

we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..

C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild

Through build process we get a error:


External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):

We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?

with best regards

Anton Kalcik

View 5 Replies View Related

Best Solution

Dec 3, 2003

Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.

Thanks

View 5 Replies View Related

Need A Solution

Oct 3, 2001

Using SQL 7 (of course), is there a way to give a user access to create and modify Stored procedures without actually giving them DBO rights?

View 1 Replies View Related

Best Possible Solution

Jan 11, 2005

Hi folks, guidance required!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.



Howdy!

View 2 Replies View Related

Just A Solution

Sep 27, 2007

Hello All,
I have a transaction table that holds nearly a million records and my master with 60000 records,I want to fetch details based on two dates.

I use Union operator to query the info from a view,is this right or fetch the result into a temporary table and fetch the result from temporary table using the same Union operator.

Which is the better way to fetch details faster.

Nirene

My SP is pasted below just advice me to execute this more efficiently,cos this SP is called thru a Web application.

CREATE PROCEDURE GL @SDt Varchar(10),@EDt Varchar(10),@Loccode Char(5),@OP Char(4)
AS

Declare @OpBalQry NVarchar(1000),@TrnQry NVarchar(2000),@MainSQry NVarchar(500),@MainEQry NVarchar(500)
Declare @Cocode Char(5)
Declare @CashGL Char(6),@Gldesc Varchar(50)

IF EXISTS (Select 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Gltmp')
Begin
Drop Table #Gltmp
End

Select @Cocode=Cocode from Location Where Loccode=@Loccode
Select @CashGL=Glcode,@Gldesc=Gldesc from Glmast Where Cocode=@Cocode and Subgroupcode='CASH'

Set @OpBalQry='Select A.Glcode,'''' as Trtype,''' + @SDt + ''' as Refdt,'' Opening Balance'' as Refno,
Max(B.Gldesc) as Narration,Case When (Sum(A.Deb)-Sum(A.Cre))<0 Then (-1*(Sum(A.Deb)-Sum(A.Cre))) Else 0 End as Dr,
Case When (Sum(A.Deb)-Sum(A.Cre))>=0 Then (Sum(A.Deb)-Sum(A.Cre)) Else 0 End as Cr from
(
Select Glcode,
(Case When Drcrflag=''D'' Then Opbal Else 0 End) as Deb,
(Case When Drcrflag=''C'' Then Opbal Else 0 End) as Cre from Obdata
Union
Select Glcode,
(Case When Drcrflag=''D'' Then Tramt Else 0 End) as Deb,
(Case When Drcrflag=''C'' Then Tramt Else 0 End) as Cre from Trans_Journal
Where Refdt <Convert(Datetime,''' + @SDt + ''',103)
) A,Glmast B Where B.Cocode=''' + @Cocode + ''' and A.Glcode=B.Glcode Group By A.Glcode
Union
'
Set @TrnQry='SELECT T.Glcode,T.Trtype,Convert(Varchar,Max(T.Refdt),103) as Refdt , '''' AS Refno,
(Rtrim(Ltrim(Max(G.Gldesc))) + '' -- From Sub-Ledger'') AS Narration,
SUM(CASE WHEN T.Drcrflag = ''D'' THEN T.tramt ELSE 0 END) AS Dr,
SUM(CASE WHEN T.Drcrflag = ''C'' THEN T.tramt ELSE 0 END) AS Cr
FROM Trans_Journal T,Glmast G
WHERE T.Glcode=G.Glcode and G.Cocode=''' + @Cocode + ''' and (T.Slcode IS NOT NULL AND Len(Rtrim(Ltrim(T.Slcode)))>0) and
(T.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and T.Refdt <=Convert(Datetime,''' + @EDt + ''',103)
and T.Loccode= ''' + @Loccode + ''' and (G.Subgroupcode<>''BANK'' or G.Subgroupcode<>''CASH''))
GROUP BY T.Glcode,T.Trtype
UNION
SELECT A.Glcode,'''' as Trtype,Convert(Varchar,A.Refdt,103) as Refdt, A.Refno, B.Gldesc AS Narration,
CASE WHEN A.Drcrflag = ''D'' THEN A.Tramt ELSE 0 END AS Dr,
CASE WHEN A.Drcrflag = ''C'' THEN A.Tramt ELSE 0 END AS Cr
FROM Trans_Journal, Glmast B
WHERE (A.Glcode = B.Glcode and B.Cocode=''' + @Cocode + ''') and
(A.Slcode IS NULL OR Len(Rtrim(Ltrim(A.Slcode)))=0) and
(A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''',103))
and A.Loccode=''' + @Loccode + ''' and (B.Subgroupcode<>''BANK'' or B.Subgroupcode<>''CASH'')
Union
Select Glcode,Trtype,Convert(Varchar,Max(Refdt),103) as Refdt,'' '' as Refno,Max(Narration) as Narration,Sum(Dr) as Dr,Sum(Cr) as Cr from
(Select A.Glcode,A.Trtype,A.Refdt,B.Gldesc as Narration,
CASE WHEN A.Drcrflag =''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag =''C'' THEN A.Tramt ELSE 0 END AS Cr
from Trans_Journal,Glmast B
WHERE
(A.Glcode=B.Glcode and B.Cocode=''' + @Cocode + ''' and A.Refdt>=Convert(Datetime,''' + @SDt + ''',103)
and A.Refdt<=Convert(Datetime,''' + @EDt + ''' ,103)
and A.Loccode=''' + @Loccode + ''') and (B.Subgroupcode=''BANK'' or B.Subgroupcode=''CASH'')
Union
Select ''' + @CashGL + ''' as Glcode,Trtype,Refdt,''' + @Gldesc + ''' as Narration,
CASE WHEN Drcrflag =''C'' THEN Tramt ELSE 0 END AS Dr, CASE WHEN Drcrflag =''D'' THEN Tramt ELSE 0 END AS Cr
from Trans_Transnarr
WHERE (Trtype=''CPM'' or Trtype=''CRT'') and Loccode=''' + @Loccode + ''' and
Refdt>=Convert(Datetime,''' + @SDt + ''' ,103) and Refdt<=Convert(Datetime,''' + @EDt + ''',103)) TT
Group By Glcode,Trtype) X'

Set @MainSQry='Select Glcode,Trtype,Refdt,Refno,Narration,Dr,Cr Into #Gltmp from ('

Declare @Fullqry NVarchar(4000)

If @OP='WOB'
Begin
Set @TrnQry = @OpBalQry+@TrnQry
End

Set @FullQry = @MainSQry+@TrnQry

Exec sp_executesql @FullQry

Select Glcode,Trtype,Refdt,Refno,Narration,Ltrim(Rtrim(Str(Dr,14,2))) as Dr,Ltrim(Rtrim(Str(Cr,14,2))) as Cr from #Gltmp SQL2XML
Where Dr+Cr<>0 Order By Glcode,Trtype,Refdt,Refno for XML AUTO

Drop Table #Gltmp
GO

View 3 Replies View Related

Better Solution

Mar 28, 2008

hi,

i want to get following output:
id_order | type | number
-------------------------
1234 | A | 1
1235 |A | 0
1235 |B | 0
1236 |B | 1
1237 |C | 0
1237 |D | 0


create table tbl_order
(
id_order int
,type nvarchar(40)
)

insert into tbl_order (id_order, type) values (1234, 'A' )
insert into tbl_order (id_order, type) values (1235, 'A' )
insert into tbl_order (id_order, type) values (1235, 'B' )
insert into tbl_order (id_order, type) values (1236, 'B' )
insert into tbl_order (id_order, type) values (1237, 'C' )
insert into tbl_order (id_order, type) values (1237, 'D' )
insert into tbl_order (id_order, type) values (1238, 'A' )
insert into tbl_order (id_order, type) values (1239, 'D' )
insert into tbl_order (id_order, type) values (1239, 'B' )
insert into tbl_order (id_order, type) values (1239, 'A' )


select
id_order
,type
--,isnull(orderX,'') as number
,case when orderX > 1 then 1 else 0 end as number2
from tbl_order
left join (select t2.id_order as orderX
from tbl_order as t2
where
(select count(t1.id_order)
from tbl_order as t1
where
t1.id_order = t2.id_order) = 1
)as x
on tbl_order.id_order = x.orderX


Is there any better/faster select sentance to do this? i'm using sql2000.

thank you

View 1 Replies View Related

Possible Solution?

Mar 3, 2008

Hi, i am not sure if i can solve the problem with SSIS. I wanted do do it hardcoded with C# or so.

So, for my project I need to download zip-files on a daily-base. In these zip-files are xml-Files. And in these files is information stored for inserting or updating tables on a MS-SQL-Server.

So, my question, is it possible to solve that with SSIS? And if, is it easy to understand and to learn within a few days?

I haven't found good sources for information on how to get the data out of XML-Files.

View 18 Replies View Related

Any Solution For That?

Jun 15, 2006

I've got a .dts which load data to several Sql2k servers by Copy SQL Server Objects Task. I'd like to add another task for a Sql25k but I can't because of it's requesting SMO.

It seems that such task need DMO. I know that there are other ways for do such stuff in that DTS but I wonder if there is any trick or shortcut for to avoid this barrier.

View 1 Replies View Related

Please Help Me To Get A Solution I

Dec 26, 2007

Dear All expertise please help me

The following is the table structure of Holiday

EmployeeID(Integer)
HolidayFrom(date)
HolidayTo(date)

The following record exist in the table

EmployeeId HolidayFrfom Holidayto
-----------------------------------------------------------------------------------------------
5 25/12/2007 28/12/2007
1 02/01/2008 10/01/2008
2 03/01/2008 03/01/2008
3 25/01/2008 27/01/2008


I want a report to get the employee who did take the leave from the given date range. For example I am giving the date range as follows

Criteria for Report
----------------------------------
date from 03/01/2008
Date to 04/01/2008


When I given the date range as above two record have to be displayed . Because Employee 1 and 2 will be on leave on that date.

EmployeeId HolidayFrfom Holidayto
-----------------------------------------------------------------------------------------------
1 02/01/2008 10/01/2008
2 03/01/2008 03/01/2008

So please help me to find out an SQL to display the records from the given criteria of two date range



View 2 Replies View Related

What Is The Best Solution?

May 25, 2007

Hi all,

I think i have a quite specific problem that we have to resolve with replication. however, i don't know which type of replication to use and how to configure it for this:

To simplify the case a bit:
We have one central server which has a table Sales(id, shopid, article).
Also our 50 shops have the same table Sales(id, shopid, article).

spec 1:
When a shop sells an article, the row of the Sales table at the local shop has to be replicated to the central server. This way, the central server its Sales table has all sold articles from all shops.

spec2:
But now we have also an internet shop. All e-orders arrive immediately in the table Sales at the Central Serve with a specific shopid where the article will arrive. Now i have to find a way to replicate the new rows from the Central Server to the specific Shop.... Remark: the other shops should not have rows of other shops.

which type of replication would i use the best and how?

Thanks !

View 6 Replies View Related

Someone Must Have A Solution For

Feb 12, 2007

The encrypted value for the "LogonCred" configuration setting cannot be decrypted. (rsFailedToDecryptConfigInformation) (Microsoft.ReportingServices.Diagnostics)



Please: a step by step solution.

View 6 Replies View Related

Add New Database To Solution

Mar 20, 2006

I'm running VS 2005 Enterprise, and SQL Server 2005 Developer / workgroup edition.I'm trying to follow a few online examples to play around, and some of these examples require that you add a SQL DB to your solution by going:-right click on website-Add new Item-Add SQL DataBase.WHen I do that, I get the error that SQL Express isn't installed.  Now the question is, why would I want SQL Express installed when I have SQL server 2005 already installed? My Machine can support it, but it's sort of a waste when I need to run SQL Server 2000, SQL Server 2005 and then SQL Server express all at the same time.Any advice?

View 3 Replies View Related

MDX Query Solution

Jan 3, 2002

Dear All,

I'm new in SQL Server 2000. Anyone has a good materials or sites to recommend which talk about Multidimensional Expression? Especially MDX work in "Analysis Manager".

You information are highly appreaciate. Thanking you in advance.

View 1 Replies View Related

Scalable Solution??

Feb 24, 2005

can anyone please point me to any good links for scalable solutions in sql server..

thanks

View 1 Replies View Related

Set-based Solution

Mar 15, 2006

Thought I'd got my head round using a set-based approach but my brain's gone dead on this one


CREATE TABLE #mytable (SWID INT , T INT , DateA DATETIME , DateB DATETIME)

INSERT #mytable (swid , t , DateA , DateB)

SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 , CAST('31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 63967 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 1 ,CAST('31-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 1 ,CAST( '31-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 0 , CAST('15-Mar-2006' AS DATETIME), CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST( '01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) ,CAST( '01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME), CAST('01-Feb-2007'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 , CAST('31-Dec-2005'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '31-Dec-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST('30-Nov-2005'AS DATETIME) , CAST( '01-Aug-2006' AS DATETIME)
UNION ALL
SELECT 10051 , 9999 ,CAST( '30-Nov-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
select * from #mytable order by SWID desc, DateA desc

The Columns where T values are 1 and 0 are OK having already been derived. I need to UPDATE the remaining rows from the Default T Value of 9999
to Decrementing values (starting at -1) commencing at the highest remaining (ie non 9999 T Value) DateA value and working 'backwards'
'grouping' on SWID

The DateB value is irrelevant for this purpose


The desired output is below with derived T values

SWIDTDateADateB
6396712006-03-31 00:00:00.0002006-08-01 00:00:00.000
6396712006-03-31 00:00:00.0002007-02-01 00:00:00.000
6396702006-03-15 00:00:00.0002006-08-01 00:00:00.000
6396702006-03-15 00:00:00.0002007-02-01 00:00:00.000
63967-12006-02-28 00:00:00.0002006-08-01 00:00:00.000
63967-12006-02-28 00:00:00.0002007-02-01 00:00:00.000
63967-22006-01-31 00:00:00.0002006-08-01 00:00:00.000
63967-22006-01-31 00:00:00.0002007-02-01 00:00:00.000
1005112006-03-31 00:00:00.0002006-08-01 00:00:00.000
1005112006-03-31 00:00:00.0002007-02-01 00:00:00.000
1005102006-03-15 00:00:00.0002006-08-01 00:00:00.000
1005102006-03-15 00:00:00.0002007-02-01 00:00:00.000
10051-12006-02-28 00:00:00.0002006-08-01 00:00:00.000
10051-12006-02-28 00:00:00.0002007-02-01 00:00:00.000
10051-22006-01-31 00:00:00.0002006-08-01 00:00:00.000
10051-22006-01-31 00:00:00.0002007-02-01 00:00:00.000
10051-32005-12-31 00:00:00.0002006-08-01 00:00:00.000
10051-32005-12-31 00:00:00.0002007-02-01 00:00:00.000
10051-42005-11-30 00:00:00.0002006-08-01 00:00:00.000
10051-42005-11-30 00:00:00.0002007-02-01 00:00:00.000


Thanks in advance

View 3 Replies View Related

Best Primary Key Solution?

Apr 4, 2006

Hi there,

Looking for a bit of help with my problem:

Say i have 3 tables-

tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)

tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)

tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID(int)


A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

I thought is would be easier to make up a unique key for each client with a script eg

client name: PJ Computers
Unique key Generated: PJCOMP58784

Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

So my question is what's the best way of accomplishing this?

Any help would be much appreciated, thanks :)

View 1 Replies View Related

Backp Solution

Nov 9, 2006

Hi ,

What is the best Backup plan that can be give for a sql databse that is on a very high usage.
Will a six hour backup will decrease the performance of the SQL server...

Sipin

View 1 Replies View Related

Set Based Solution For ....

Jan 8, 2004

CREATE TABLE [Cube_fact_table] (
[ISIN_ID] [bigint] NOT NULL ,
[CLIMSTID] [bigint] NOT NULL ,
[HOLDID] [bigint] NOT NULL ,
[Quantity] [bigint] NULL ,
[Holding] [numeric](15, 0) NOT NULL ,
[Cost] [numeric](18, 3) NOT NULL ,
[Close_Price] [numeric](18, 3) NOT NULL ,
[Tran_Value] [bigint] NULL ,
[Date] [datetime] NULL ,
[UCOA] [bigint] NULL ,
[COA] [bigint] NULL
) ON [PRIMARY]
GO




INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '1', '2000', 2000, 170.100, 170.100, '340200', '11/28/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('1', '1', '3', '-1000', 1000, 185.500, 185.500, '-185500', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '5', '48600', 48600, 225.000, 225.000, '10935000', '11/27/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '8', '-48575', 25, 243.000, 243.000, '-11803725', '12/4/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '9', '12575', 12600, 254.000, 254.000, '3194050', '12/5/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '10', '-12120', 480, 232.000, 232.000, '-2811840', '12/5/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '11', '12120', 12600, 219.000, 219.000, '2654280', '12/6/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '12', '-12120', 480, 265.000, 265.000, '-3211800', '12/6/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '13', '4261', 4741, 224.000, 224.000, '954464', '12/8/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '14', '9059', 13800, 223.000, 223.000, '2020157', '12/11/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '15', '10200', 24000, 233.000, 233.000, '2376600', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '16', '-12188', 11812, 243.000, 243.000, '-2961684', '12/12/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '17', '12188', 24000, 234.000, 234.000, '2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '18', '-12188', 11812, 234.000, 234.000, '-2851992', '12/13/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '19', '788', 12600, 254.000, 254.000, '200152', '12/16/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '20', '8000', 20600, 223.000, 223.000, '1784000', '12/17/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '21', '-13754', 6846, 231.000, 231.000, '-3177174', '12/17/2003 12:00:00 AM', NULL, NULL)
INSERT [cube_fact_table] ([ISIN_ID], [CLIMSTID], [HOLDID], [Quantity], [Holding], [Cost], [Close_Price], [Tran_Value], [Date], [UCOA], [COA]) VALUES ('2', '1', '22', '10554', 17400, 245.000, 245.000, '2585730', '12/19/2003 12:00:00 AM', NULL, NULL)





Now what i want to do is this ...

UCOA is to be updated with the average of all previous transactions where the climstid and isin_id are the same.

example

take case where holdid(transaction no) in the sample data is 14.
What i need is a query which will sum up the data in the tran_value column upto holdid 5.

Again I need a set based solution. Gurus .. please help

View 3 Replies View Related

What Is The Best Backup Solution?

Mar 2, 2004

SQL Gurus,

What is the best backup solution?

Product:MS SQL 2000 Enterprise Edition
Environment:webserver

Goal:Off-site backup of database without onsite tech intervention

Obstacles:
* Webhost does not want SQL Server Agent running for fear of viruses
* Database is 3.4GB, LDF is 3.7GB, and a Full Backup is 3.1GB.

Connection / Access:Enterprise Manager, RDP and FTP all work to access data and backups.

I have looked into doing a full backup then differential backups, doing transaction replication, and log shipping. The client doesn't really need replication, he just wants to know that if his server with the production database goes up in flames he has a way of getting back in business without losing more than one day of data.

I am hoping there is a method of "restoring" on his off-site server the data that is "backed up" on the production server. Is that possible if we're doing differential backups?

Is that the best solution or is there a better way?

Thanks for any recommendations or advice,
Alec Sherman

View 13 Replies View Related







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