OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,

Sorry but exiting problem!

The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.

Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).

I'm importing data from Excel. Before import, I want to validate it against my master list values.

[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]

In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]

I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.

I think you got what the situation is!! Woh!

Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!

- The Code -

---

create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx

create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')

-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine

---

Thanks in advance...

Hi,

normally the OLEDB provider is expecting to get something back from the query. So try to put in either a return 0 or a simple Select or string stating ('Statement executed') at the bottom of the query.

BTW, why don′t you just use the select statement rather than creating tables and so on.. select 1 union select 2 ?

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de

sql

opening up odbc data source in the query query inside of the server manager

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.

Thanks in advance

Daniel Buchanan.

If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

You can set up linked servers on one of the SQL Servers and join the data.

http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx

hth

BobP

Opening SqlConnection - VB.NET

I used to use rdo in VB6 and now I'm trying to figure out how to use the SqlDataReader class in VB.NET. I want to use an ODBC data source to specify the connection info. I used to give the rdoConnection object a connect string that looked something like this:
"DSN=[data source name];UID=[sql server user];PWD=[pwd]"
I don't understand the connect string given in all the examples I've found (nor does it seem to work on my system...)

mySqlConnection =New SqlConnection("server=(local)\VSdotNET;Trusted_Connection=yes;database=northwind")
Does anyone have any ideas? I'm open to explanations as well as solutions :)
Thanks,
jdm

Already tried to look at: http://www.connectionstrings.com/ ?|||jsmiller, are you receiving an error message?
Can you shell out to DOS (EDIT: i.e. acommand prompt, sheesh my age is showing), and run this command:
osql -L
This will tell you the name of the SQL Servers to which you haveaccess. Whatever is returned is what should go into the server=part of the connection string. If you are still having trouble,please paste your connection string and error message and we will tryto help you through it.
|||

Success! The thing is that I don't want to specify a server name but rather a System DSN... So I think based on a little more research (thanks connectionstrings.com), I don't want to use a sqlConnection but an odbcConnection. Here is my latest attempt:

Dim myOdbcConnectionAs OdbcConnection

Dim myOdbcCommandAs OdbcCommand

Dim myOdbcDataReaderAs OdbcDataReader

myOdbcConnection =New OdbcConnection("DSN=DSN;User Id=sa;Password=PWD")

myodbccommand =New OdbcCommand(SQLString, myOdbcConnection)

myOdbcConnection.Open()

myOdbcDataReader = myOdbcCommand.ExecuteReader(CommandBehavior.CloseConnection)
The program breaks on the myOdbcConnection.Open line, with an error
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
So if I add "Trusted_Connection=true" to the connect string everything seems to work out. At this point, my question is what does the trusted connection variable really mean? Am I making a security mistake?
I'm new to this so any comments welcome.
Thanks!
jdm

OpenDataSource Problem

begin
select @.datasource = 'Data Source="c:/'
+@.FOLDERNAME
+'/'
+@.FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'
select
*
into
#excel_table
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @.DATASOURCE)...sheet1$
end

need to do this ... but using variables in opendatasource not allowed.

so how do we go about it

What i need to do is allow upload of any excel file ... may contain errors and then perform some checks on the data before putting it into another table with a fixed structure.select @.query ='create procedure sp_excel_upload as select
*
into
excel_table
from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Da ta Source="'
+@.FOLDERNAME
+@.FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'+'''' + ')...sheet1$'
exec (@.query)
exec sp_excel_upload
/* put checks here */
select * into final_table from excel_table
drop procedure sp_excel_upload
drop table excel_table

How much performance overhead would this approach add on say about 40 files daily ?

Any ideas ??|||Guys .. Gurus ...

Nobody has any thoughts about this ??sql

Open source tools for performance analysis

Hi all,
Does anyone know of an open source tool capable of analyzing SQL server
performance (something comparable to Spotlight maybe or even something like
OpenSQLTrace with a good interface) ?
Thanks,
kokonut,
I don't know of any Open Source products to do this. SQL Profiler in
combination with Windows Performance monitor is pretty good for this
kind of work.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kokonut wrote:
> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL server
> performance (something comparable to Spotlight maybe or even something like
> OpenSQLTrace with a good interface) ?
> Thanks,
>
|||There is a freeware mode for Quest's Spotlight on SQL Server that's enabled without a key. In the interest of fairness, I think Idera has something similar.
There's also the SQL2H tool that does some tracking of SQL Server perfmon counters. It's one of the top downloads on http://www.microsoft.com/sql.
Hope this helps,
-kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL
> server performance (something comparable to Spotlight maybe or even
> something like OpenSQLTrace with a good interface) ?
> Thanks,
>

Open source tools for performance analysis

Hi all,
Does anyone know of an Open Source tool capable of analyzing SQL server
performance (something comparable to Spotlight maybe or even something like
OpenSQLTrace with a good interface) ?
Thanks,kokonut,
I don't know of any Open Source products to do this. SQL Profiler in
combination with Windows Performance monitor is pretty good for this
kind of work.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kokonut wrote:
> Hi all,
> Does anyone know of an Open Source tool capable of analyzing SQL server
> performance (something comparable to Spotlight maybe or even something lik
e
> OpenSQLTrace with a good interface) ?
> Thanks,
>|||There is a freeware mode for Quest's Spotlight on SQL Server that's enabled
without a key. In the interest of fairness, I think Idera has something sim
ilar.
There's also the SQL2H tool that does some tracking of SQL Server perfmon counters. It
's one of the top downloads on http://www.microsoft.com/sql.
Hope this helps,
-kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. ([url]www.sqlpass.org[/url
])

> Hi all,
> Does anyone know of an Open Source tool capable of analyzing SQL
> server performance (something comparable to Spotlight maybe or even
> something like OpenSQLTrace with a good interface) ?
> Thanks,
>sql

Open source tools for performance analysis

Hi all,
Does anyone know of an open source tool capable of analyzing SQL server
performance (something comparable to Spotlight maybe or even something like
OpenSQLTrace with a good interface) ?
Thanks,kokonut,
I don't know of any Open Source products to do this. SQL Profiler in
combination with Windows Performance monitor is pretty good for this
kind of work.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kokonut wrote:
> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL server
> performance (something comparable to Spotlight maybe or even something like
> OpenSQLTrace with a good interface) ?
> Thanks,
>|||There is a freeware mode for Quest's Spotlight on SQL Server that's enabled without a key. In the interest of fairness, I think Idera has something similar.
There's also the SQL2H tool that does some tracking of SQL Server perfmon counters. It's one of the top downloads on http://www.microsoft.com/sql.
Hope this helps,
-kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL
> server performance (something comparable to Spotlight maybe or even
> something like OpenSQLTrace with a good interface) ?
> Thanks,
>

Open Source Tool: Data Compare

Hi all,

Does anyone know any open source tool out there for these purposes?

1. Compare data rowcounts from two databases of the same structure.(On different servers).

2. Compare the structures of two databases.(On different servers).

3. Tool a developer can run on his/her desktop to capture a log of the database that his application is hitting.

Again all, I am new to the database world. Thank you for all assistance.For table structure compare tool there is http://sourceforge.net/projects/dabcos/

It was not finished the last time I used it.

Tim S|||We developed Database Comparing tool for our internal needs. This tool is mostly for programmers to support development/testing/production databases in different environments. Please take a look at http://www.dataassistance.com/Product_data_analysis.html
any feedback will be very welcome.

www.dataassistance.com|||For table structure compare tool there is http://sourceforge.net/projects/dabcos/

It was not finished the last time I used it.

Tim S

I look for this tools for a long time! Very useful tools for me!
Thanks!|||Indeed and I've used the reference from this site many times... a must for a Developer.

open source sql server project(s)

Where could some one find sql server open source project? Source Forge
projects for sql servers are very front end intensive. Are there any open
source sql server projects that would involve lots of database/server side
work that some one could participate in?
TIA..Hi
You should ask people from Microsoft.
BTW , did you mean open source like MySQL provided?
"sqlster" <trisha@.nospam.nospam> wrote in message
news:0AF98BC0-786A-46E4-B676-86A8B04B8ACA@.microsoft.com...
> Where could some one find sql server open source project? Source Forge
> projects for sql servers are very front end intensive. Are there any open
> source sql server projects that would involve lots of database/server side
> work that some one could participate in?
> TIA..|||By open source, I mean sourcforge.net type of projects. Some one comes up
with an idea and a project, posts the requirements, and volunteers around th
e
world work on the modules independently. This helps in "hands on" experience
.
"Uri Dimant" wrote:

> Hi
> You should ask people from Microsoft.
> BTW , did you mean open source like MySQL provided?
> "sqlster" <trisha@.nospam.nospam> wrote in message
> news:0AF98BC0-786A-46E4-B676-86A8B04B8ACA@.microsoft.com...
>
>|||Hi
> By open source, I mean sourcforge.net type of projects.
Does it relate somehow to SQL Server ?
"sqlster" <trisha@.nospam.nospam> wrote in message
news:46A40D79-2B1E-41B1-A994-BE3BCE496790@.microsoft.com...
> By open source, I mean sourcforge.net type of projects. Some one comes up
> with an idea and a project, posts the requirements, and volunteers around
> the
> world work on the modules independently. This helps in "hands on"
> experience.
> "Uri Dimant" wrote:
>

Open Source DB Structures

I'm wondering if anyone knows of a good site to find open source database structures (such as ERD's, scripts, or something of that nature)?

I think I have a pretty good handle on database design, but it would be nice to have a resource like that so that I can compare the design of my DB to DB's that people (smarter and more experienced than me) have created for similar purposes.

What I'm thinking of is a site like Open Source Web Design (www.oswd.org) for Databases. Anyone know of anything?

You can check out http://www.databaseanswers.org/data_models/ for a bunch of sample schema. It's a place to start.

Mike

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=myDSNn

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005847&SiteID=1

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=my

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005847&SiteID=1

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=my

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005847&SiteID=1

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

Open a script file for a script

Hi,
Is it possible to open a sql file a run the content from another file?
MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
refman/5.0/en/batch-commands.html).
I saw this news
[url]http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/18aa1a599d5e4ec/7ea0f09197e729ee?lnk=gst&q=run+file+script&rnum=3& hl=en#7ea0f09197e729ee[/url]
but it is not exactly what I want to do.
Let me know if it's possible.
Thank you.
Ben
What tool are you using to run the script? Also, does the script file exist on the server machine or
the client machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179729481.030659.246610@.n15g2000prd.googlegr oups.com...
> Hi,
> Is it possible to open a sql file a run the content from another file?
> MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> refman/5.0/en/batch-commands.html).
> I saw this news
> [url]http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/18aa1a599d5e4ec/7ea0f09197e729ee?lnk=gst&q=run+file+script&rnum=3& hl=en#7ea0f09197e729ee[/url]
> but it is not exactly what I want to do.
> Let me know if it's possible.
> Thank you.
> Ben
>
|||On May 21, 4:47 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> What tool are you using to run the script? Also, does the script file exist on the server machine or
> the client machine?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179729481.030659.246610@.n15g2000prd.googlegr oups.com...
>
>
>
I'm using the SQL Server Management Studio.
The file will be run on the client machine.
The reason why I'm asking for this function is because we have 300+
SQL script files that need to be ran in a specific order. Opening them
1 by 1 and running them using the manager is *really* time consuming.
The final script that will run all the others will be used by my
client as a unique "run all" script.
Thank you for your answer.
Ben
|||You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use :r
to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
restriction last time I tested (2000).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegro ups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>
|||Since you need to run the 300+ files in a certain order, I would use a
simple vbscript and SMO to do the executes. Use the FileSystemObject to
open each file in the correct order (dos dir /b command output to file will
get you the list of files, which you can then order as appropriate), and
execute against a smo connection to the appropriate server/db.
TheSQLGuru
President
Indicium Resources, Inc.
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegro ups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>
|||On May 22, 4:30 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Since you need to run the 300+ files in a certain order, I would use a
> simple vbscript and SMO to do the executes. Use the FileSystemObject to
> open each file in the correct order (dos dir /b command output to file will
> get you the list of files, which you can then order as appropriate), and
> execute against a smo connection to the appropriate server/db.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegro ups.com...
>
>
>
>
>
>
>
>
Ok,
I'll try both method and see which one is the easiest to implement.
Thank you for your messages.
Ben
|||On May 21, 6:43 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use :r
> to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
> restriction last time I tested (2000).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegro ups.com...
>
>
>
>
>
>
>
>
Hi Tibor,
Thanks for your solution, but I won't be able to us it as most of the
files are using GO in them. I can't remove this command as the scripts
are generated by different teams in the company.
I'll try the solution of TheSQLGuru.
Ben
|||> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
This is one of the reasons to always read BOL. Reading current BOL, it seems like you *can* have
batch terminator in the inner script. Quotes from BOL:
"If the file contains Transact-SQL statements that arenot followed by GO, you must enter GO on the
line that follows :r."
"The file will be read and executed after a batch terminator is encountered. You can issue multiple
:r commands. The file may include any sqlcmd command. This includes the batch terminator GO."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179790434.126680.210820@.r3g2000prh.googlegro ups.com...
> On May 21, 6:43 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Hi Tibor,
> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
> I'll try the solution of TheSQLGuru.
> Ben
>

Open a script file for a script

Hi,
Is it possible to open a sql file a run the content from another file?
MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
refman/5.0/en/batch-commands.html).
I saw this news
http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/18aa1a599d5e4ec/7ea0f09197e729ee?lnk=gst&q=run+file+script&rnum=3&hl=en#7ea0f09197e729ee
but it is not exactly what I want to do.
Let me know if it's possible.
Thank you.
BenWhat tool are you using to run the script? Also, does the script file exist on the server machine or
the client machine?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> Hi,
> Is it possible to open a sql file a run the content from another file?
> MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> refman/5.0/en/batch-commands.html).
> I saw this news
> http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/18aa1a599d5e4ec/7ea0f09197e729ee?lnk=gst&q=run+file+script&rnum=3&hl=en#7ea0f09197e729ee
> but it is not exactly what I want to do.
> Let me know if it's possible.
> Thank you.
> Ben
>|||On May 21, 4:47 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What tool are you using to run the script? Also, does the script file exist on the server machine or
> the client machine?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> > Hi,
> > Is it possible to open a sql file a run the content from another file?
> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> > refman/5.0/en/batch-commands.html).
> > I saw this news
> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
> > but it is not exactly what I want to do.
> > Let me know if it's possible.
> > Thank you.
> > Ben
I'm using the SQL Server Management Studio.
The file will be run on the client machine.
The reason why I'm asking for this function is because we have 300+
SQL script files that need to be ran in a specific order. Opening them
1 by 1 and running them using the manager is *really* time consuming.
The final script that will run all the others will be used by my
client as a unique "run all" script.
Thank you for your answer.
Ben|||You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use :r
to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
restriction last time I tested (2000).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> What tool are you using to run the script? Also, does the script file exist on the server machine
>> or
>> the client machine?
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <benjamin.am...@.gmail.com> wrote in message
>> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>> > Hi,
>> > Is it possible to open a sql file a run the content from another file?
>> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
>> > refman/5.0/en/batch-commands.html).
>> > I saw this news
>> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
>> > but it is not exactly what I want to do.
>> > Let me know if it's possible.
>> > Thank you.
>> > Ben
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>|||Since you need to run the 300+ files in a certain order, I would use a
simple vbscript and SMO to do the executes. Use the FileSystemObject to
open each file in the correct order (dos dir /b command output to file will
get you the list of files, which you can then order as appropriate), and
execute against a smo connection to the appropriate server/db.
--
TheSQLGuru
President
Indicium Resources, Inc.
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> What tool are you using to run the script? Also, does the script file
>> exist on the server machine or
>> the client machine?
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <benjamin.am...@.gmail.com> wrote in message
>> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>> > Hi,
>> > Is it possible to open a sql file a run the content from another file?
>> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
>> > refman/5.0/en/batch-commands.html).
>> > I saw this news
>> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
>> > but it is not exactly what I want to do.
>> > Let me know if it's possible.
>> > Thank you.
>> > Ben
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>|||On May 22, 4:30 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Since you need to run the 300+ files in a certain order, I would use a
> simple vbscript and SMO to do the executes. Use the FileSystemObject to
> open each file in the correct order (dos dir /b command output to file will
> get you the list of files, which you can then order as appropriate), and
> execute against a smo connection to the appropriate server/db.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> > On May 21, 4:47 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> What tool are you using to run the script? Also, does the script file
> >> exist on the server machine or
> >> the client machine?
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <benjamin.am...@.gmail.com> wrote in message
> >>news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> >> > Hi,
> >> > Is it possible to open a sql file a run the content from another file?
> >> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> >> > refman/5.0/en/batch-commands.html).
> >> > I saw this news
> >> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
> >> > but it is not exactly what I want to do.
> >> > Let me know if it's possible.
> >> > Thank you.
> >> > Ben
> > I'm using the SQL Server Management Studio.
> > The file will be run on the client machine.
> > The reason why I'm asking for this function is because we have 300+
> > SQL script files that need to be ran in a specific order. Opening them
> > 1 by 1 and running them using the manager is *really* time consuming.
> > The final script that will run all the others will be used by my
> > client as a unique "run all" script.
> > Thank you for your answer.
> > Ben
Ok,
I'll try both method and see which one is the easiest to implement.
Thank you for your messages.
Ben|||On May 21, 6:43 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use :r
> to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
> restriction last time I tested (2000).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> > On May 21, 4:47 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> What tool are you using to run the script? Also, does the script file exist on the server machine
> >> or
> >> the client machine?
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <benjamin.am...@.gmail.com> wrote in message
> >>news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> >> > Hi,
> >> > Is it possible to open a sql file a run the content from another file?
> >> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> >> > refman/5.0/en/batch-commands.html).
> >> > I saw this news
> >> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
> >> > but it is not exactly what I want to do.
> >> > Let me know if it's possible.
> >> > Thank you.
> >> > Ben
> > I'm using the SQL Server Management Studio.
> > The file will be run on the client machine.
> > The reason why I'm asking for this function is because we have 300+
> > SQL script files that need to be ran in a specific order. Opening them
> > 1 by 1 and running them using the manager is *really* time consuming.
> > The final script that will run all the others will be used by my
> > client as a unique "run all" script.
> > Thank you for your answer.
> > Ben
Hi Tibor,
Thanks for your solution, but I won't be able to us it as most of the
files are using GO in them. I can't remove this command as the scripts
are generated by different teams in the company.
I'll try the solution of TheSQLGuru.
Ben|||> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
This is one of the reasons to always read BOL. Reading current BOL, it seems like you *can* have
batch terminator in the inner script. Quotes from BOL:
"If the file contains Transact-SQL statements that arenot followed by GO, you must enter GO on the
line that follows :r."
"The file will be read and executed after a batch terminator is encountered. You can issue multiple
:r commands. The file may include any sqlcmd command. This includes the batch terminator GO."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179790434.126680.210820@.r3g2000prh.googlegroups.com...
> On May 21, 6:43 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use
>> :r
>> to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
>> restriction last time I tested (2000).
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <benjamin.am...@.gmail.com> wrote in message
>> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
>> > On May 21, 4:47 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> What tool are you using to run the script? Also, does the script file exist on the server
>> >> machine
>> >> or
>> >> the client machine?
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> <benjamin.am...@.gmail.com> wrote in message
>> >>news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>> >> > Hi,
>> >> > Is it possible to open a sql file a run the content from another file?
>> >> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
>> >> > refman/5.0/en/batch-commands.html).
>> >> > I saw this news
>> >> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
>> >> > but it is not exactly what I want to do.
>> >> > Let me know if it's possible.
>> >> > Thank you.
>> >> > Ben
>> > I'm using the SQL Server Management Studio.
>> > The file will be run on the client machine.
>> > The reason why I'm asking for this function is because we have 300+
>> > SQL script files that need to be ran in a specific order. Opening them
>> > 1 by 1 and running them using the manager is *really* time consuming.
>> > The final script that will run all the others will be used by my
>> > client as a unique "run all" script.
>> > Thank you for your answer.
>> > Ben
> Hi Tibor,
> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
> I'll try the solution of TheSQLGuru.
> Ben
>

Open a script file for a script

Hi,
Is it possible to open a sql file a run the content from another file?
MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
refman/5.0/en/batch-commands.html).
I saw this news
0f09197e729ee" target="_blank">http://groups.google.com.au/group/m...
0f09197e729ee
but it is not exactly what I want to do.
Let me know if it's possible.
Thank you.
BenWhat tool are you using to run the script? Also, does the script file exist
on the server machine or
the client machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> Hi,
> Is it possible to open a sql file a run the content from another file?
> mysql can do it using the "source" function. (http://dev.mysql.com/doc/
> refman/5.0/en/batch-commands.html).
> I saw this news
> ea0f09197e729ee" target="_blank">http://groups.google.com.au/group/m...ea0f09197e729ee
> but it is not exactly what I want to do.
> Let me know if it's possible.
> Thank you.
> Ben
>|||On May 21, 4:47 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> What tool are you using to run the script? Also, does the script file exis
t on the server machine or
> the client machine?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>
>
>
>
>
>
>
I'm using the SQL Server Management Studio.
The file will be run on the client machine.
The reason why I'm asking for this function is because we have 300+
SQL script files that need to be ran in a specific order. Opening them
1 by 1 and running them using the manager is *really* time consuming.
The final script that will run all the others will be used by my
client as a unique "run all" script.
Thank you for your answer.
Ben|||You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you
do this, you can use :r
to have the tool to read a file. The file cannot contain GO (batch separator
), at lest this was a
restriction last time I tested (2000).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>|||Since you need to run the 300+ files in a certain order, I would use a
simple vbscript and SMO to do the executes. Use the FileSystemObject to
open each file in the correct order (dos dir /b command output to file will
get you the list of files, which you can then order as appropriate), and
execute against a smo connection to the appropriate server/db.
TheSQLGuru
President
Indicium Resources, Inc.
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>|||On May 22, 4:30 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Since you need to run the 300+ files in a certain order, I would use a
> simple vbscript and SMO to do the executes. Use the FileSystemObject to
> open each file in the correct order (dos dir /b command output to file wil
l
> get you the list of files, which you can then order as appropriate), and
> execute against a smo connection to the appropriate server/db.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Ok,
I'll try both method and see which one is the easiest to implement.
Thank you for your messages.
Ben|||On May 21, 6:43 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If y
ou do this, you can use :r
> to have the tool to read a file. The file cannot contain GO (batch separat
or), at lest this was a
> restriction last time I tested (2000).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Hi Tibor,
Thanks for your solution, but I won't be able to us it as most of the
files are using GO in them. I can't remove this command as the scripts
are generated by different teams in the company.
I'll try the solution of TheSQLGuru.
Ben|||> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
This is one of the reasons to always read BOL. Reading current BOL, it seems
like you *can* have
batch terminator in the inner script. Quotes from BOL:
"If the file contains Transact-SQL statements that arenot followed by GO, yo
u must enter GO on the
line that follows :r."
"The file will be read and executed after a batch terminator is encountered.
You can issue multiple
:r commands. The file may include any sqlcmd command. This includes the batc
h terminator GO."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179790434.126680.210820@.r3g2000prh.googlegroups.com...
> On May 21, 6:43 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Hi Tibor,
> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
> I'll try the solution of TheSQLGuru.
> Ben
>

Only transform unique rows

Hi,

I have created a SSIS package which includes a Data Flow that will transform my data. The Data Flow Source for this Data Flow is a SQL Server table with approx. 60 mill. rows. Theses rows ought to be unique but isn't quite, so I would like to ensure that only one of each row makes it throug to my Data Flow Destination, another SQL Server table.

What is the most efficient way to ensure this - is there some Data Flow Transformation that can assure this, should I write a SQL statement that deletes all duplicates before the Data Flow or should I take a third approch?

- SuneI can see two ways to do so:

1. OLE DB source -> Sort -> OLE DB dest

Configure Sort transform to "Remove rows with duplicate sort values".

2. OLE DB source -> Aggregate -> OLE DB dest

Configure Aggregate transform to group by all columns.

No.2 might be more efficient. Would you like to try and tell us the results? I'm very interested to know.|||Actually there is a third way and that is to use the T-SQL statement to do the dedupe. This can be much more efficient than either of the 2 mentioned above since the engine is optimized to do this especially if it can use an index. I think you would have to do some benchmarks to determine which is optimal for your particular scenario.

Thanks,
Matt|||I have actually been using a T-SQL statement up until now and I has been working okay. However all of a sudden performance have degraded significantly which made me wonder if there was any alternative ways.

I hope to get around to make some benchmarks during the weekend and promise to post the results.

Thanks for your help so far.

- Sune

Only copy missing rows from one database to another

Hello everyone,

I'm trying to create a performant script to copy records from a table in a source database, to an identical table in a destination database.

In SQL 2000, I used to create a little lookup which did a count using certain fields. If the record was missing, I executed an INSERT query, otherwise an UPDATE query. The result was that the table on the destination side was always up to date. Duplicate rows were out of the question.

This was, if I'm not mistaking, a Data Transformation, using a bit of custom VBA code to govern the transfer. For each source row, the custom code was executed. Depending on the result of the custom code, a different query was launched.

Now I'm trying to do the same using SSIS in SQL 2005. Is there a task which does this for me, or do I have to script again? In the latter case, which type of task would I use?

(I thought of the Script Task, but then I would need to set up quite a bit myself.)

Thank you,

Bram

Look at the "checking to see if a record exists if so update else insert" post on the main page.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1