I'm try to a multiple insert from one database to another by using this code:
insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)
(select top 30 acno,surname,name,id,type
from [PretoriaDB].[dbo].[WorkingDB])
but I keep on getting this error:
Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can any one help!!
Hello All,
I have to create dynamic insert statements for the table. For example there are DevTableA and ProdTableA tables. I worte a SQL to get the new records added in the DevTableA but are not there in ProdTableA. The result gives me a list of rows. These tables have a column 'LanguageID' and 'LText'
The compare result has records only for LanguageID = 0. One I see the compare result. I am suppose to create insert statements for LanguageID = 1,2,5 and 6 and update the Ltext for those languages. The Ltext for other languages is in spreadsheet.
Can anyone advice me how to create the insert statements from the comapre result and add 4 more insert statements for LanguageID = 1,2,5 and 6 with their respective Ltext.
So far I thought I can create #table. Looks like I need more than 1 # table.
Thanks in advance
-S
Hi, I'm trying to create a form where new names can be added to a database. The webform looks like this:<body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> Name:<asp:TextBox ID="newName" runat="server" /> <INPUT id="NewUserBtn" type="button" value="Create New User" name="NewUserBtn" runat="server" onServerClick="NewBtn_Click"> </form>And the code behind looks like this:Public Sub NewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserBtn.ServerClick Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection("server=databaseserver;database=db;uid=uid;pwd=pwd") MyCommand = New SqlDataAdapter("insert into certifications (name) values ('" & newName.Text & "'); select * from certifications", MyConnection)
DS = New DataSet MyCommand.Fill(DS, "Titles")
Response.Redirect("WebForm1.aspx", True) End SubWhen I try to insert one name it works. When I try to insert a second name, it overwrites the old one. Why is that?Thanks.James
Hi All,
Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @@Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.
Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users
INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@@Identity,'SomeOtherValue')
Thanks
Tanveer
hi I was just writing for some general advice. If I am to do many updates on a table is it okay to keep updating with many insert statements inside of a loop closing the connection each time? Here is some pseudo code to give you an idea:
cnn.open();
cmd = "insert into Cats values (red,4994,homer)";
for(int i=0; i<10000; i++)
{
cmd.executenonquery();
cnn.close();
}
I need to insert multiple rows for input rows that meet certain conditions.
My input data is as follows.
ZIPCode, Plus 4, Range, City, State
What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.
For instance say I have the following data.
54952, 1001, 10, Menasha, WI
What I need imported is :
54952, 1001, Menasha, WI
54952, 1002, Menasha, WI
54952, 1003, Menasha, WI
54952, 1004, Menasha, WI
54952, 1005, Menasha, WI
54952, 1006, Menasha, WI
54952, 1007, Menasha, WI
54952, 1008, Mensaha, WI
54952, 1009, Mensaha, WI
54952, 1010, Mensaha, WI
54952, 1011, Mensaha, WI
Any help in pointing me in the right direction would be great. Thanks for your help in advance.
I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS
[code]....
I m getting some syntactical errors.
Hello all,
I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.
Imagine a site that tracks movies. I have 3 tables:
Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)
This is an overly simple example, but it gets to the heart of my problem.
Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance
create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )
that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry
Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.
Thanks.
Hello,
I have a stored procedure that updates my table with values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case.
I will elaborate a little more on what I need. From my windows app I have a datatable with 100 new rows that need to be written to my db. This I can do. However, a problem crops up should there be an error during the transfer. Let's say I have 100 rows in my datatable in my windows app, and row 57 causes an error, what is happening is that rows 1-56 are committed and 57-100 are not. What I need is for 1-100 to NOT be committed to my DB should there be a snag along the way. I need a code sample as I'm having way too much difficulty with this as is.
The basic code that copies to my db(sans rollback):
BEGIN
SET NOCOUNT ON
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END
Thank You.
I am trying to insert a message into my database for every "league" I have in the database. I would like to do this in one sproc but am not sure how this would be done. Here is the loop if it has to be done outside of sql
Code:
message = "This is a test message."
leagues[] = getAllLeaguesInSite()
begin transaction
for each league in leagues
insertLeagueMessage(league, message)
if noErrors
commit transaction
else
rollback transaction
-----
as you can see it would be nice to be able to do this all in mssql . If it can be done please let me know.
Thanks
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
  Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
 Artikelnummer nvarchar(18),
 Artikelbezeichnung nvarchar(80),
 Artikelgruppe nvarchar(13),
Â
[code]...
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.
Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form
AFTER INSERT
AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)
--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)
Hi:
I'm trying to create an ad-hoc query on a Asp.net page for user. Besides the usual Boolean operators, Field Names, Comparison operators & Field Values, the ad-hoc query also involves multiple tables, eg [Customers] , [Members] & [Orders].
Now I have difficulties on writing a TSQL sp on how to take the dynamic query with different tables under consideration. User might simply query each individual tables (eg, Customers with age > 25) or combination of tables (eg, Membered Customers with Orders Amt > 1000 between 1/1/2005 - 1/31/2005)
I have look up a lot dynamic query on the net but all are with only 1 single table to hit. Could anyone give me a direction on how to write a dynamic query script with multiple tables under consideration? Much appreciated.
ps: The ad-hoc query only contains these defined tables, no other table will be involved.
Hello SQL Team!
I'm stuck at this problem for days and need help.
The problem is with the GO keyword. I know the GO causes the batch to get executed and all local variables are lost. But I can't seem to find a work around. I would like each stored procedure to get executed in different batches.
create table sql_cmd(cmd nvarchar(255))
insert into sql_cmd(cmd) values('exec user_sp param1,''param2'')
insert into sql_cmd(cmd) values('exec user_sp2 param1,''param2'')
declare @sql nvarchar(255)
declare c_sql cursor --small table peformance not a problem. Also open to other suggestions
for select cmd from sqlcmd
open c_sql
fetch next from c_sql
into @sql
while @@fetch_status = 0
begin
print @sql
exec sp_executesql @sql
GO
fetch next from c_sql
into @sql
end
I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)
That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.
how to check multiple parameter instead of dynamic query.
View 1 Replies View Related
Hi Evry one,
I Have Multiple Flat Files in Source Folder(They have Naming Conventions With Todays Date ex: Flatfile_20082204_1,Flatfile_20082204_2,Flatfile_20082204_3 ),
I need to Extract Each and Evry file by Dynamically, and Transform the Flat File then load that Flat file into the Destination Folder with Standard Prefix and Todays Date with a Sequence No ex:Flatfile_20082304_A,Flatfile_20082304_B, Flatfile_20082304_C
Please HELP Me
Thanks In Advance.
How to pass dynamic values in xml path query?
WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI Â
INNER JOIN TERMINALGROUP TGÂ ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TLÂ ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.
I have a problem while creating a dynamic source connection.
i have four files while which comes with different extension , apparently the name of the files are same for example.
9500.txt, the other one is 9500. rtf, 9500.dat, 9500.map, They are all text file but with different formats. I have problem selecting a dynamic source connection for each of this files.
when i create variable for input file name the file connection gets confused and throws me error with input file.
please help
Thank you
The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.
Table A
Col1Â
Col2
A
Alpha
For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.
Result:Â
1 PROC_A_ALPHA
2Â PROC_B_BETA
3Â PROC_C_charlie
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
View 4 Replies View RelatedI am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you
View 7 Replies View RelatedI am facing a reporting service issue.
System information:
1. Our Reporting Server installed on a DB server.
2. We have one windows application which executes on the same server, which generates the reports snapshots.
3. One set of reports with single Shared data source
Scenario:
We have many countries for which report history snapshots needs to be generated. The report information is stored in different databases named as database_CountryCode on the same DB server.
Questions:
1. We need to dynamically change the shared data source connection string to point to the respective country database, when generating the report for that particular country. --> We found out this can be accomplised by using parameterized connection string in report specific data sources in SQL 2005. Can we achive something similar in SQL 2003 Reports as well?
2. We also need to instantiate the reporting service web service in multiple threads, one for each country, where in each thread picks up the corresponding country code, changes the connection string and generates reports snapshots. Can this be accomplished? I know this goes against the entire idea of licensing, but my question is just about feasibility of this idea.
Addnl Info
The CreateReportHistorySnapshot method of reportingservice.asmx returns a snapshotID, which is the timestamp of report snapshot creation. We tried creating the same report snapshot for the same country in 2 threads. Whenever the timestamp for both snapshots was same, only one report snapshot actually got created. Can this be overcome?
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...
my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...
if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...
this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...
but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??
Cheers,
Justin
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
Just curios,
I have four 72 GB Drive on a RAID 5
Disk Specs
IO/Second = 130 per disk
Speed RPM = 15 K
When I did a load test of inserting data into a table
with four Columns
Col1 INT
Col2 VARCHAR(32)
Col3 VARCHAR(4000)
Col4 DATETIME
I could insert around 1044 Inserts per second where as
I thought I could do max of 520 Inserts ( 130 * 4 ) because
each disk can only take 130 Inserts multiplied by 4 Disks
gives me a theoritical limit of 520.
Also How does the Query Analyser Connects to the datbase
Server.. does it use ODBC
Thx
Venu