how to reinitialize a subscription with TSQL ??

how to reinitialize a subscription with TSQL ??Hassan,
have a look at these in BOL:
sp_reinitsubscription
sp_reinitpullsubscription
sp_reinitmergesubscription
sp_reinitmergepullsubscription
Rgds,
Paul Ibison
sql

How to reference a previous field Alias in TSQL as in Jet SQL?

My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'

SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable

The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:

A B C
1 2 3

However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?

The Jet syntax is non-standard SQL syntax so it will not work in SQL Server. You will have to use a derived table or CTE (in SQL Server 2005) or use a view or computed column in the table (if the expression involves columns from a single table).

select A, B, A+B as C

from (select 1 as A, 2 as B

from sometable

) as t

with t as (

select 1 as A, 2 as B

from sometable

)

select A, B, A+B as C

from t

How to read/select data from a text file in TLSQL

Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)Why is dts not an option ? Please provide a sample your data from the text file - is it delimited or fixed ?|||Is it possible to read a "csv" file?

It is (as I've understood) via ODBC, but I guess ODBC won't help in this particular case?|||Originally posted by thazanm
Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)

Thanks for ur reply.
I wanna transfer data offline, so i cant use DTS. I am doing this offline data transfer using bcp(getting data from source database and making files then read from files and put it into target database). This is helpfull for me when the target database is fresh, when the target databse is having data, it need some validation for duplication, parent child relationship based on identity columns.

All I want is, I am creating few file for each table using bcp with -c or
-n option. After creating this I wanna read the data from created files to do some validation in TSQL and then load into target database.

I mentioned offline mean, I am not sure what will be the target database and I am not sure about their database informations. This script has to go as a pre install script before our product get installed.

I wanna put this in a simple way. In oracle there is Pl/SQL package available to open, read and write data from files to table and vice versa like UTL_FILE.<procedures>. Is there any functions or procedures available in SQLServer TSQL.

Thanks in advance
Thazul|||You may have already mentioned why you can't do this - in situations like this I have a holding database and work with the data using tsql this way. Massage the data with a stored procedure and then output the data to the destination database/table.|||Alrigth, I am sorry for the improper information provided.
Well I am already having a TSQL script which will transfer data from one database to another database after doing all kind of validations. The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.
I this facility(reading from files having table like data) is available then I can go for complete batch file.
I really appricate ur suggestion, if anyone can provide me the syntax or comman, it will be great.

Thanks in advance,
Thazul|||Are you looking for a way to read in a file, run validations, then output the data to a file ?|||RE:The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.

In some situations one may attach an MDF file (pre-populated with the necessary data in tables), and subsequently use it as the staging area database for validation, transfer, etc. (to other DB targets). In this way the stageing area DB is effectively populated "off-line" (from a client perspective).|||Originally posted by rnealejr
Are you looking for a way to read in a file, run validations, then output the data to a file ?

Hi,
I am looking for reading the data from file and run validation and then store it into database tables.

Thanks in advance|||I am confused as to why you can't use dts within your tsql - but you can accomplish this task by using bcp within your tsql, have your tsql validate and spit it out to a table(s).
If this does not satisify your requirements, please let me know - a step by step detail of the entire process would be helpful- offline is a little vague at this point, since tsql implies online.

How to Query a nested Hierarchy in TSQL

All:
I have a table called Users and there is a column called ParentID that
either contains 0 or another valid userID. This is what I want to do: When I
have a userID, I want to retrieve all UserIDs until ParentID for a searched
user is 0.
Script to simulate the environment:
create table users
(userID Int,
parentID Int)
insert into users
select 1, 0
union all
select 2, 1
union all
select 3, 2
union all
select 4, 3
union all
select 5, 4
union all
select 6, 4
union all
select 7, 5
union all
select 8, 6
union all
select 9, 7
union all
select 10, 8
Situation: I will be supplied with a specific user ID and I need to retun
all userIDs in comma delimited format until ParentID becomes 0 in the
hierarchy.
Example:
UserID 1 should return only one row as the ParentID is 0 for userID 1
UserID 8 should return following userIDs
6,4, 3, 2, 1
I hope everything is clear.This article may give you some idea.
http://www.windowsitpro.com/Article...5715/15715.html
Regards
Mark wrote:
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
I
> have a userID, I want to retrieve all UserIDs until ParentID for a searche
d
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.|||This article may give you some idea.
http://www.windowsitpro.com/Article...5715/15715.html
Regards
Mark wrote:
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
I
> have a userID, I want to retrieve all UserIDs until ParentID for a searche
d
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.|||Mark,
Try this function:
create function dbo.fn_getpath(@.uid as int) returns varchar(8000)
as
begin
declare @.path as varchar(8000);
set @.path = cast(@.uid as varchar(10));
while @.uid <> 0
begin
set @.uid = (select parentid from users where userid = @.uid);
set @.path = @.path + ',' + cast(@.uid as varchar(10));
end
return @.path;
end
go
select dbo.fn_getpath(5)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:1C6A7C66-191E-41A7-AD3C-33E84232CCF6@.microsoft.com...
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
> I
> have a userID, I want to retrieve all UserIDs until ParentID for a
> searched
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||"Muhammad Akhter" <mnadeemakhter@.gmail.com> wrote in message
news:1134511951.052763.116790@.g44g2000cwa.googlegroups.com...
> This article may give you some idea.
> http://www.windowsitpro.com/Article...5715/15715.html
Lets just count the *years* that this kind of approach is the only one
available:(:)|||Get a copy of TREES & HIERARCHIES IN SQL for several approaches to this
problem.
Why do you wish to destroy First Normal Form (1NF)? It is the
foundation of RDBMS, after all.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134875755.271066.18350@.g44g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for several approaches to this
> problem.
Good luck with the book...although some may wonder why
it still is appropriate in the 21st century:)