q

CREATE TABLE U(col1 INT);
INSERT INTO U VALUES(2);
INSERT INTO U VALUES(7);
INSERT INTO U VALUES(9);
CREATE TABLE V(col1 INT);
INSERT INTO V VALUES(3);
INSERT INTO V VALUES(7);
INSERT INTO V VALUES(NULL);
SELECT * FROM U WHERE
col1 NOT IN(SELECT col1 FROM V);
i expected it to return 2 and 9 but it returned nothing. can anyone explain?This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||If you are using the NOT IN Opereator with a subquery and the subquery
contains any NULL values, the subquery will return NULL!. This can be
dangerous, and this is not the case if you use IN.
http://toponewithties.blogspot.com/...es.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column
this month in SQLMag (Don't Avoid the UNKNOWN
<http://www.windowsitpro.com/Article...47010.html?Ad=1> ).
If you want a fuller explanation, you should read his article.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:

>This is because of NULL value in the V table. Comparing to NULL, we don't
>know whether the known values are equal or different, so we have to make an
>agreement how to deal in such situations. In your case, you see the result.
>If you want to get the values you are mentioning, change the query to
>SELECT * FROM U WHERE
> U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
>
>|||this is where i took the code from. i have subscribed to sqlmag but cant see
the entire article.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:OA$
QFNGrFHA.3096@.TK2MSFTNGP15.phx.gbl...
That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column this
month in SQLMag (Don't Avoid the UNKNOWN). If you want a fuller explanation
, you should read his article.
mike hodgson
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:
This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);

Putting Reports in Folders

Using Visual Studio I am trying to arrange my reports.
Here is what I have found:
1. I cannot create folders in Visual Studio.
2. I can deploy individual reports after changing the project property
(destination folder) to have reports arranged in folders.
3. I can deploy multiple projects - each landing in different folders
to have reports arranged in folders.
4. I can deploy a project the vanilla way then in Report Manager move
reports to other folders.
My disappointment is with number 1.
Number 2 is a pain in the neck.
If I do number 3, I have to have so many Data Sources defined it's a
mess.
Number 4 is so much work, I hate it.
HERE'S THE QUESTION
What's the best way to do it?
Thanks in advance.
JerryI am doing #3 & have a folder on Report Manager to share the Data sources.
Data sources still are moved along with the reports to their corresponding
folders. I moved those ones to the shared Data Sources folder, and it works
pretty good for me.
- David
"Jerry Nixon" wrote:
> Using Visual Studio I am trying to arrange my reports.
> Here is what I have found:
> 1. I cannot create folders in Visual Studio.
> 2. I can deploy individual reports after changing the project property
> (destination folder) to have reports arranged in folders.
> 3. I can deploy multiple projects - each landing in different folders
> to have reports arranged in folders.
> 4. I can deploy a project the vanilla way then in Report Manager move
> reports to other folders.
> My disappointment is with number 1.
> Number 2 is a pain in the neck.
> If I do number 3, I have to have so many Data Sources defined it's a
> mess.
> Number 4 is so much work, I hate it.
> HERE'S THE QUESTION
> What's the best way to do it?
> Thanks in advance.
> Jerry
>sql

putting names of objects to control-flow loop creating objects

please help newbie

I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?

belss you for helpfireball wrote:

Quote:

Originally Posted by

please help newbie
>
I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?
>
>
>
belss you for help

Firstly the obvious question: Why? Where are the names from these
objects coming from? If you can write a query to extract the names then
you could just use Query Analyzer or Management Studio to paste those
names into an editable script and then run the script directly. That
way there is no need for a loop.

If you must do it programmatically then you'll have to do something
with dynamic SQL. See:

http://sommarskog.se/dynamic_sql.html
Personally I'd say that if you have so many schemas that you need a
loop to create them then you definitely have too many schemas... or you
are using them in a highly unconventional manner.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal w

Quote:

Originally Posted by

Where are the names from these objects coming from

from text file I got

Quote:

Originally Posted by

if you have so many schemas that you need a loop

first idea is to build scripts in engineer maneer (reusing code blocks - not
to copy them - so managing any changes is better)

second reason - I got my analysis data model in RaRose, which doesn't really
support automatization of creating data model. I use creating
quasi-sqlserver scripts, so I have object names and so. It's not really
large amount of objects (a few schemas, about 100 tables) - but it does
change any time.
Sorry if it sounds a bit werid :-) I'm a newbie

third idea was to create that way descriptions to my obiects (tables,
attributes) in a loop (having descriptions in xls file, for example)

All hints will be appeciated.

ps:

Quote:

Originally Posted by

then you definitely have too many schemas...

you are definitely right.

Quote:

Originally Posted by

are using them in a highly unconventional manner.

like, let's say - to make some perfiormance tests/statistics? (I really
don't do so :-))|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal

Quote:

Originally Posted by

>Where are the names from these objects coming from

>
from text file I got

But text file is not SQL, but you have to transform it to SQL?

Doing this from SQL is not really fun. If you are on SQL 2005, you
could do this through the CLR, but you would still go through hoops.
Do this from a client application: Perl, VBscript or whatever your
favourite may be.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci

Quote:

Originally Posted by

But text file is not SQL

well, the point is to put it into script.sql any way which I will be able to
fetch it into my loop|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci
>

Quote:

Originally Posted by

>But text file is not SQL

>
well, the point is to put it into script.sql any way which I will be
able to fetch it into my loop

Without having seen your file, it's difficult to tell, but it does not sound
as if trying to read it from SQL is a very good idea. You probably much
better off doing this in a client language.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Putting DDL statemnets (CREATE SCHEMA), in a transaction

Are T-SQL DDL Statements allowed within a transaction in SQL Server 2005?

Hi,

I'm working with some folks on a VS add-in that maps conceptual data models to physical DBMS implementations. Currently, the only target that doesn't generate a schema creation script inside a transaction is SQL Server 2005. Being a development project, having the CREATE SCHEMA script in a transaction would be useful, to avoid the need to clean out the parts of the DDL script that did run, and just start with a new iteration. When I asked why, they thought that SQL Server didn't allow DDL statements to run inside a transaction. Searching this, I could only find references that suggested you shouldn't (for performance reasons - but these are not an issue in this case).

The rules for this are likely in Books online, etc..., but searches I tried gave gave too many references, or too few.

If you know the answer or the exact reference, please pass that along. Also, if you know of an alternative way of removing a schema from a DB, without having to delete all the individual items within that schema first, please post that as well.

Thanks BRN..

Hi,

Here is some good info concerning your issue: http://www.sybase.com/detail?id=42077.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi and thanks for the quick response - I'll check it out. BRN..

|||

Hi,

Took a look at the SYBASE ref. That talks about MS having determined table create, alter, drop, not being allowed in a transaction due to 1) not wanting the DB structure altered 2) Performance.

As Mentioned, performance not an issue in this case. Also, I was hoping to find a MS document that stated their restrictions, and if they could be over-ridden, even if not recommended.

Still, more info than I had before, so thanks again. BRN..|||

Brian,

Not sure why you are consulting sybase documentation for SQL2005 issue.

The create schema statement can be run in a user transaction. You should be able to find most of the information @. http://msdn2.microsoft.com/en-us/library/ms189462.aspx

On the second issue, I assume you are asking if there is a way to drop a schema along with all objects contained in it in one shot. Unfortunately, this is not possible right now. You need to drop or move objects out of the schema before dropping it. We are considering this feature for a future release.

|||

Sameer,

Thanks for the link and the answer - I'll check out the link, and pass that along to the development team. The SYBASE reference was from an earlier reply, and the content was generic RDBMS, pointing out some preferences by specific vendors.

The reasons requiring schema objects to be dropped before dropping the schema are pretty easy to figure out - when a DB is populated. If there was a way to allow dropping a schema in one shot during the development phase, that would be helpful.

There might be reasons to allow dropping complete schemas in a production DB too; but that would be very dependant on how schemas (in the 2005 namespace seperate from ownership form), were utilized in the DB design. I'm looking at some of this in the project I mentioned.

Thanks again for the replies. BRN..

Put .PDF file into SQL Database

Every invoice printer from the AS/400 will create a .pdf file into a PC.
I want to put all our archive Invoices into SQL Server.
Please let me know the requirment in creating the DB.
How to load the document into the SQL Table?
How to retrieve the document from the SQL Table?
and How to re-load the document into the SQL Table?
I would like to know the different in how to handle it between a thick
client use VB.Net / C#.Net and ASP.NET.Kam
Why not just storing files on filesystem ?
http://www.aspfaq.com/show.asp?id=2149
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:F365CE62-FEE2-42BA-BFF1-95997D2C6D03@.microsoft.com...
> Every invoice printer from the AS/400 will create a .pdf file into a PC.
> I want to put all our archive Invoices into SQL Server.
> Please let me know the requirment in creating the DB.
> How to load the document into the SQL Table?
> How to retrieve the document from the SQL Table?
> and How to re-load the document into the SQL Table?
> I would like to know the different in how to handle it between a thick
> client use VB.Net / C#.Net and ASP.NET.
>|||The type of application you are describing is called a document management
system, and it is a common pattern. There are scores of examples and sample
applications on the web, so just google on "document management system and
asp.net"
http://www.asptoday.com/Content.aspx?id=2236
Binary files can be stored in a SQL Server columns of type [image].
http://msdn.microsoft.com/library/d...r />
_8orl.asp
Importing the files into SQL Server would be best handled by a DTS / SSIS
package or client side application. It sounds like perhaps this be a
background process rather than an end user managed process. However, if
uploading via the web is a requirement then:
http://msdn2.microsoft.com/en-us/library/ms227669.aspx
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:F365CE62-FEE2-42BA-BFF1-95997D2C6D03@.microsoft.com...
> Every invoice printer from the AS/400 will create a .pdf file into a PC.
> I want to put all our archive Invoices into SQL Server.
> Please let me know the requirment in creating the DB.
> How to load the document into the SQL Table?
> How to retrieve the document from the SQL Table?
> and How to re-load the document into the SQL Table?
> I would like to know the different in how to handle it between a thick
> client use VB.Net / C#.Net and ASP.NET.
>

Pushing Recordset into MSRS

Hi experts,
Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to
create a separation between the report and the underlying data source? This
would help us to swap the data source without changing the reports.
Thanks,Hi,
In a short term you can create a custom dataset extension to get your ADO
recordset and "bind" it to the report. Accidentally, I have developed one
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B846
8707-56EF-4864-AC51-D83FC3273FE5 ). It allows you get an ADO.NET dataset
from somewhere (e.g. you BLL), pass it to the report as a report parameter
and expose it as a RS dataset. It also suports reporting off persisted as
XML files datasets.
In a long run, the next version of RS will include WinForm and ASP.NET
controls which will work in both connected and disconnected mode. The later
will support binding to ADO.NET datasets.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in message
news:BCDCCDFE-9AEE-4FDF-9C24-1C420F8943B4@.microsoft.com...
> Hi experts,
> Can we push an ADO recordset into MSRS (instead of MSRS pulling the data)
to
> create a separation between the report and the underlying data source?
This
> would help us to swap the data source without changing the reports.
> Thanks,
>

push subscription error Xprepl.dll

I used a working publication as a template to create a publication.
Everything was good
I then opened the propeties and added a push subscribtion and
selected execute snapshot immediately.
The merge snapshot agent errored out.
A review of the session shows the following:
connection to publisher is good
connection to subscriber is good
connection to database good
execute <article>.sc good
fail: can not find DLL Xprepl.dll
Any ideas?
Is this in your binn directory? It is called xprepl.dll or xpcobrepl.dll
there.
I don't have a sql 2000 system here, but can you try this
sp_helpextendedproc and see if any of the procs reference it?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mj" <mj@.discussions.microsoft.com> wrote in message
news:9F4BBB11-3610-4E9C-B961-554BB322D938@.microsoft.com...
> I used a working publication as a template to create a publication.
> Everything was good
> I then opened the propeties and added a push subscribtion and
> selected execute snapshot immediately.
> The merge snapshot agent errored out.
> A review of the session shows the following:
> connection to publisher is good
> connection to subscriber is good
> connection to database good
> execute <article>.sc good
> fail: can not find DLL Xprepl.dll
>
> Any ideas?
>
|||I will verify this shortly.
2nd question:
does the subscribing server have to be enterprise, standard or does it
matter?
"Hilary Cotter" wrote:

> Is this in your binn directory? It is called xprepl.dll or xpcobrepl.dll
> there.
> I don't have a sql 2000 system here, but can you try this
> sp_helpextendedproc and see if any of the procs reference it?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:9F4BBB11-3610-4E9C-B961-554BB322D938@.microsoft.com...
>
>
|||Latest update:
my subscriber is running sql server standard edition, service pack 3a
my publisher is running sql server enterprise edition, service pack 4
the subscriber has xprelp.dll in binn directory
and is going to run sp_helpextendedproc and get back.
I will update this link shortly.
Thanks
"mj" wrote:
[vbcol=seagreen]
> I will verify this shortly.
> 2nd question:
> does the subscribing server have to be enterprise, standard or does it
> matter?
>
>
> "Hilary Cotter" wrote:
|||This topology is supported. MS recommends running consistent service packs
between all servers of the same version.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mj" <mj@.discussions.microsoft.com> wrote in message
news:0917B4D9-C17B-43E6-A481-620DAAB5F1FB@.microsoft.com...[vbcol=seagreen]
> Latest update:
> my subscriber is running sql server standard edition, service pack 3a
> my publisher is running sql server enterprise edition, service pack 4
> the subscriber has xprelp.dll in binn directory
> and is going to run sp_helpextendedproc and get back.
> I will update this link shortly.
> Thanks
>
> "mj" wrote:

push publication for heterogeneous subscriber

Hi,
I have added an oracle linked server as a subscriber, when I create a push publication, I can not see the oracle linked server as an enabled subscriber under the enabled subscriber list in the 'Push Publication' wizard. Is this something I need to worry o
r this is just how linked Oracle server works?
Thanks.
Once you have created your linked server to Oracle you have to Tools | Replicaiton | Configure Distributors, Publishers, Susbcribers, select the Subscribers tab, click on new, select ole db data source, locate your Oracle Linked Server, enter the account
info, click on it.
When you create your publication make sure you enable it for hetergeneous susbcribers.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Push Binaries after installation SQL Server 2000 virtual server

I forget to set Client Network Utility to Create a Named Pipes Alias for
installation of a named instance of SQL Server 2000 virtual server on a
Windows 2003-based cluster fails to push binaries to the other node. SQL
Server 2000 virtual server installed fine on the present node but was unable
to push the binaries to the other node.
I have corrected the Named Pipes Alias problem. Is there away to push the
binaries to the other node that the cluster we recognize?
Thanks,
Treat it like a failed node. Run the installer to remove then re-add the
node. BOL topic 'Maintaining a Failover Cluster' has details.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:6F653B39-BBAA-4619-AEBA-3B8B6AD87037@.microsoft.com...
> I forget to set Client Network Utility to Create a Named Pipes Alias for
> installation of a named instance of SQL Server 2000 virtual server on a
> Windows 2003-based cluster fails to push binaries to the other node. SQL
> Server 2000 virtual server installed fine on the present node but was
unable
> to push the binaries to the other node.
> I have corrected the Named Pipes Alias problem. Is there away to push the
> binaries to the other node that the cluster we recognize?
> Thanks,
>
|||The need to create the named pipes alias has nothing to do with pushing the
binaries. Without the alias setup cannot connect to SQL Server to run the
necessary scripts. Without the alias I would be suspect that SQL Server
successfully installed on either node. I would run a complet setup again.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Purpose of "NT AUTHORITY\SYSTEM" login in SQL Server 2005

Hi All

Does anybody know what the "NT AUTHORITY\SYSTEM" login create during a SQL Server 2005 instillation is used for?

Does this login pose a security risk, and can it be removed safely? It seems to me as if it is similar to the "Bultin\Administrator" login which we remove from our production servers?

Regards

Stevo

Yes it willbe similar to Builtin\Administrator if they got access and by default you remove the access to NT AUTHORITY\SYSTEM from SQL Server.

BOL

Built-in account. You can choose from a list of the following built-in Windows service accounts:

Local System account. The name of this account is NT AUTHORITY\System. It is a powerful account that has unrestricted access to all local system resources. It is a member of the Windows Administrators group on the local computer, and is therefore a member of the SQL Server sysadmin fixed server role

Security Note:

The Local System account option is provided for backward compatibility only. The Local System account has permissions that SQL Server Agent does not require. Avoid running SQL Server Agent as the Local System account. For improved security, use a Windows domain account with the permissions listed in the following section, "Windows Domain Account Permissions."

Network Service account. The name of this account is NT AUTHORITY\NetworkService. It is available in Microsoft Windows XP and Microsoft Windows Server 2003. All services that run under the Network Service account are authenticated to network resources as the local computer.

Security Note:

Because multiple services can use the Network Service account, it is difficult to control which services have access to network resources, including SQL Server databases. We do not recommend using the Network Service account for the SQL Server Agent service.

Pulling row values from a dataset using embedded code?

I am trying to figure out a way to pull the values of one of my report
columns once the report has been rendered to create a comma separated list of
those values to pass as a paramater to another report. I figure i will have
to do this in the code part of RS. Can anyone help me with the proper
functions and syntax to walk through the rows returned by my report and pull
the values from the column I need.
I imagine it would look like this
function GetItemList()
Dim rtnstr As String
Dim x As Interger
Set rtnstr = ""
for x = 0 to (RowNumber("DatasetName")-1)
rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
next
return rtnstr
end function
What I am stuck on is how to pull the values from the column in the
particular dataset I need and also how to determine the Scope of the dataset
to know how long th loop needs to run for.
ThanksOut of the box, you can only base a report on the following data sources: SQL
Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
into one of these categories; in other words, there is no data processing
extension that allows you to read data from one report into another report.
You should re-design your solution. What about writing a stored procedure
that writes the data to a second table, appending commas as required to
create the csv format you want? You could then report off this second table?
Charles Kangai, MCDBA, MCT
"jordang" wrote:
> I am trying to figure out a way to pull the values of one of my report
> columns once the report has been rendered to create a comma separated list of
> those values to pass as a paramater to another report. I figure i will have
> to do this in the code part of RS. Can anyone help me with the proper
> functions and syntax to walk through the rows returned by my report and pull
> the values from the column I need.
> I imagine it would look like this
> function GetItemList()
> Dim rtnstr As String
> Dim x As Interger
> Set rtnstr = ""
> for x = 0 to (RowNumber("DatasetName")-1)
> rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> next
> return rtnstr
> end function
> What I am stuck on is how to pull the values from the column in the
> particular dataset I need and also how to determine the Scope of the dataset
> to know how long th loop needs to run for.
> Thanks|||Hi Charles
My Report is already driven by a stored procedure, however, there are local
filters the user can apply to the report through the report viewer as well.
So As far as the stored proc generates the dataset, I'm good to handle those
results and create the list i need. The problem is when the user applies a
filter that is specific to that report and thus not sent back to the stored
proc. I was hoping there was a way I could avoid rewriting my stored proc to
include all my report parameters, or furthermore, writing another stored proc
to generate my list.
Thanks for your help.
"Charles Kangai" wrote:
> Out of the box, you can only base a report on the following data sources: SQL
> Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
> into one of these categories; in other words, there is no data processing
> extension that allows you to read data from one report into another report.
> You should re-design your solution. What about writing a stored procedure
> that writes the data to a second table, appending commas as required to
> create the csv format you want? You could then report off this second table?
> Charles Kangai, MCDBA, MCT
>
> "jordang" wrote:
> > I am trying to figure out a way to pull the values of one of my report
> > columns once the report has been rendered to create a comma separated list of
> > those values to pass as a paramater to another report. I figure i will have
> > to do this in the code part of RS. Can anyone help me with the proper
> > functions and syntax to walk through the rows returned by my report and pull
> > the values from the column I need.
> >
> > I imagine it would look like this
> >
> > function GetItemList()
> > Dim rtnstr As String
> > Dim x As Interger
> > Set rtnstr = ""
> > for x = 0 to (RowNumber("DatasetName")-1)
> > rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> > next
> > return rtnstr
> > end function
> >
> > What I am stuck on is how to pull the values from the column in the
> > particular dataset I need and also how to determine the Scope of the dataset
> > to know how long th loop needs to run for.
> >
> > Thanks

Pulling HTML out of a Database

Good Morning,

I'm not quite sure this is the right place to post this, but i'll try anyway.

I have a website where I allow people to create their own layouts in HTML, and in the HTML I have them placeing markers (eg. |1| ) where they would like to place something from a database. All the HTML is stored in a SQL Server DB, and when I pull it out, it's fine, except it won't allow me to Replace any of the markers with values from the DB.

Here's and example line of HTML that's held in the DB:

<table width='50%' border='1'><tr><td>|0|</td><td>|1|</td></tr>

and when I do a strHtml.Replace("|0|", dReader["somefield"].ToString()) or even just a strHtml.Replace("|0|", "Test") it never changes it.

Anyone have any ideas?

Thanks,

Deepthought

String.Replace returns a new string with the values replaced. Should be doing something like this in your code

strHtml= strHtml.Replace("|0|", dReader["somefield"].ToString())

Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

PULL Subscription Problem

I am trying to create a PULL subscription from EM between two SQL 2000
servers on the same network. At the end of the wizard I get the following
error message.
ERROR 15004: Name cannot be NULL
I cannot find any detail information of what is the cuase of this error any
possible resolution. Please help.
what do you get when you issue a select @.@.servername on the publisher and
subscriber?
If you get (NULL) follow the instructions here for more information.
http://support.microsoft.com/default...b;en-us;302223
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jeremy Brown" <JeremyBrown@.discussions.microsoft.com> wrote in message
news:7DD7EE37-2311-46CD-946F-963316B4A3F9@.microsoft.com...
>I am trying to create a PULL subscription from EM between two SQL 2000
> servers on the same network. At the end of the wizard I get the following
> error message.
> ERROR 15004: Name cannot be NULL
> I cannot find any detail information of what is the cuase of this error
> any
> possible resolution. Please help.
|||Thank you Hilary. The article put me on the right track and I was able to
solve my problem. Thanks again.
"Hilary Cotter" wrote:

> what do you get when you issue a select @.@.servername on the publisher and
> subscriber?
> If you get (NULL) follow the instructions here for more information.
> http://support.microsoft.com/default...b;en-us;302223
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Jeremy Brown" <JeremyBrown@.discussions.microsoft.com> wrote in message
> news:7DD7EE37-2311-46CD-946F-963316B4A3F9@.microsoft.com...
>
>

Pull subscription creation error

Upon creating a new Pull Subscription on a Subscriber, I kept on getting
the error message "SQL Server enterprise Manager could not create a pull
subscription to publication 'XXXX' Error 14234: The specified '@.server' is
invalid(valid values are returned by sp_helpserver). Where did I go wrong
and what is the problem? Thanks.
Georgi,
you could check that the server names haven't been altered:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you. I checked but both publisher and subscriber have server names
other than NULL ...
Let me explain more my situation.
I want to make replication of database over internet, i want to use VPN. I
already setuped the VPN service so it gives IP-s that are not within the
range of the local network and are fixed for the server and for the
connections.
I was trying to make push subscription because it seemed to me that it's
easier to implement. So the subscription was successefuly added , the
snapshot agent generated snapshot but then this snapshot was not
automatically applied on Subscriber and the distributor agent cried out that
"objects are missing at subscriber". I don't know how to apply the
subscription manually, i tried to change it's path via Alternate
subscription location but it didn't helped. So i abandoned the push
subscription and now i'm trying to make pull subscription. I don't know if
i'm not missing someting of the security privilegies of the logins used, or
maybe must declare the servers in Remote Servers?
I'm quite confused with all this Replication stuff, i'm still new to it.
I've looked to the Replication section in SQL Server online but i can't find
solutions of this delicate problems (maybe it's written somewhere i'm still
reading ). Can you give me link(s) to some other comprehensive sources of
information, maybe good HOWTOS?
Thank you in advance
Georgi Peshterski
"Paul Ibison" <Paul.Ibison@.pygmalion.com> wrote in message
news:eRZf%23mUjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Georgi,
> you could check that the server names haven't been altered:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

pull subscription chaning identity seed/increment

I have an anynonomous pull merge set and working. But, I create a new
table on the client machine with increment and seed as -1000 and -20.
The table is created fine. But when I execture the first synchrnoize
data from the client, the table is changed to be 1, 2, which is what the
server has. I am using SQL-DMO on the client with the following code:
With myMergeObj
.Distributor = inxRS.Globals.gServer
.DistributorLogin = "inxsql"
.DistributorPassword = "inxsql4u"
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.DistributorNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.DistributorAddress = inxRS.Globals.gServerIP
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = in_comp
.Publication = Trim(in_comp) & "RS"
.PublisherLogin = "inxsql"
.PublisherPassword = "inxsql4u"
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.PublisherNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.PublisherAddress = inxRS.Globals.gServerIP
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = in_comp
.SubscriberSecurityMode = SECURITY_TYPE.NT_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.ANONYMOUS
.SubscriptionName = Trim(in_comp) & "RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
.ReinitializeSubscription(false)
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
I can't tell if it is a problem in the pull or in how the publication
was/is setup.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I removed the reinit statement and I still have the same problem.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||FIXED IT.
Darin
*** Sent via Developersdex http://www.codecomments.com ***

Pull subscription

Hi,
I'm trying to create a pull subscription but it fails. I
would like to synchronize data between my laptop (remote
or in network) and our SQL Server.
I managed to create a push subscription replicating every
hour but it would be better to have a pull subscription.
I always get an error saying that the schema script could
not be propagated to the subscriber. I already spent days
to find a solution.
I tried to set the snapshot folder to default or a
(shared) networkfolder. I can connect to the
publisher/distributor (same machine).
Any help?
are you pulling using ftp?
Also can you check how your configured your subscriber? On your publisher go
to Tools, Replication, Configure Publisher, distributor, and subscribers.
Then click on your subscriber, and make sure it is impersonating the SQL
Server agent on the publisher.
Then connect with your subscriber, expand the subscription database, expand
the pull subscription folder, right click on your pull subscription, and
select security and make sure you are using the impersonate options there as
well.
now make sure your sql server agent on the subscribre runs under the same
account as the sql server agent on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Bruno" <anonymous@.discussions.microsoft.com> wrote in message
news:aa3b01c4d6cb$09dfbee0$a601280a@.phx.gbl...
> Hi,
> I'm trying to create a pull subscription but it fails. I
> would like to synchronize data between my laptop (remote
> or in network) and our SQL Server.
> I managed to create a push subscription replicating every
> hour but it would be better to have a pull subscription.
> I always get an error saying that the schema script could
> not be propagated to the subscriber. I already spent days
> to find a solution.
> I tried to set the snapshot folder to default or a
> (shared) networkfolder. I can connect to the
> publisher/distributor (same machine).
> Any help?
>

Pull Subscription

I am setting up a pull subscription and I keep getting an error
message:
"SQL Server Enterprise Manger could not create a pull subscription to
publication 'XX'. Error 14053: Cannot load the DLL replincrementlsn
extended procedure, or one of the DLLs it references. ..."
Any suggestions on how to resolve this error. Thanks in advance.
Hi,
I feel that there is some installation issues. Did you install any service
packs recently whicgh didnt completed fully?Check for the procedures
sp_replincrementlsn and sp_replincrementlsn_internal (Extended proc) in
MASTER database.
If the procedures are not there then create it by scripting it out from some
other SQL servers and try
setting up the subscription again.
Thanks
Hari
MCDBA
"Scott" <scott@.jucovics.com> wrote in message
news:3fe08b2e.0406291147.18387555@.posting.google.c om...
> I am setting up a pull subscription and I keep getting an error
> message:
> "SQL Server Enterprise Manger could not create a pull subscription to
> publication 'XX'. Error 14053: Cannot load the DLL replincrementlsn
> extended procedure, or one of the DLLs it references. ..."
> Any suggestions on how to resolve this error. Thanks in advance.
|||Scott,
if you did a custom install, you need to have selected Replication Support.
If you have installed a service pack, check that it fully installed - have a
look at sqlsp.log file from the c:\windows directory this might shed some
light on any failed actions during the sp installation.
HTH,
Paul Ibison
|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<uKJ#q6mXEHA.3612@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Hi,
> I feel that there is some installation issues. Did you install any service
> packs recently whicgh didnt completed fully?Check for the procedures
> sp_replincrementlsn and sp_replincrementlsn_internal (Extended proc) in
> MASTER database.
> If the procedures are not there then create it by scripting it out from some
> other SQL servers and try
> setting up the subscription again.
> --
> Thanks
> Hari
> MCDBA
> "Scott" <scott@.jucovics.com> wrote in message
> news:3fe08b2e.0406291147.18387555@.posting.google.c om...
I tried to script out he sp_replincmentlsn and since it is an extended
stored procedure I'm not sure how? I tried various methods and when I
tried to create the extended stored proc manually, it said it is
"already an object in the database" even though I cannot see it under
the Master database. I do see "sp_replincrementlsn_internal", I'm
trying to rerun the SQL SERVER SP3 right now.

PULL huge table from SQL Server Problem

Hey Guys

I have meet the same problem, too.

I create a table with 1,380,000 rows data,

the db real size about 114 MB.

The primary key size is nchar(6).

When I use RDA pull, I found that the primary

key in the PDA disappear. So, It took a long time

to get query response.

But when I delete some rows to 680,000 rows of data.

After I pull, The primary key can pull from the SQL Server.

PS: I didn't change any code. Just delete some rows.

Is that SQL-Mobile's bug?

PS: 1.Database and Temp Database limitation both are 384MB

2.If I use query analyzer to add primary key it works! so strange!!

3.Pull process return "S_OK".

4.After Pull process finished, the db connection still alive. It seems not like

time out problem.

5.Local Connection String:"Data Source='%s\\%s';SSCEatabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"

We do have tests that do PULL of 200 MB data and almost 10 Lakh rows. Is that possible for you to give the schema and few more details of your environment so that we can try reproducing and find the root cause.

Thanks,

Laxmi

publish report

Hello,

I have create a web base project that will programmatically publish report into report server 2005.

But when i run the code, i need to create a shared data source and set the DataSourceReference.

Is there any ways else so that i no need to create a shared data source but still can publish the report in report server 2005 using custom data source?

Thanks.

You don't have to use shared datasource. You can just define a report specific datasource, in which case, all the datasource properties are embedded in the rdl. http://msdn2.microsoft.com/en-us/library/ms159165.aspx