Jump To Report Without Running It Automatically

Hi

I need to be able to click a hyperlink in one report to open a linked report, but without automatically running the linked report.

Scenario.

I have a report showing customer details and on this page there is a link to a price enquiry report. This price enquiry report has two parameters: customer code and product code.

When I click the price enquiry hyperlink in the customer details report I can only attach a value to the customer code parameter. The product code parameter is not known at this stage and would have to be provided by the user after the price enquiry report is opened (but not run). The customer code parameter should already be filled (carried in the link).

When I click the link at the moment I get the error "The 'product' parameter is missing a value".

Any advice much appreciated.

Thanks,

SQLServant

Sometime ago, I made a post about this. It was stated that it's by design and that all parameter values must be passed to the report.

I believe using the hyperlink feature might work though you'd have to manually append all parameter names and values that you -do- want to pass (reportname&param=val&param2=val2).

Joins on uniqueidentifier columns

I have recently introduced Microsoft'suser/roles facility by running aspnet_regsql.exe against my database so that Iwould be able to link activities with staff members.

In testing I was able to

1. Join 2 tables (Reservations and aspnet_Users) with the join fields being of type uniqueidentifier, using SQL Server Manager Studio Express diagram facility;
2. Create a record in aspnet_Users using Microsoft's Website Admin tool
3. Create a record in Reservations and paste in the contents of uniqueidentifier field in aspnet_Users using SQL Server Manager Studio Express

All the basic tests had been fine so I created a web pagewith a detailsview of the Reservations table and made a templated field (in theinsertitemtemplate), replaced the text box with a dropdown and did a bind ofthat dropdown to the relevant records in the aspnet_Users table i.e. UserNameand UserId columns with UserName being displayed. The source is as follows:

<asp:TemplateFieldHeaderText="Taken By"SortExpression="RES_Taken_By_Staff_ID">

<EditItemTemplate>

<asp:TextBoxID="TextBox5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="SDSStaff"DataTextField="UserName"

DataValueField="UserId">

</asp:DropDownList><br/>

<asp:SqlDataSourceID="SDSStaff"runat="server"ConnectionString="<%$ ConnectionStrings:ReservationsDBConnectionString%>"

SelectCommand="SELECT[UserId], [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Everything looks OK, the correct data appears in the dropdownbut when I hit the INSERT button I receive the following failure message:

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

Description:An unhandled exceptionoccurred during the execution of the current web request. Please review thestack trace for more information about the error and where it originated in thecode.

Exception Details:System.Data.SqlClient.SqlException: Implicitconversion from data type sql_variant to uniqueidentifier is not allowed. Usethe CONVERT function to run this query.

I can't quite see what I should do so I would begrateful for any help.

HI

I hopeInserting with a SqlDataSource Using uniqueidentifier Parameters can help you.

|||

Hi,

Thank you for this. The references did not cover Template Fields where SqlDataSource does not have a type attribute so I'm still stymied!

|||

Me,too. There is clearly something strange about inserting a uniqueidentifier that is not your primary key column into SQL from ASP.net (like a foreign key).

I followed the same advice quoted in the article above. It worked for my select & update statements, but not for my insert statements.

I cannot insert a foreign key into a table from ASP.net at all. Please help if you found a fix or workaround for this problem.

|||

Hi,

Yes, very odd! I've reported this as a bug so maybe confirmation/correction may be available from MS

|||

My workaround for now is to convert the uniqueidentifier foreign key to an integer.

I managed to get it working, but it's not ideal

- Roger

|||

Please try delete all TemplateField filds in grid.

Bag in grid:

I haved

<asp:GridView ID="GridViewMain" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="FAQID" DataSourceID="SqlDataSourceMain"
EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="LotName" HeaderText="LotName" SortExpression="LotName" />
<asp:TemplateField HeaderText="From User" SortExpression="fromUser">
<ItemTemplate>
<asp:HyperLink ID="LinkFromUser" runat="server" Text='<%# Bind("[fromUser]") %>'
NavigateUrl='<%# "~/UserManagment/UserView.aspx?Filter=" + Eval("[From]") %>'>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
<EditItemTemplate>
</EditItemTemplate>

without EditItemTemplate i have error

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

sql

Joining Excel Database with MSDE Database Table, & Import

Looking for the basic procedure to join an Excel spreadsheet with a table fr
om a database running on MSDE, and then updating the MSDE table with select
columns of info from the Excel spreadsheet. I believe the first set of synt
ax will join the file with
MSDE table, and the second set of syntax will run the import/update. If you
can assist, please advise. Thank you in advance.Here is the code how to link Excel sheet to SQL Server:
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
-- Dejan Sarka, SQL Server MVPAssociate MentorSolid Quality LearningMore
than just Trainingwww.SolidQualityLearning.com"G. Benson"
<anonymous@.discussions.microsoft.com> wrote in message
news:E7453061-6084-4E62-9759-C06341C28CAC@.microsoft.com...
> Looking for the basic procedure to join an Excel spreadsheet with a table
from a database running on MSDE, and then updating the MSDE table with
select columns of info from the Excel spreadsheet. I believe the first set
of syntax will join the file with MSDE table, and the second set of syntax
will run the import/update. If you can assist, please advise. Thank you in
advance.|||Dejan, thank you for your expertise. So I fully understand your solution, a
few questions for you:
? am I running each section of SQL code from within Enterprise Manager
? it has been previously suggested that I should be using DTS for these ty
pes of updates -any thoughts
? if id# exists in both the Excel file and in the SQL database (and is use
d as the common relation) -my only objective is to update a blank column of
names in the SQL database with the values of names from the Excel file -are
all of these steps necessar
y for this type of simple update
I am also a novice when it comes to SQL -could you offer a brief explainatio
n of the function behind each section of SQL statements.
Thanks again.|||Dejan, thank you for sharing your expertise. I apologize, but I am a novice
in this area and would like to ask for your clarification on the following:
? my only objective is to use the id field as the link between the Excel f
ile and the SQL database, and simply update a column of blank names in the S
QL database to the column of names located in the Excel file -are all of the
se commands you provided re
quired/complete to accomplish this task
? would it be possible for you to explain what each section of your SQL co
mmands actually performs
? are each of these commands executed exclusively in the Enterprise Manage
r
? I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this link
/update procedure for novic
es (ie Adobe GoLive writes HTML code for non-programmers)
Thank you again for your time and knowledge.|||Dejan, thank you for sharing your expertise. I apologize, but I am a novice
in this area and would like to ask for your clarification on the following:
? my only objective is to use the id field as the link between the Excel f
ile and the SQL database, and simply update a column of blank names in the S
QL database to the column of names located in the Excel file -are all of the
se commands you provided re
quired/complete to accomplish this task
? would it be possible for you to explain what each section of your SQL co
mmands actually performs
? are each of these commands executed exclusively in the Enterprise Manage
r
? I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this link
/update procedure for novic
es (ie Adobe GoLive writes HTML code for non-programmers)
Thank you again for your time and knowledge.
-- G. Benson wrote: --
Looking for the basic procedure to join an Excel spreadsheet with a table fr
om a database running on MSDE, and then updating the MSDE table with select
columns of info from the Excel spreadsheet. I believe the first set of synt
ax will join the file
with MSDE table, and the second set of syntax will run the import/update. I
f you can assist, please advise. Thank you in advance.|||Dejan, thank you very much for taking the time and offering your assistance.
I apologize in advance for my lack of knowledge in this area, and would li
ke to request further clarification regarding the following:
* my primary objective is to use the ID field in the Excel file and ID field
in the SQL database as the common link between the two sources; once linked
, I simply need to update a currently blank column of names in the SQL datab
ase with the values of name
s from the Excel file -are the SQL commands you provided all that is require
d to completely link and update this column of data
* are all of these commands executed in steps (one after the other) from wit
hin Enterprise Manager
* could you briefly state what each section of your SQL commands actually pe
rforms
* it has been previously suggested that if this may occur on a regular basis
, I should use DTS - is DTS part of Enterprise Manager; is it a standalone p
roduct or does it come as part of MSDE
* does a standalone software product exist which novices can use to perform
these link/updates (ie similar to Adobe GoLive or MS FrontPage generating al
l HTML code for non-programmers)
Thank you again for sharing your advice and expertise.|||> . am I running each section of SQL code from within Enterprise Manager
This is supposed to run in Query Analyzer. You can use the same code in your
application as well.

> . it has been previously suggested that I should be using DTS for these
types of updates -any thoughts
DTS is another possibility, of course.

> . if id# exists in both the Excel file and in the SQL database (and is
used as the common relation) -my only objective is to update a blank column
of names in the SQL database with the values of names from the Excel
file -are all of these steps necessary for this type of simple update
>
For an occasional connection, you could use the Openrowset function as well,
but is is quite complicated to write the correct connection string for the
function.

> I am also a novice when it comes to SQL -could you offer a brief
explaination of the function behind each section of SQL statements.
>
1. sp_addlinkedserver system SP stores connection string as a server alias
in the master.dbo.sysservers table. So you don't have to type the connection
string every time you need the data from Excel.
2. sp_addlinkedsrvlogin szstem SP establishes securitz context for the
linked server. If linked server is Oracle, for example, zou have to login
there. As Excel does not have some special securitz, we use 'false' as the
2nd parameter.
3. sp_tables_ex and sp_columns_ex szstem SP give you info about tables and
columns on the remote source.
4. Select, Create, Insert are standard T-SQL commands.
5. In Excel, you can name a range of cells with a custom name, so you don't
have to refer to them a Sheet1$.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||> . I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this
link/update procedure for novices (ie Adobe GoLive writes HTML code for
non-programmers)
>
In addition:
DTS is a general tool for transferring the data between OLE DB sourcers. You
can read basic of DTS at
http://msdn.microsoft.com/library/d...r />
71v7.asp.
You can check an advance usage of DTS for transferring the data from Excel
at http://www.sqldts.com/default.aspx?243.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Here is a brief description of my actual data:
Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03
SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114
The primary objective is to update the LOG_SOURCE column of the SQL database
with the values from the Excel worksheet (using ITEMNO as the link). Pleas
e advise as to the following:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'
* What should I change Excel version name to (using Microsoft Office Excel 2
003)
* could I avoid defining table ranges to save a step or two and simply use c
olumn ranges -in other words, what commands could we eliminate by using Shee
t1 A1:A5 & Sheet1 B1:B5
* Does DTS come as part of MSDE & if so, how do I access it? Would I save a
n time by using DTS rather than your suggested procedure?
* What part of your code triggers the actual update of the SQL server's LOG_
SOURCE column with the Excel values?
Thank you again for your time and expertise.|||Dejan,
Thank you for your reply. Here is a brief description of my actual data:
Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03
SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114
My primary objective is to update the LOG_SOURCE column of the SQL table wit
h the values from the Excel worksheet (using ITEMNO as the link).
I have to apologize in advance for my limited SQL experience, but here is co
de that I've collected thus far to perform the link:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'
(not sure what I should change Excel version name to if I'm using Microsoft
Office Excel 2003)
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
(I wonder if I could avoid defining table ranges to save a step or two and s
imply use column ranges -in other words, what commands could we eliminate by
using Sheet1 A1:A5 & Sheet1 B1:B5 ?)
(what part of your code triggers the actual update of the SQL server's LOG_S
OURCE column with the Excel values?)
Here is the code I've collected thus far to perform the update:
update Items set log_source = t1.log_source
update items
set
items.LOG_SOURCE = t1.LOG_SOURCE
from t1
inner join Items
on items.ItemNo = t1.ItemNo
I'd like to consolidate this code or simplify this process, if possible. Al
so, does DTS come as part of MSDE & if so, how do I access it? Would I save
any time by using DTS rather than your suggested procedure?
Thank you again for your time and expertise.

Joining Excel Database with MSDE Database Table, & Import

Looking for the basic procedure to join an Excel spreadsheet with a table from a database running on MSDE, and then updating the MSDE table with select columns of info from the Excel spreadsheet. I believe the first set of syntax will join the file with MSDE table, and the second set of syntax will run the import/update. If you can assist, please advise. Thank you in advance.Here is the code how to link Excel sheet to SQL Server:
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
-- Dejan Sarka, SQL Server MVPAssociate MentorSolid Quality LearningMore
than just Trainingwww.SolidQualityLearning.com"G. Benson"
<anonymous@.discussions.microsoft.com> wrote in message
news:E7453061-6084-4E62-9759-C06341C28CAC@.microsoft.com...
> Looking for the basic procedure to join an Excel spreadsheet with a table
from a database running on MSDE, and then updating the MSDE table with
select columns of info from the Excel spreadsheet. I believe the first set
of syntax will join the file with MSDE table, and the second set of syntax
will run the import/update. If you can assist, please advise. Thank you in
advance.|||Dejan, thank you for your expertise. So I fully understand your solution, a few questions for you
â?¢ am I running each section of SQL code from within Enterprise Manage
â?¢ it has been previously suggested that I should be using DTS for these types of updates -any thought
â?¢ if id# exists in both the Excel file and in the SQL database (and is used as the common relation) -my only objective is to update a blank column of names in the SQL database with the values of names from the Excel file -are all of these steps necessary for this type of simple updat
I am also a novice when it comes to SQL -could you offer a brief explaination of the function behind each section of SQL statements
Thanks again.|||Dejan, thank you for sharing your expertise. I apologize, but I am a novice in this area and would like to ask for your clarification on the following:
â?¢ my only objective is to use the id field as the link between the Excel file and the SQL database, and simply update a column of blank names in the SQL database to the column of names located in the Excel file -are all of these commands you provided required/complete to accomplish this task
â?¢ would it be possible for you to explain what each section of your SQL commands actually performs
â?¢ are each of these commands executed exclusively in the Enterprise Manager
â?¢ I have been told that if I were going to perform this task repeatedly, I should be using DTS -could you advise (is this part of Enterprise Manager), or maybe there is another tool you might suggest which simplifies this link/update procedure for novices (ie Adobe GoLive writes HTML code for non-programmers)
Thank you again for your time and knowledge.|||Dejan, thank you for sharing your expertise. I apologize, but I am a novice in this area and would like to ask for your clarification on the following
â?¢ my only objective is to use the id field as the link between the Excel file and the SQL database, and simply update a column of blank names in the SQL database to the column of names located in the Excel file -are all of these commands you provided required/complete to accomplish this tas
â?¢ would it be possible for you to explain what each section of your SQL commands actually perform
â?¢ are each of these commands executed exclusively in the Enterprise Manage
â?¢ I have been told that if I were going to perform this task repeatedly, I should be using DTS -could you advise (is this part of Enterprise Manager), or maybe there is another tool you might suggest which simplifies this link/update procedure for novices (ie Adobe GoLive writes HTML code for non-programmers
Thank you again for your time and knowledge
-- G. Benson wrote: --
Looking for the basic procedure to join an Excel spreadsheet with a table from a database running on MSDE, and then updating the MSDE table with select columns of info from the Excel spreadsheet. I believe the first set of syntax will join the file with MSDE table, and the second set of syntax will run the import/update. If you can assist, please advise. Thank you in advance.|||Dejan, thank you very much for taking the time and offering your assistance. I apologize in advance for my lack of knowledge in this area, and would like to request further clarification regarding the following
* my primary objective is to use the ID field in the Excel file and ID field in the SQL database as the common link between the two sources; once linked, I simply need to update a currently blank column of names in the SQL database with the values of names from the Excel file -are the SQL commands you provided all that is required to completely link and update this column of dat
* are all of these commands executed in steps (one after the other) from within Enterprise Manage
* could you briefly state what each section of your SQL commands actually perform
* it has been previously suggested that if this may occur on a regular basis, I should use DTS - is DTS part of Enterprise Manager; is it a standalone product or does it come as part of MSD
* does a standalone software product exist which novices can use to perform these link/updates (ie similar to Adobe GoLive or MS FrontPage generating all HTML code for non-programmers
Thank you again for sharing your advice and expertise.|||> . am I running each section of SQL code from within Enterprise Manager
This is supposed to run in Query Analyzer. You can use the same code in your
application as well.
> . it has been previously suggested that I should be using DTS for these
types of updates -any thoughts
DTS is another possibility, of course.
> . if id# exists in both the Excel file and in the SQL database (and is
used as the common relation) -my only objective is to update a blank column
of names in the SQL database with the values of names from the Excel
file -are all of these steps necessary for this type of simple update
>
For an occasional connection, you could use the Openrowset function as well,
but is is quite complicated to write the correct connection string for the
function.
> I am also a novice when it comes to SQL -could you offer a brief
explaination of the function behind each section of SQL statements.
>
1. sp_addlinkedserver system SP stores connection string as a server alias
in the master.dbo.sysservers table. So you don't have to type the connection
string every time you need the data from Excel.
2. sp_addlinkedsrvlogin szstem SP establishes securitz context for the
linked server. If linked server is Oracle, for example, zou have to login
there. As Excel does not have some special securitz, we use 'false' as the
2nd parameter.
3. sp_tables_ex and sp_columns_ex szstem SP give you info about tables and
columns on the remote source.
4. Select, Create, Insert are standard T-SQL commands.
5. In Excel, you can name a range of cells with a custom name, so you don't
have to refer to them a Sheet1$.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||> . I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this
link/update procedure for novices (ie Adobe GoLive writes HTML code for
non-programmers)
>
In addition:
DTS is a general tool for transferring the data between OLE DB sourcers. You
can read basic of DTS at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_71v7.asp.
You can check an advance usage of DTS for transferring the data from Excel
at http://www.sqldts.com/default.aspx?243.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Here is a brief description of my actual data:
Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03
SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114
The primary objective is to update the LOG_SOURCE column of the SQL database with the values from the Excel worksheet (using ITEMNO as the link). Please advise as to the following:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'
* What should I change Excel version name to (using Microsoft Office Excel 2003)
* could I avoid defining table ranges to save a step or two and simply use column ranges -in other words, what commands could we eliminate by using Sheet1 A1:A5 & Sheet1 B1:B5
* Does DTS come as part of MSDE & if so, how do I access it? Would I save an time by using DTS rather than your suggested procedure?
* What part of your code triggers the actual update of the SQL server's LOG_SOURCE column with the Excel values?
Thank you again for your time and expertise.|||Dejan
Thank you for your reply. Here is a brief description of my actual data
Excel File name = inventory.xl
File location = C:\Documents and Settings\POS\Desktop\inventory.xl
Excel current worksheet sample (both columns are 16 character strings)
ITEMNO LOG_SOURC
A1 13110 B1 White 3 Rin
A2 13111 B2 White 3 Rin
A3 13112 B3 09/09/0
A4 13113 B4 09/09/0
A5 13114 B5 09/09/0
SQL Table Name = ITEM
SQL current table sample (both columns are 16 character strings)
ITEMNO LOG_SOURC
1311
1311
1311
1311
1311
My primary objective is to update the LOG_SOURCE column of the SQL table with the values from the Excel worksheet (using ITEMNO as the link)
I have to apologize in advance for my limited SQL experience, but here is code that I've collected thus far to perform the link
EXEC sp_addlinkedserver 'ExcelSource'
'Jet 4.0'
'Microsoft.Jet.OLEDB.4.0'
'C:\Documents and Settings\POS\Desktop\inventory.xls'
NULL
'Excel 5.0
(not sure what I should change Excel version name to if I'm using Microsoft Office Excel 2003
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false
EXEC sp_tables_ex ExcelSourc
EXEC sp_columns_ex ExcelSourc
SELECT
FROM ExcelSource...Sheet1
CREATE TABLE test_exce
(id int
name varchar(255)
G
INSERT INTO test_exce
SELECT
FROM ExcelSource...Sheet1
SELECT
FROM test_exce
/* Now define two ranges in Excel on the 2nd sheet as tables *
/* Select the range, Insert->Name->Define *
/* Note: sp_tables_ex does not recognize the defined tables *
/* We can still refer to the tables explicitly *
EXEC sp_tables_ex ExcelSourc
EXEC sp_columns_ex ExcelSourc
SELECT
FROM ExcelSource...Table
SELECT
FROM ExcelSource...Table
(I wonder if I could avoid defining table ranges to save a step or two and simply use column ranges -in other words, what commands could we eliminate by using Sheet1 A1:A5 & Sheet1 B1:B5 ?
(what part of your code triggers the actual update of the SQL server's LOG_SOURCE column with the Excel values?
Here is the code I've collected thus far to perform the update
update Items set log_source = t1.log_sourc
update item
se
items.LOG_SOURCE = t1.LOG_SOURC
from t
inner join Item
on items.ItemNo = t1.ItemN
I'd like to consolidate this code or simplify this process, if possible. Also, does DTS come as part of MSDE & if so, how do I access it? Would I save any time by using DTS rather than your suggested procedure
Thank you again for your time and expertise.

Joining Excel Database with MSDE Database Table, & Import

Looking for the basic procedure to join an Excel spreadsheet with a table from a database running on MSDE, and then updating the MSDE table with select columns of info from the Excel spreadsheet._ I believe the first set of syntax will join the file with MSDE table, and the second set of syntax will run the import/update._ If you can assist, please advise._ Thank you in advance.You can connect to a csv file (which can be opened in Excel and which an Excel file can be converted into) in good form just as you can to a regular database.

The .csv file needs to be uploaded to the server.

And you can have two recordsets open at the same time.

So I'd suggest going through this recordset one row at a time and within this loop add a new record to the "real" database's recordset.

And for help connecting to a text file using the Jet OLE DB provider:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJetTe xt

And based on the above link realize that the actual filename does NOT go in the connection string - rather it goes in the SQL statement (definitely a little tricky).|||Paul,

Thank you for your reply. Here is a brief description of my actual data:

Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):

ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03

SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):

ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114

My primary objective is to update the LOG_SOURCE column of the SQL table with the values from the Excel worksheet (using ITEMNO as the link).

I have to apologize in advance for my limited SQL experience, but here is code that I've collected thus far to perform the link:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'

(not sure what I should change Excel version name to if I'm using Microsoft Office Excel 2003)

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

(I wonder if I could avoid defining table ranges to save a step or two and simply use column ranges -in other words, what commands could we eliminate by using Sheet1 A1:A5 & Sheet1 B1:B5)

Here is the code I've collected thus far to perform the update:

update Items set log_source = t1.log_source

update items
set
items.LOG_SOURCE = t1.LOG_SOURCE
from t1
inner join Items
on items.ItemNo = t1.ItemNo

I'd like to consolidate this code or simplify this process, if possible. It has also been suggested that I might use DTS for this type of procedure - does DTS come as part of MSDE & if so, how do I access it?

Thank you again for your time and expertise.

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

join SQL server into domain

Hi all,
I have 3 W2K3 servers running in WORKGROUP: one SQL2K5 running as the
backend database server; one Sharepoint server; one SQL Report Server web
server.
I'm going to join these 3 servers into AD domain, will it cause any problem?
Will it break the connection between the SQL server and the other 2 servers?
Anyone has experience on this? Thanks!
MliHi
If you rename the server then you will need to
http://msdn2.microsoft.com/en-us/library/ms143799.aspx and also change any
connections strings to the new name. Logins from the old workgroup may need
removing and new ones added for the domain. Service accounts may require
changing and orphaned users may need to be resolved
http://support.microsoft.com/kb/314546. If you create any new users they wil
l
need to have permissions added.
John
"MLi" wrote:

> Hi all,
> I have 3 W2K3 servers running in WORKGROUP: one SQL2K5 running as the
> backend database server; one Sharepoint server; one SQL Report Server web
> server.
> I'm going to join these 3 servers into AD domain, will it cause any proble
m?
> Will it break the connection between the SQL server and the other 2 server
s?
> Anyone has experience on this? Thanks!
>
> Mli
>
>
>|||Thanks John.
I'm not gonna change the server name, however because it will be joined a
domain, the DNS name will be changed. Is there any concern?
MLi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:26F04787-0DB9-4C8C-8D60-1EA65E52F8E7@.microsoft.com...[vbcol=seagreen]
> Hi
> If you rename the server then you will need to
> http://msdn2.microsoft.com/en-us/library/ms143799.aspx and also change any
> connections strings to the new name. Logins from the old workgroup may
> need
> removing and new ones added for the domain. Service accounts may require
> changing and orphaned users may need to be resolved
> http://support.microsoft.com/kb/314546. If you create any new users they
> will
> need to have permissions added.
> John
> "MLi" wrote:
>|||Hi
The name of the instance will still be the name of the old servername,
SELECT @.@.SERVERNAME will still show the old one. That is why you need to dro
p
it and create the new one.
John
"MLi" wrote:

> Thanks John.
> I'm not gonna change the server name, however because it will be joined a
> domain, the DNS name will be changed. Is there any concern?
>
> MLi
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:26F04787-0DB9-4C8C-8D60-1EA65E52F8E7@.microsoft.com...
>
>

join SQL server into domain

Hi all,
I have 3 W2K3 servers running in WORKGROUP: one SQL2K5 running as the
backend database server; one Sharepoint server; one SQL Report Server web
server.
I'm going to join these 3 servers into AD domain, will it cause any problem?
Will it break the connection between the SQL server and the other 2 servers?
Anyone has experience on this? Thanks!
MliHi
If you rename the server then you will need to
http://msdn2.microsoft.com/en-us/library/ms143799.aspx and also change any
connections strings to the new name. Logins from the old workgroup may need
removing and new ones added for the domain. Service accounts may require
changing and orphaned users may need to be resolved
http://support.microsoft.com/kb/314546. If you create any new users they will
need to have permissions added.
John
"MLi" wrote:
> Hi all,
> I have 3 W2K3 servers running in WORKGROUP: one SQL2K5 running as the
> backend database server; one Sharepoint server; one SQL Report Server web
> server.
> I'm going to join these 3 servers into AD domain, will it cause any problem?
> Will it break the connection between the SQL server and the other 2 servers?
> Anyone has experience on this? Thanks!
>
> Mli
>
>
>|||Thanks John.
I'm not gonna change the server name, however because it will be joined a
domain, the DNS name will be changed. Is there any concern?
MLi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:26F04787-0DB9-4C8C-8D60-1EA65E52F8E7@.microsoft.com...
> Hi
> If you rename the server then you will need to
> http://msdn2.microsoft.com/en-us/library/ms143799.aspx and also change any
> connections strings to the new name. Logins from the old workgroup may
> need
> removing and new ones added for the domain. Service accounts may require
> changing and orphaned users may need to be resolved
> http://support.microsoft.com/kb/314546. If you create any new users they
> will
> need to have permissions added.
> John
> "MLi" wrote:
>> Hi all,
>> I have 3 W2K3 servers running in WORKGROUP: one SQL2K5 running as the
>> backend database server; one Sharepoint server; one SQL Report Server web
>> server.
>> I'm going to join these 3 servers into AD domain, will it cause any
>> problem?
>> Will it break the connection between the SQL server and the other 2
>> servers?
>> Anyone has experience on this? Thanks!
>>
>> Mli
>>
>>|||Hi
The name of the instance will still be the name of the old servername,
SELECT @.@.SERVERNAME will still show the old one. That is why you need to drop
it and create the new one.
John
"MLi" wrote:
> Thanks John.
> I'm not gonna change the server name, however because it will be joined a
> domain, the DNS name will be changed. Is there any concern?
>
> MLi
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:26F04787-0DB9-4C8C-8D60-1EA65E52F8E7@.microsoft.com...
> > Hi
> >
> > If you rename the server then you will need to
> > http://msdn2.microsoft.com/en-us/library/ms143799.aspx and also change any
> > connections strings to the new name. Logins from the old workgroup may
> > need
> > removing and new ones added for the domain. Service accounts may require
> > changing and orphaned users may need to be resolved
> > http://support.microsoft.com/kb/314546. If you create any new users they
> > will
> > need to have permissions added.
> >
> > John
> >
> > "MLi" wrote:
> >
> >> Hi all,
> >>
> >> I have 3 W2K3 servers running in WORKGROUP: one SQL2K5 running as the
> >> backend database server; one Sharepoint server; one SQL Report Server web
> >> server.
> >>
> >> I'm going to join these 3 servers into AD domain, will it cause any
> >> problem?
> >> Will it break the connection between the SQL server and the other 2
> >> servers?
> >>
> >> Anyone has experience on this? Thanks!
> >>
> >>
> >> Mli
> >>
> >>
> >>
> >>
> >>
>
>