Copy file on network

I have a job which call a SP and that SP generates a excel file dump using
bcp. Now form my website i want to access that excel file. Both my webserver
and db server is on different meachine.
How can i achive this?
can BCP generate output on network. Or can i copy generated file to
webserver using some command through SP ?Vikram
If I understood properly , you can create a linked server to EXCEL file.
HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"Vikram" <aa@.aa> wrote in message
news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
>I have a job which call a SP and that SP generates a excel file dump using
> bcp. Now form my website i want to access that excel file. Both my
> webserver
> and db server is on different meachine.
> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
>
>|||But in linked server i have to have excel file. But i do not want this, i
want to have excel file generated by bcp...
any other way ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> Vikram
> If I understood properly , you can create a linked server to EXCEL file.
> HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
>
> "Vikram" <aa@.aa> wrote in message
> news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
using
>|||DTS?
"Vikram" <aa@.aa> wrote in message
news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> But in linked server i have to have excel file. But i do not want this, i
> want to have excel file generated by bcp...
> any other way ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> using
>|||NO I am suing BCP , calling it from sp using xp_cmdshell
I dont want to use DTS as sp whic return data use temp table and also i have
many sp whose data i have to export to excel
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2370krlPPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> DTS?
>
> "Vikram" <aa@.aa> wrote in message
> news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
i
file.
>|||> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
UNC path depending on your permissions.
EXEC master..xp_cmdshell "bcp DB.dbo.Table out
\\SERVER1\Share\test.xls -c -T"
ftp via xp_cmdshell could work.

copy data from one table to another with addition insert value

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

If you need the ID of the first insert, for the second query then you cannot do a batch insert. There are ways to be a little creative such as doing the 2 inserts separately and doing a batch update on the second query. It depends on your data though.

|||

Thanks Dinakar,

Yes i need the id of the first insert for the second query.

As I need to alter one field value in the select data I am copying from do you think I will need to do a batch update after the second query. Is there no way of doing the insert,select and batch update all on the second query?

Many thanks

Mark :)

|||

markbpriv:

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

Do your first insert, then get the identity, then do your second insert.

Example:

declare myIdentityintINSERT INTO Table1 (col1, col2)VALUES ('test1','test2')SET @.myIdentity =@.@.IDENTITYINSERT INTO Table2SELECT myIdentity ,'test','another test'

Good luck.

|||

There is a way you can trick the SQL Server. Do you have any column in the first table that has any column which has a column like "userid" or "username" that you hardcode from your source data? or any column that has a fixed value across all rows?

|||

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

|||

markbpriv:

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

Please chekout the seconf post here:http://www.aspspider.com/rss/Rss19894.aspx

If not help:
Make sure the stored procedure is exists in your database.
Execute the SP with the owner name: MyName.MyStoredProcedureName

I guess, you are concatenating a value to a SELECT statment maybe and that value is getting you False.

I found this link which support my guess:http://p2p.wrox.com/topic.asp?TOPIC_ID=1773

Please let me know if this help you or not.

Good luck.

Converting to Hex

Hi All,

I'm needing to take a value inputted by a user via html form and convert it to a hex value upon inserting into SQL2000 db. I only need to store the 8 chars after "0x". Is there any T-SQL that can pull this off? CAST or CONVERT? Sorry if this is a silly question and hope I supplied enough info...

Thx,
Mike
Check this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1) for Umachandar's solution (which are always great!)

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.
You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
|
|||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.
|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegr oups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:
>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
--
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>