Hi Guys
I have two tables ##tablerecoveryTemp and middba
the 2 columns in the tables I am interested in are netword_id from ##tablerecoverytemp and mid from middba
I want to insert a 2 into a ##error if any of the netword_id is in the middba table in the mid column. Any help would be great thanks.
something like
If exists (select * from ##tablerecoveryTemp where (network_id = middba.mid)
Begin
Insert into #Error values (2)
End
I have data as below: select ID,code,go,back from tableA
ID code go back
2 C US UK
2 A UK US
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
I only want to pull out whole ID group which code Z exist. I tried as below:
select ID,code,go,back from tableA A
where exists
( select ID,go,back
from tableA B with (nolock)
where A.ID = B.ID
and A.code='Z')
But it only appear:
ID code go back
1 Z UK US
How can I appear with the group as well?
ID code go back
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
Hello all,
I have a branch a data, now i need to search through a database to check whther it is exist in that database or not, any syggestion?
Example:
Now i have data 123, 234, 345. Let say data 123 and 234 is exist in that database, but data 345 is not exist in that database.
What SQL am i suitable to use to get those result exist(123, 234) and those result not exist (345)?
The database structure is someting like this:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
3 345 345
1) The data consider 'exist' if it exist in column NAME or column O_NAME (either one).
2) The data consider 'not exist' if it not exist in neither NAME column nor O_NAME column
After the SQL query, someting is expected:
Exist:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
Not Exist:
ID NAME O_NAME
-- ----- --------
3 345 345
I'll appreciate if anyone can provide me a solution..Thanks!
I creating disaster recovery plans along with my Y2K testing. As I upgraded from 6.5 to 7.0 on my test server I noticed this sql switch that seems to toggle between the 2 versions. Is this true? Can you run both versions on the same server with a different application running on each?
View 2 Replies View Relatedppl,
how do i use the exist command to check for data existance?
i need to transfer multiple rows from table1 to table2 but not the duplicated one,
anyone help
some one told me to use statment where not exists, but i don't know how, i need an example and if there is another solution kindly assist.
Hi all.
Can anyone tell me how to search a column for a value........for example 'STANDARD' and if it doesnt exist, enter it?
thanks all.
This question is related to my post: how to copy a table from one database to another
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1
I decided to start a new thread for this issue because it is so frustrating. I am hoping someone knows the answer to this specific question.
Why would sql server insist that a database does not exist even though it obviously does exist?
I get the error message: Database 'tf_1' does not exist.
Why would it say it does not exist when it is clearly viewable in SMSS. It attached without problem. I can view the tables in the database. I know it's there; SMSS knows it's there; the VB app I wrote knows it's there. But, when I try to run a query, sql server says it doesn't exist! What gives?
I am receiving an error that the server does not exist, due to this command on my webconfig page. I am using VS 2003. The name of the database I am using does not show up anywhere in this command. Should it? How will it know which server to access my database from? The database is called epscor (see data source). Any clues? Thanks, Steve
<sessionState mode="SQLServer" sqlConnectionString="data source=epscor;user id=xxxx;password=xxxxx" cookieless="false" timeout="60"/>
I have created windows userid and i am running my web application using "windows" authentication
i have created login id for the windows user id in sql enterprise manager and given access to database.
my web.config file has the following connection
<appSettings>
<add key="connectionstring" value ="Server=APTGsh;Truested-Connection="true"Database="aspnetstore" />
</appSettings>
But after this also i am getting the error message" sql server does not exist". It is very difficult to find where the problem is
help would be more appericated.
i am able to connect to the server using the following syntax
<add key="connectionstring" value="server=apt;uid=sa;pwd=ganesh;database=aspnetstore" />
but i am trying is using windows authentication and not forms authentication.
I want to delete an tables if it existand i also want o delete stored procedures if existhow is its syntax?
View 1 Replies View Relatedhi,
i am posting again because i have not find solution so far...
i am using dot net version 1.1. and i am able to make connection throught server explorer.the connection is comming successful on test connection.
but when i am putting this object in code behind like
SqlConnection1.Open()
then it is giving error
sql server does not exists.
i am not able to connect with following string also
con = New SqlConnection(" server=***;database=***;uid=***;pwd=***")
con.open
while this string is working fine means in my web site this is working live .
but when i want to connect to a server which is located in my office then it is not making connection
office server - microsoft windows server 2003
while making connection with the same string when i am using dot net version 2.0
We formatted and prepared a new server for production running Win 2003 server.
The database is set up for the SQL Server 2000 sp4
I have all permissions set exactly as the staging server and can access that with no problem.
I keep getting this error for the prod server:
SQL Server does not exist or access denied.
Now, I played around with the conn string for the stage server and reproduced the error ONLY when I changed the name to the server to one that did not exist.
We are on a local network and can access the new prod server - the sql database via SQL Server Enterprise Manager. The database I created is there and running.
I have narrowed it down to it must be something with the server itself.
Does IIS need to also be running on the server?
The .Net Framework?
All permissions are set but is there something else I might check?
Thanks,
Zath
Hi All,
When i try to connect to My server, i get a message..
"the server does not exist" . the server is a SQL Server 7.o server.
Any help would be great.
Thanks
Shiva
Hi,
I'm completely stuck in here.
I have two tables, for simplicity i'll call them tbl_X and tbl_Y and i'll call the unique key Ukey
I need to create a stored procedure that must be run daily on a scheduled time. When executed, it must compare these two tables and insert rows from tbl_X into tbl_Y when a row exists in tbl_Y but not in tbl_X.
The following code returns the rows that are in tbl_Y, but not in tbl_X:
----------
SELECT Ukey
FROM tbl_X
WHERE NOT EXISTS
(SELECT Ukey
FROM tbl_Y
WHERE tbl_Y.Ukey = tbl_X.Ukey)
----------
But....how do i insert these rows into tbl_X ?
I've tried to declare the tbl_Y.Ukey and use that to do an INSERT statement, but that didn't work out.
Any help or example code is highly appriciated!
hi,
I would like asked an opinion about the system of my coding if is ok? I have one table here to modify. My point here is to count the record to avoid a duplicate record .
rs.open "Select count (field) as name from table Where field='001'",cn
If rs!name > 0 then
Update sqlstatement
Else
Insert sqlstatement
Endif
Any suggestion pls..
Hi guys,
I have 4 child table that contain same fieldname. Data inside this 4 table might be redundant among the 4 table. I call it as tbl1, tbl2, tbl3 and tbl4 fieldname is id_cert. what i mean redundant is, let say in tbl1 got data 1001 maybe in tbl3 also got this id. So my problem is i want to check whatever data in this 4 tbl is not exist in master table call masTbl(fieldname to check is id_no). can I do this without using many inner join script and generate dunmp table? Pls help me on this. Thanks in advanced.
Regards,
Shaffiq
Hi guys help please..is there a function in MS SQL that check if a particular value exist in a row and would return a boolean value base from what found, Return True if it found something and False if it does not found one. I've try the EXISTS function but I cant get the rigth syntax..Any help will be greatly appreciated!
OR Maybe you can help me directly with my problem. I want to check first in my Table 1 with 3 columns if value X exists in column 1 and if X exists UPDATE that column with value Y and if value X does not exists INSERT something in the Table 1. Any suggestion or Comments will be greatly appreciated!
Hello,
How can find an column has index or not , using T-Sql ?
Kind Regards,
sasan.
ACADEMIC_YEAR COURSE_REFERENCE TERM
------------- ---------------- --------
2014 -1465586182 TERM4
2014 -772484485 TERM4
With the above data, user must check and delete the row only if exist
I have query some thing like this:
DELETE FROM epas_publish_course where COURSE_REFERENCE IN ( SELECT * FROM (SELECT COURSE_REFERENCE FROM epas_publish_course WHERE COURSE_REFERENCE) AS X) ;
which deleting the complete table but i need to delete one particular row...
does anyone know if this
execresultset
exists, ive created a table with queries using this:
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME('<insert_username>')
set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
but im trying to execute each result in the table
Hi,
i am repeatedly getting this error while trying to open connection.Sql Server does not exist or Access Denied.I am trying to connect to my local server.
Dim inscmd As New SqlCommand
myconn = New SqlConnection("server=local;database=TEST;Trusted_Connection=yes;user id=;password=;")
inscmd = New SqlCommand("Insert into Details(ID,Name,Address) Values (@ID,@Name,@Address)", myconn)
inscmd.Parameters.Add("@ID", SqlDbType.NChar, 10)
inscmd.Parameters.Add("@Name", SqlDbType.VarChar, 40)
inscmd.Parameters.Add("@Address", SqlDbType.VarChar, 40)
inscmd.Parameters("@ID").Value = txtId.Text
inscmd.Parameters("@Name").Value = txtId.Text
inscmd.Parameters("@Address").Value = txtId.Text
Try
inscmd.Connection.Open()
inscmd.ExecuteNonQuery()
inscmd.Connection.Close()
Plz correct me..
Hi,
We have snapshot replication set up on a SQL 2005 box where the publisher is a SQL 2000 box. Replication was running properly for weeks. Because of some application upgrades, we had to remove the publication and subscription and re-apply once the upgrade was done. Replication is still running properly, however when we look at the job history on the subscription, we get an error message:
Could not show the Properties window
The specified @job_id (.....) does not exist. (Microsoft SQL Server, Error: 14262).
I suspect this is referring to the old job but am not sure how to clear this from memory. I can view the job history on the actual job within SQL Agent and the job is running properly, but this error still occurs when I view the history on the subscription itself.
I did run
select * from MSreplication_subscriptions
And only see one record which I assume is the current job...
Any information would be great.
Thanks!
I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.
Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)
Can anyone give me a clue what to do?
I have a variable scoped to the package named "Filecount". When I try to reference it in a precedence constraint expression (@Filecount > 0) I get "The variable 'Filecount' was not found in the variables collection. The variable might not exist in the current scope."
Anybody know why? Thanks.
I execute the following:
-- Turn on advanced options
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- turn on clr
sp_configure 'clr enabled', 1
GO
RECONFIGURE;
GO
and get this error:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
When I run surface area config, I select Configuration for Features, but under MSSQLSERVER, I do not see the entry for Database Engine that should have the CLR Integration under it.
Any suggestions? Thanks in advance.
I tried File System Task component,
it seems can not perform this task?
any ideas? thanks
here is my original Sql Code I add a parameters @CustId in line 13 when CustID not Null, I want to add this to the Where clause line 146 and 156 if CustId not null then where clause will add a reference like CustId = @CustIDif CustId is Null then not reference @custID how to Add a if ~else clause to my code? i tried all day.. but it doesn't work.. 1 SET QUOTED_IDENTIFIER OFF
2 GO
3 SET ANSI_NULLS OFF
4 GO
5
6
7
8 ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]
9 @DTBEG DATETIME,
10 @DTEND DATETIME,
11 @Remark INT,
12 @BankId VARCHAR (128)
13 @CustID CHAR
14
15
16 as
17
18
19
20 SELECT
21 *
22 FROM
23 (
24 SELECT
25 ZT_Master.PriKey,
26 ZT_Master.BankId,
27 ZT_Master.TDateTime,
28 ZT_Master.PNo,
29 ZT_Master.Remark,
30 ZT_Master.CustId,
31 ZT_Master.ProcStatus,
32 ZT_Customer.[Name],
33 --ZT_Customer.AccountBAK AS Account,
34 --ZT_Customer.SCAccountBAK AS SCAccount
35 (SELECT TOP 1 SUBSTRING(PCLNO, 3, 12) FROM ZT_Detail WHERE ZT_Master.PriKey = ZT_Detail.MasterKey AND ZT_Detail.TXTYPE = 'SD') AS Account,
36 (SELECT TOP 1 SUBSTRING(PCLNO, 3, 12) FROM ZT_Detail WHERE ZT_Master.PriKey = ZT_Detail.MasterKey AND ZT_Detail.TXTYPE = 'SC') AS SCAccount
37 FROM
38 ZT_Master LEFT JOIN ZT_Customer ON ZT_Master.CustId=ZT_Customer.Id
39 ) a
40 ,-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41 (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
42 CtcbBanSDTotal=ISNULL(本行代收ç†æ•¸,0),
43 CtcbBanSDTotalAMT=ISNULL(本行代收金�,0),
44 OtherBanSDTotal=ISNULL(他行代收ç†æ•¸,0),
45 OtherBanSDTotalAMT=ISNULL(他行代收金�,0),
46 CtcbBanSCTotal=ISNULL(本行代付ç†æ•¸,0),
47 CtcbBanSCTotalAMT=ISNULL(本行代付金�,0),
48 OtherBanSCTotal=ISNULL(他行代付ç†æ•¸,0),
49 OtherBanSCTotalAMT=ISNULL(他行代付金�,0),
50 GoodSDTotal=ISNULL(代收æˆ?功ç†æ•¸,0),
51 GoodSDTotalAMT=ISNULL(代收�功金�,0),
52 GoodSCTotal=ISNULL(代付æˆ?功ç†æ•¸,0),
53 GoodSCTotalAMT=ISNULL(代付�功金�,0),
54 BadSDTotal=ISNULL(代收失敗ç†æ•¸,0),
55 BadSDTotalAMT=ISNULL(代收失敗金�,0),
56 BadSCTotal=ISNULL(代付失敗ç†æ•¸,0),
57 BadSCTotalAMT=ISNULL(代付失敗金�,0)
58 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
59 本行代收ç†æ•¸,
60 本行代收金�,
61 他行代收ç†æ•¸,
62 他行代收金�,
63 本行代付ç†æ•¸,
64 本行代付金�,
65 他行代付ç†æ•¸,
66 他行代付金�,
67 代收æˆ?功ç†æ•¸,
68 代收�功金�,
69 代付æˆ?功ç†æ•¸,
70 代付�功金�
71 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
72 本行代收ç†æ•¸,
73 本行代收金�,
74 他行代收ç†æ•¸,
75 他行代收金�,
76 本行代付ç†æ•¸,
77 本行代付金�,
78 他行代付ç†æ•¸,
79 他行代付金�
80 -------------------------------------------------------------------代收æˆ?功ç†æ•¸èˆ‡ä»£æ”¶æˆ?功金é¡?--------------------------------------------------------------
81 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
82 本行代收ç†æ•¸,
83 本行代收金�,
84 他行代收ç†æ•¸,
85 他行代收金�
86 FROM (SELECT MasterKey,
87 COUNT(PriKey) AS 本行代收ç†æ•¸,
88 SUM(AMT) AS 本行代收金�
89 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) = '822' GROUP BY MasterKey) a
90 FULL JOIN (SELECT MasterKey,
91 COUNT(PriKey) AS 他行代收ç†æ•¸,
92 SUM(AMT) AS 他行代收金�
93 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) <> '822' GROUP BY MasterKey) b
94 ON a.MasterKey=b.MasterKey) a
95 -------------------------------------------------------------代付æˆ?功ç†æ•¸èˆ‡ä»£ä»˜æˆ?功金é¡?---------------------------------------------------------------
96 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
97 本行代付ç†æ•¸,
98 本行代付金�,
99 他行代付ç†æ•¸,
100 他行代付金�
101 FROM (SELECT MasterKey,
102 COUNT(PriKey) AS 本行代付ç†æ•¸,
103 SUM(AMT) AS 本行代付金�
104 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) = '822' GROUP BY MasterKey) a
105 FULL JOIN (SELECT MasterKey,
106 COUNT(PriKey) AS 他行代付ç†æ•¸,
107 SUM(AMT) AS 他行代付金�
108 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) <> '822' GROUP BY MasterKey) b
109 ON a.MasterKey=b.MasterKey) b
110 ON a.MasterKey=b.MasterKey) a
111 -----------------------------------------------------------------æˆ?功ç†æ•¸èˆ‡æˆ?功金é¡?---------------------------------------------------------------------------
112 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
113 代收æˆ?功ç†æ•¸,
114 代收�功金�,
115 代付æˆ?功ç†æ•¸,
116 代付�功金�
117 FROM (SELECT MasterKey,
118 COUNT(PriKey) AS 代收æˆ?功ç†æ•¸,
119 SUM(AMT) AS 代收�功金�
120 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE='00' OR RCODE='') GROUP BY MasterKey) a
121 FULL JOIN (SELECT MasterKey,
122 COUNT(PriKey) AS 代付æˆ?功ç†æ•¸,
123 SUM(AMT) AS 代付�功金�
124 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE='00' OR RCODE='') GROUP BY MasterKey) b
125 ON a.MasterKey=b.MasterKey) b
126 ON a.MasterKey=b.MasterKey) a
127 ---------------------------------------------------------------------失敗ç†æ•¸èˆ‡å¤±æ•—金é¡?---------------------------------------------------------------------------------
128 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
129 代收失敗ç†æ•¸,
130 代收失敗金�,
131 代付失敗ç†æ•¸,
132 代付失敗金�
133 FROM (SELECT MasterKey,
134 COUNT(PriKey) AS 代收失敗ç†æ•¸,
135 SUM(AMT) AS 代收失敗金�
136 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE<>'00' AND RCODE<>'') GROUP BY MasterKey) a
137 FULL JOIN (SELECT MasterKey,
138 COUNT(PriKey) AS 代付失敗ç†æ•¸,
139 SUM(AMT) AS 代付失敗金�
140 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE<>'00' AND RCODE<>'') GROUP BY MasterKey) b
141 ON a.MasterKey=b.MasterKey) b
142 ON a.MasterKey=b.MasterKey) b
143
144
145
146 WHERE a.PriKey=b.MasterKey AND
147 --TDateTime BETWEEN @DTBEG AND @DTEND AND
148 TDateTime BETWEEN '2007/9/5' AND '2007/9/5' AND
149
150 (a.Remark=@Remark OR @Remark=2) AND
151 (a.BankId=@BankId OR @BankId='')
152 ORDER BY TDateTime,CustId,PNo
153
154
155
156 WHERE a.PriKey=b.MasterKey AND
157 --TDateTime BETWEEN @DTBEG AND @DTEND AND
158 TDateTime BETWEEN '2007/9/5' AND '2007/9/5' AND
159
160 (a.Remark=@Remark OR @Remark=2) AND
161 (a.BankId=@BankId OR @BankId='')
162 ORDER BY TDateTime,CustId,PNo
163
164
165 GO
166 SET QUOTED_IDENTIFIER OFF
167 GO
168 SET ANSI_NULLS ON
169 GO
170
171
alo people..
so..this is my question regarding to gridview.. is possible to put the field inside the gridview if it is not exist in the database..
example here is the database:
Name salaryADAM 30 BOY 50
so right now i want to check their yearly salary..so mysql command will look like thisSELECT NAME,SALARY,SALARY*12 AS YEARLY_SALARYFROM TABLE1;
right now the result of the query will look like thisName Salary yearly_salaryADAM 30 360BOY 50 600my question again is, is it possible to put the yearly_salary inside the gridview since it is not originally from the table 1?
ps;/ i;m using vb.net(asp.net) and mysql..and also i use the sqldatasource..
thanx everyone for the help...
Table : Department
Col :
Department ID
Name
Description
CREATE PROCEDURE GetDepatmentID AS
SELECT Department ID, Name
FROM Department
RETURN
Trying to create strored procedure and get the error that "Department" column does not exist.
Please know verily new to this so have patience
As part of a DTS job, I run an ActiveX script that loops through each file in a directory and imports the data into a single table. I'd like to display each filename as it is being imported, but I don't want to use the msgbox command for obvious reasons. Is there a status message box I can fill to display whatever text I want? If so, what command would I use?
View 2 Replies View RelatedIs there a way that I can backup the DTS Packages?
View 1 Replies View Relatedhi i am running this program
and its giving some problem while execution.
When the program tries to create the table expt1..its says
Msg 2714, Level 16, State 1
There is already an object named 'expt1' in the database.
and if i try to drop the table expt1 it says
Msg 3701, Level 11, State 1
Cannot drop the table 'expt1', because it doesn't exist in the system catalogs.
How should I resolve this problem...?
==========================
Declare @cmdstring varchar(255)
Declare @filename varchar(32)
DECLARE @v datetime
SELECT @v = GetDate()
Create table expt1(GMSKEY char,CONTRACT char,ORIGINALCONTRACT char,STATEMENTID char,
STATEMENTDATE char ,partner char ,ADDRESSSEQ char,BOOKINGAMOUNT char,
BOOKINGCURRENCY char,TAXEDIND char,TAXTYPE char,
DUEPERIOD char,ACCOUNTNUMBER char,PRODMONTH char,PRODYEAR char,BOOKINGUNIT char,
BOOKINGQUANTITY char,STATEMENTSEQ char,COSTID char,DELIVERYMETHOD char)
SELECT
DETAILTRANS.GMSKEY,
DETAILTRANS.CONTRACT,
DETAILTRANS.ORIGINALCONTRACT,
DETAILTRANS.STATEMENTID,
convert(varchar,DETAILTRANS.STATEMENTDATE,106) statementdate,
DETAILTRANS.partner,
DETAILTRANS.ADDRESSSEQ,
DETAILTRANS.BOOKINGAMOUNT,
DETAILTRANS.BOOKINGCURRENCY,
DETAILTRANS.TAXEDIND,
DETAILTRANS.TAXTYPE,
TITLECT.DUEPERIOD,
DETAILTRANS.ACCOUNTNUMBER,
DETAILTRANS.PRODMONTH,
DETAILTRANS.PRODYEAR,
DETAILTRANS.BOOKINGUNIT,
DETAILTRANS.BOOKINGQUANTITY,
DETAILTRANS.STATEMENTSEQ,
DETAILTRANS.COSTID,
TITLECT.DELIVERYMETHOD,
'' RECEND
INTO expt1
FROM DETAILTRANS,TITLECT
where DETAILTRANS.TITLEID=TITLECT.TITLEID
AND DETAILTRANS.CONTRACT IS NOT NULL
AND DETAILTRANS.CONTRACT!='N/A'
AND DETAILTRANS.EXPREV='E'
AND DETAILTRANS.PRELIMORFINAL='F'
AND DETAILTRANS.postedDATE IS NULL
ORDER BY DETAILTRANS.GMSKEy
select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
Select @cmdstring = 'bcp GM_PROD..expt1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa'
exec master..xp_cmdshell @cmdstring
drop table expt1
===========
Thanks
Harish