Answer Questions

• Dan Lingman FOR XML AUTO returns only 512 bytes when executed in vb.net

  I have a stored procedure in vb.net that executes a sql statement to retreive a record from a table as xml using "for xml auto". The code looks something like the following: sql = "select top 1 * from sometable for xml auto" cmd = new sqlcommand(sql, cn) xmlstring = cmd.executescalar.tostring The stored procedure runs fine except when it is run by SQL Agent. Then, it only returns the first 512 bytes from the database. I'm stumped. Does anyone have any ideas Thanks. Farid (In SQL 2000 Query Analyser), you can adjust the 512 setting up to 8192 characters. To do this go to : Tools_Options_"Results" tab, and your "Maxiumum characters per column" setting to 8192 (which ...Show All

• JGttttt Deploying reports on remote report server

  Hello all, I am trying to deploy my rdl's to a remote report server. I have given a valid path in the properties window before deploying...https://RemoteServerName/ReportServer. Now I get the error : Error 1 The permissions granted to user 'domain\user' are insufficient for performing this operation. what permission do I need on the remote server for deployment Thanks..... 1. Open up the Report Manager (or use SQL Server Management Studio). 2. Navigate to the folder where your reports are and go to the folder properties and then Security tab. 3. Click Edit Item Security and click OK on the dialog that follows. 4. Click New Role Assignment. 5. Type in the Windows account (domain\user) or group that you want g ...Show All

• Dany V Sharepoint Server

  I am not sure if this is the right forum but I would like to know how do I link up an external sql server db to Sharepoint such that Sharepoint server is able to extract information from it to perform some evaluations Regards Alu Furthermore it's a 'runtime' extraction of data such as this: user will submit some eforms via sharepoint and the server is able to link up all forms that have to be submitted ( from the database) with the submission...and using KPI to be able to calculate what are the forms that have not been submitted.... Regards Alu ...Show All

• Adam.Kahtava DataPump performance problem

  I'm using an SSIS under SQL 2005; i transfer data with a DataFlow Component: DataSource = OLEDB Source; Destination = OLEDB Destination The source is a View returning about 100000 records; if i execute it in query analyser it takes few seconds. But if i use it as DataPump Source it takes HOURS to execute it... I've noticed that If i modify the SELECT query inserting a "TOP 10000000000", it takes few seconds.. Why Am i missing something Netoblivion wrote: I'm using an SSIS under SQL 2005; i transfer data with a DataFlow Component: DataSource = OLEDB Source; Destination = OLEDB Destination The source is a View returning about 100000 records; if i execute it in qu ...Show All

• AndrejJ Connections to SQL Server Files (*.mdf) require SQL Server Express 2005

  When ever I click on an MDF file in VS2005 I get the following message. Required Components Missing. Connections to SQL Server Files (*.mdf) require SQL Server Express 2005 to function properly.  Please verify the installation of the component or download from the URl: http:go.microsoft.com/fwlink/ linkID=49251. I think this may have been due to the install order and options I selected. 1. VS2005 Pro, without SQL Server Express 2005 as I was planning to install SQL2005 Developer. 2. SQL2005 Developer. I then noticed the above problem and installed SQL Server Express 2005 from the VS Pro DVD.  I still have the problem.  Any ideas how I can fix this I have similar problem... I changed data ...Show All

• MagedSalah Setting provider-specific property

  Hi, How can I set the provider-specific property Please let me know whether my code is correct. For example, I want set provider-specific property set DBPROPSET_SQLSERVERDATASOURCE for memory based bulk copy operation. //Initialize the COM library. ... //Obtain access to the SQL Native Client OLE DB provider. hr = CoCreateInstance(...); InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE; ... InitProperties[1].dwPropertyID = DBPROP_INIT_CATALOG; ... InitProperties[2].dwPropertyID = DBPROP_AUTH_INTEGRATED; ... InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD; ... rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT; rgInitPropSet[0].cProperties = 4; rgInitPropSet[0].rgProperties = InitProperties; ...Show All

• Shahab03 SQL Server dumping continuously after running SSIS package

  My problem is the following: I've created a SSIS package that will run without any problems in my development environment, but when I move the databases and SSIS packages to another testenvironment it will trigger the SQL Server to dump on every sub-sequent command. The chain of events: Create the SSIS package and databases in my development environment. Run tests wich has no problems. Move the SSIS package and create backups of the database from my development environment and restore the database in the testenvironment which is in an different AD using an AD-account from that AD. I run the SSIS package without any problems. But when I look in the SQL Server logs I can see that the SQL Server is dumping on what I pr ...Show All

• jk67 Relationships and constraints across databases

  (I wasn't sure which forum to pos this in. Let me know if you think there is a better one.) I have an application that breaks down into two sets of data, Set A and Set B, for our purposes. Both Sets currently reside in the same database. Set A is a fairly static set of data, changing maybe once a week, if not less often. Set B is empty at first but it has a lot of dependencies to Set A. Items are chosen from Set A to fill Set B but never the otherway around. Here is the problem. We continue to copy the "master" database whenever a new customer signs up. The problem is, anytime we have to update data on Set A we have to do it across multiple databases. Set B is never copied as its customer owned. What I would like to do is c ...Show All

• Tom Avogardo ALTER DATABASE SET PARTNER OFF

  I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove Here's my senario: We are using SQL 2005’s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db’s. My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates Do db mirroring certificates expire Yes they do expire. By default, SQL will set the certificat ...Show All

• Blufire48 Addicted to Stored Procedures: A time series problem. Is it possible?

  It's been about two weeks since I first began using SQL Stored procedures and now I am thinking already about changing many of my plans and doing those operations in them. There is a problem I want to address and see if it is possible to accomplish it in SQL Server instead of the client C# code. I have a time series. In simplified presentation it is a table of two columns. The first one is float, the second is DateTime. There might be quite a few rows in the table: A1 ... DT1 A2 ... DT2 ................ An.... DTn What I need to do is to find patters among A1,A2,....An elements. They vary in magnitude in a rather random order. They constitute what some would call a Markov chain. I demonstrate what I want in a couple of real life example ...Show All

• Christian Sparre SQL Server 2005 Hotfix 2185 available

  The hotfix that fixes the TOPCOUNT problem ( http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=657681&SiteID=1 ) is now available from CSS. We have already installed  the fix on a number of x64 servers. The official hotfix will only be available to customers who call CSS and ask for the QFE in KB Article 924694. Unfortunately, the KB article is still not written.  If you require this hotfix, call in and reference the KB#, and the bug number SQL Hotfix #1161.   ...Show All

• shai Connecting to SQL Server Express 2005 using VB.NET 2005 fails

  Dear Sir, We are using VB.NET 2005 (Winform) and the backend SQL Server Express 2005. We would like to connect the Express (.mdf) database from Remote Machine. What type of connection string we have to use We tried the following ConnectionString but we got the error: Provider=SQLNCLI.1;Server=.\SQLEXPRESS;AttachDbFileName=S:\MLJData.mdf;Database=MLJData;Trusted_Connection=Yes; Also suggest what are all the prerequisites to run sql server express 2005 database in client machine. We did installed the following tools in client machine: 1. .NET framework 2.0 2. sqlncli.msi (SQL Server Native Client Setup) Please provide us the solution for this. Thanks M. Gopalan Sofist India okugops@hotmail.com hi, databases ...Show All

• MyerTheFlyer Verifying MergeSynchronizationAgent no longer requires STA threading model

  I've seen a few posts in the MSDN documentation (see links below) stating that the MergeSynchronizationAgent no longer requires the STA threading model in SQL Server 2005 SP1. However, I'm still receiving the following exception message in my synchronization code (where it attempts to access the SynchronizationAgent property): The MergeSynchronizationAgent class must be instantiated on a Single-Threaded Apartment (STA) thread. I have Service Pack 1 for SQL Server 2005 installed on both my server as well as my local client (the client is running SQL Server 2005 Express). How can I verify the correct files are there (e.g. are the some specific date/time values for the RMO/COM objects Here's the list of MSDN links mentioned above ...Show All

• V.E parameters

  Hello, i am new to ssrs and am trying to generate a report with 4 parameters. 2 of which are dates. The other 2 are drop downlists. Now the report works fine when i enter all 4 parameters. But in some cases i want to leave one of the parameters unentered . It doesnt all me to do that. Gives a error saying i need to enter the parameter. How do u get aroud this issue I have seperate dataset for this parameters list and am using a where clause in my main query. Can anyone please help me out with this Thx Ashish the parameters which are not needed all times can be set as 1. under parameter properties check allow null values option 2. take default value for parameter as null hope these will solve u r p ...Show All

• Squid Dog Strange Query Behaviour

  Hi, I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query 'SELECT ID FROM Table2' returns: Invalid column name 'ID'. OK. Now when I run the query 'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)' it returns all the records from Table1. What gives Is this a bug, or am I missing something Hi, I got it thank u....! But why the result is "no rows selected" when table2 is empty. Even if it considers table1.id in select id from table2 , it has to retrieve all the ids right!! Otherwise it should rise an error saying that id not ...Show All