Created View With Join Query - Unable To Fetch Details

Feb 12, 2014

I created a view with a simple join query

there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as

"select orderid from joins where orderid=NULL"

Query :

create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid

View 1 Replies


ADVERTISEMENT

FETCH Index Details Of A Table

Jan 23, 2008

I Use the below QUERY to fetch the index details of a table from SQL Server 2000 database .

EXEC sp_helpindex 'mytable'

Is there any alternative for the above query .
Because the above query is not fetching some indexes in some version of SQL Server database .

I am trying the above query against SQL Server 2000,2005 and SQL Server 7 versions .

View 4 Replies View Related

Problem With Query On A View Created By A Union

Jun 27, 2006

Hi,

I have a view V1 created as a plain UNION from 2 tables:

SELECT * FROM T1
UNION
SELECT * FROM T2

If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned.
However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.

Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?

thx,
smiley61

View 2 Replies View Related

Transact SQL :: Powershell Script To Fetch Database Usage Details For Multiple Servers (report)

Oct 28, 2015

Is there a way to fetch database usage details for multiple SQL servers (report) usirng powershell script.

Details: servername, databasename, datafile usage, logfile usage, free % age...etc.

View 3 Replies View Related

Fetch Data Of User Who Have Created Profile Within 7 Days - DateDiff Function

May 2, 2015

I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .

I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.

select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC

System.DateTime.Now is a function for getting current date time in C#

The query is neither giving error nor giving desired result.

View 4 Replies View Related

T-SQL (SS2K8) :: Unable To Fetch A Value From XML?

Aug 5, 2014

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<Attributes><Attribute name="ABCD">50405</Attribute></Attributes>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT * FROM OPENXML (@DocHandle, '/Attributes/Attribute')
WITH (name varchar(100),
Value varchar(100) '@name/text()[1]' )
EXEC sp_xml_removedocument @DocHandle

THe result of the above query is

nameValue
ABCDABCD

How do we get the value 50405 from the above xml

View 5 Replies View Related

Cross Join/? Query In A View Help

Feb 4, 2004

Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|

#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1

How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).

Thanks

robbied111

View 2 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

Fetch Metadata From A Column With An Alias In A View

Aug 2, 2007

Hi!

I have created a view and one of the columns in the view has an alias assigned to it.

I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup
from which table each column in the view orginated from except for the column that has an alias assigned to it.

Is there any other way to lookup a column that has an alias assigned to it?


Thanks alot!

Adam

View 2 Replies View Related

View Transaction Log Details

Aug 31, 2004

Please may I have your assistance with the following
I’m struggling to view the transaction log for a database.
I’m using the following command structure

DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length)

2 - very detailed information (plus object name, index name,
page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump
of the current transaction log's row.

-1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XACTID

Which I got from a web site, however the output does not make sense to me

Ideally I would like to analyse which SQL insert commands are predominantly using the log file

Any ideas how to do this or a tool that u recommend

Thanks in advance for your help
:confused:

View 4 Replies View Related

View SQL Transaction Log Details

May 30, 2006

I have a client site running SQL 2000. It seems some data rows havebeen deleted and several tables have been dropped. I have a current dbbackup which contains a 34GB log file, but I don't know how to view it.Is there any way to search through the log file and determine when thedata was lost or what process/user dropped the tables?Thanks,Tull.

View 1 Replies View Related

Insert To A Sql Database From A Details View!?!?!!!?!?

Apr 12, 2007

I have tried just about everything i could find on the internet to fix this.
Anyway.....I have a sql databasewith 3 colums: Client ID, Client Name, and Client Address
I have no trouble reading the database through the gridview, but when i try to add a script that allows me to add to that database off of my site, I get error after error.
Anyone who can figure this out must be a sql genius (or I am a complete idoit). 
Here is the code I have so far: 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<script runat="server">
Dim strClient_Name As String
Dim strClient_Address As String
</script>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Client ID"
DataSourceID="SqlDataSource1" Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="Client Name" HeaderText="Client Name" SortExpression="Client Name" />
<asp:BoundField DataField="Client Address" HeaderText="Client Address" SortExpression="Client Address" />
<asp:BoundField DataField="Client ID" HeaderText="Client ID" InsertVisible="False"
ReadOnly="True" SortExpression="Client ID" />
<asp:CommandField ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:clientInfoConnectionString %>"
InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"
SelectCommand="SELECT [Client Name], [Client Address], [Client ID] FROM Clients">
</asp:SqlDataSource>
</asp:Content>
 
Best Regards and Thank You,
The King

View 6 Replies View Related

Details View ItemUpdating Problem

May 14, 2008

 
Good dady to everyone, can someone help me with the code in item updating in detailsView, here is my fields ID w/ Primary Key, LastName, FirstName, MiddleName, Address,TelNo,CelNo, EmailAddress, Nationality, [Marital Status], Gender, Age, BirthPlace, BirthDate im using ms access database and microsoft visual studio here is my code pls help me... 
good dady to everyone,
can someone help me with the code in item updating in detailsView, here is my  fields
ID w/ Primary Key, LastName, FirstName, MiddleName, Address,TelNo,CelNo, EmailAddress, Nationality, [Marital Status], Gender, Age, BirthPlace, BirthDate
im using ms access database and microsoft visual studio here is my code pls help me...
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class Details : System.Web.UI.Page
{string stcron = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Database\db1.mdb;Persist Security Info=False";
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection();System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
System.Data.OleDb.OleDbCommand dc = new System.Data.OleDb.OleDbCommand();protected void Page_Load(object sender, EventArgs e)
{AccessDataSource1.DataFile = ConfigurationSettings.AppSettings["dbLocation"].ToString();
cn = new System.Data.OleDb.OleDbConnection(stcron);DataTable dt = new DataTable();
int ID = Int32.Parse(Request.QueryString["rowID"]);AccessDataSource1.SelectCommand = "Select * from tblUser where id=" + ID + "";FormView1.DataSourceID = "AccessDataSource1";
 
 
 
}
 protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{TextBox Textbox1 = (TextBox)PreviousPage.FindControl("Textbox1");
TextBox Textbox2 = (TextBox)PreviousPage.FindControl("Textbox2");TextBox Textbox3 = (TextBox)PreviousPage.FindControl("Textbox3");
TextBox Textbox4 = (TextBox)PreviousPage.FindControl("Textbox4");TextBox Textbox5 = (TextBox)PreviousPage.FindControl("Textbox5");
TextBox Textbox6 = (TextBox)PreviousPage.FindControl("Textbox6");TextBox Textbox7 = (TextBox)PreviousPage.FindControl("Textbox7");
TextBox Textbox8 = (TextBox)PreviousPage.FindControl("Textbox8");TextBox Textbox9 = (TextBox)PreviousPage.FindControl("Textbox9");
TextBox Textbox10 = (TextBox)PreviousPage.FindControl("Textbox10");TextBox Textbox11 = (TextBox)PreviousPage.FindControl("Textbox11");
TextBox Textbox12 = (TextBox)PreviousPage.FindControl("Textbox12");TextBox Textbox13 = (TextBox)PreviousPage.FindControl("Textbox13");
int rowID = Convert.ToInt32(e.Keys["ID"].ToString());if (Textbox1 != null)
{
cn.Open();
dc = cn.CreateCommand();string strQry = @"UPDATE tblUser Set (LastName,FirstName,MiddleName,Address,TelNo,CelNo,EmailAdd,Nationality,[Marital Status],Gender,Age,BirthPlace,BirthDate)" + "Values ('" + Textbox1.Text + "','" + Textbox2.Text + "','" + Textbox3.Text + "','" + Textbox4.Text + "','" + Textbox5.Text + "','" + Textbox6.Text + "','" + Textbox7.Text + "','" + Textbox8.Text + "','" + Textbox9.Text + "','" + Textbox10.Text + "','" + Textbox11.Text + "','" + Textbox12.Text + "','" + Textbox13.Text + "')";
dc.CommandText = strQry;
dc.ExecuteNonQuery();
cn.Close();
}
}
}
tnx in advance
migs

View 8 Replies View Related

Adding An Image To Details View

May 20, 2006

I'm some what of a noob when it comes to ASP.NET 2.0 and SQL Server 2005 Express editions, so please bare with me. I have created a basic database that is only one table.  One of the fields is LogPicture with type of image. I just want to go into the table and manually place a picture in the table, but it seems like I can not get the format down.  I have tried including the picture I want into the project, but no dice.  I've also tried putting the physical pathname (on the harddrive) and the web address of this field in there.  Suggestions please?

View 2 Replies View Related

Inserting Records Using The Details View Or Programmatically

Aug 26, 2006

I'm a new user to vwd.  If I use a details view control on my page, I have noticed that the "New" link is not visible unless there is at least one record in the table. Is there any way of making it visible where there aren't any records?My web pages are currently hosted at vwdhosting. I have uploaded my database with the record structure onto the web site and I am using a remote connection string to access it. I have had users updating data in another table on the remote database. If I add records to my new table locally and upload the database to the remote site, all the data that my users have been adding will be lost. So, if I can't add my first record using a control on my web page when there are no records in the table, should I be doing it programmatically? If so, how?Thanks,Julie 

View 2 Replies View Related

Join Invoice Total Record To Details?

Oct 1, 2012

If you will I am trying to join a master invoice table to its detail records. The problem is I can't quite get the records to match correctly. There is a master record that has the net total of the invoice that corresponds to however many detail records for that invoice. I am attempting to get the records to line up in a query. I am having trouble because the key fields match the total up with each detail record. So for example in this record set below the 3825.75 value appears for each detail record so when I total the invoice column the figure is way too high. The detail has a 4462.54 and a -636.79 for a net of 3825.75. I tried to line the example up for better illustration. I copied it off a pdf and I am trying to replicate it programmatically.

0712RW-IN 7/31/2012 8/30/2012 4,462.54 0.00 3,825.75 INV 7/31/2012 4,462.54
C/M 8/31/2012 636.79- Reference: 0712RW
CREATE TABLE [dbo].[INVOICE](
[SRC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ARDIVISIONNO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CUSTOMERNO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Code]....

View 1 Replies View Related

Unable To Save Already Created Package

Sep 28, 2000

I have about 100 DTS packages and they were created by a user who has left the company. Now I( the new user)am trying to open the packages from another client machine and trying to save them back. I am connecting to SQL server where the packages are stored using NT authentication(domain account).
When I try to save the package, I get the following error

Ole DB, Only the owner of the DTS package "packagename", or a member of the sysadmin role can create new versions of it".

What I have tried is that I can save the same package with a new name without being a member of the sysadmin role but I cannot save the package with the same name.
ANy help will be greatly appreciated.

View 2 Replies View Related

Grid And Details View Not Showing Remote Database Data

Oct 27, 2006

hi,i have an sql database on my server on the world wide web.i can make a connection  to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection  by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database  C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas? 

View 2 Replies View Related

Unable To Login To Database Created By Admin

Oct 12, 2007

Using SQL Server 2005 under XP Professional.  I am attempting to log into the Server Management Studio as "UserX".  I had previously been using Server Management while logged in as admin.
 I now get this error message when attempting to connect to the database server:
 **********************************************************************TITLE: Connect to Server------------------------------Cannot connect to [**NAME OF SERVER**].------------------------------ADDITIONAL INFORMATION:Login failed for user '[**NAME OF SERVER**].UserX. (Microsoft SQL Server, Error: 18456)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476------------------------------BUTTONS:OK------------------------------
*******************************
 Microsoft help doesn't have a specific help for that particular error message.  Any hints as to what is going wrong?

View 4 Replies View Related

Is There A Way To Open SSRS 2005 Tree View In A Table In A Details Row Upward?

Apr 4, 2008

Hi.

I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).

Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?

I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.

Thanks
Novi

View 2 Replies View Related

How To View/edit SSIS Packages Already Created?

May 7, 2007

I created a few in SqlServer2005 DEV version and ran them manually with success. But where are they? I chose the SqlServer storage option and I see them when I create Job and steps but how do I view/edit these packages from SqlServer Mgmt. Studio? The SSIS service is running.

View 6 Replies View Related

Created View In Sql Management Studio Not Showing In Ssrs

Mar 17, 2008

hi all

Using Sql Server 2005 / Reporting Services

I created a view in sql server management Studio but can't see it in Reporting Services.

I appear to have the same security permissions?

what else am I missing?

thanks
jewel

View 1 Replies View Related

Referencing Colum Names In Recordset Which Is Created By Join - URGENT!

Jul 30, 2006

Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam

View 5 Replies View Related

Analysis :: How To Save Parameter From A Report Created In Browse View Under Cube

Nov 10, 2015

After connected to Analysis Services and following the path below:

Analysis Service - Databases - Cubes

I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.

I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?

View 3 Replies View Related

Help Me Out W/ My Query.. Should I Use Fetch?

Mar 13, 2008

hi,


i want to be able to get the columns from my table except for its pk. the long method is to selecting all columns by listing them one by one right? but what if i got 100 columns..

so.. i wanted to use the information_schema..

i have:

use dbase;
select column_name
from information_schema.columns
where table_name = 'table1'
and column_name != 'pkid'

so there i'm listing the columns, but how am i suppose to select those columns from my real table?



thanks in advance!

View 4 Replies View Related

Three Table Join - Display Newest Comments Left On Photos Created By Members

Jul 6, 2014

I am trying to tweak some code which is used to display the newest comments left on photos created by my members.

The existing code is this:

SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC

So the latest comment left was for photo #210879 from user "Cla" (redacted user names). The 2nd newest comment would be for photo #211072 from a member named "mo". pdate is a date field

However for the script I have coded I don't want all of the photo comments to show up. This is because I use access levels based on the type of location (higher levels mean more restricted galleries). I check the access levels as I go through the recordsets.

I use this method to get the top 15 comments:

SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC

Now I have to use two other tables to determine the access level. Since PHOTOCOMMENTS is just a list of photo #'s and the people who left comments for those photos, I need to:

a) determine what location the photo is from and
b) determine the access level of that location

I use: select creator,access from locations where id=(select dir from photos where id="&pnumber&")"

This is a two step process as you can see. The first part is:

select dir from photos where id=(pnumber)

ID is the same value as pnumber seen in PHOTOCOMMENTS. That is to say PHOTOS.ID = PHOTOCOMMENTS.PNUMBER

If I haven't confused you yet, the executed code for the first example would be:

select dir from photos where id=210879

which would get me a value for DIR. DIR is the location number which would be:

select creator,access from locations where id=(dir value)

Just to simplify it a bit....

There are three tables (shown below)

PHOTOCOMMENTS
PHOTOS
LOCATIONS

I need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)

but then also

select creator,access from locations (The last table shown)
where id=(select dir from photos where id="&pnumber&")"

So the first table PHOTOCOMMENTS has to also join PHOTOS table where PHOTOS.DIR = PHOTOCOMMENTS.PNUMBER in order to get the value of "DIR" and then DIR is joined to the LOCATIONS tables where PHOTOS.DIR = LOCATIONS.ID

Here is the actual code, which I am trying to make into a single SQL command

strSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC"
set ors = oconn.Execute(strSQL)
tl = 0
do until ors.eof or tl > 15
' until we have 15 results because not every recordset will be of the proper security level

[Code] ....

Bonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2

View 4 Replies View Related

Query To Fetch Data

Jul 23, 2005

Hi,I have a table with two columns Task and Employee. It lists all thetasks and the assigned employees. The same task will have multipleroles with an employeename or 'manager' as the data.If I have the following data'sales', 'john''sales', 'manager''dev', 'manager''make_coffee', 'manager''browse', 'jane''browse', 'manager'I need to get the rows wherever an employee is named (sales and browsefor example) and get manager for the rest.I can make it in two queries. Look for not manager in one and then formanager. Is there anyway to get them in a single query?If I need to look for 'sales', I need to get 'john' and not 'manager'.How to do that in a single query?Another need is to list all tasks with assigned. So for the above, Ishould get the following list'sales', 'john''dev', 'manager''make_coffee', 'manager''browse', 'jane'with two queries, I will get'sales', 'john''browse', 'jane''dev', 'manager''make_coffee', 'manager'which is ok. Order/sequence is not important.TIA,Sreelatha

View 2 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

Query To Fetch Latest Record

Jun 29, 2012

Table has details like below:

NAME UPATE-TIMESTAMP
==== ===============
mary time1
mary time2
mary time3
tom time1
tom time2
tom time3
tom time4
richard time1
richard time2

Output Expected:

NAME UPATE-TIMESTAMP
==== ===============
mary time3
tom time4
richard time2

In summary, the requirement is to fetch the latest upated record for each unique NAME.

View 1 Replies View Related

Fetch Query That Triggered The TRIGGER

Dec 26, 2006

HelloThe problem is need to find out the querry that has updated or insertedinto the table and in turn 'Triggered the Trigger'. I have the username, the machine name, Application name, but not the query. The updateis not desired and the application is doing it but the applicationbeing so large we are unable to pin-point the code which is doing thedammage.Pls help!RegardsAnubhav

View 1 Replies View Related

Need Details On Query Profiler

Jul 23, 2005

Where is the information coming from that is displayed in queryprofiler.Is it the command that is retrieved before it is processed, or is itwhat is actually processed.

View 3 Replies View Related

Sql Query To Details About Database

Sep 11, 2007



Is there any query to list all tables,column name,datatype,size,total record in each tables for particular database
ex
Database:Northwind
Tables:categories,(categoryid varchar(50),Category name varchar(50)...............),1023kb ,25 records like this
i think there is no such a simple query...
Atleast tell How to get all column names and datatype of particular table and size of table

View 5 Replies View Related

Retrieve All Names That Don't Have A Year (was Query To Be Written To Fetch ....)

Feb 3, 2005

Having two tables.

1. ID Year
----------
1 1990
2 1991
3 1992



2. Name Year
--------------
ABC 1
XYZ 2
123 3


By passing year as an argument, Want to retrieve all the Names that does not have records for that particular year.

For example, if pass 1990, then the result set should be 'XYZ' and '123'

Thanks in advance

View 2 Replies View Related







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