|
Thank you for your kindly help!
|
|
|
|
|
I would keep the index on Name and create another index on Name,Description,Vendor,Instock,Price
* This should solve any Bookmark problem - have you ran execution plan on your query? if this is a heavily 'write' table, you will pay the price when rebuilding indexes
|
|
|
|
|
Hi All,
I'm having some troubles working on my first stored proc using dynamic SQL and I've hit a brick wall and can't work out what I'm doing wrong. I have two tables that are always the same structure, however I wish to write a re-useable stored proc to copy records to a new table based on some value comparison. I have this in my stored proc at the moment:
ALTER PROCEDURE [dbo].[sp_CopyFileScorerChanges]
-- Add the parameters for the stored procedure here
@SourceTable NVARCHAR(100),
@TargetTable NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @zSQL NVARCHAR(4000)
SET @zSQL = N'INSERT INTO ' + @SourceTable + '(
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
)
SELECT
@FilePath,
@CurrentCategory,
@CurrentSubCategory,
@CurrentBpm,
@OldCategory,
@OldSubCategory,
@OldBpm
FROM ' + @TargetTable +
'WHERE CurrentCategory <> OldCategory'
EXEC( sp_ExecuteSQL @zSQL, N'@FilePath NVARCHAR(4000),
@CurrentCategory NVARCHAR(100),
@CurrentSubCategory NVARCHAR(100),
@CurrentBpm INT,
@OldCategory NVARCHAR(100),
@OldSubCategory NVARCHAR(100),
@OldBpm INT',
@FilePath,
@CurrentCategory,
@CurrentSubCategory,
@CurrentBpm,
@OldCategory,
@OldSubCategory,
@OldBpm
)
END
I'm currently getting a 'Incorrect syntax near 'sp_ExecuteSQL' - Expecting STRING, TEXT_LEX, VARIABLE or GLOBAL_VAR. I just want to call the stored proc with two table names, the source table and the target table.
Any help would be really appreciated.
Thanks,
|
|
|
|
|
You are trying to use a varable in dynamic sql, you cannot. Everywhere you are using @Var needs to be turned into a literal (like you do for target table).
When building dynamic sql I do a print @SQL and try and run the results, this will then give you more detailed results. It will also show that you are trying to insert @FilePath int a varchar without surrounding singles '
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Gotcha. All working now. Thank you!
|
|
|
|
|
Mycroft Holmes wrote: You are trying to use a varable in dynamic sql, you cannot. Everywhere you are using @Var needs to be turned into a literal (like you do for target table).
Wrong! sp_executesql will allow you to pass parameters into the SQL. He's using string concatenation for the table names because this is one place in SQL you cant use a variable.
|
|
|
|
|
You're right, I never use sp_execute, just execute(sql). What was wrong with his proc then?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
I am getting following error when configuring Distributor or New Publication.I am trying to make local server as distributor and publisher.Can anyone help me to get rid of following error:
==================================
SQL Server could not configure 'WIN2K3STD' as a Distributor. (Microsoft.SqlServer.ConnectionInfo)
===================================
Property LoginSecure cannot be changed or read after a connection string has been set. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionSettings.ThrowIfConnectionStringIsSet(String propertyName)
at Microsoft.SqlServer.Management.Common.ConnectionSettings.get_LoginSecure()
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.InstallDistributor(WizardInputs inputs, Boolean bScripting)
at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.InstallDistributor(Boolean& anyExceptions, Boolean bScripting, ApplicationException& outerEx, StringBuilder command)
|
|
|
|
|
i want to give permission to a user how is not in my network to access my db , the user needs to use the windows authentication also mean i can't use sql logins, so is there any solution for this.
|
|
|
|
|
I may be wrong but can't you simply type in the domain/username when setting up a user. I know you would normally chase through the lists but I think you can also just type it in.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi Experts,
im new to sqlserver,my task is...i had a two country tables(t_country and p_country).i want to list the countries which are not in p_country.
thnks in advance
|
|
|
|
|
Select * from tCountry where country not in (Select country from pcountry)
|
|
|
|
|
i will not talk about Normalization here and will post only regarding your question only
try to use "NOT IN " in query
select ID from T_country where id not in (select ID from P_country)
if you dont have any keys then use the common fields exists in both the tables like in your case you can use Country Name but will not recommend you makes query so slow try to use any numeric fields
and you can also use not equal join
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
Hi
i've installed ssrs in my sql server database machine (with xp sp2)
but when i go to this link : http://localhost/reports i got this error :
The report server is not responding. Verify that the report server is running and can be accessed from this computer.
i tried this 2 links, but still i got the same error :
http://technotes.towardsjob.com/sql-server/troubleshooting-ssrs-error-the-report-server-is-not-responding/
http://sanjaysainitech.blogspot.com/2009/03/error-report-server-is-not-responding.html
Note : in second link i don't know how to give permisson to my account in .config files. maybe that's my problem
any help ?
|
|
|
|
|
Dear all,
I have 3 Dbf files that have to be imported to Sql Server 2000.Master table & dbf files are same fields n data type.Can anyone help me ?
|
|
|
|
|
you want to merge them or want to overwrite ?
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
thanks for reply.
I just want to merge with old datas.
|
|
|
|
|
You can use Import Data Option exists in Sql Server but it will not let you insert data if their are any constrains made on the column of the tables means if their are constrains like no duplicate etc... then it will not let you otherwise it will work
here are some softwares for merging data also
http://wareseeker.com/free-sql-merge-tables/[^]
i made one utility which exports data into an xml file then can import that data into sql server if you can make a utility then it will be another option
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
Hi All,
I have created a Insert Trigger and a Update Trigger on a table in a database.
Now an external application (developed in .NET and I have no source code access) makes an insert successfully in the table.
But the trigger doesn't fire.
When I directly execute a insert script on the table from SQL Query Analyzer, then the trigger fires but when insert is done by the external application (.NET app) the trigger doesn't fire.
Can somebody help me out with this...I'm completely clueless as to why it is behaving this way.
Thanks
Debsoft
|
|
|
|
|
This is weird.. If any insert operation is made trigger gets fired automatically.
Open SQL Server profiler -> and check what exactly is running from inside that application. It must be invoking some insert statement on the database.
|
|
|
|
|
MSDN has a very nice description on how to launch FDHOST. However, it does not fit my computer settings. I do as it says-
Start/All Programs/ Microsoft SQL Server 2008/Configuration Tools/SQL Server Configuration Manager
and at that point I don't have SQL Server 2008 Services. Right clicking anything does not give me MSSQLFDLauncher
I have
SQL Server Configuration Manager (local)
SQL Server Services
SQL Server Browser Stopped
SQL Server (SQLEXPRESS) Running
SQL Server Agent (SQLEXPRESS) Stopped
SQL Server Network Configuration
Protocols for SQLEXPRESS
Shared Memory Enabled
Named Pipes Disabled
TCP/IP Disabled
VIA Disabled
SQL Native Client 10.0 Configuration
Client Protocols
Name Order Enabled
Shared Memory 1 Enabled
TCP/IP 2 Enabled
Named Pipes 3 Enabled
VIA Disabled
Aliases
nothing
right clicking
produces the following alternatives:
Open
Refresh
View
Export List
Properties
New Alias
As far as I can see there is no way to start FDHOST from the SQL Server Configuration Manager.
So how do I start FDHOST? Or where do I find MSSQLFDLauncher and how do I start it? MSSQLFDLauncher is the Service Account for Full Text Search.
Mark H
|
|
|
|
|
Hello experts,
I'm trying to design a framework for generating reports by storing the meta data in an XML.
The following are the sources (in both SQL / Oracle providers).
1) Tables/Views
2) Stored Procedures
3) OLAP Cubes
I'll write a wrapper to sync changes to these Tables/Views/SPs/Cubes so that the XML is up to date.
Tables/Views
============
The user will be able to store subset of the columns and filter criteria (1 or many) and should be able to run the same report at a later stage.
Stored Procedures
=================
Whenever the user wants to run the stored procedure, he/she will supply the parameters and get the output.
OLAP Cubes
==========
Predetermined cubes and/or cubes with specific members as parameters.
I'm thinking the following way using strategy pattern for retrieving data.
1) IReportData: Gets the report data in a dataset/cellset
2) IReportParameters: Gets report parameters (For SPs?)
3) IReportFilters: Report filters / criteria /criteria set
3) IReportSQL: The final executable SQL
4) IReportFormat: Final rendering methodology
What do you guys think about this? Do you have any design pattern (s) that you would like to recommend?
Thanks,
RK.
You can never try. You either do it or you don't.
modified on Saturday, September 5, 2009 3:02 PM
|
|
|
|
|
You might want to take this to the design forum for a wider response.
Ever since I first heard of XML, way back in the 90s, I figured you should be able to use XSLT to format a report and then chuck an xml data file at it for generic type reports. Never followed up on it as I loathe XML.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you! I posted it in the design and architecture forum. I did not know it was there.
You can never try. You either do it or you don't.
|
|
|
|
|
Hi everyone.
For my current employer I've developed a few systems and deployed them. The deployment of the databases themselves has always been fairly easy. I use migratordotnet to update the schema as I continually develop these systems. That bit works great for me and I've never had any problems.
The problem I am starting to have though is that as the number of releases comes down now to maybe 3 or 4 a year. The schema structures are usually vastly different and this gives me a real headache when I need to migrate the data from a previous version to the latest schema. At the moment to do this I'm writing custom migrations for each release. However the database now has over 60 tables so these migrations are getting very long and more and more bugs are starting to appear.
So my question is how does everyone else tackle this problem? Are there industry standard approaches that I'm not aware of? Are the standard tools that I'm not using that help with this?
Any help / links to docs would be great!
Phil
|
|
|
|